v5:userguide:stored_procedure
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
v5:userguide:stored_procedure [2016/01/13 02:31] – ↷ Links adapted because of a move operation mnewnham | v5:userguide:stored_procedure [2019/01/22 01:11] (current) – Updated recordset retrieval example mnewnham | ||
---|---|---|---|
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, | + | INSERT INTO act (ACTNO, |
+ | SELECT v_INT+10, | ||
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 [[v5: | + | The first step is to access the procedure using the [[v5: |
<code php> | <code php> | ||
$procedure = $db-> | $procedure = $db-> | ||
Line 38: | Line 39: | ||
die (' | die (' | ||
</ | </ | ||
- | ==== Step 2: Setting Parameters ==== | + | ==== Step 2: Setting Parameters ==== |
- | Next, the parameters must be set. Stored Procedure Parameters may be either Input, | + | |
+ | Next, the parameters must be set. Stored Procedure Parameters may be either | ||
+ | |||
+ | <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. | ||
+ | </ | ||
=== 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-> | $ok = $db-> | ||
</ | </ | ||
+ | |||
=== 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 | $parameter3Name | ||
Line 71: | Line 80: | ||
$ok = $db-> | $ok = $db-> | ||
</ | </ | ||
+ | |||
==== 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-> | ||
+ | if (!$procedure) | ||
+ | die (' | ||
+ | |||
+ | /* | ||
+ | * Use the procedure name as the source of the data. | ||
+ | * You can use any of the ADOdb commands that retrieve recordsets | ||
+ | */ | ||
+ | |||
+ | $result = $db-> | ||
+ | while($r = $result-> | ||
+ | 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.1452648707.txt.gz · Last modified: 2017/04/21 11:39 (external edit)