* FILENAME :    CallSPMoreResults.php
*               Simple PHP PDO ODBC example to illustrate how to generate
*               multiple rowsets using SELECTs in a stored procedure
*               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 data base 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);

    // Create new stored procedure
    $stmt = $dbh->prepare($sqlCreateSP);

    // 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);

    do {

        // 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);

    // Close statement and data base connection
    $stmt = NULL;
    $dbh = NULL;

} catch (PDOException $e) {
   echo "Exception Occurred :" . $e->getMessage();

See Also

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.