Regarding reultset in stored procedures

Is it possible for stored provedure to return a resultset
and that too if it do not have any out parameters
if we use callable statment then also we cannot get resultset
and if we use preparedStatement then
prepared statement is not used for stored procedures
I was asked this question in an interview but cud not give a
satisfactory reply
another question which wasd asked from me was
that is it true that it is difficult to debug a stored procedure
pls reply if anyone knows it

Originally posted by Gaurav Chikara:
Is it possible for stored provedure to return a resultset
and that too if it do not have any out parameters
if we use callable statment then also we cannot get resultset

it is possible to return a resultset from a callable statement in 2 ways:
1. as a return value from the callable statement

2. as an output parameter

another question which wasd asked from me was that is it true that it is difficult to debug a stored procedure

This depends. I have training as a Database Administrator as well as a programmer, so debugging stored procedures is as easy as debugging a java application. If you don't know how to read the stored procedure and you just call it, hoping for the best, it may be harder to debug.

Originally posted by Jamie Robertson:
String procedure = "{? = CALL someStoredProcedure(?, ?,...)}"; CallableStatement cstmt = con.prepareCall(procedure); ResultSet rs = cstmt.execute();
2. as an output parameter
String procedure = "{CALL someStoredProcedure(?, ?,...)}"; CallableStatement cstmt = con.prepareCall(procedure); cstmt.registerOutParameter(1, Types.OTHER); //this is resultset output parameter ... cstmt.execute(); //method to get resultset as out parameter depends on database

This depends. I have training as a Database Administrator as well as a programmer, so debugging stored procedures is as easy as debugging a java application. If you don't know how to read the stored procedure and you just call it, hoping for the best, it may be harder to debug.

Hi Jamie Robertson
since we know that execute method donot return anything
then how is it possbile that it will return a resultset as u mentioned
can u or anyone clrify it?

Originally posted by Jamie Robertson:
String procedure = "{? = CALL someStoredProcedure(?, ?,...)}"; CallableStatement cstmt = con.prepareCall(procedure); ResultSet rs = cstmt.execute();
2. as an output parameter
String procedure = "{CALL someStoredProcedure(?, ?,...)}"; CallableStatement cstmt = con.prepareCall(procedure); cstmt.registerOutParameter(1, Types.OTHER); //this is resultset output parameter ... cstmt.execute(); //method to get resultset as out parameter depends on database

This depends. I have training as a Database Administrator as well as a programmer, so debugging stored procedures is as easy as debugging a java application. If you don't know how to read the stored procedure and you just call it, hoping for the best, it may be harder to debug.

Hi Jamie Robertson
since we know that execute method donot return anything
then how is it possbile that it will return a resultset as u mentioned
can u or anyone clarify it?

OK, this needs to be modified a little(was in a hurry Friday!):
If there are no input values for the StoredProcedure, only a return value of cursor/resultset then there is an example in the sun tutorial
here.
I believe if you use this code (not tested) it should work to:

Jamie