RE: RPG400-L Digest, Vol 8, Issue 306

Here is an example...

You use STRSQL and create this stored procedure
CREATE PROCEDURE LIBRARY/RPGAPP(
IN METHOD CHAR ( 10),
OUT RECORDCOUNT CHAR ( 7))
RESULT SETS 1
LANGUAGE RPGLE
NOT DETERMINISTIC
CONTAINS SQL
EXTERNAL NAME LIBRARY/RPGAPP
PARAMETER STYLE GENERAL

IN and OUT need to match your PLIST fields


In the RPG

A data structure with the data columns you want to return. Set the OCCURS to the maximum you ever think you could return at one time.

D ResultSet DS OCCURS(10000)
D RS_Lname LIKE(Lname)
D RS_Fname LIKE(Fname)
D RS_Mname LIKE(Mname)

A work field to count records

D Total S 5 0

... inside your read data loop...

C EVAL Total = Total + 1
C Total OCCUR ResultSet
C EVAL RS_Lname = Lname
C EVAL RS_Fname = Fname
C EVAL RS_Mname = Mname

... After your read loop is done..

C/EXEC SQL
C+ set result sets array :ResultSet for :Total rows
C/END-EXEC

I will always return the number of records in the result set as a parameter on the *ENTRY PLIST just in case the programmer on the other end need to know the number of records returned in the result set.

I have also found that there is a need to sort the result set which there are many options. One is how I use to deal with sorted subfile lists which was to load an array, sort the array, write the array into the data structure. You could also use embedded SQL instead of the native READ loop and tell the SELECT to sort for you or whatever you favorite way to sort a temporary list.

You are limited to a single result set when using this technique. Stored procedures allow for multiple result sets but you can't use the data structure technique to do that.

The RPG source needs to be SQLRPGLE since you have embedded SQL

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of DLee@xxxxxxxx
Sent: Wednesday, April 22, 2009 2:08 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: RPG400-L Digest, Vol 8, Issue 306

Mike

Thanks for your reply

It is the result set I want to send back, but I don't know how to handle
that.

I need to give you more information I see I sent the email before I
finished providing information. sorry.

Here is what I have in the sqlrpgle pgm so far.

What I want to do is receive a user selected last name (LNAM) and call
this program thru a stored procedure, and return all the records belonging
to insured with the last name of 'BARON' for example, along with some
other info like first name, and the policy number. In some cases this
could return hundreds of records, which probably needs to be limited
somehow.

I'm not clear what I have to do to return a result set. Below is a
general idea of what I have for sql. Also not sure what I have to do in
the stored procedure.

I have done one external procedure before to pass back parms which seems
to work fine.(pat on backLOL).

Any help is appreciated.


C *ENTRY PLIST
C PARM LNAM
C PARM FNAM
C PARM POLM

D INDTL E DS EXTNAME(NAMS11)

C EXSR SQLOPN

C DOW SQLSTT = *ZEROS
C EXSR SQLGET

C*----------------------------------------------------------------
C* DECLARE SQL CURSOR AND SETUP SELECT STATEMENT
C*----------------------------------------------------------------
C SQLOPN BEGSR
C/EXEC SQL
C+ DECLARE IN CURSOR FOR
C+ SELECT *
C+ FROM SPFILES/INSN001 ORDER BY INLNAM, INPOLM
C/END-EXEC
C/EXEC SQL
C+ OPEN IN
C/END-EXEC
C ENDSR

C*----------------------------------------------------------------
C* GET DETAIL RECORDS FROM FILE RENCDST FILE
C*----------------------------------------------------------------
C SQLGET BEGSR
C/EXEC SQL
C+ FETCH IN INTO :INDTL
C/END-EXEC



Darrell Lee
Information Technology
Extension 17127