File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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 EJB 3 in Action this week in the EJB and other Java EE Technologies 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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: nested resultsets
 
Similar Threads
Newbie question... :(
Problem in running one resultset into another
Problem in running one resultset into another
intersolv JDBC driver for DB2 on OS/390
JSP and JDBC