This is an old revision of the document!
Table of Contents
Stored Procedure Example
Description
This example shows the creation of a basic stored procedure and accessing it using ADOdb
The Procedure
This procedure uses the IBM DB2 SAMPLE database. It performs the following functions:
- Accepts 2 input parameters, a 6 character keyword and 20 character name
- Finds the highest existing account number in the table
- Adds 10 to that number
- Creates a new record, using the new account number and the input values
- Makes the new account number available to the PHP procedure calling it
Procedure Code
CREATE OR REPLACE PROCEDURE PROC_SAMPLE2(IN PARAMETER1 VARCHAR(6), IN PARAMETER2 VARCHAR(20), OUT PARAMETER3 INT) BEGIN DECLARE v_PARAM1 VARCHAR(6); DECLARE v_PARAM2 VARCHAR(20); DECLARE v_INT INTEGER; SET v_PARAM1=PARAMETER1; SET v_PARAM2=PARAMETER2; SELECT MAX(ACTNO) INTO v_INT FROM act; INSERT INTO act (ACTNO,ACTKWD,ACTDESC) SELECT v_INT+10,v_PARAM1,v_PARAM2 FROM SYSIBM.SYSDUMMY1; SET PARAMETER3=v_INT+10; END
Creation
The procedure can be created by simply creating a PHP string using the above code, then executing it using $db→execute($string)
.
Accessing The Procedure
Step 1: Preparation
The first step is to access the procedure using the prepareSp() method. This method access the procedure and returns a handle, that we will use later. If the procedure name provided is not valid, then the method returns false.
$procedure = $db->prepareSp('PROC_SAMPLE2'); if (!$procedure) die ('Invalid or inaccessible stored procedure name');
Step 2: Setting Parameters
Next, the parameters must be set. Stored Procedure Parameters may be either Input,Output or Input-Output parameters. ADOdb provides access to input-output parameters via the outParameter method. The parameter names must match the names of the input/output parameters defined in the stored procedure.
Input Parameters
Because both the parameter names and values are passed by reference, they must be initialized as strings before passing to the methods
/* * Correct */ $parameter1Name = 'PARAMETER1'; $parameter1value = 'TESTVAL'; $parameter2Name = 'PARAMETER2'; $parameter2value = 'ACCOUNT NAME'; $ok = $db->inParameter($procedure,$parameter1Name,$parameter1Value); $ok = $db->inParameter($procedure,$parameter2Name,$parameter2Value); /* * Incorrect */ $ok = $db->inParameter($procedure,'PARAMETER1','TESTVAL'); $ok = $db->inParameter($procedure,'PARAMETER2','ACCOUNT NAME');
Output Parameters
Output and Input-Output Parameters are passed by reference, the variables used to hold them must be created and initialized before the stored procedure is executed.
$parameter3Name = 'PARAMETER3'; $parameter3Value = 0; $ok = $db->outParameter($procedure,$parameter3Name,$parameter3Value);
Executing The Stored Procedure
The stored procedure is executed by passing the handle of the procedure to the execute statement.
$result = $db->execute($procedure); print "parameter3 IS NOW $parameter3Value";
The values of any output parameters are set by the execute statement, and may be accessed without further action.
Retrieving Recordsets
If the stored procedure was one that returned a recordset, that can be accessed via the result of the execution, in the normal way.