Solved

multi ResultSet?

Posted on 2003-11-18
5
589 Views
Last Modified: 2010-04-01
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
Comment
Question by:efungkw
  • 2
  • 2
5 Comments
 
LVL 4

Assisted Solution

by:vk33
vk33 earned 150 total points
ID: 9777344
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
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 50 total points
ID: 9777350
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
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9777360
ooops Vk33........... I guess I am a victim of close timing......... :-)
0
 

Author Comment

by:efungkw
ID: 9777369
oh~~!!

I got it~ thanks!!!
0
 
LVL 4

Expert Comment

by:vk33
ID: 9777587
Hah, well done Kuldeepchaturvedi! ;)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

An article on effective troubleshooting
February 24, 2017 — On February 23, Travis Ormandy, a vulnerability researcher at Google, reported on Twitter (https://twitter.com/taviso/status/834900838837411840) that massive stores of data have been leaked by CloudFlare, a company that provide…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question