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