I have a PHP page which executes and inserts 3 different queries in a SQL Server database.
What is the best way to perform error handling with this?
Basically, I only want to execute them in series, so if the first one fails then DO NOT continue and basically put an error back to the client.
I have currently stored them in variables like
$sql_1 = "insert into ..."
and
$sql_2 = "insert into ..."
Then I was using if statements which were doing the calling of the variable but it just seems very messy.
I would reccommend that you do all queries in a stored procedure, which you then call from your PHP page, the stored procedure can then return an error if something fails. A very basic excample of stored procedure
CREATE PROCEDURE MyProcedure
BEGIN TRY
BEGIN TRANSACTION
Insert ....
delete....
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT 'Transaction failed'
END CATCH
Why not simply use transactions? In case any of the queries fails, you execute rollback and post error to the client.
I use the following, query is just a wrapper around mssql_query + some additional logic with ERROR_MESSAGE() to get more informative error messages.
You might want to use PDO, i got this from an old project, works on MSSQL 2008.
function StartTransaction() {
$sql = "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ";
$res = $this->Query($sql);
$sql = "set implicit_transactions on;";
$res = $this->Query($sql);
}
function RollbackTransaction() {
$sql = "IF @@TRANCOUNT > 0 ROLLBACK TRAN";
$res = $this->Query($sql);
$sql = "set implicit_transactions off;";
$res = $this->Query($sql);
$sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
$res = $this->Query($sql);
}
function CommitTransaction() {
$sql = "IF @@TRANCOUNT > 0 COMMIT TRAN";
$res = $this->Query($sql);
$sql = "set implicit_transactions off;";
$res = $this->Query($sql);
$sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
$res = $this->Query($sql);
}
You could maybe use a exit ([ string $status ] ); this would stop your script straight away and you could display an error or change the header location to an error page. http://php.net/manual/en/function.exit.php
other wise use a series of if statement?
Php has Try and Catch syntax for effective error handling. The following links might be of interest.