<?php
/***********************************************************************
* FILENAME : CallSP.php
*
* DESCRIPTION :
* Simple ODBC (PDO ODBC) example to SELECT data from a table
* via a stored procedure
*
* Illustrates the most basic call, in the form :
*
* {CALL pSelect_Records ()}
*
* ODBC USAGE :
* Connects to Data Source using Data Source Name
* Drops and recreates a procedure 'pSelect_Records'
* Executes the procedure using stmt->execute()
* Calls stmt.fetchall() to retrieve a rowset of all rows
* For each record displays column values
* Closes statement and data base connection
*/
$dsn ="odbc:DATASOURCE";
// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pSelect_Records AS
SELECT PersonID, FirstName, LastName, Address, City
FROM TestTBL1 ORDER BY PersonID;";
// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pSelect_Records') \
DROP PROCEDURE pySelect_Records";
// Procedure Call Statement
$sqlExecSP="{call pSelect_Records ()}";
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();
// Prepare and execute the stored procedure. This is a simple call
// to a stored procedure which returns the results of a select statement
$stmt = $dbh->prepare($sqlExecSP);
$stmt->execute();
do {
// Use fetchAll() to get results back from stored proc
$result = $stmt->fetchAll();
printf ("\n--");
foreach($result as $rst)
{
printf ("\n%d", $rst['PersonID']);
printf ("\n%.20s", $rst['FirstName']);
printf ("\n%.20s", $rst['LastName']);
printf ("\n%.20s", $rst['Address']);
printf ("\n%.20s\n",$rst['City']);
}
} while ($stmt->nextRowset());
// Drop stored procedure used
$stmt = $dbh->prepare($sqlDropSP);
$stmt->execute();
// Close statement and data base connection
$stmt = NULL;
$dbh = NULL;
} catch (PDOException $e) {
echo "Exception Occurred :" . $e->getMessage();
}
?>
See Also
