sanglebms
asked on
java.sql exception,rs.next() ..nested queries
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.
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.
oops:
while (rs1.next()) {
while (rs1.next()) {
ASKER
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??/
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??/
ASKER
nope that doesnt work!!!
are you closing rs2 after each loop?
ASKER
yes,I am..your solution doesnt work!
ASKER
yes,I am..your solution doesnt work!
ASKER
yes,I am..your solution doesnt work!
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
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
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
ASKER
you havent understood my question....there is a diff between "concurrent queries" and "nested queries"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Statement s1 = conn.createStatement();
rs1 = s1.executeQuery(query1);
while (rs.next()) {
rs2 = s1.executeQuery(query2);
while (rs2.next()) {
}
rs2.close();
}
rs1.close();