ADOdb

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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 [[v5:reference:connection:preparesp|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.
<code php>
$procedure = $db->prepareSp('PROC_SAMPLE2');
if (!$procedure)
    die ('Invalid or inaccessible stored procedure name');
</code>
==== 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 [[v5:reference:connection:outparameter|outParameter()]] method. The parameter names must match those defined in the stored procedure.

<WRAP right round important>
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.
</WRAP>

=== Input Parameters ===


<code php> 
/*
 * 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');
</code>

=== Output Parameters ===

<code php>
$parameter3Name  = 'PARAMETER3';
$parameter3Value = 0;

$ok = $db->outParameter($procedure,$parameter3Name,$parameter3Value);
</code>

==== Executing The Stored Procedure ====
The stored procedure is executed by passing the handle of the procedure to the execute statement.
<code php>
$result = $db->execute($procedure);

print "parameter3 IS NOW $parameter3Value";
</code>
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**.

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