wood burning stoves*
The moose likes JDBC and the fly likes nested resultsets Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "nested resultsets" Watch "nested resultsets" New topic
Author

nested resultsets

Rahul Agarwal
Ranch Hand

Joined: Sep 13, 2000
Posts: 52
I am having a streange problem.I am using jdbc odbc bridge and sql server as database.
what i am trying to do is as follows:
stmt=dbCon.createStatement();
rs=stmt.executeQuery(Select * from A.....);
while(rs.next())
{rs1=stmt.executeQuery(Select * from B.....);\\......int ff=stmt.executeUpdate("insert into another table....);
\\.....
while(rs1.next())
{ rs2=stmt.executeQuery(Select * from C.....);
\\......
\\.....
while(rs2.next())
{ rs3=stmt.executeQuery(Select * from D.....);
\\......
\\.....
}
}
}
The problem is that this nesting is not working properly and only executes once and not for whole loop.This was working fine on Ms Access but not on sql-server7 when I shifted my database to it.
Please help me out.....waiting...
[This message has been edited by Rahul Agarwal (edited September 14, 2000).]
[This message has been edited by Rahul Agarwal (edited September 14, 2000).]
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
Your problem is that a statement is only supposed to process one query at a time. Some JDBC drivers may let you get away with it, but in general it is much better to use a separate statement for each query, eg.

I can't help wondering why you need to do this, though. SQL is very powerful, and good at processing nested queries on its own.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Rahul Agarwal
Ranch Hand

Joined: Sep 13, 2000
Posts: 52
I am having a streange problem.I am using jdbc odbc bridge and sql server as database.
what i am trying to do is as follows:
stmt=dbCon.createStatement();
rs=stmt.executeQuery(Select * from A.....);
while(rs.next())
{rs1=stmt.executeQuery(Select * from B.....);\\......
\\.....
int ff=stmt.executeUpdate("insert into another table....);
while(rs1.next())
{ rs2=stmt.executeQuery(Select * from C.....);
\\......
\\.....
while(rs2.next())
{ rs3=stmt.executeQuery(Select * from D.....);
\\......
\\.....
}
}
}
u don't seem to understand the problem in the second round it is not inserting in the table and stating that there is an invalid cursor state
Please help me out.....waiting...
Prabhu Chandrasekaran
Ranch Hand

Joined: Oct 14, 2000
Posts: 36
Hi Rahul,
I met with the same problem when working in Servlets/Oracle-8i.
The problem was rectified when you use a seperate Statement variable for the nested resultset.
Reply after checking your code.
Bye.

------------------
Rajesh Hegde
Ranch Hand

Joined: Sep 15, 2000
Posts: 112
Hi rahul,
I faced similar problem. The solution is - close the resultset using rs.close() at the end of the loop. Let me know if that solves ur problem.
Bye
Butch C
Greenhorn

Joined: Oct 18, 2000
Posts: 7
You should use the separate stmt solution as mentioned above.
The problem the second time around is the ResultSet is hosed
because the stmt used to generate it was re-executed.
Per the JavaDoc...
A ResultSet is automatically closed by the Statement that
generated it when that Statement is closed, re-executed,
or used to retrieve the next result from a sequence of multiple
results.
 
wood burning stoves
 
subject: nested resultsets