aspose file tools*
The moose likes JDBC and the fly likes Problem in running one resultset into another Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem in running one resultset into another" Watch "Problem in running one resultset into another" New topic
Author

Problem in running one resultset into another

arun mahajan
Ranch Hand

Joined: Dec 07, 2001
Posts: 305
I was trying for the following code but it runs one time and gives error after tha:

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
Ranch Hand

Joined: May 10, 2001
Posts: 3244
Moving this to JDBC


Dave
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Dec 07, 2001
Posts: 305
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
Greenhorn

Joined: Jul 30, 2002
Posts: 8
Hi,
if(rs2.next())
{
//do something
}
after this statement u close rs2 resultset.
why don't u use subquery


madhu babu
arun mahajan
Ranch Hand

Joined: Dec 07, 2001
Posts: 305
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Dec 07, 2001
Posts: 305
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
Greenhorn

Joined: Apr 21, 2001
Posts: 22
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.


<a href="mailto:pondyprem@yahoo.com" rel="nofollow">pondyprem@yahoo.com</a> <br />Sr.Programmer Analyst ( Bioinformatics)<br />Monsanto Entrprises
Premkumar N
Greenhorn

Joined: Apr 21, 2001
Posts: 22
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem in running one resultset into another