GeeCON Prague 2014*
The moose likes JDBC and the fly likes how to get data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to get data" Watch "how to get data" New topic
Author

how to get data

NIHAOMA CHENG
Greenhorn

Joined: Aug 16, 2001
Posts: 5
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
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
you have named your resultset rs...you name the other resultset rs2
Dan
NIHAOMA CHENG
Greenhorn

Joined: Aug 16, 2001
Posts: 5
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
Greenhorn

Joined: Aug 16, 2001
Posts: 2
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
Greenhorn

Joined: Aug 16, 2001
Posts: 5
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
Greenhorn

Joined: Aug 15, 2001
Posts: 29
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
Greenhorn

Joined: Aug 16, 2001
Posts: 5
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
Greenhorn

Joined: Aug 18, 2001
Posts: 5
You are looking for getMoreResults( ) method of the ResultSet Interface.
jenkinstechnology llc
Greenhorn

Joined: Aug 18, 2001
Posts: 7
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

paul
NIHAOMA CHENG
Greenhorn

Joined: Aug 16, 2001
Posts: 5
Thanks Paul and Gary!
Can't wait to try it on Monday :-)
 
GeeCON Prague 2014
 
subject: how to get data