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

Add Code to Return an Array

The following code shows a simple RPG IV (RPGLE) program that has a char(2) state input parameter and returns records from file (QIWS/QCUSTCDT) that match the passed in state value:

FQCUSTCDT IF E K DISK

D* Multi-occurrence data structure for returning rows to calling procedure

D CUSTLIST DS OCCURS(100)

D DCUSNUM 6S 0

D DLSTNAM 8A

D DINIT 3A

D DSTREET 13A

D DCITY 6A

D DSTATE 2A

D DZIPCOD 5S 0

D DCDTLMT 4S 0

D DCHGCOD 1S 0

D DBALDUE 4S 0

D DCDTDUE 1S 0

D ROWCOUNT S 10I 0

C*--------------------------------------------------------------*

C* Inputs

C *ENTRY PLIST

C PARM PSTATE 2

C*--------------------------------------------------------------*

C MOVE '0' OFF 1

C MOVE '1' ON 1

C Z-ADD 0 ROWCOUNT

C READ QCUSTCDT 99

C** Read Loop

C *IN99 DOWEQ OFF

C* Only add records where the passed in state matches

C STATE IFEQ PSTATE

C ADD 1 ROWCOUNT

C** Write record to CUSTLIST at occurrence ROWCOUNT

C ROWCOUNT OCCUR CUSTLIST

C Z-ADD CUSNUM DCUSNUM

C MOVE LSTNAM DLSTNAM

C MOVE INIT DINIT

C MOVE STREET DSTREET

C MOVE CITY DCITY

C MOVE STATE DSTATE

C Z-ADD ZIPCOD DZIPCOD

C Z-ADD CDTLMT DCDTLMT

C Z-ADD CHGCOD DCHGCOD

C Z-ADD BALDUE DBALDUE

C Z-ADD CDTDUE DCDTDUE

C ENDIF

C** Read next record

C READ QCUSTCDT 99

C ROWCOUNT IFEQ 100

C MOVE ON *IN99

C ENDIF

C ENDDO

C/EXEC SQL

C+ SET RESULT SETS ARRAY:CUSTLIST FOR:ROWCOUNT ROWS

C/END-EXEC

C MOVE ON *INLR

Any file could have been used here and any number of parameters 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 ROWCOUNT, as shown in the SET RESULT SET statement.

Defining the Stored Procedure

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

CREATE PROCEDURE WOWRPG63.MYPROC (IN STATE CHAR(2))

LANGUAGE RPGLE
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'MYLIB/RPGPGM'
PARAMETER STYLE GENERAL;

In the example above, there is one char(2) parameter, you’re returning a 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 WOWRPG63.MYPROC TO WOW

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 (?100841)

The above example has one parameter in the procedure call. The parameter is a prompt parameter using field descriptor 100841. 100841 is assigned to the state field for file QIWS.QCUSTCDT and the number will vary for each system and connection.

The properties of the operation need to contain a StoredProcedure property group to designate which field descriptors are to be used for the input parameter and the results set:

StoredProcedure {

tables: qiws.qcustcdt;

}

For more details on using stored procedures, including the use of field descriptors, see the chapter entitled Stored Procedures.