<?php
/**********************************************************************
* FILENAME :    CallSPInsertRecords.php
*
* DESCRIPTION :
*               Uses a stored procedure to insert records into a table.
*
* ODBC USAGE :
*               The stored procedure performs 3 actions:-
*                   a - Select all records
*                   b - Select specific record based on identity field
*                   c - Update specific record based on identity field
*               and has 7 parameters:-
*                   1 - action    - 0 Select all records,
*                                 - 1 Select specific record
*                                 - 2 Update record
*                   2 - record ID - unique record id for selecting or
*                                   updating a specific record
*                   3 - row count - returned when update performed to
*                                   indicate rows updated.
*                   4 to 7        - field values - new column values used by
*                                   an update.
*               PDO($dsn) - connect to data source.
*               prepare() and execute()  - drop, recreate and execute
*                                   stored procedure
*               bindParam() - to bind 6 input params and 1 output param
*               fetchAll() and nextRowset() - to retrieve rowsets
*               closeCursor() - to free up the connection between selects
*/

$dsn ="odbc:DATASOURCE";

// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pInsert_Rec
           (@pRecId INT OUTPUT,
            @pError INT OUTPUT,
            @FName varchar(255),
            @LName varchar(255),
            @Addr varchar (255),
            @City varchar (255) )
            AS
            INSERT INTO TestTBL1 (FirstName, LastName, Address, City)
                   VALUES (@FName, @LName, @Addr, @City);
            SELECT @pRecId=@@IDENTITY;
            SELECT @pError=@@Error";

// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
           WHERE type='P' AND name='pInsert_Rec')
           DROP PROCEDURE pInsert_Rec";

// Stored Procedure Call Statement
$sqlExecSP   = "{CALL pInsert_Rec(?, ?, ?, ?, ?, ?)}";

// Returned ID of new record
$pRecID=0;

// Returned error status
$pError=0;

// Number of records to insert
$numRecs=0;

// Fields values to insert
$FName="";
$LName="";
$Addr="";
$City="";

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

    // Prepare stored procedure
    $stmt = $dbh->prepare($sqlExecSP);

    // Bind parameter buffers
    $stmt->bindParam(1, $pRecID, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
    $stmt->bindParam(2, $pError, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
    $stmt->bindParam(3, $FName,  PDO::PARAM_STR);
    $stmt->bindParam(4, $LName,  PDO::PARAM_STR);
    $stmt->bindParam(5, $Addr,   PDO::PARAM_STR);
    $stmt->bindParam(6, $City,   PDO::PARAM_STR);

    // Get number of records to insert
    printf ("\nNumber of records to insert : "); $numRecs=intval(fgets(STDIN));
    printf ("\nEnter Details for %d  Records :", $numRecs);

    // Get record column values for each record
    for ($i=0;$i<$numRecs;$i++) {
        printf ("\nFirst Name : "); $FName=fgets(STDIN);
        printf ("Last Name  : "); $LName=fgets(STDIN);
        printf ("Address    : "); $Addr=fgets(STDIN);
        printf ("City       : "); $City=fgets(STDIN);

        $FName=preg_replace( "/\r|\n/", "",  $FName);
        $LName=preg_replace( "/\r|\n/", "",  $LName);
        $Addr=preg_replace( "/\r|\n/", "",  $Addr);
        $City=preg_replace( "/\r|\n/", "",  $City);

        // Insert new record using data entered by user
        printf ("\nInserting : %s, %s, %s, %s\n", $FName,$LName,$Addr,$City);
        $stmt->execute();

        printf ("\nStatus : %d", $pError);
        printf ("\nNew Record ID : %d\n", $pRecID);
    }

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


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