Differences

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

--- v5:userguide:stored_procedure [2017/04/21 11:50] – external edit 127.0.0.1
+++ v5:userguide:stored_procedure [2019/01/22 01:11] (current) – Updated recordset retrieval example mnewnham
@@ Line 23: / Line 23: @@
   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;
+  INSERT INTO act (ACTNO,ACTKWD,ACTDESC)
+         SELECT v_INT+10,v_PARAM1,v_PARAM2 FROM SYSIBM.SYSDUMMY1;
   SET PARAMETER3=v_INT+10;
   END
@@ Line 38: / Line 39: @@
     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 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 ===
-Because both the parameter names and values are passed by reference, they must be initialized as strings before passing to the methods
 <code php>
 /*
@@ Line 63: / Line 71: @@
 $ok = $db->inParameter($procedure,'PARAMETER2','ACCOUNT NAME');
 </code>
 === 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>
 $parameter3Name  = 'PARAMETER3';
@@ Line 71: / Line 80: @@
 $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.
@@ Line 80: / Line 90: @@
 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.
+ 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>