Solved

multi ResultSet?

Posted on 2003-11-18
5
591 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
best way to search/remove a file from an EAR file 3 119
How to Post an If Statement in JSP 3 70
spring example errors 33 563
glassfish admin console 1 51
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The goal of this blog is to: > note what has impeded us from reaching effective life on-call > provide 3 steps to mastering life on-call > highlight what will be achieved with effective life on-call
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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