RE: Return Multiple Record Formats with One Stored Procedure?

Yes you can return multiple datasets. I'm not sure how to do it the way you are doing it. However, I can show you a recent program I just wrote.

First I declared 3 cursors with the keywords of "WITH RETURN" in it.

Exec SQL
DECLARE R1 CURSOR WITH RETURN FOR
SELECT * FROM QTEMP/REGIONDESC;

Exec SQL
DECLARE A1 CURSOR WITH RETURN FOR
SELECT * FROM QTEMP/AREADESC;

Exec SQL
DECLARE U1 CURSOR WITH RETURN FOR
SELECT * FROM QTEMP/UNITDESC;

Each of these tables are built in qtemp. So after I populate the files.

I simply Open each cursor and return from the subprocedure. You must leave the cursors open on the return.

Exec SQL Open R1;
Exec SQL Open A1;
Exec SQL Open U1;
// This procedure returns all three result sets, if you don't
// specify the "SET RESULT SETS" instruction then the procedure
// will return all three in the order they were opened.
Return;

Then the creation of the SQL procedure was defined like so...
CREATE PROCEDURE BEFLIBT.GETALLUNITDESC (
IN COMPANY CHAR(3) ,
IN DIVISION DECIMAL(3, 0) ,
IN FISYR DECIMAL(4, 0) ,
IN FISPD DECIMAL(2, 0) )
DYNAMIC RESULT SETS 3
LANGUAGE RPGLE
SPECIFIC BEFLIBT.GETALLUNITDESC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'BEFLIBT/BEFG@RAUDN(GETALLUNITDESC)'
PARAMETER STYLE GENERAL ;

Your procedure cannot return more result sets than what you define on the "DYNAMIC RESULT SETS 3" instruction on the stored procedure.

Hope this helps.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Naughton
Sent: Tuesday, August 17, 2010 10:50 AM
To: RPG programming on the IBM i / System i
Subject: Return Multiple Record Formats with One Stored Procedure?

I've got a SQLRPGLE stored procedure that I call from a web application. It reads a single record and returns all the fields in a recordset to the web application. The DS in the RPG program that's used to return the record set is defined like this:

D OUTDS E DS Occurs(1) ExtName(MYFILE)

So far, so good. But now I would also like to be able to return a companion record from another file, and if possible I'd like to do it all from one stored procedure. At first, I thought I might be able to return multiple record sets, which would be
ideal, as it would give me some flexibility that would be useful down the road, but after doing some research and testing it looks like that's not possible. My next idea was to return one recordset that contained all the fields from both files, and
that's where I'm having trouble.

I've tried both:

D MYFILEDS E DS ExtName(MYFILE)
D MYFIL2DS E DS ExtName(MYFIL2)

D OUTDS E DS Occurs(1) Qualified
D MYFDS LikeDS(MYFILEDS)
D MYF2DS LikeDS(MYFIL2DS)

and

D OUTDS E DS Occurs(1) Qualified
D MYFDS LikeRec(MYREC)
D MYF2DS LikeRec(MYREC2)

but the compiler gives me SQL5011 errors with the message "Host structure array OUTDS not defined or not usable".

Is there any way to do this, or should I just be content with making two separate calls?

Thanks very much for your help!

PS: Related question: is it better to use a stored procedure, or just use a straight SQL call from the web application? In the first case, I know I want to use a stored procedure, as I want to do some testing before deciding if I want to return anything,
but once that's done sending the companing record is automatic.

Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.