aspose file tools*
The moose likes JDBC and the fly likes open Resultset and closed connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "open Resultset and closed connection" Watch "open Resultset and closed connection" New topic
Author

open Resultset and closed connection

Murali Mohan
Ranch Hand

Joined: Jul 07, 2003
Posts: 47
Hi,
I think when we do next() , it works on local object and does not go to database. But we can't iterate through Resultset after closing connection. why can't next() works after closing connection??
Regards,
Murali
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8964
    
    9

Originally posted by Murali Mohan Mohan Murali:
why can't next() works after closing connection??

Because your assumption is incorrect. ResultSet does not copy all the rows returned by a SQL query, it requests some number of rows from the database as they are needed (the get/setFetchSize() method returns/sets how many). Close the Connection object and ResultSet can no longer get rows from the database.


[How To Ask Questions On JavaRanch]
Murali Mohan
Ranch Hand

Joined: Jul 07, 2003
Posts: 47
/*
ResultSet does not copy all the rows returned by a SQL query, it requests some number of rows from the database as they are needed (the get/setFetchSize() method returns/sets how many). */
I want to do next() operation on returned rows only.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Originally posted by Joe Ess:
it requests some number of rows from the database as they are needed (the get/setFetchSize() method returns/sets how many).

Therefore make the fetch size large enough to hold them before closing the Connection.
Java Programmer
Greenhorn

Joined: Apr 20, 2004
Posts: 9
You must get ResultSet object before closing the Connection then close the Connection and do whatever you need to do with resultset you acquired:
ResultSet rs=statementEx.executeQuery(sql_statement);
connection.close()
while(rs.next()){
// your processing here
//
}

I think when we do next() , it works on local object and does not go to database. But we can't iterate through Resultset after closing connection. why can't next() works after closing connection??
Regards,
Murali[/QB]


Smiling on Java
Rudy Dakota
Ranch Hand

Joined: Jul 27, 2002
Posts: 54
Hi Murali,
That 's just the way JDBC works: the ResultSet depends on the Connection. If you close the Connection, the ResultSet is closed as well. We, simple souls uisng the stuff, can try and understand the reasons behind this, but that effort will not change the facts. That 's what the JDBC specification said. Period.
Good riding,
Rudy.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I had a look though the JDBC API Tutorial and Reference (link to the bunkhouse review) and it also pointed out the javax.sql.RowSet from the JDBC 2.0 extension. It should be available bundled with J2SE 1.5, but you can also look here to get it as a separate download.
An extension of the RowSet is the CachedRowSet, which allows you to wrap a ResultSet with a RowSet and makes the data available after the Connection is closed. It is also useful since it can decorate non-scrollable ResultSets with 'scrollability'. It's worth reading about the other implementations at the same time, it kept me up quite last night...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: open Resultset and closed connection