Calling CL Programs on the IBM i on Power from PHP using DB2 Stored Procedures

Overview

This document aims to guide you through the process of creating a CL program, a SQL stored procedure, and a PHP script that can send and receive information to and from the CL program by calling the stored procedure. Knowledge of how to write and compile CL, how to connect to the database, execute SQL and run PHP scripts is all assumed.

CL Program Structure

CL source should follow a basic structure that is proven to allow two way communications between the CL program and PHP. Other more advanced techniques in CL and use of other languages may be possible but that is outside the scope of this document. To match the SQL stored procedure parameters to the CL program parameters we need to know the order, size and type of the CL parameters. So if our entry looks like this:

PGM        PARM(&NAME &AGE)
DCL        VAR(&NAME) TYPE(*CHAR) LEN(50)
DCL        VAR(&AGE) TYPE(*INT)

We know that when creating the stored procedure we will need two parameters, the first is CHAR(50) and the second is INT the parameter names do not need to match. The CL I wrote for this guide sets the name and age parameters so I can retrieve these values in PHP. When moving the CL program into production it is important to know the library where the program exists as our stored procedure must be created with this information. In the production environments where my applications will be deployed we will create a stored procedure for each program that must be called from PHP. This will be part of a setup process that will handle the execution of SQL to ensure the creation of procedures and many other requirements of our application.

SQL Stored Procedure Structure

The SQL used to create a stored procedure should also follow a basic structure. Again advanced techniques may be employed but for the purposes of this document these are out of scope. When we created and compiled our CL we took note of the parameter order, type and size. We also noted the library where the program exists. Now we will execute the following SQL.

First we will ensure the procedure we’re creating does not already exist.

DROP PROCEDURE MYLIB/RTVPARM

Then we create the procedure as follows.

CREATE PROCEDURE MYLIB/RTVPARM
(INOUT PARAM1 CHAR(50), INOUT PARAM2 INT)
LANGUAGE CL NOT DETERMINISTIC NO SQL EXTERNAL NAME
MYLIB/RTVPARM PARAMETER STYLE GENERAL

PHP Script Structure

The PHP script follows the http://php.net documentation for connecting to DB2. Here is the PHP.

<?php
$db = 'localhost';
$u = null;
$p = null;
$o = array(
  'i5_libl' => 'MYLIB'
);
$c = db2_pconnect($db, $u, $p, $o);
$s = db2_prepare($c, 'CALL RTVPARM(?,?)');
db2_bind_param($s, 1, 'name', DB2_PARAM_INOUT);
db2_bind_param($s, 2, 'year', DB2_PARAM_INOUT);
$name = 'December';
$year = 2012;
var_dump($name, $year);
db2_execute($s);
var_dump($name, $year);
?>

The code above, if everything was done correctly, will produce the following result.

string(8) "December"
int(2012)
string(5) "THINK"
int(1911)

Conclusion

Hopefully this guide will help you realize simple communication between PHP scripts and CL programs.