<?php
/**********************************************************************
* FILENAME : CallSPDeleteRecord.php
*
* DESCRIPTION :
* Example illustrates the use of a stored procedure in PHP
* PDO to list and then delete a record from a table.
*
* ODBC USAGE :
* Connects to Data Source using Data Source Name
* Drops and recreates a procedure 'pDelete_Rec'
* Prepares call to pDelete_Rec and binds 3 params:
* 1. (INPUT) - action 0 - SELECT, 1 - DELETE
* 2. (INPUT) - record to delete
* 3. (OUTPUT) - records deleted count
* Executes the procedure using stmt->execute() with
* action = SELECT, Calls stmt->fetchall() to retrieve
* a rowset of all rows and displays them
* Prompts for record to delete calls pDelete_Rec with
* action = DELETE and record id to delete.
* Retrieves record deleted count returned
* Repeats the above until finished
* Closes statement and data base connection
*/
// Actions for the stored prcedure
define ('SELECT', 0);
define ('DELETE', 1);
// Datasource name
$dsn ="odbc:DATASOURCE";
// Stored Procedure Create Statement
$sqlCreateSP="CREATE PROCEDURE pDelete_Rec
(@action INT,
@pRecID INT,
@pRowCount INT OUTPUT)
AS
if (@action=0)
BEGIN
SELECT PersonID, FirstName, LastName FROM TestTBL1 ORDER BY PersonID;
END;
ELSE
BEGIN
DELETE FROM TestTBL1 WHERE PersonID=@pRecID;
SELECT @pRowCount=@@ROWCOUNT;
END";
// Stored Procedure Drop Statement
$sqlDropSP="IF EXISTS (SELECT * FROM sys.objects
WHERE type='P' AND name='pDelete_Rec')
DROP PROCEDURE pDelete_Rec";
// Stored Procedure Call Statement
$sqlExecSP = "{CALL pDelete_Rec(?, ?, ?)}";
// Returned ID of record to delete
$pRecID=0;
// If action is 0 - list record ids
// If action is not 0 - delete record with id @pRecID
$action=0;
// No of rows effected by delete
$pRowCount=0;
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 1 and 2 as INPUT, 3 as output
$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);
do {
// Start by displaying all records
$action=SELECT;
$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", rtrim($rst['FirstName']), rtrim($rst['LastName']));
}
printf ("\n");
} while ($stmt->nextRowset());
// Get record to delete
printf ("\nDelete record numbered : "); $pRecID=intval(fgets(STDIN));
if ($pRecID==0) goto end;
printf ("Deleting record %d", $pRecID);
// Set action to delete
$action=DELETE;
$stmt->execute();
// Show whether record was deleted
printf ("\nRecord %d ", $pRecID);
if ($pRowCount==0)
printf ("NOT ");
printf ("Deleted");
// Delete 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();
}
?>
See Also
