CODEWITHSUNDEEP
javadatabaseoracle-databaseoracle-sqldeveloper
sealz
sealz

Reputation: 5408

Oracle DB Query Runs in sqlDev but not in Java Program

I have been messing with Oracle DB queries that run from my JAVA app. I can successfully get them all to run in SQL Developer. But when I am trying to execute them from my JAVA app I usually get UpdatadbleResultSet Error/Exception on certain queries.

Also, sometimes I receive, ExhaustedResultset. As I mention at the bottom I will re work the question to break it down(When I get a chance). I keep editing and pretty soon it'll be a book.

Why is this? I cannot seem to pinpoint the problem.

Some queries run successfully such as:

SELECT table_name 
FROM all_tables

SELECT column_name, data_length 
FROM all_tab_columns 
WHERE table_name = 'mytable'

But when I try and run something like

SELECT length(<myColumnName>) 
FROM mytable

I get the updateableResultSetError

I am running my queries as methods called on button clicks (example below).

static void testQuery() {   
 String query = "SELECT blah from blah"
 String length;
 ResultSet rs = db.runQuery(query);
 Length = rs.getString("length(myCol)")
 System.out.println(length);
}

I have also tried while rs.next()

I can only think that for some reason I am unable to get into each table and I can only pull the "bigger" picture.

EDIT: Explained DB Connection

I am connecting using some other jarfiles that have been added to my project.

private static IDriver driver = null;
private static Database db = null;

I then pass in all my connection credentials in a separate method.

private void connectDB(){
driver = new OracleDriver();
db = new Database(driver)   
driver.getPassword;
driver.getetc;
driver.getEtc;
}

EDIT:

When I getstacktrace all I am returning is.

Ljava.lang.StatckTraceElement;(assortment of random characters).

I may not be getting stack traces right so someone can fill me in. After all I am offering a bounty.

Also I will edit this question and break it down again when I have the time.

Upvotes: 0

Views: 1945

Answers (3)

Matthew Farwell
Matthew Farwell

Reputation: 61695

Your problem is that you're trying to update a query that can't be updated, hence the updateable result error. It seems that whoever is creating your database connection or executing your query is creating an updatable result set.

You can't use certain types of select in an updatable result set: you can't use aggregated functions (such as length, min, max), you can't use select * etc.)

For the full list see Result Set Limitations and Downgrade Rules

Upvotes: 1

beny23
beny23

Reputation: 35008

I've got an inkling what may be happening here (which would explain why some queries work, and some don't). Accoring to the jdbc ResultSet javadocs, when using the getString() method of the result set, the column label.

the label for the column specified with the SQL AS clause. If the SQL AS clause was not specified, then the label is the name of the column

As "length(myCol)" is neither a label nor a column name, it may be that it fell over because of that (but without stacktrace it is difficult to say what your problem actually is).

Try 

String query = "SELECT length(myCol) AS myCol_len FROM myTable"
ResultSet rs = db.runQuery(query);
String length = rs.getString("myCol_len");

Though are you sure, you didn't want:

int length = rs.getInt("myCol_len");

Alternatively (as written by Kal), you can use the column index to get the data from the result set, which oblivates the need for a SQL AS label:

String query = "SELECT length(myCol) FROM myTable"
ResultSet rs = db.runQuery(query);
String length = rs.getString(1);

Upvotes: 1

Kal
Kal

Reputation: 24910

Try retrieving the value in your select statement via the columnIndex instead of the column name and see if that makes a difference.

Currently, its hard to tell what your db.runQuery() does since that code is not posted. 

String query = "SELECT length(myCol) FROM myTable";
String length;
ResultSet rs = db.runQuery(query);

while (rs.next()) {
 length = rs.getString(1);
 System.out.println(length);
}

Upvotes: 1

Related Questions