[Logo]
Forums Register Login
nested resultsets
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).]
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.
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...
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.

------------------
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
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.
Wink, wink, nudge, nudge, say no more ... https://richsoil.com/cards


This thread has been viewed 1040 times.

All times above are in ranch (not your local) time.
The current ranch time is
Feb 23, 2018 13:26:58.