ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:userguide:stored_procedure

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:

  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

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 those defined in the stored procedure.

Because both the parameter names and values are passed by reference, the variables used to hold them must be created and initialized before they are passed on to the methods.

Input Parameters

/*
 * 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

$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. This is an example using the SQL Server demo database Northwnd.

/*
* SQL Server connection assumed
*/
 
/*
* Because the procedure name has spaces in it, 
* we must surround the procedure name using [ ] . You can also use " "
*/
$procedure = $db->prepareSp('[PR_Alphabetical list of products_SelectAll]'); 
if (!$procedure)
    die ('Invalid or inaccessible stored procedure name');
 
/*
* Use the procedure name as the source of the data. 
* You can use any of the ADOdb commands that retrieve recordsets
*/
 
$result = $db->execute($procedure);
while($r = $result->fetchRow())
	print_r($r);
/*
* Prints
Array
(
    [productid] => 7
    [productname] => Uncle Bob's Organic Dried Pears
    [supplierid] => 3
    [categoryid] => 7
    [quantityperunit] => 12 - 1 lb pkgs.
    [unitprice] => 30.0000
    [unitsinstock] => 15
    [unitsonorder] => 0
    [reorderlevel] => 10
    [discontinued] => 0
    [categoryname] => Produce
)
etc
*/
v5/userguide/stored_procedure.txt · Last modified: 2019/01/22 01:11 by mnewnham