To have an RPG program return result sets, you’ll need to add code similar to the following in your RPG program:
C*******************************************************
C* Opens the cursor for Stored Proc *
C*******************************************************
*
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR SELECT * FROM MYLIB/FILE1
C/END-EXEC
C/EXEC SQL
C+ Open C1
C/END-EXEC
C/EXEC SQL
C+ set result sets cursor c1
C/END-EXEC
In the example above, all of the data from file FILE1 in MYLIB will be returned to the calling procedure. When SQL runs a select statement, the resulting rows comprise the result table. A cursor (C1) provides a way to access a result table. It is used within a program to maintain a position in the result table. SQL uses a cursor to work with the rows in the result table and to make them available to your program.
Next you’ll need to define an external stored procedure for the RPG program above:
CREATE PROCEDURE MYLIB.MYPROC ( )
DYNAMIC RESULT SETS 1
LANGUAGE RPGLE
SPECIFIC MYLIB/MYPROC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'MYLIB/RPGPGM'
PARAMETER STYLE GENERAL;
In the example above, there are no parameters, you’re returning one result set, the program language is RPGLE and the program name is RPGPGM in MYLIB. Run the SQL statement using one of the available SQL interfaces such as STRSQL, iSeries Navigator (Run SQL Scripts) or MySQL Query Browser.
AUTHORITY TIPS: Make sure the WOW user ID has proper authority to the program, as well as any files accessed by the program. You can grant authority to the program by running SQL similar to the following:In the example above, there are no parameters, you’re returning one result set, the program language is RPGLE and the program name is RPGPGM in MYLIB. Run the SQL statement using one of the available SQL interfaces such as STRSQL, iSeries Navigator (Run SQL Scripts) or MySQL Query Browser.
GRANT EXECUTE ON SPECIFIC PROCEDURE MYLIB.MYPROC TO WOW
The above example assumes the WOW user ID is WOW.
Defining an operation to call the external stored procedure is very similar to defining any other operation. The primary difference is what’s specified for the “Operation Code”. Instead of specifying an SELECT statement, you’ll specify the procedure call:
CALL MYLIB.MYPROC()
The above example has no parameters in the procedure call. For more details on using stored procedures, including the use of field descriptors, see the chapter entitled Stored Procedures.
After the RPG program is called, the table MYLIB/FILE1 is still open and having more than one user call the same operation could result in an “In Use” error for the other users. One possible solution is to have the original RPG program only populate the result table. Then define a second “wrapper” program to call the first program, copy the table to QTEMP and then return the results set from the QTEMP version of the table. Below is an example of a wrapper program written in RPG Free:
HDFTACTGRP(*NO)
DReportPgm Pr Extpgm('MYPGM')
*
D Run Pr ExtPgm('QCMDEXC')
D Cmd 200A CONST
D len 15P 5 CONST
*
/Free
//Create the file in QTEMP
Monitor;
run('DLTF FILE(QTEMP/FILE1)':200);
On-Error;
EndMon;
//Create empty file in QTEMP
run('CRTDUPOBJ OBJ(FILE1) FROMLIB(MYLIB) +
OBJTYPE(*FILE) TOLIB(QTEMP)':200);
run('OVRDBF FILE(FILE1) TOFILE(QTEMP/FILE1)':200);
run('CLRPFM emppfbk':200);
//Call the program that populates FILE1.
// The override ensure that the QTEMP file is populated.
ReportPgm();
run('DLTOVR *ALL':200);
//Open the cursor for Stored Proc
Exsr Resultset;
return;
/End-Free
C*******************************************************
C* Opens the cursor for Stored Proc *
C*******************************************************
C Resultset Begsr
*
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR SELECT * FROM QTEMP/EMPPFBK
C/END-EXEC
C/EXEC SQL
C+ Open C1
C/END-EXEC
C/EXEC SQL
C+ set result sets cursor c1
C/END-EXEC
C Resultset endsr