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.
efungkwAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.