It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes SQLException: Exhausted Resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQLException: Exhausted Resultset " Watch "SQLException: Exhausted Resultset " New topic

SQLException: Exhausted Resultset

Kaush Kane
Ranch Hand

Joined: May 22, 2006
Posts: 37
Hi All,
In my operation I am having 11 threads that have their own Result Set object to process. When I access my these Result Set objects after some time I get SQLException: Exhausted Resultset from all the 11 threads.

Then I tried executing only a single thread. It functions properly and my operation was successful.

Searching through the Internet for this exception I found some articles that said that this exception can occur if you are not accessing your Result Set properly (i.e. not using or closing the result set etc). But as my single thread operation was successful I dont think any of these as cause of my issue.

I found one post on this forum where it was said that this error might occur if lots of Result Sets are open at the same time. This seems to be the possible cause of the issue I am facing. Apart from the 11 threads that their own Result Set object there would be some other operations as well going on on my system as this is a large application.
Also when I execute a single thread then my operation executes successfully, this strongs suggests me that lots of Result Sets being open at the same time to be the cause of issue I am facing.

Could you please let me know your ideas on this?
Also is there any way I can overcome this issue?
One way I read is by closing the Result Set object as soon as after being used. But that we are already doing in the application. Is there any other way to overcome this?

Please let me know.

Thanks and regards,
Kaustubh Kane
Shawn Stark

Joined: Jan 02, 2008
Posts: 7
Why do you want to open multiple result sets?
Are you doing multiple different queries?
Maybe load a data model with the data and just reference the model.
I haven't ventured in to the world of data modeling to far yet. I would
have to know exactly what you want to do and dive into the research.
You may want to create a data structure to hold objects assigned there data from the data base.
One way to hold data:
You can create an array in its own class of type data object you would like it to hold add a method to pass an object to it, assign passed object to array element[cnt] and increment count..

If you need it to be dynamic
try this
if cnt >= array1.size
new array2[array1.size+10]
array2 = array 1

if you need to use the name of old array recreate it
with new size and assign the data from array2 to it

If you think about it you can put limits on it and run
multiple instances.....

Hope these ideas help..
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33107

Are these ResultSets from the same Statement/Connection object or from different ones? If the former, there could be a contention problem.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

That was my thought too. I doubt ResultSets are thread safe, so using 11 threads is almost guaranteed to return incorrect results. Better to read the results in one thread and then process in 11 threads afterwards.
Shawn Stark

Joined: Jan 02, 2008
Posts: 7
There is a problem you might run into reading all records to a holding object of some sort. What if your database holds 10,000 records, that would consume a lot of memory. A resultSet dose not use all the memory that the records it referances would consume. It is only an interface that works with your data base.

Using well designed queries and page techniques would eliminate this issue. I would realy like to know what it is your working on. I get the idea you are building a multi-dimensional grid such as what might look like a rubics cube.. (6 tables each with 3 columns and 3 rows) if you place all columns in one table you would have to use a data structure to simplify the code to animate the cube.

OK maybe its not quite like that but maybe you are trying to distribute data to other incoming connections. If these incoming connections request somthing beond what has been preloaded in the data structure you will have to rereference the resultSet and pass current rec# as an absolute for cursor position. The use of your resultSet is very dependant on your query that created it. In this instance I would multi thread a data structure holding your data structures so each individual has there own data, this is a precaution to the chance thread one neads records on page 5 while thread 2 is still working on page 1. You will be able to request from resultSet specific to fill that users data model. Make a flow chart or somthing. I realy want to understand why you want to muti-thread it dose not sound like a small program to me.
I agree. Here's the link:
subject: SQLException: Exhausted Resultset
It's not a secret anymore!