Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: Returning 2 resultsets from DB2 stored procedure

    Hi friends,

    I need to return 2 resultsets from a db2 stored procedure.

    have to return the count variable in a resultset A and have to return a "select" query in resultset B.

    Cursor c1 and cursor c2 is used in this case to return the resultsets.

    If i declare and open cursors, I could not return the 2 resultsets and only the query resultset is returned. Please help me out ASAP.
    Last edited by prem18; 06-24-08 at 16:08.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can return 2 result sets without a problem. So you may have some other issue, which definitively includes to tell us which DB2 version you are running on which platform, which error messages you got, which code you used and how you determined that you "could not return the 2 resultsets".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2007
    Posts
    63

    Post

    Thank u for imm response. please find it whether the below info is helpful:

    cursor 1 is used to return the count value and cursor 2 is used to return a select query...

    I declared the cursor 1 as

    DECLARE rs1 CURSOR WITH RETURN FOR
    SELECT Count(A.FIELDNAME) INTO sp_count
    FROM USER1.TABLE1 AS A WHERE
    <CONDITION1 > AND <CONDITION2> AND ...

    open rs1;


    DECLARE rs2 CURSOR WITH RETURN FOR
    SELECT <field names>
    FROM USER1.TABLE1 AS A WHERE
    <CONDITION1 > AND <CONDITION2> AND ...

    open rs2;

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The first statement doesn't make much sense. You have a SELECT ... INTO ... statement in a cursor declaration. I would have expected a SQL error being raised on that already.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2007
    Posts
    63
    Exactly im getting the same error.
    If i have to avoid INTO clause, then what is the alternative way to fetch the count variable ?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What do you want to do with the "count" variable? Do you need it in the 2nd query? If not, then you don't have to fetch it. But if you do need it, you can SELECT ... INTO ... it into a local variable, then execute the query again (or use a query that just returns the value of the local variable) to also have it returned via a result set.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Apr 2007
    Posts
    63
    Yes. I need to return the resultset as such ( WIth the statement
    "Open Resultset1;")

    I have given the following sequence of statements in Development center:

    Open Resultset1;----Stmt 1

    Fetch Resultset1 into sp_count; ( sp_count is declared as integer)


    Here I could get the value of sp_count as the exact count but the Stmt 1 is returning null values in the resultset instead of count value.

    Any help in this regard is greatly appreciated.

    Thanks

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Please show us the complete code and tell us which DB2 version you are using on which platform.

    Also, the FETCH gets the count - and consumes the row. You can't expect to also return the result set and still be able to access the already consumed row. Either you use a scrollable cursor and scroll back after the fetch, or you return the count separately, or you don't fetch it in the first place.

    p.s: I hope that stmt 1 and 2 don't execute the same statement where stmt 1 collects the count and stmt 2 the actual data. That is typically bad application design because DB2 has to perform twice the work.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Apr 2007
    Posts
    63
    Thanks for suggestion. I will get back with the code asap

Posting Permissions

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