<?php
/**********************************************************************
* FILENAME : CallSPReturnCount.php
*
* DESCRIPTION :
* Illustrates how to obtain a return value from a stored
* procedure using PHP PDO and ODBC.
*
* The return value is a count of the number of records
* where the 'FirstName' field starts with a specific letter
*
* ODBC USAGE :
* Connect to the datasource
* Drops existing stored procedure if exists
* Create new stored procedure
* Prepares stored procedure
* Bind return as parameter 1, type SQL_INTEGER
* For each letter in the parameter list
* Binds as parameter 2, type SQL_VARCHAR
* Execute pCountNames stored procedure
* Output number of records
* Drop stored procedure used
* Close statement and database connection
*/
// Random array of letters
$params = array(
array("pName"=>'b%'),
array("pName"=>'c%'),
array("pName"=>'d%'),
array("pName"=>'e%'),
array("pName"=>'h%'),
array("pName"=>'i%'),
array("pName"=>'k%'),
array("pName"=>'m%'),
array("pName"=>'p%'),
array("pName"=>'r%'),
array("pName"=>'t%')
);
// Datasource name
$dsn ="odbc:DATASOURCE";
// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pCountNames
( @pName varchar (3) ) AS
DECLARE @recCount INT;
SELECT @recCount=count(*) FROM TestTBL1 WHERE FirstName LIKE @pName;
RETURN @recCount;";
// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
WHERE type='P' AND name='pCountNames')
DROP PROCEDURE pCountNames";
// Stored Procedure Call Statement
$sqlExecSP = "{? = CALL pCountNames(?)}";
try {
$recs=0;
// Connect to the datasource
$dbh = new PDO($dsn);
// Drop existing stored procedure if exists
$stmt = $dbh->prepare($sqlDropSP);
$stmt->execute();
// Create new stored procedure
$stmt = $dbh->prepare($sqlCreateSP);
$stmt->execute();
// Prepare stored procedure
$stmt = $dbh->prepare($sqlExecSP);
// Bind parameter 1 (RETURN Value)
$stmt->bindParam(1, $recs, PDO::PARAM_INT, 4);
// For each letter in the parameter list
foreach($params as $name) {
// Bind parameter 2 for next letter
$stmt->bindParam(2, $name['pName'], PDO::PARAM_STR);
// Execute pCountNames stored procedure
$stmt->execute();
// Output Results
printf ("\nCount of names starting with '%.1s' is %d", $name['pName'], $recs);
}
printf ("\n");
// Drop stored procedure used
$stmt = $dbh->prepare($sqlDropSP);
$stmt->execute();
// Close statement and database connection
$stmt = NULL;
$dbh = NULL;
} catch (PDOException $e) {
echo "Exception Occurred :" . $e->getMessage();
}
?>
Further information
