Solved

multi ResultSet?

Posted on 2003-11-18
5
588 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
When we talk about DevOps toolchains, I sometimes wonder how many people really get what we’re talking about. I don’t know if it’s just semantics or tone or something else, but sometimes I think it just sounds like buzzword sausage. So it’s always …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now