How to call and debug a stored procedure from PHP, the free Community version, with PDO ODBC installed using Yum via Access Client Solutions (ACS) on the IBM i
Now that we have the opportunity to run the free Community edition of PHP on the IBM i in part courtesy of the Alan Seiden and his great team at Seiden Group and IBM we have a little setup to do in order to get stored procedures working. Stored procedures allow us to call RPG programs from PHP. This is useful if we want to retain business logic from existing RPG assets or access native objects like validation lists that would be difficult to process through PHP.
If you haven’t already loaded the client application for Access Client Solutions (ACS) go to the IBM ACS homepage and follow the downloads link and instructions at https://www.ibm.com/support/pages/ibm-i-access-client-solutions
Once you have loaded the ACS client fire it up and hit tools and run the Opensource package manager as pictured below.
Select the following packages to be installed ibm-iaccess, unixODBC and unixODBC-devel.
Now you can run stored procedures using PHP’s PDO (PHP Data Objects) to connect to the DB2 database on the IBM i. Let’s start off with a sample script that shows how to create a connection and a run a select query.
//---------------------------------------------------------------------------------------------// // All source code included in this document are provided on an "AS IS" basis without warranty // // of any kind. NOVAGEM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR // // INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES INCLUDING LOST PROFITS OR // // SAVINGS. // //---------------------------------------------------------------------------------------------//
<?php
// Set to display full error reporting for testing and debugging
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
// Setup ODBC connection to server
$constrng = 'odbc:DSN=*LOCAL;UID=USER;PWD=PASSWORD';
$dbh = new PDO($constrng);
// Prepare and execute query
$query = $dbh->prepare(
'select * from QIWS.QCUSTCDT');
$query->execute();
// Fetch returned rows and output as a table
echo "<table><tr><th>Customer</th><th>Name</th><th>Balance Due</th></tr>";
foreach ($query as $key=>$val) {
echo "<tr>";
echo "<td>".$val['CUSNUM']."</td>";
echo "<td>".$val['LSTNAM']."</td>";
echo "<td>".$val['BALDUE']."</td>";
echo "</tr>";
}
echo "</table>";
?>
In this second script we are going to run an RPG program as a stored procedure. First we need to define the procedure in the database schema. This is easily done through ACS. Go into “Schemas” from the main page of the client.
Then drill down into Schemas. If none are listed then right click on Schemas and create new one.
Next drill down into the schema and right click on Procedures in order to define a new one. Select New and External.
Set the following parameters for the procedure definition:
| Parameter | Description |
|---|---|
| Name | Name of the procedure (Usually program name) |
| Schema | Name of the schema that the stored proc will be associated. (Use drop down to select) |
| Specific name | Leave as System-generated |
| Language | Select the language that the stored proc is written in (RPGLE code for our test example) |
| Parameter style | Select SQL |
| Program schema | select from drop down (Will be library where RPG program lives) |
| Program | Select program from dropdown (Will be TSTSP for our program example) |
| Program type | For our example select Main (program) |
| Text | Add any optional text to describe the stored proc |
Next click on the parameter tab to define the parameters going to and from the stored proc and hit the add button to add the first parameter.
| Parameter | Description |
|---|---|
| Name | Name of parameter (Keep this same as the name in the RPG program) |
| Mode | IN, OUT or INOUT (IN for a parameter that is only being sent the the RPG program, OUT for a parameter that is only being returned from the RPG program and INOUT for a parameter that goes to and from the RPG program) |
| Data type | This should match the data type in the RPG program ie in our program varchar and char respectively. |
| Length | Only needed for parameters where they are returned from the RPG program but ACS will force you to put the maximum size in. |
| Encoding | Default |
You can also define your stored procedure using an SQL command. Here is the SQL for our stored procedure defined above:
CREATE PROCEDURE CGICHAPELB.TSTSP (
IN MESSAGETEXT VARCHAR(200) ,
IN MESSAGERECIPIENT CHAR(10) ,
OUT RETURNPARAMETER TIMESTAMP )
LANGUAGE RPGLE
SPECIFIC CGICHAPELB.TSTSP
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'CGICHAPELB/TSTSP'
PARAMETER STYLE SQL ;
GRANT ALTER , EXECUTE
ON SPECIFIC PROCEDURE CGICHAPELB.TSTSP
TO GILES WITH GRANT OPTION ;
GRANT EXECUTE
ON SPECIFIC PROCEDURE CGICHAPELB.TSTSP
TO PUBLIC ;
Here is the source of the ILE RPG program called as a stored procedure from PHP.
**free
// RPG test program to highlight running a stored procedure from PHP
ctl-opt dftactgrp(*NO) option(*NOSHOWCPY:*NODEBUGIO);
dcl-s qCmd char(6000);
dcl-pr QCMDEXC extpgm ;
*N char(6000) options(*varsize) const;
*N packed(15:5) const;
end-pr;
// Parameters for stored proc
dcl-pi TSTSP;
messageText varchar(200);
messageRecipient char(10);
returnParameter timestamp;
end-pi;
/copy qcpysrc,spdbg
// Setup command to send message to *SYSOPR (Note messageRecipient trimmed because not defined as varchar)
qCmd='SNDMSG MSG('''+messageText+''') TOUSR('+%trim(messageRecipient)+')';
QCMDEXC(qCmd:%len(%trimr(qCmd)));
// Populate timestamp return parameter
returnParameter=%timestamp();
// Close pgm
*INLR = *ON;
return;
Once our stored proc has been defined we can now call it from our PHP script. Note input parameters don’t need the PARM_INPUT definition on the end of the bind parameter. Varchar and char are defined as PARAM_STR and numerics as PARAM_INT. Other types are available as well.
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
// Setup ODBC connection to server
$constrng = 'odbc:DSN=*LOCAL;UID=USER;PWD=PASSWORD';
$dbh = new PDO($constrng);
$messageText="********** <<<<<<<<<< ---------- Test message from stored proc: TSTSP ---------- >>>>>>>>>> **********";
$messageRecipient="*SYSOPR";
$returnParameter='0001-01-01-00.00.00.000000';
// Prepare call to stored proc
$sql = 'CALL CGICHAPELB.TSTSP(?,?,?)';
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sth = $dbh->prepare($sql);
// Bind parameters and execute stored proc
$sth->bindParam(1, $messageText, PDO::PARAM_STR);
$sth->bindParam(2, $messageRecipient, PDO::PARAM_STR);
$sth->bindParam(3, $returnParameter, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);
$sth->execute();
// Output date / time returned from stored proc
$date=DateTime::createFromFormat('Y-m-d H:i:s.u', $returnParameter); // Convert timestamp from IBM i format into PHP date object
echo "Stored proc ran on " . $date->format('l, F jS, Y \a\t h:i:s a') . "<br>"; // Output date / time returned from stored proc
?>
You may have noticed the copy book called spdbg in the example RPG stored procedure program above. This is how I debug stored procs as they get run in QUSRWRK subsystem as QZDASOINIT jobs and are difficult to pin down for the STRSRVJOB command needed for the debug STRDBG command. I use a data area to signal the program to stop while writing the QZDASOINIT job number, name and user to the data area for the STRDBG command. I use 3 commands DBRES, DBGO and DBIGN to control debugging the stored procedure. This is how it works. Run the DBRES command which resumes debug mode. Then call your stored proc from PHP. When the program that contains the spdbg copybook reads the data area it writes the job details to the data area and waits fro the next command which is DBGO. This command prompts for a program name and then issues a STRSRVJOB and STRDBG command using the job details read from the data area and the program prompted for in the command. The DBIGN command stops the program from waiting on hold for the DBGO command. DBWA displays the QUSRWRK subsystem where the stored procs are run. Here is the source for the spdbg RPG copybook program and accompanying commands.
CRTDTAARA DTAARA(CGICHAPELB/RRDEBUG) TYPE(*CHAR) LEN(28)
**free
dcl-pr sleep int(10) extproc('sleep');
interval int(10) value;
end-pr;
dcl-ds *n PSDS;
jobName char(10) pos(244);
jobUsr char(10) pos(254);
jobNbr char(6) pos(264);
end-ds;
dcl-ds RRDEBUG dtaara len(28) ;
pgmName char(10) pos(1);
pgmUsr char(10) pos(11);
pgmNbr char(6) pos(21);
loopWhileBlk char(1) pos(27);
dontDbg char(1) pos(28);
end-ds ;
dow loopWhileBlk=*BLANK;
in *lock RRDEBUG;
if dontDbg='Y';
out RRDEBUG;
leave;
endif;
pgmName=jobName;
pgmUsr=jobUsr;
pgmNbr=jobNbr;
out RRDEBUG;
sleep(1);
enddo;
CMD PROMPT('Resume debugging')
/* Resume stored proc debugging */
PGM
CHGDTAARA DTAARA(RRDEBUG (27 2)) VALUE(' ')
ENDPGM
CMD PROMPT('Start debug')
/* Stored proc debug prep */
PGM
DCL VAR(&JOB) TYPE(*CHAR) LEN(10)
DCL VAR(&USR) TYPE(*CHAR) LEN(10)
DCL VAR(&NBR) TYPE(*CHAR) LEN(6)
ENDDBG
MONMSG MSGID(CPF0000)
ENDSRVJOB
MONMSG MSGID(CPF0000)
CHGDTAARA DTAARA(RRDEBUG (27 1)) VALUE(' ')
RTVDTAARA DTAARA(RRDEBUG (1 10)) RTNVAR(&JOB)
RTVDTAARA DTAARA(RRDEBUG (11 10)) RTNVAR(&USR)
RTVDTAARA DTAARA(RRDEBUG (21 6)) RTNVAR(&NBR)
STRSRVJOB JOB(&NBR/&USR/&JOB)
? SD
CHGDTAARA DTAARA(RRDEBUG (27 1)) VALUE('X')
ENDPGM
CMD PROMPT('Start debug')
/* Ignore stored prec debugging */
PGM
CHGDTAARA DTAARA(RRDEBUG (28 1)) VALUE('Y')
ENDPGM
CMD PROMPT('End debug')
/* End debug environmnet */
PGM
ENDDBG
MONMSG MSGID(CPF0000)
ENDSRVJOB
MONMSG MSGID(CPF0000)
CHGDTAARA DTAARA(RRDEBUG (27 1)) VALUE(' ')
ENDPGM
CMD PROMPT('End debug')
/* WRKACTJOB QUSRWRK */
PGM
CHGDTAARA DTAARA(RRDEBUG (27 1)) VALUE(' ')
WRKACTJOB SBS(QUSRWRK)
ENDPGM