SEO

#1 (**permalink**) 02-27-2008, 01:09 PM

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.

#2 (**permalink**) 02-27-2008, 01:09 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Parsing resultset from procedure to procedure to caller