PHP & SQL Server Error Handling?

Question

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.

Hedinn · Answer 1 · 2013-10-11T12:08:08.587

1

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

edited Oct 11 '13 at 12:08

answered Oct 11 '13 at 12:01

Hedinn

864
4
7

Very good point... Yes I will be using Stored Proc's but during early development I was doing some hard coded SQL (ewww) just to get me off the ground and then turn into SP's. I'll defanately go down that path, I guess I can then rollback transactions in the SP as suggested – realtek Oct 11 '13 at 12:04
There might be some work involving passing all the data needed for all the inserts in one go. – hidden_4003 Oct 11 '13 at 12:16
True, but there is no information on how many or what kind of fields he would be passing so I left it out @hidden_4003 – Hedinn Oct 11 '13 at 12:26

hidden_4003 · Answer 2 · 2013-10-11T12:14:07.483

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);
}

really? do you have an example? That would be just what I need! thanks — realtek, Oct 11 '13 at 12:03

score 0 · Answer 3 · answered Oct 11 '13 at 12:04

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?

score 0 · Answer 4 · edited May 23 '17 at 12:10

0

Php has Try and Catch syntax for effective error handling. The following links might be of interest.

PHP Try and Catch for SQL Insert

http://php.net/manual/en/language.exceptions.php

edited May 23 '17 at 12:10

Community

1
1

answered Oct 11 '13 at 12:08

Opentuned

1,477
17
21

PHP & SQL Server Error Handling?

4 Answers4