46. Utilizing Existing RPG Applications

Utilizing Existing RPG Applications

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.

Calling an RPG Program That Returns a Result Set

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.

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

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 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 in

the first section of the Builders Guide.