Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Unanswered: Problem compiling stored procedure that calls another stored procedure

    We've been trying to have a stored procedure get results from another
    stored procedure but get an error when compiling the calling
    procedure:

    [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "ALLOCATE
    rsCur CURSOR FOR" was found following "RE targetProcedure; ".
    Expected tokens may include: "<psm_labellable_stmt>". LINE
    NUMBER=20. SQLSTATE=42601

    Here is an example using the SAMPLE database:

    CREATE PROCEDURE DB2INST1.targetProcedure ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE clientcur CURSOR WITH RETURN TO CALLER
    FOR SELECT salary, bonus
    FROM employee;

    OPEN clientcur;
    END P1

    CREATE PROCEDURE DB2INST1.callingProcedure ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE at_end integer default 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE var1 decimal;
    DECLARE var2 decimal;

    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    CALL targetProcedure();
    ASSOCIATE RESULT SET LOCATORS(result1)
    WITH PROCEDURE targetProcedure;
    ALLOCATE rsCur CURSOR FOR result1;
    END P1


    Thanks,

    Daniel

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    I checked our applications...we replicate the code...because stored procedures can't call another stored procedure.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Dec 2002
    Posts
    3

    Question But the Application Dev Guide says you can :-(

    Thanks for the reply. Your response confuses me though, since the application development guide spends some time discussing nested procedures. Here's the page where they're defined:
    http://www-3.ibm.com/software/data/d...htm#HDRPSMNEST

    Also the code I posted was based on examples given in the section called "Receiving Result Sets as a Caller" located here:
    http://www-3.ibm.com/software/data/d...e3.htm#db2a060

    Any thoughts on this disparity?

    Thanks again,

    Daniel

    Originally posted by Bruce A. Baasch
    I checked our applications...we replicate the code...because stored procedures can't call another stored procedure.

    Good Luck,

  4. #4
    Join Date
    Dec 2002
    Posts
    3

    Figured it out

    The examples in the Writing SQL Procedures section of the Application Development Guide have bad syntax for allocate cursor. They leave out the phrase "RESULT SET". The correct syntax is:

    ALLOCATE cursor-name CURSOR FOR RESULT SET rs-locator-variable

    -Daniel

  5. #5
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    My mistake...they do work...we're prohibited from using them in our installation.

    Evidently there were major problems in the calling procedures when the called procedures were changed...so nesting was outlawed.

    Sorry about that,
    Bruce Baasch

  6. #6
    Join Date
    Dec 2003
    Posts
    78

    same code with mistakes

    hello,
    i got the same errors with the sample code i pasted from Application Development pdf.
    I corrected the part with allocate as daniel suggested , and the error was corrected. however i still got errors building the code.
    Here is the main procedure to be called:
    **********EXAMPLE6()
    CREATE PROCEDURE DB2ADMIN.EXAMPLE6 ( )
    SPECIFIC DB2ADMIN.EXAMPLE6
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare cursor

    DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR
    SELECT
    DB2ADMIN.SALES.SALES_DATE AS SALES_DATE,
    DB2ADMIN.SALES.SALES_PERSON AS SALES_PERSON,
    DB2ADMIN.SALES.REGION AS REGION,
    DB2ADMIN.SALES.SALES AS SALES
    FROM
    DB2ADMIN.SALES;

    -- Cursor left open for client application
    OPEN cursor2;
    -- CALL EXAMPLE5();

    END P1
    **********end of example6********

    here is the caller stored procedure that calls example6() and intended to return example6's resultset:
    *****examplemvs**********
    CREATE PROCEDURE DB2ADMIN.EXAMPLEMVS ( )
    SPECIFIC DB2ADMIN.EXAMPLEMVS
    RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE result1 RESULT_SET_LOCATOR VARYING;

    CALL EXAMPLE6();
    ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE EXAMPLE6;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;

    -- ALLOCATE cursor-name CURSOR FOR RESULT SET rs-locator-variable

    WHILE (at_end = 0) DO
    SET total1 = total1 + var1;
    SET total2 = total2 + var2;
    FETCH FROM rsCur INTO var1, var2;
    END WHILE;
    END P1
    *******end of example6**************


    ******error message is stored procedure builder
    DB2ADMIN.EXAMPLEMVS - Drop stored procedure completed.

    DB2ADMIN.EXAMPLEMVS - Create stored procedure returns -206.

    DB2ADMIN.EXAMPLEMVS: 18: [IBM][CLI Driver][DB2/NT] SQL0206N "AT_END" is not valid in the context where it is used. LINE NUMBER=18. SQLSTATE=42703



    DB2ADMIN.EXAMPLEMVS - Changes rolled back.

    DB2ADMIN.EXAMPLEMVS - Build failed.


    *************************************


    I took the code from ibm's pdf, which cliamed that the code would return the resultset.
    Can someone help me??

    SHEDB

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

  8. #8
    Join Date
    Dec 2003
    Posts
    78

    i did it but..

    Thanks Damian,first of all.
    I examined your example.
    I also made some corrections to my code and managed to build it. However i still could not make it return me the resultset. I want to see the same resultset of the callee procedure.But i can only get the last row.Here is what i did:
    ************************
    CREATE PROCEDURE DB2ADMIN.EXAMPLEMVS (out var2 varchar(15),
    out var3 varchar(15),out var4 int)
    SPECIFIC DB2ADMIN.EXAMPLEMVS
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    CALL EXAMPLE6();
    ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE EXAMPLE6;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;


    WHILE (at_end = 0) DO
    FETCH FROM rsCur INTO var2,var3,var4;
    END WHILE;


    END P1

    **************************end of code****


    how can i see all the results?
    is there anyway to see the results in a resultset?

  9. #9
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    You'll have to declare a cursor referencing a temporary table, iterate through your results set and insert the values into the temporary table and then open your cursor!!!

    It's rubbish I know but that's what you gotta do!

  10. #10
    Join Date
    Dec 2003
    Posts
    78

    step by step :)

    hello
    thanks again Damian.
    I modifed my code as you suggested,below.
    i declare a temporary table, then insert the results in it.
    But after that, when i declare a cursor to select * from the temp table to get the results set, it gave the error under the code.
    I pasted it.
    Where should the cursor declaration be? Or what is wrong with the simple select???

    *******code******************

    CREATE PROCEDURE DB2ADMIN.EXAMPLEMVS (out var2 varchar(15),
    out var3 varchar(15),out var4 int)
    SPECIFIC DB2ADMIN.EXAMPLEMVS
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN


    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    DECLARE at_end SMALLINT DEFAULT 0;


    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;


    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPSALES
    (COLUMN2 VARCHAR(15),COLUMN3 VARCHAR(15),COLUMN4 int)
    WITH REPLACE
    ON COMMIT
    PRESERVE ROWS
    NOT LOGGED
    in BIG_TABLESPACE ;



    CALL EXAMPLE6();
    ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE EXAMPLE6;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;



    WHILE (at_end = 0) DO
    FETCH FROM rsCur INTO var2,var3,var4;
    INSERT into SESSION.TEMPSALES values(var2,var3,var4) ;

    END WHILE;

    DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR
    SELECT * FROM SESSION.TEMPSALES;

    open cursor2;



    END P1

    **************************

    ***********error********************

    DB2ADMIN.EXAMPLEMVS - Drop stored procedure completed.

    DB2ADMIN.EXAMPLEMVS - Create stored procedure returns -104.

    DB2ADMIN.EXAMPLEMVS: 44: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=44. SQLSTATE=42601



    DB2ADMIN.EXAMPLEMVS - Changes rolled back.

    DB2ADMIN.EXAMPLEMVS - Build failed.
    *************************************

  11. #11
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: step by step :)

    hello
    thanks again Damian.
    I modifed my code as you suggested,below.
    i declare a temporary table, then insert the results in it.
    But after that, when i declare a cursor to select * from the temp table to get the results set, it gave the error under the code.
    I pasted it.
    Where should the cursor declaration be? Or what is wrong with the simple select???
    The procedure won't compile because the temporary table doesn't yet exist. To get round this, you must first declare your temp table and then nest a BEGIN-END block in which you can reference the table.
    Code:
    BEGIN
      declare global temporary table...
      BEGIN
        doStuffWithTemporaryTable
      END
    END
    There is another scenario where you might want to reference a temporary table that is declared in the called procedure. To get round this, you would have to repeat the process, including the 'repeated' declaration of the temporary table. However, in this instance, you would only want to 'fool' the compiler and not to actually execute the declaration.
    Code:
    BEGIN
      if 1 = 0
      then
        declare global temporary table...
      end if;
      BEGIN
        doStuffWithTemporaryTable
      END
    END
    HTH

  12. #12
    Join Date
    Dec 2003
    Posts
    78

    Unhappy it did not work:(((

    hi,
    Thanks again Damian.
    It did not work. I put begin-ends as you suggested but it still gives syntax error.
    And strangely, it is not about temp table. I changed the select statement with an existing regular table, but it gave the same stupid error.(
    SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=47. SQLSTATE=42601


    (it is SELECT * FROM SALES; statement line.IT gives the same erro when i put the fields instead of *)


    here is the code(there is a table called sales in the database):

    CREATE PROCEDURE DB2ADMIN.EXAMPLEMVS (out var2 varchar(15),
    out var3 varchar(15),out var4 int)
    SPECIFIC DB2ADMIN.EXAMPLEMVS
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL

    P1: BEGIN
    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPSALES
    (COLUMN2 VARCHAR(15),COLUMN3 VARCHAR(15),COLUMN4 int)
    WITH REPLACE
    ON COMMIT
    PRESERVE ROWS
    NOT LOGGED
    in BIG_TABLESPACE ;

    CALL EXAMPLE6();
    ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE EXAMPLE6;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;

    BEGIN
    WHILE (at_end = 0) DO
    FETCH FROM rsCur INTO var2,var3,var4;
    INSERT into SESSION.TEMPSALES values(var2,var3,var4) ;
    END WHILE;
    DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR
    SELECT * FROM SALES;
    OPEN cursor2;
    END;
    END ;
    END P1

  13. #13
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: it did not work:(((

    I assume that cursor2 should be declared on session.tempsales?

    This should be declared up front (i.e. before your main body or any exit/continue handlers are declared). the order of precedence should be documented somewhere but I can't seem to find it!

    This should hopefully work though...

    Code:
    CREATE PROCEDURE DB2ADMIN.EXAMPLEMVS (out var2 varchar(15),
                                          out var3 varchar(15),out var4 int)
        SPECIFIC DB2ADMIN.EXAMPLEMVS
        DYNAMIC RESULT SETS 1
        LANGUAGE SQL
    
    P1: BEGIN     
      DECLARE at_end SMALLINT DEFAULT 0;     
      DECLARE not_found CONDITION FOR SQLSTATE '02000';      
      DECLARE CONTINUE HANDLER FOR not_found 
      SET at_end = 1;          
    
      DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPSALES
        (COLUMN2 VARCHAR(15),COLUMN3 VARCHAR(15),COLUMN4 INT)
          WITH REPLACE    
          ON COMMIT PRESERVE ROWS
          NOT LOGGED
          IN BIG_TABLESPACE ;      
      
      BEGIN
    
        DECLARE result1 RESULT_SET_LOCATOR VARYING;
    
        DECLARE cursor2 CURSOR WITH RETURN TO CALLER FOR 
                             SELECT * FROM SESSION.TEMPSALES; 
     
        CALL EXAMPLE6();
        
        ASSOCIATE RESULT SET LOCATOR(result1) WITH PROCEDURE EXAMPLE6;
    
        ALLOCATE rsCur CURSOR FOR RESULT SET result1;
        
        WHILE AT_END = 0
        DO                    
          FETCH FROM rsCur INTO  var2,var3,var4;
          
          INSERT into SESSION.TEMPSALES values(var2,var3,var4)   ;          
        END WHILE;              
    
        OPEN cursor2;
    
      END;  
    END P1

  14. #14
    Join Date
    Dec 2003
    Posts
    78

    Thumbs up it worked:)

    BIG BIG THANKS DAMIAN,
    the code really worked.
    I'm attaching it so that anyone who needs a stored procedure thar returns the result set of a called stored procedure can use it.

    THANKs AGAIN
    Attached Files Attached Files

Posting Permissions

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