nested resultsets

[Rahul Agarwal]

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]

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.

Statement stmt1=dbCon.createStatement();
rs1=stmt1.executeQuery(Select * from A.....);
while(rs1.next())
{
  Statement stmt2=dbCon.createStatement();
  rs2=stmt2.executeQuery(Select * from B.....);/
  while(rs2.next())
  { 
    Statement stmt3=dbCon.createStatement();
    rs3=stmt3.executeQuery(Select * from C.....);
    while(rs3.next())
    { 
      Statement stmt4=dbCon.createStatement();
      rs4=stmt4.executeQuery(Select * from D.....);
      stmt4.close();
    }
    stmt3.close();
  }
  stmt2.close();
}
stmt1.close();

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.

[Rahul Agarwal]

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]

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]

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]

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.