This example shows the creation of a basic stored procedure and accessing it using ADOdb

This procedure uses the IBM DB2 SAMPLE database. It performs the following functions:

1. Accepts 2 input parameters, a 6 character keyword and 20 character name
2. Finds the highest existing account number in the table
3. Adds 10 to that number
4. Creates a new record, using the new account number and the input values
5. Makes the new account number available to the PHP procedure calling it

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

The procedure can be created by simply creating a PHP string using the above code, then executing it

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');

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.

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 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);

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.

If the stored procedure was one that returned a recordset, that can be accessed via the result of the execution, in the normal way.