Oracle DB Query Runs in sqlDev but not in Java Program

Question

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.

It doesn't look like an exception to me, it looks like a reference to an object of type UpdatableResultSet — Hunter McMillen
– Hunter McMillen, Commented Aug 15, 2011 at 17:19
You shouldn't capitalize your variable names in Java, it makes them look like classes. — dee-see
– dee-see, Commented Aug 15, 2011 at 17:22
BTW: you're calling rs.next() twice: once in the while statement, and once in the loop. Remove the second one. — JB Nizet
– JB Nizet, Commented Aug 15, 2011 at 17:24
Still troubleshooting, and I have found out that I never actually enter the loop. — sealz
– sealz, Commented Aug 15, 2011 at 18:14

Matthew Farwell · Accepted Answer · 2011-08-21 11:31:59Z

1

+50

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

answered Aug 21, 2011 at 11:31

Matthew Farwell

61.8k18 gold badges134 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sealz Over a year ago

hmm thats weird, as I said they work perfectly outside the java program. I will look into this.

Matthew Farwell Over a year ago

sqldeveloper won't try to create an updatable result set, so any select will work in sqldeveloper.

sealz Over a year ago

So is there anyway to bypass this when running a query within the program? Ill reward you the bounty if you know :)

Matthew Farwell Over a year ago

You can get round it by creating your own connection to the database and subsequently your own preparedstatement and resultset. Then you have complete control.

Kal · Accepted Answer · 2011-08-15 17:30:30Z

1

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);
}

answered Aug 15, 2011 at 17:30

Kal

24.9k8 gold badges67 silver badges66 bronze badges

2 Comments

sealz Over a year ago

I actually tried this earlier too I still receive the same UpdatableResultSet. My DB conn seems to be fine I can run other things. I will try and explain that in question

Kal Over a year ago

You should post the stacktrace of the entire exception that you say you are getting -- UpdatableResultSet Error/Exception

beny23 · Accepted Answer · 2011-08-19 12:54:03Z

1

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);

answered Aug 19, 2011 at 12:54

beny23

35.1k6 gold badges84 silver badges86 bronze badges

1 Comment

sealz Over a year ago

Thanks for the comment when I get a chance I will try it. I have tried what Kal mentioned and it throws the same error back at me.

Collectives™ on Stack Overflow

Oracle DB Query Runs in sqlDev but not in Java Program

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related