468,766 Members | 1,266 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,766 developers. It's quick & easy.

Nested Stored Procedure returning result sets in DB2 on AS/400

Here is the question
1. I have two stored procedures P1 and P2.
2. I want to call stored procedure P2 from P1.
3. Stored Procedure P2 returns a result set(as an open cursor).
4. In P1, I want to retrieve the result set returned by P2 into a
cursor, iterate through the cursor and insert records into a temporary
table created in P1.
5. Here are the steps I followed inside the stored proced P1:

/* Declare the variable */
DECLARE rSet RESULT_SET_LOCATOR VARYING;
/* Call the stored procedure */
CALL P2();

/* Establish a link between each result set and its locator using the
ASSOCIATE LOCATORS. */
ASSOCIATE RESULT SET LOCATORS (rSet) WITH PROCEDURE P2;

/* Associate a cursor with each result set. */
ALLOCATE C1 CURSOR FOR rSet;

.... and so on.......
6. When I try to run the stored procedure, I got the following error:
Message: [SQL0204] RESULT_SET_LOCATOR in *LIBL type *SQLUDT not found.
7. After some research I found that RESULT_SET_LOCATOR is not supported
on AS/400, in an IBM Red Book. I am not able to find any alternative
ways of doing this. This document is probably dated, but I am not able
to find the latest version of this document.
8. For me putting the P2 code in line in P1 code is not an option as I
already have too much code in P1.
9. We are currently on V5R3

I am literally stuck!. Please help!!!

Any help is deeply appreciated.

Thanks

Sahadev

Nov 12 '05 #1
0 2098

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Rhino | last post: by
reply views Thread by SOI_0152 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.