Send me an email
  PHP on System I

Using PHP to test stored procedures
Move your mouse over the image seen above to display a larger image. Move your mouse away from the image to remove the larger image window.
Using PHP to test stored procedures on the System I
About 100 stored procedures were written for the purpose of either returning data to the calling application (Java or Oracle) or sending data to be updated from the external application to the System I database. Stored procedures on the System I can be called from any other platform, but it is difficult to test the overall functionality from the System I itself. As a developer I used iSeries Navigator to unit test, but that tool was installed on limited desktops, and also required an understanding of SQL. In order to help the quality assurance staff test each stored procedure that was written, as well as demonstrate each ones functionality, PHP was used in conjunction with HTML forms on the IBM i. The PHP and HTML forms were all stored on the IFS so that anyone connected to the system had easy access to run the procedures.
How to run an IBM i stored procedure from PHP
The form above is a standard HTML form. The CGI action program associated with the form is a PHP script that executes a stored procedure on the System I. The procedure is called just like any other stored procedure call using SQL. Examples from both the HTML form and the CGI PHP script can be seen below. This example returns output parameters and a results set, which is basically an array of data. The script will retrieve the contents of the array using a fetch statement, and display the results in a table (similar to a subfile).

This is where the action program is defined in the HTML form.
When the user clicks the submit button, program IS1042R416PROC.PHP is called.
PHP script IS1042R416PROC.PHP is the program that calls the SQL stored procedure that is stored on the IFS of the System I. This script does several things that allow the DB2 database to be accessed. First it must connect to the system using a user profile and password. Then define the stored procedure parameters and execute the SQL statement.

1 - Connect to the System I

2 - Define your input and output parameters (values returned are placed in variable $field_value_1, etc.)

3 - Execute the stored procedure (use question marks as parameter placeholders)

4 - Displaying a results set using fetch (contents of a file similar to a subfile display)

Creating the stored procedure on the System I
A stored procedure on the System I is not an object. You can view all the stored procedures on the sytem by running a query over file QSYS/SYSPROCS. To create the stored procedure you simply execute an SQL statement. Source is not necessary to create stored procedures, but I always do for two reasons. First, it provides documentation on the System I, and second it provides an easy way to recreate the stored procedure when necessary.


Tony Davis, November 2015,