* FILENAME :    CallSPFindID.php
*               Simple PDO ODBC example to use a stored procedure to find
*               and return a record.
*               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(

// Datasource name
$dsn ="odbc:DATASOURCE";

// Stored Procedure Create Statement
           ( @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);

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

    $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']);

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

    // Drop stored procedure used
    $stmt = $dbh->prepare($sqlDropSP);

    // 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.