<?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();
}
?>

See Also


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