- Calling an RPG Program That Returns a MODS (Array) in RPG Free

Calling an RPG Program That Returns a MODS (Array) in RPG Free

Add Code to Return an Array

The following code shows a simple RPG Free program that receives an integer and loops through

a customer master file (CSTMSTPF) for the number of times specified in the pRows parameter:

h dftactgrp(*no)

fCstMst1 if a e k disk prefix('CS.')

d cs e ds qualified extname(CSTMSTPF)

d CustRS pr

d pRows 10i 0

d CustRS pi

d pRows 10i 0

d CustList ds occurs(100)

d CSTMST 481a

d i s 10i 0

/free

i = 0;

setll *Loval CstMst1;

read CstMst1;

dow not %eof(CstMst1);

if i >= pRows; leave;

endif;i = i + 1;

%occur(CustList) = i;

CSTMST = cs;

read CstMst1;enddo;

exsr setResult;

*inlr = *On;

begsr setResult;

/end-free

C/EXEC SQL

C+ SET RESULT SETS ARRAY:CustList FOR:I ROWS

C/END-EXEC

/free

endsr;

/end-free

Any file could have been used here and any number of parms could have been passed in to

dictate the criteria for the end result set. The key is that each time a record is read, that

entire record is added to a multiple occurrence data structure (array CustList). Once the

looping has completed, an SQL result set is created based on the multiple occurrence data

structure and is sized based on the value in variable I, as shown in subroutine setResult.

Defining the Stored Procedure

Next you’ll need to define an external stored procedure for the RPG program above:

CREATE PROCEDURE MYLIB.MYPROC (IN LOOPCT INTEGER)

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 is one integer parameter, you’re returning 1 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:

GRANT EXECUTE ON SPECIFIC PROCEDURE MYLIB.MYPROC TO WOW

NOTE: The above example assumes the WOW user ID is WOW.

Defining the WOW Operation

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 a SELECT

statement, you’ll specify the procedure call:

CALL MYLIB.MYPROC(10)

The above example has one parameter in the procedure call. For more details on using stored procedures,

including the use of field descriptors, see the chapter entitled Stored Procedures in the first section of the

Builders Guide.