Very Computer

[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

I guess it is the order of your declarations. I had a similar problem.
Look at:

http://groups.google.de/groups?hl=de&lr=&ie=UTF-8&threadm=3CEC90D5.40...

Replace

     ALLOCATE rsCur CURSOR FOR result1;

with

     ALLOCATE rsCur CURSOR FOR RESULT SET result1;

Did you use the ALLOCATE CURSOR example provided in the Stored
Procedures section of the Application Development Guide as the basis
for your code? That's what I did and got exactly the same error. It's
wrong - misses out the RESULT SET keywords. The Command Reference
Guide gives the correct syntax

Hope this helps you out
Steve

Once I've got my cursor open on the result set from the proc I've just
called, how do I then return that result set to the caller of my proc.
What I'd like to do would be something similar to the following -

   ...

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

   -- Cursor left open for client application
   OPEN rsCur;

END P1

Problem here is that use of the WITH RETURN is illegal in the ALLOCATE
command. Anyone know the correct way to have a proc return the result
set of a target proc that it has called?

Thanks
Steve