<?php
/**********************************************************************
* FILENAME : CallSPOutputCount.php
*
* DESCRIPTION :
* Illustrates how to use an OUTPUT parameter in a stored
* procedure using PHP PDO and ODBC.
*
* A count of the number of records where the 'FirstName'
* field starts with a specific letter is retrieved via an
* OUTPUT parameter in a stored procedure.
*
* 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%')
);
$dsn ="odbc:DATASOURCE";
// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pOutputCount
( @pName varchar (3), @recCount INT OUTPUT ) AS
SELECT @recCount=count(*) FROM TestTBL1 WHERE FirstName LIKE @pName;";
// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
WHERE type='P' AND name='pOutputCount')
DROP PROCEDURE pCountNames";
// Stored Procedure Call Statement
$sqlExecSP = "{CALL pOutputCount(?, ?)}";
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);
// For each letter in the parameter list
foreach($params as $name) {
// Bind parameter 1 for next letter
$stmt->bindParam(1, $name['pName'], PDO::PARAM_STR);
// Bind parameter 2 to return the count. The length indicates
// it is an output paramater
$stmt->bindParam(2, $recs, PDO::PARAM_INT, 4);
// 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
