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 · Accepted Answer · 2013-10-11 12:08:08Z

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, 2013 at 12:08

answered Oct 11, 2013 at 12:01

Hedinn

8644 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

realtek Over a year ago

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

hidden_4003 Over a year ago

There might be some work involving passing all the data needed for all the inserts in one go.

Hedinn Over a year ago

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

Dean Ridgeley · Accepted Answer · 2013-10-11 12:04:09Z

0

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?

answered Oct 11, 2013 at 12:04

Dean Ridgeley

2031 gold badge2 silver badges6 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:10:57Z

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, 2017 at 12:10

CommunityBot

11 silver badge

answered Oct 11, 2013 at 12:08

Opentuned

1,52717 silver badges21 bronze badges

Comments

hidden_4003 · Accepted Answer · 2013-10-11 12:14:07Z

0

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

edited Oct 11, 2013 at 12:14

answered Oct 11, 2013 at 12:02

hidden_4003

1,8192 gold badges15 silver badges20 bronze badges

1 Comment

realtek Over a year ago

really? do you have an example? That would be just what I need! thanks

Collectives™ on Stack Overflow

PHP & SQL Server Error Handling?

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related