Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

nested resultsets

 
Rahul Agarwal
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Rahul Agarwal
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic