<?php
/**********************************************************************
* FILENAME : CallSPEditRecord.php
*
* DESCRIPTION :
* Uses a stored procedure to update a record within a table.
* The user is given a list of records to choose from
*
* 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
*
*/
// Action values for pEdit_Rec stored proc.
// Indicates to stored proc whether to select all records, select
// one record to edit or update current selected record.
define ('SELECTALL', 0);
define ('SELECTONE', 1);
define ('UPDATEREC', 2);
// Datasource name
$dsn ="odbc:DATASOURCE";
// Stored procedure create statement
$sqlCreateSP="CREATE PROCEDURE pEdit_Rec
(@action INT,
@pRecId INT,
@pRowCount INT OUTPUT,
@FName nvarchar(256),
@LName nvarchar(256),
@Addr nvarchar (256),
@City nvarchar (256) )
AS
IF (@action=0)
BEGIN
SELECT *
FROM TestTBL1 ORDER BY PersonID;
END;
ELSE
IF (@action=1)
BEGIN
SELECT *
FROM TestTBL1 WHERE PersonID=@pRecID;
END
ELSE
IF (@action=2)
BEGIN
UPDATE TestTBL1 SET
FirstName=@FName,
LastName=@LName,
Address=@Addr,
City=@City
WHERE PersonID=@pRecID;
SELECT @pRowCount=@@ROWCOUNT;
END";
// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
WHERE type='P' AND name='pEdit_Rec')
DROP PROCEDURE pEdit_Rec";
// Stored Procedure Call Statement
$sqlExecSP = "{CALL pEdit_Rec(?, ?, ?, ?, ?, ?, ?)}";
// Record ID selected or being updated
$pRecID=0;
// Records affected by the update - 1 or 0 expected
// If record exists and updated ok, 1 is returned.
// If record does not exist, 0 is returned.
$pRowCount=0;
// Action parameter.
$action=SELECTALL;
// Existing record column values
$currFName="";
$currLName="";
$currAddr="";
$currCity="";
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();
// Prepare stored procedure
$stmt = $dbh->prepare($sqlExecSP);
// Bind parameters
$stmt->bindParam(1, $action, PDO::PARAM_INT);
$stmt->bindParam(2, $pRecID, PDO::PARAM_INT);
$stmt->bindParam(3, $pRowCount, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
$stmt->bindParam(4, $currFName, PDO::PARAM_STR);
$stmt->bindParam(5, $currLName, PDO::PARAM_STR);
$stmt->bindParam(6, $currAddr, PDO::PARAM_STR);
$stmt->bindParam(7, $currCity, PDO::PARAM_STR);
do {
// Retrieve all records in table
$action=SELECTALL;
$stmt->execute();
do {
// Use fetchAll() to get results back from stored proc
$result = $stmt->fetchAll();
foreach($result as $rst) {
printf ("\n%4d, ", $rst['PersonID']);
printf ("%.10s, %.10s, %.10s, %.10s", rtrim($rst['FirstName']), rtrim($rst['LastName']), rtrim($rst['Address']), rtrim($rst['City']));
}
printf ("\n");
} while ($stmt->nextRowset());
// Get number of records to insert
printf ("\nEdit Record ? "); $pRecID=intval(fgets(STDIN));
if ($pRecID==0) goto end;
$action=SELECTONE;
$stmt->execute();
$pRowCount=0;
do {
// Use fetchAll() to get results back from stored proc.
// Only expecting one record since select is on identifier
// column.
$result = $stmt->fetchAll();
foreach($result as $rst) {
printf ("\n%4d, ", $rst['PersonID']);
printf ("%.10s, %.10s, %.10s, %.10s",
rtrim($rst['FirstName']),
rtrim($rst['LastName']),
rtrim($rst['Address']),
rtrim($rst['City']));
$currFName=rtrim($rst['FirstName']);
$currLName=rtrim($rst['LastName']);
$currAddr=rtrim($rst['Address']);
$currCity=rtrim($rst['City']);
$pRowCount++;
}
} while ($stmt->nextRowset());
// Check we have a record and of so prompt for replacement column
// values
if ($pRowCount==0) {
printf ("No Record %d ", $pRecID);
} else {
// Request column values and strip out unwanted
// newline/carriage return characters
printf ("\nEnter New Details for Record %d", $pRecID);
printf ("\nCurrent First Name : %s, Change To : ",$currFName);
$currFName=preg_replace( "/\r|\n/", "", fgets(STDIN));
printf ("Current Last Name : %s, Change To : ",$currLName);
$currLName=preg_replace( "/\r|\n/", "", fgets(STDIN));
printf ("Current Address : %s, Change To : ",$currAddr);
$currAddr=preg_replace( "/\r|\n/", "", fgets(STDIN));
printf ("Current City : %s, Change To : ",$currCity);
$currCity=preg_replace( "/\r|\n/", "", fgets(STDIN));
// Display contents of update fields
printf ("\nUpdating Record %d with : \n", $pRecID);
printf ("\nFirst Name : %s", $currFName);
printf ("\nLast Name : %s", $currLName);
printf ("\nAddress : %s", $currAddr);
printf ("\nCity : %s\n", $currCity);
// Since we are using the same buffers for reading and writing
// the record(s), we do not need to rebind the parameters for
// the update. Set action to UPDATEREC and update.
$action=UPDATEREC;
$stmt->execute();
// Show whether record was updated
printf ("\nRecord %d ", $pRecID);
if ($pRowCount==0)
printf ("NOT ");
printf ("Updated");
}
// Update another?
printf ("\nAgain (Y/N) ? "); $ans=strtoupper(fgets(STDIN));
if (strncmp($ans, "N", 1)==0) goto end;
// Close cursor and go round again
$stmt->closeCursor();
} while (1);
end:
// 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();
}
?>
Further information
