?
Solved

java.sql exception,rs.next() ..nested queries

Posted on 2003-03-01
13
Medium Priority
?
1,970 Views
Last Modified: 2008-02-01
I have a problem with java.sql.I am getting an Invalid handle exception for nested result sets ...

example:-

//Conn is the common connection

Statement s1 = conn.createStatement();
rs1 = s1.executeQuery(query1);
while (rs.next){

Statement s2 = conn.createStatement();
rs2= s2.executeQuery(query2)
while (rs2.next){
}

}

The rs2 resultset works fine,but when it gets back into the outer while loop the exception is thrown.

I know that the problem is becuase the nested statements are usign the same connection.
I know of two ways to work around this
(1) use different connections
(2) remove nesting (save the result rs1,and then retrieve rs2)

But I wnated to know if anyone knows of any other way to work around this problem.

let me know.

Thanks.
0
Comment
Question by:sanglebms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 8048659
you can (or at least should be able to) use the same statement for both resultsets:

Statement s1 = conn.createStatement();

rs1 = s1.executeQuery(query1);
while (rs.next()) {
   rs2 = s1.executeQuery(query2);
   while (rs2.next()) {

   }
   rs2.close();
}
rs1.close();
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 8048663
oops:
while (rs1.next()) {
0
 

Author Comment

by:sanglebms
ID: 8048678
what if the two while statements are in diff fuctions

ex.
//global declares statement s1

rs1 = s1.executeQuery(query1);
while (rs.next()) {

   addchildren()
}

addChildren(){
rs2 = s1.executeQuery(query2);
  while (rs2.next()) {

  }
}

Are you sayign a common statements wills till work??/
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:sanglebms
ID: 8048719
nope that doesnt work!!!
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 8048739
are you closing rs2 after each loop?
0
 

Author Comment

by:sanglebms
ID: 8048772
yes,I am..your solution doesnt work!
0
 

Author Comment

by:sanglebms
ID: 8048800
yes,I am..your solution doesnt work!
0
 

Author Comment

by:sanglebms
ID: 8048841
yes,I am..your solution doesnt work!
0
 
LVL 6

Expert Comment

by:Ajar
ID: 8050691
Hi .. you should avoid using resultsets in nested loops
i.e
suppose following algo:
 Query1 = Select TABLE1.AGE from TABLE1
 resultset1 = execute Query1
 while resultset1.next :
 { //WHILE LOOP 1
   Query2 = Select * from TABLE2 where TABLE2.age <   
                                  resultset.getinteger(1)
   resultset2 = execute query2
   while resultset2.next
   {//WHILE LOOP 2
   
    do something with resultset
   }  
 }

 The above example causes the problem for the JDBC to
manage the old resultset1 and the new resultset2 simultaneously . remember while in loop 1 the resultset1 is still open.

If you have a situation where you want to execute a sequence of queries based on the results of the old query then follow following steps

make the first query
execute it
read and store its resultset in file or vector rowwise.
close the resultset

now make new queries based on old results  and execute the queries one at atime

Important is handle one query at a time on the JDBC


0
 

Expert Comment

by:doddjames
ID: 8081383

The spec says it should be OK, from what I can tell.  I am surprised this doesn't work.

http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame9.html

0
 

Author Comment

by:sanglebms
ID: 8082938
you havent understood my question....there is a diff between "concurrent queries" and "nested queries"
0
 

Accepted Solution

by:
doddjames earned 40 total points
ID: 8087195

The spec effectively says the Connection should be able to keep track of more than one Statement at a time.  Whether the Nth statement execution is nested, invoked from a different Thread (etc.) is largely irrelevant.

Out of interest, what database and driver are you using?

I'm using Oracle 9i with Oracle's thin type IV driver, and I can't recreate your problem with this hacked-together code:

    public void doStuff()
    throws Exception {
        Connection connection = getDatabase().getConnection();
        Statement s1 = connection.createStatement();
        ResultSet r1 = s1.executeQuery( "select * from bar" );
        while ( r1.next() ) {
            String txt1 = r1.getString( "foo" );
            System.out.println( txt1 );
            Statement s2 = connection.createStatement();
            ResultSet r2 = s2.executeQuery( "select * from bar" );
            while ( r2.next() ) {
                String txt2 = r2.getString( "foo" );
                System.out.println( txt2 );
            };
        };
    }

Also, you have not posted the actual code you are using, merely pseudocode.  There might be something fundamentally wrong with your code that we haven't seen.
0
 

Expert Comment

by:CleanupPing
ID: 9058987
sanglebms:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month12 days, 6 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question