Nils wrote:
> I would like to have the SPinner() to return the cursor (cc) to
> SPouter, which returns it to caller (client).
>
> My simple procedures is as follows:
>
> CREATE PROCEDURE SPinner ()
> LANGUAGE SQL
> result sets 1
> BEGIN
> declare cc cursor with return TO CALLER for
> select mytable.col1, mytable.col2 from mytable;
> open cc;
> END
>
> CREATE PROCEDURE SPouter ()
> LANGUAGE SQL
> result sets 1
> BEGIN
> call SPinner();
> END
>
> ... but this doesn't get the cc-cursor into SPouter(). I've then been
> looking at "allocate cursor" in conjunction with "associate" like
> this:
>
> CREATE PROCEDURE SPouter ()
> LANGUAGE SQL
> result sets 1
> BEGIN
> DECLARE loc RESULT_SET_LOCATOR VARYING;
> call SPinner();
> ASSOCIATE RESULT SET LOCATOR(loc) WITH PROCEDURE SPinner;
> ALLOCATE C1 CURSOR FOR RESULT SET loc;
> open C1;
> END
>
> ... but it returns SQLSTATE: 51030 upon runtime.
You have to consume the cursor in the caller.
In your case I think using a temporary table to park the result set
would be the best option.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab