By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

Parsing resultset from procedure to procedure to caller

P: n/a
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.
Dec 8 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Dec 8 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.