• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQLException: Exhausted Resultset

 
Kaush Kane
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 resultset.next() 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
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 34229
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kaush,
Are these ResultSets from the same Statement/Connection object or from different ones? If the former, there could be a contention problem.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic