retrieve multiple ResultSet

Hi:

I have a stored procedure in the database that returns 2 separate tables. How do I retrieve data from both tables using java? e.g. I am doing something like:

String query = "exec spMySP "+ myparameter;
ResultSet rs = s.executeQuery(query);

But the above code only gives me one table...
sdc248Asked:
Who is Participating?
 
StillUnAwareConnect With a Mentor Commented:
The code should be:

s.executeQuery(query);
ResultSet rs1 = s.getReusltSet();
//You must do the operations on rs1 here, cause after calling next method the ResultSet rs1 will be closed
//After processing the rs1 check whether there is another table pending and advance the pointer to it
if(s.getMoreResults()) {
  ResultSet rs2 = s.getResultSet();
  //here again, You should process the results from rs2, before calling any essential methods in the Statement s.
}
0
 
StillUnAwareCommented:
The Statement class has methods:

boolean getMoreResults();
boolean getMoreResults(int current);

these will inform You whether there is the next result set from a query.
You should use after You're done with the first result set.
0
 
sdc248Author Commented:
If I am sure there will be 2 result sets, can I just do the following?

s.executeQuery(query);
ResultSet rs1 = s.getReusltSet();
ResultSet rs2 = s.getResultSet();
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Nguyen Huu PhuocSenior ManagerCommented:
One store procedure return two tables? Really? Can you post your sp code here?
Phuoc
0
 
sdc248Author Commented:
Got you. Thanks.


For phuocnh:
Example of stored procedure returning multiple tables:

create proc spMySP
as
begin
select * from mytable1
select * from mytable2
end
0
 
Nguyen Huu PhuocSenior ManagerCommented:
Yeah, thank you for your code.
But I think this code is very complicated. Why don't you split it into two store procedures.
Phuoc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.