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
v5:userguide:stored_procedure [2018/06/07 09:26]
dregad [Step 2: Setting Parameters] add wrap to highlight requirement to initialize variables; formatting and rewording
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 89: 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.txt · Last modified: 2019/01/22 01:11 by mnewnham