how to get data

[NIHAOMA CHENG]

I have two select statements in a store procedure, then I use JDBC to excute the store procedure. My question is how can I jump from one ResultSet to the other. For example,
ResultSet rs=statement.execute("excecute mystoreprocedure");
while(rs.next())
dosomething();
but I can only get result from the first select statement, how can I get result from the second. I am new to this, any help will be appreciated.

[Daniel Dunleavy]

you have named your resultset rs...you name the other resultset rs2
Dan

[NIHAOMA CHENG]

I think I did not state clear about my question. For example I create a procedure as:
create proc myprocedure
as
begin
select * from table A
select * from table B
end
commit
then in my java code:
ResultSet rs=statement.executeQuery("excecute myprocedure");
while(rs.next())
dosomething();
It seems that I can only get result from table A, my question is how can I get result from table B.
thanks

[Gopi Padmanabhan]

I want to clarify three things to you.
1)This statement that you have given cannot be true:
ResultSet rs=statement.executeQuery("excecute myprocedure");
The parameter for the method executeQuery cannot be a stored procedure. It can only be a SQL query String which will return
a result set.
2)To execute a stored procedure you will not use the above method
rather you should use CallableStatement Object and execute method.
3) Stored procedure are typically used to execute some business logic and they typically returns variables (Example : return Code). they are never used to get result sets.
If you have above situation, you can always get two different
result sets.
Hope my answer is clear and it helps. Thanks.

[NIHAOMA CHENG]

Thanks for reply. I use jconnect for Sybase. The Java code did work with no problem if I just use one select in store procedure. The reason I use two select in stor procedure is because the stuff need to do in database is little bit complicate and I don't want too many metwork back and forth. Anyway, just hope can find a way to deal with it, maybe there is no support for this in the JDBC driver.

[Himanshu Khanna]

Why don't U write a second procedure for the second select ?
Get the data from the 1st select & than get the data from the second select ..

[NIHAOMA CHENG]

two select are happen in one transaction, data related, I thought about seperate them at the beginning, but couldn't find a better way. That's different story. Sorry, I can't put the store procedure here. Thanks for your reply.

[Gary Labowitz]

You are looking for getMoreResults( ) method of the ResultSet Interface.

[jenkinstechnology llc]

3) Stored procedure are typically used to execute some business logic and they typically returns variables (Example : return Code). they are never used to get result sets.

sorry, this statement is just wrong. Stored procs are very often used to return result sets, they allow another interface level to be created between the databases and tables and the middle tier or client code utilizing them. when a data structure changes, you only change the stored proc, not the resultant data set columns.
as for the answer they were looking for, Gary had it close, it is the getMoreResults() method, but it is on the Statement interface, not the ResultSet...
here is a small snippet, sp's would work similar

     Statement stmt = cnx.createStatement();
     ResultSet rs = stmt.executeQuery( "select * from accounts; select * from users" );
     // do things with rs, like rs.first(), rs.next(), etc.
     stmt.getMoreResults();
     rs = stmt.getResultSet();
     // do things with rs again, like rs.first(), rs.next(), etc. but this time you are onto the second set of results...
 

paul

[NIHAOMA CHENG]

Thanks Paul and Gary!
Can't wait to try it on Monday :-)