473,405 Members | 2,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

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 2388

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
1
by: Todd Peterson | last post by:
I'm a newbie to DB2 and am trying to figure out how to write a stored procedure, using dynamic SQL statements to return a result set. I believe the majority of the hurdles I have been facing might...
2
by: Twan Kennis | last post by:
Question: How do I pass a returning resultset from a nested Stored Procedure (which opens a cursor including option "WITH RETURN TO CALLER") as a returning resultset from it's own? When I...
18
by: Rhino | last post by:
I am having a problem with a nested SQL Procedure on DB2 V8.2.1 on Windows. This simple-looking proc is giving me this error message when I try to build it in the Development Center: ...
6
by: Terentius Neo | last post by:
Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a select statement? I mean something like this: Select c.number, call procedure( c.number ) as list from table c With best...
1
by: Peter Rilling | last post by:
To minimize database calls, I would like to have a single stored procedure return all the data necessary to display on my page. This information is related from several different tables and what I...
0
by: balaji krishna | last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i...
0
by: adougall | last post by:
I would like to know what the outcome was on the topic for Nested Stored Procedure returning result sets in DB2 on AS/400 was. I am getting the same problem RESULT_SET_LOCATOR in *LIBL type *SQLUDT...
0
by: SOI_0152 | last post by:
Hi all! Happy New Year 2008. Il hope it will bring you love and happyness I'm new on this forum. I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c. Everything works...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.