• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can we extract the data from resultset after the connection get closed.

 
yogendra singh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can we fetch the data from the resultset after the connection get closed.
If our select quesry results in a very large number of rows. how does resultset accomodate it..where the whole data results get stored in the memeory..in our application memory where resultset occupied memory or at database memory ???
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Once the connection is closed you can no longer use any of the resources (statements, prepared statements, result sets). So you need to do all of your processing while the resources are open.
You should explicitly close the result sets, statements and connections when you are done, to make sure the resources are released on the database side.
 
yogendra singh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Wayne L Johnson:
Once the connection is closed you can no longer use any of the resources (statements, prepared statements, result sets). So you need to do all of your processing while the resources are open.
You should explicitly close the result sets, statements and connections when you are done, to make sure the resources are released on the database side.

okey once connection get closed , resultset is automaticaaly get closed.
but why is it so ?? why is resultset is on connection dependent.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65342
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Let's say that the result of your query results in half a million rows getting selected. Do you really want to wait until all that data is transferred to you regardless of whether you are going to use it or not?
The result set needs to maintain a connection to the DB so that it can fetch and buffer data in a reasonable manner.
 
RSA
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As far as I know the ResultSet is live i.e. with every .next() it goes to the database to fetch the next row (at least in Oracle). Oracle in turn holds the pointers to the result data in a temp table space. Please correct me if I am wrong.
>Ruchir
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ruchir Amin:
As far as I know the ResultSet is live i.e. with every .next() it goes to the database to fetch the next row (at least in Oracle). Oracle in turn holds the pointers to the result data in a temp table space. Please correct me if I am wrong.
>Ruchir

Probably something like this. This gives me something to research a bit. But a better guess would be that n number of rows are cached in memory for .next() and when more rows are needed, then the DB is hit again. The way you suggested just doesn't seem very efficient to me. But, I don't know for sure. I think I will see what I can find out.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another reason a ResultSet is tied to the connection is because you can create an Updatable ResultSet. So you can take the ResultSet, manipulate the data in it locally, then update the DB via the ResultSet. This, of course, would require a connection to the DB.
 
RSA
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is some explanation on the ResultSet working...it is specific to Mckoi JDBC driver.
>Ruchir
 
Lu Battist
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are some things you can do if you main goal is just to return the connection as soon as possible.
1) Gather all the results in a container for later use. (Vector, ArrayList)
Then you can close the resultset and the connection and still access the data. This approach doesn't work with very large results sets.
2) Limit the result set of the query to small group of rows.
Most databases have a set way of doing this. In db2 it is something like this:
"select * from mytable where key < ? and key > ? order by key fetch first 30 rows only"
You supply the lower and upper limits on the key to keep you're result set small. Process it and close the connection. When you need more data do another query.
 
yogendra singh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Gregg Bolinger:
[QB]
Probably something like this. This gives me something to research a bit. But a better guess would be that n number of rows are cached in memory for .next() and when more rows are needed, then the DB is hit again.

Your mean to say both our application's cache memory and database's temp memory used to store the data reulsted from a querey.
say if a select query results in 10000 rows. database temp memmory will store say first 3000 rows , and our application memory will store 100 rows. for first 100 rows resultset will fetch it brom cache on local and for next it will look upon database temp cache and so on..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic