Tips: Run and call System i stored procedures

Use these tips for running and calling System i™ stored procedures.

Running a System i stored procedure

ODBC provides a standard interface for calling stored procedures. The implementation of stored procedures differs significantly across various databases.
This simple example follows the recommended approach for running a System i stored procedure.

1.)Set up a create procedure statement for the stored procedure and create it. The creation of the stored procedure only needs to be done once and it does not have to be done through ODBC. The definition that it provides is available to all ODBC as well as integrated i5/OS® applications.
2.)Prepare the stored procedure call.
3.)Bind the parameters of the procedure, indicating whether each parameter is to be used for input to the procedure, output from the procedure, or input/output.
4.)Call the stored procedure.


Example:

CREATE PROCEDURE MYLIB.MYPROC (INOUT PARM1 CHAR(10)) EXTERNAL NAME MYLIB.MYPGM LANGUAGE COBOL GENERAL

In the above example, the procedure name is MYLIB.MYPROC.

This procedure references the COBOL program MYLIB.MYPGM. The program takes one input parameter called PARM1 which is a char field of length 10. This procedure does not return a result set.