Problem in running one resultset into another

[arun mahajan]

I was trying for the following code but it runs one time and gives error after tha:

Statement st1 = conn.createStatement();
Statement st2 = conn.createStatement();
ResultSet rs1 = st1.executeQuery("select client from clienttable");
ResultSet st2=null;
while(rs1.next())
{
String cl = rs1.getString("client");
rs2 = st2.executeQuery("select subclient from subtable where client='"+cl+"'");
if(rs2.next())
{
//do something
}
}

it runs for one time and than gives error:
java.sql.sqlException Object has been closed
I am trying it with JRUN 3.1 and java 1.3
When I tried debug I found that at the end of first run:
1. conn was available
2. rs1 and rs2 was available
3. st1 and st2 was available
To try for workaround when I created two connection separately it runs very well.
I tried finding the reason for this behaviour in API but failed. Am I missing something on API?
can somebody please explain this
[ July 29, 2002: Message edited by: arun mahajan ]

[Dave Vick]

Moving this to JDBC

[Jamie Robertson]

searching the API won't give you the answers for which you are searching. The API is a template which should be followed. Some do a better job than others. The best place to look is in your drivers documentation for features supported and limitations of those features. I imagine your driver has a limitation of one statement per connection ( not all that uncommon for "lower end" drivers ). If you want more statements per connection you'll have to find a driver that supports this feature ( it may be a different driver or just an updated version of your current driver ).
Jamie

[Jamie Robertson]

try ( DatabaseMetaData method ):
public int getMaxStatements()
throws SQLException
How many active statements can we have open at one time to this database?
Returns:
the maximum number of statements that can be open at one time; a result of zero means that there is no limit or the limit is not known
Throws:
SQLException - if a database access error occurs

to see how many active statements are allowable for each connection.

[arun mahajan]

Thanks Jamie for your reply...
I tired running a small program to find the MaxStatements supported and it is giving me zero(0) that menas unlimited... I am surprised and more curious to know why there is such a behaviour...
can some body please suggest any other check or explanation?
regards,
arun

[Madhu Bala]

Hi,
if(rs2.next())
{
//do something
}
after this statement u close rs2 resultset.
why don't u use subquery

[arun mahajan]

THANKS madhu_r777 for your reply..
Perhaps subquery will do the job...but I was more interested about the behaviour of this. I may sound peculiar but would be highly obliged if you could help me.

regards,
Arun

[Jamie Robertson]

Actually, upon closer inspection ( barring that it is a cut and paste error ) your problem is a simple one.
Statement st2 = conn.createStatement();
ResultSet rs1 = st1.executeQuery("select client from clienttable");
ResultSet st2=null;
You are using the same variable name as the statement(st2) and setting it to null. Don't know why you are not getting compile errors though.
Jamie
[ July 30, 2002: Message edited by: Jamie Robertson ]

[arun mahajan]

Thanks Jamie for keeping your interest alive for such a peculiar problem... but I verified and found that it was a typing error actually it is rs2 not st2 for ResultSet object...
Any other suggestion please..
regards,
Arun

[Premkumar N]

Arun :
I remember runnig into the same problem, and solving the same...

I hope I still remember the soln

Try the following:
bring the st2 initialization statement inside the loop it would work!
I mean the following line to inside the while loop, and just before calling st2.executeQuery.

The javadoc clearly states that one-instance-of-statement-can-be-used-only-once
=====================
"Only one ResultSet object per Statement object can be open at any point in time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All statement execute methods implicitly close a statment's current ResultSet object if an open one exists.
=====================
"
Statement st2 = conn.createStatement();

Please let me know if this was helpful and worked.

[Premkumar N]

What i ment is:
If the control goes say 3 times in while(rs1.next()) ...

Then you are trying to use the same st2 (object-instance) to return another result set .. which voilates the rules spefied in javadoc
Any questions let me know ..
HTH
Prem