• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

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

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ???
 
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 67753
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is some explanation on the ResultSet working...it is specific to Mckoi JDBC driver.
>Ruchir
 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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..

 
If somebody says you look familiar, tell them you are in porn. Or in these tiny ads:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic