Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2007
    Posts
    6

    Unanswered: How to return a cursor in DB2 stored procedure

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2007
    Posts
    6
    Thanks for that quick reply Marcus . I'll chk tht out

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Make sure there is no automatic commit during the execute() call; it would close the cursor.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Dec 2007
    Posts
    6
    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
    Last edited by av_domain; 12-18-07 at 10:37.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2007
    Posts
    6
    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

  8. #8
    Join Date
    Dec 2007
    Posts
    6
    Tnx Guys adding DYNAMIC RESULT SETS 1 solved the problem . Thanks again for all your replies

  9. #9
    Join Date
    Jan 2010
    Posts
    3

    Smile Thanks,

    asasasasasa
    Last edited by imamulu_tunduru; 01-15-10 at 00:13.

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Oh, two and a half year is enough to understand HOW !

    Good job !

  11. #11
    Join Date
    Jan 2010
    Posts
    3
    sdasasasasasa

  12. #12
    Join Date
    Jan 2010
    Posts
    3
    Quote Originally Posted by Lenny77 View Post
    Oh, two and a half year is enough to understand HOW !

    Good job !
    ''I'm Very new to DB2.. You..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •