|
Chapter 23 |
To utilize existing legacy code, such as an RPG or Cobol programs, you’ll need to create an external stored procedure for each program to be called. Once a procedure exists, WOW can call the procedure, which in turn calls the program. Creating a procedure registers the program so that SQL can locate it, as well as defining properties needed to correctly call the program, such as the program name, the parameters required, the language the program was written in, etc.
23.1.1 Add Code to Return a Result Set
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.
23.1.2 Defining the Stored Procedure
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:
GRANT EXECUTE ON SPECIFIC PROCEDURE MYLIB.MYPROC TO WOW
The above example assumes the WOW user ID is WOW.
23.1.3 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 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.
23.2.1 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.
23.2.2 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
The above example assumes the WOW user ID is WOW.
23.2.3 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.
23.3.1 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*
D ROWCOUNT S 10I 0
C*
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*
C/EXEC SQL
C+ SET RESULT SETS ARRAY:CUSTLIST FOR:ROWCOUNT ROWS
C/END-EXEC
C*
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.
23.3.2 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.
23.3.3 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.