Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    13

    Question Unanswered: No result in second call of stored procedure

    Hi,<BR><BR>

    I am using DB2 UDB V7.2.5 for Windows. I try to call a stored procedure "procB" twice in a stored procedure "procA". However, only the first call of the stored procedure "procB" can return the result. <BR>

    Please advise how can I get the result return from both call of "procB".<BR><BR>

    The following is the sample code for my case.<BR>
    =====================================<BR><BR>
    CREATE PROCEDURE procA()<BR>
    DYNAMIC RESULT SETS 1 <BR>
    LANGUAGE SQL <BR>
    BEGIN <BR>
    <BR>
    DECLARE SQLCODE INT;<BR>
    DECLARE l_sqlcode INT DEFAULT 0;<BR>
    DECLARE temp_var VARCHAR(30);<BR>
    <BR>
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND<BR>
    <BR>
    SET l_sqlcode = SQLCODE; <BR>
    <BR>
    set temp_var = 'AAAAAAAAAAAAAAAAAAA';<BR>
    call procB(temp_var);<BR>
    <BR>
    set temp_var = 'BBBBBBBBBBBBBBBBBBB'; <BR>
    call procB(temp_var);<BR>
    <BR>
    END!<BR>
    <BR>
    <BR>
    CREATE PROCEDURE procB(IN v_msg VARCHAR(30))<BR>
    DYNAMIC RESULT SETS 1 <BR>
    LANGUAGE SQL <BR>
    BEGIN <BR>
    DECLARE SQLCODE INT;<BR>
    DECLARE l_sqlcode INT DEFAULT 0;<BR>
    <BR>
    DECLARE DMSG varchar(30);<BR>
    <BR>
    DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR<BR>
    SELECT DMSG<BR>
    FROM table (VALUES 1) as temptable ;<BR>
    <BR>
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND<BR>
    SET l_sqlcode = SQLCODE;<BR>
    <BR>
    VALUES (v_msg) INTO DMSG;<BR>
    <BR>
    OPEN temp_cursor;<BR>
    <BR>
    END!<BR><BR>

    =====================================<BR>
    I suppose the result will be as follows.<BR><BR>

    D:\>db2 call procA()<BR>
    <BR>
    1<BR>
    AAAAAAAAAAAAAAAAAAA<BR>
    <BR>
    1<BR>
    BBBBBBBBBBBBBBBBBBB<BR>
    <BR>
    "PROCA" RETURN_STATUS: "0"<BR>
    =====================================<BR><BR>
    Thanks,<BR>
    Vincent<BR>

  2. #2
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39

    Re: No result in second call of stored procedure

    Hi,

    Try changing the

    CREATE PROCEDURE procA()<BR>
    DYNAMIC RESULT SETS 1

    to
    CREATE PROCEDURE procA()<BR>
    DYNAMIC RESULT SETS 2

    Thanks
    Sateesh

    Originally posted by winwinxp
    Hi,<BR><BR>

    I am using DB2 UDB V7.2.5 for Windows. I try to call a stored procedure "procB" twice in a stored procedure "procA". However, only the first call of the stored procedure "procB" can return the result. <BR>

    Please advise how can I get the result return from both call of "procB".<BR><BR>

    The following is the sample code for my case.<BR>
    =====================================<BR><BR>
    CREATE PROCEDURE procA()<BR>
    DYNAMIC RESULT SETS 1 <BR>
    LANGUAGE SQL <BR>
    BEGIN <BR>
    <BR>
    DECLARE SQLCODE INT;<BR>
    DECLARE l_sqlcode INT DEFAULT 0;<BR>
    DECLARE temp_var VARCHAR(30);<BR>
    <BR>
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND<BR>
    <BR>
    SET l_sqlcode = SQLCODE; <BR>
    <BR>
    set temp_var = 'AAAAAAAAAAAAAAAAAAA';<BR>
    call procB(temp_var);<BR>
    <BR>
    set temp_var = 'BBBBBBBBBBBBBBBBBBB'; <BR>
    call procB(temp_var);<BR>
    <BR>
    END!<BR>
    <BR>
    <BR>
    CREATE PROCEDURE procB(IN v_msg VARCHAR(30))<BR>
    DYNAMIC RESULT SETS 1 <BR>
    LANGUAGE SQL <BR>
    BEGIN <BR>
    DECLARE SQLCODE INT;<BR>
    DECLARE l_sqlcode INT DEFAULT 0;<BR>
    <BR>
    DECLARE DMSG varchar(30);<BR>
    <BR>
    DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR<BR>
    SELECT DMSG<BR>
    FROM table (VALUES 1) as temptable ;<BR>
    <BR>
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND<BR>
    SET l_sqlcode = SQLCODE;<BR>
    <BR>
    VALUES (v_msg) INTO DMSG;<BR>
    <BR>
    OPEN temp_cursor;<BR>
    <BR>
    END!<BR><BR>

    =====================================<BR>
    I suppose the result will be as follows.<BR><BR>

    D:\>db2 call procA()<BR>
    <BR>
    1<BR>
    AAAAAAAAAAAAAAAAAAA<BR>
    <BR>
    1<BR>
    BBBBBBBBBBBBBBBBBBB<BR>
    <BR>
    "PROCA" RETURN_STATUS: "0"<BR>
    =====================================<BR><BR>
    Thanks,<BR>
    Vincent<BR>
    OCP,IBM UDB,SYBASE DBA
    TCS, BANGALORE, INDIA

  3. #3
    Join Date
    Sep 2003
    Posts
    13

    Post Re: No result in second call of stored procedure

    Thanks Sateesh. I have tried your suggestion. It didn't work. However, I copied the "procB" to "procC" and call both in the "procA". The result return from "procA" is correct, both "procB" and "procC" can return the result successfully. But, my program must need to call the "procB" more then one time in "procA". Please help!


    Originally posted by msateeshkumar
    Hi,

    Try changing the

    CREATE PROCEDURE procA()<BR>
    DYNAMIC RESULT SETS 1

    to
    CREATE PROCEDURE procA()<BR>
    DYNAMIC RESULT SETS 2

    Thanks
    Sateesh

  4. #4
    Join Date
    Sep 2003
    Posts
    13

    Re: No result in second call of stored procedure

    Can anybody help me? Please!

  5. #5
    Join Date
    Aug 2003
    Posts
    106
    Try closing the cursor before second call of PROCB

    You are the creator of your own destiny!

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You might also want to change the WITH RETURN TO CLIENT to WITH RETURN TO CALLER.

    Andy

Posting Permissions

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