<?php
/***********************************************************************
* FILENAME : CallSPMoreResults.php
*
* DESCRIPTION :
* Simple PHP PDO ODBC example to illustrate how to generate
* multiple rowsets using SELECTs in a stored procedure
*
* ODBC USAGE :
* Connects to Data Source using Data Source Name
* Drops and recreates a procedure 'pSelect_Records'
* Executes the procedure using stmt->execute()
* Loop until no more rowsets
* Calls stmt->fetchall() to retrieve a rowset of all rows
* Displays rowset returned
* Calls stmt->nextRowset() to initiate next fetch
* Closes statement and database connection
*/
// Datasource name
$dsn ="odbc:DATASOURCE";
// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pSelect_Records AS
SELECT * FROM TestTBL1 ORDER BY FirstName;
SELECT * FROM TestTBL1 ORDER BY LastName;";
// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
WHERE type='P' AND name='pSelect_Records')
DROP PROCEDURE pSelect_Records";
# Stored Procedure Call Statement
$sqlExecSP="{call pSelect_Records ()}";
try {
// Connect to the data source
$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();
$rowset=0;
do {
$rowset++;
// Use fetchAll() to get results back from stored proc
$result = $stmt->fetchAll();
printf ("\n--\n");
foreach($result as $rst)
{
printf ("\n%d", $rst['PersonID']);
if ($rowset==1) {
printf ("\n%.20s", $rst['FirstName']);
printf ("\n%.20s", $rst['LastName']);
}
if ($rowset==2) {
printf ("\n%.20s", $rst['LastName']);
printf ("\n%.20s", $rst['FirstName']);
}
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 database connection
$stmt = NULL;
$dbh = NULL;
} catch (PDOException $e) {
echo "Exception Occurred :" . $e->getMessage();
}
?>
Further information
