<?php
/**********************************************************************
* FILENAME : CallSPFindID.php
*
* DESCRIPTION :
* Simple PDO ODBC example to use a stored procedure to find
* and return a record.
*
* ODBC USAGE :
* Creates an array of record IDs
* Connects to Data Source using Data Source Name
* Drops and recreates a procedure 'pFind_ByID' using 'execute'.
* The procedure takes just one parameter, the record ID.
* Prepares Stored Procedure for execution
* For each record ID
* Calls 'bindParam' to bind the record ID to parameter 1
* of the Stored Procedure
* Calls 'execute' to excecute the stored procedure
* Enters loop calling 'fetchAll' and 'nextRowset' to retrieve
* all records generated by the execute
* Displays record details
* Drops Stored Procedure using 'execute'
* Closes the statement and disconnects from the Data Source
*/
// Array of parameters
$params = array(
array("pId"=>6),
array("pId"=>4),
array("pId"=>8),
array("pId"=>23),
array("pId"=>9)
);
// Datasource name
$dsn ="odbc:DATASOURCE";
// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pFind_ByID
( @pPersonID int ) AS
SELECT * FROM TestTBL1 WHERE PersonID=@pPersonID;";
// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
WHERE type='P' AND name='pFind_ByID')
DROP PROCEDURE pFind_ByID";
// Stored Procedure Call Statement
$sqlExecSP = "{CALL pFind_ByID(?)}";
try {
// 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();
$stmt = $dbh->prepare($sqlExecSP);
// Prepare stored procedure
foreach($params as $id) {
// One input parameter, a record ID
$stmt->bindParam(1, $id['pId'], PDO::PARAM_INT);
printf ("\nRecord ID %d", $id['pId']);
$stmt->execute();
do {
// Fetch and display rowset (if any)
$result = $stmt->fetchAll(PDO::FETCH_NUM);
foreach($result as $rst)
{
printf ("\n%d", $rst[0]);
printf ("\n%.20s", $rst[1]);
printf ("\n%.20s", $rst[2]);
printf ("\n%.20s", $rst[3]);
printf ("\n%.20s\n",$rst[4]);
}
} while ($stmt->nextRowset());
$stmt->closeCursor();
}
// 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
