[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

retrieve multiple ResultSet

Posted on 2006-06-01
6
Medium Priority
?
652 Views
Last Modified: 2008-02-01
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...
0
Comment
Question by:sdc248
  • 2
  • 2
  • 2
6 Comments
 
LVL 14

Expert Comment

by:StillUnAware
ID: 16810112
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
 

Author Comment

by:sdc248
ID: 16810351
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
 
LVL 6

Expert Comment

by:phuocnh
ID: 16812484
One store procedure return two tables? Really? Can you post your sp code here?
Phuoc
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Accepted Solution

by:
StillUnAware earned 500 total points
ID: 16813864
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
 

Author Comment

by:sdc248
ID: 16821798
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
 
LVL 6

Expert Comment

by:phuocnh
ID: 16821908
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 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