Can we use the Statement object for multiple ResultSet objects?

[rahul kumar]

I have a single Statement object for whole of my application i want to know that when i got a ResultSet object after executeQuery() mehtod and then again used the Statement object for another executeQuery() mehtod that again returned a ResultSet object , would the first ResultSet object become unusable or not.

[Jamie Robertson]

The way that a driver behaves is dependant on the drivers implementation of the Statement Interface. But in general it usually has 1 of the following 2 behaviours:
The code:

Statement stmt = con.createStatement();
ResultSet r1 = stmt.executeQuery("SELECT....");
while (r1.next())
{
String id = r1.getString(1);
 
//this is where the difference occurs
ResultSet r2 = stmt.executeQuery("SELECT...where id = '" + id + "'");
if (r2.next())
{
//some code here
}
//this is where the error occurs 
String name = rs.getString(2);
}

Driver behaviour #1 (eg. odbc:jdbc bridge)
The maximum number of resultsets to a statement is 1. Thus, when resultset 'r2' is created, the statement closes the previously associated resultset(r1) before it proceeds to execute the query for r2. This is why an exception is thrown when you try to access r1 again after it has been closed.
Driver Behaviour #2 (eg. Oracle drivers)
The maximum number of resultsets to a statement object is undefined. I've used up to 7 resultsets on one statement object. So, the code posted above would work. When you create resultset r2, r1 remains untouched. No exceptions are thrown.
you have to find out in your documentation or by trial and error which type your's is
Jamie
ps. the same can be said for Connections to Statements.

[rahul kumar]

thanks Jamie for ur help i will surely try it .well i am using jdbc dbc bridge for MS SQL server 7.0 .i will check does it work for type 1 according to u or not.