Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

multi ResultSet?

Hi all,

Can I do the multi ResultSet?

for example.

Statement state = myConn.createStatement();
// the first resultset
ResultSet testRS = state.executeQuery("select * from testing");

if (testRS.next()){

//out.println("anything here");

do{

//the second result set with the testRS id
ResultSet ndRS = state.executeQuery("select * from nd where nd_id=" + testRS.test_id);

if (ndRS.next()){

do{

//out.println("Result here!");

}

}

}while (testRS.next());

}


after I added this code, it shows:

Operation not allowed after ResultSet closed

Why it said ResultSet is closed?
How can I settle it?

Thanks,
Eric.
0
efungkw
Asked:
efungkw
  • 2
  • 2
2 Solutions
 
vk33Commented:
Hi!

You can get only one ResultSet with one Statement without closing the ResultSet. For example this will not work:

Statement st = conn.createStatement();
ResultSet rs1 = st.executeQuery("SELECT * FROM table1");
ResultSet rs2 = st.executeQuery("SELECT * FROM table2");

You'll get an exception when trying to execute the 3rd line. To accomplish this task you have to do this:

Statement st = conn.createStatement();
ResultSet rs1 = st.executeQuery("SELECT * FROM table1");
Statement st2 = conn.createStatement();
ResultSet rs2 = st2.executeQuery("SELECT * FROM table2");

... or this:

Statement st = conn.createStatement();
ResultSet rs1 = st.executeQuery("SELECT * FROM table1");
rs1.close();
ResultSet rs2 = st.executeQuery("SELECT * FROM table2");

But as soon as you close the ResultSet you cannot access its "next" method anymore. And remember that statement and resultset are both automatically closed as soon as the object reference is lost.

For your task I would suggest something like:

Statement state = myConn.createStatement();
ResultSet testRS = state.executeQuery("select * from testing");
if (testRS.next()){
do{
   //the second result set with the testRS id
   Statement state2 = myConn.createStatement();
   ResultSet ndRS = state2.executeQuery("select * from nd where nd_id=" + testRS.test_id);
}while (testRS.next());
}

If you have any questions - feel free to ask!
Regards!
0
 
KuldeepchaturvediCommented:
Your looping is not going correctly that is why you are having this problem.... Resultsets are interfaces and are attached to the statement on which they are retrived... Here is what is happening with your code...

Statement state = myConn.createStatement(); >>>>>>>>>>>> Here you have got one statment on a connection to the database.......
// the first resultset
ResultSet testRS = state.executeQuery("select * from testing");
>>>>>>>>>>>> Now this statement will return a resultset to you which can be worked up on..........
if (testRS.next()){>>>>>>>>>>>>>>>> this line is fine.....

//out.println("anything here");

do{

//the second result set with the testRS id
>>>>>>> Now as soon as you reach this statment your previous resultset will be closed........
ResultSet ndRS = state.executeQuery("select * from nd where nd_id=" + testRS.test_id);
if (ndRS.next()){
do{

//out.println("Result here!");

}

}

}while (testRS.next());>>>>>>>>>>>>> this statement will say that your result is closed and you can not do operation on it..

}

So in essence following is the way out...

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects.

Hope this helps
0
 
KuldeepchaturvediCommented:
ooops Vk33........... I guess I am a victim of close timing......... :-)
0
 
efungkwAuthor Commented:
oh~~!!

I got it~ thanks!!!
0
 
vk33Commented:
Hah, well done Kuldeepchaturvedi! ;)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now