ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:userguide:stored_procedure

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
v5:userguide:stored_procedure [2015/10/08 03:05]
mnewnham [Creation]
v5:userguide:stored_procedure [2019/01/22 01:11] (current)
mnewnham Updated recordset retrieval example
Line 23: Line 23:
   SET v_PARAM2=PARAMETER2;​   SET v_PARAM2=PARAMETER2;​
   SELECT MAX(ACTNO) INTO v_INT FROM act;   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;​+  INSERT INTO act (ACTNO,​ACTKWD,​ACTDESC) ​ 
 +         SELECT v_INT+10,​v_PARAM1,​v_PARAM2 FROM SYSIBM.SYSDUMMY1;​
   SET PARAMETER3=v_INT+10;​   SET PARAMETER3=v_INT+10;​
   END   END
Line 32: Line 33:
 ===== Accessing The Procedure ===== ===== Accessing The Procedure =====
 ==== Step 1: Preparation ==== ==== Step 1: Preparation ====
-The first step is to access the procedure using the [[reference:​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.+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> <code php>
 $procedure = $db->​prepareSp('​PROC_SAMPLE2'​);​ $procedure = $db->​prepareSp('​PROC_SAMPLE2'​);​
Line 38: Line 39:
     die ('​Invalid or inaccessible stored procedure name'​);​     die ('​Invalid or inaccessible stored procedure name'​);​
 </​code>​ </​code>​
-==== Step 2: Setting Parameters ====     +==== 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.+ 
 +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 === === Input Parameters ===
-Because both the parameter names and values are passed by reference, they must be initialized as strings before passing to the methods ​+ 
 <code php> ​ <code php> ​
 /* /*
Line 63: Line 71:
 $ok = $db->​inParameter($procedure,'​PARAMETER2','​ACCOUNT NAME'​);​ $ok = $db->​inParameter($procedure,'​PARAMETER2','​ACCOUNT NAME'​);​
 </​code>​ </​code>​
 +
 === Output Parameters === === 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.+
 <code php> <code php>
 $parameter3Name ​ = '​PARAMETER3';​ $parameter3Name ​ = '​PARAMETER3';​
Line 71: Line 80:
 $ok = $db->​outParameter($procedure,​$parameter3Name,​$parameter3Value);​ $ok = $db->​outParameter($procedure,​$parameter3Name,​$parameter3Value);​
 </​code>​ </​code>​
 +
 ==== Executing The Stored Procedure ==== ==== Executing The Stored Procedure ====
 The stored procedure is executed by passing the handle of the procedure to the execute statement. The stored procedure is executed by passing the handle of the procedure to the execute statement.
Line 80: Line 90:
 The values of any output parameters are set by the execute statement, and may be accessed without further action. The values of any output parameters are set by the execute statement, and may be accessed without further action.
 ==== Retrieving Recordsets ==== ==== 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.+ 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>​
  
v5/userguide/stored_procedure.1444266355.txt.gz · Last modified: 2017/04/21 11:39 (external edit)