Hi all ,

I want to return the cursor in db2 to retrieve the resultset from
my java application .Keeping the cursor open dosen't help and the resultset always returns a null though the procedure runs fine from the command line. Any ideas .

Regards,
Av~

CallableStatement stmt3 = HibernateApp.getHibernateSession().connection().pr epareCall("{call answers_select_id( ? )}");
stmt3.setString(1,20);
stmt3.execute();
ResultSet rs1 = stmt3.getResultSet();
System.out.println("rs1 " + rs1); --> returns null

I am not a Java programmer, but I would check out this manual and see if it helps You:
ftp://ftp.software.ibm.com/ps/produc...S/db2a1e81.pdf

Thanks for that quick reply Marcus . I'll chk tht out

Make sure there is no automatic commit during the execute() call; it would close the cursor.

can u please tell me how do I do that ? I am new to db2 . I tried Connection.setAutoCommit(false) - there was no use

The procedure is given below .

CREATE PROCEDURE answers_select_id (IN question_id bigint )
P1:BEGIN
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT a.answer_id from answers as a where a.question_id = question_id;
open cursor1;
END P1;

Everything works so smooth in mysql but DB2 returns a null resultset always

Add the WITH HOLD clause to the cursor declaration inside the procedure or set the auto-commit property for your Java connection object to off.

I tried

CREATE PROCEDURE answers_select_id (IN question_id bigint )
P1:BEGIN
DECLARE cursor1 CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT a.answer,a.answer_id from answers as a where a.question_id = question_id;
open cursor1;
END P1;

I tried that but sill a null resultset ... Everything is fine from the command line

Tnx Guys adding DYNAMIC RESULT SETS 1 solved the problem . Thanks again for all your replies

asasasasasa

Oh, two and a half year is enough to understand HOW !

Good job !

sdasasasasasa

''I'm Very new to DB2.. You..."