PHP How to handle errors if multiple sqlsrv-querys are sent simultaneously

Question

I've got the following Table:

CREATE TABLE [dbo].[TestDB] (
    [col1] INT NOT NULL, 
    [col2] VARCHAR(50) NOT NULL

);

Now I want to add entrys:

    $params=array(123,'someString');
    $sql = "insert into testDB values(?,?)";

    $stmt = sqlsrv_query( $conntask, $sql ,$params);
    if( $stmt === false) {
        echo $sql;
        print_r($params);
        die( print_r( sqlsrv_errors(), true) );
    }

If there is an error, $stmt will be false, the message will be printed and the script terminated.

My Problem

If I want to add multiple entrys, I am sending all querys at the same time.

    $params=array(123,'someString','notANumber','someOtherString');
    $sql = "insert into testDB values(?,?) insert into testDB values(?,?)";

    $stmt = sqlsrv_query( $conntask, $sql ,$params);
    if( $stmt === false) {
        echo $sql;
        print_r($params);
        die( print_r( sqlsrv_errors(), true) );
    }

In this example, the first insert will succeed and the second one will fail because I try to put a string in an int column.
Now $stmt is not false and the query gets executed until the error occurs.

My Questions:

How can check if the Query failed at any time?
How can I make sure that a query is either executed whole or not at all?

Use `INSERT INTO table VALUES (?,?), (?,?), (?,?)...` syntax — Charlotte Dunois, May 12 '16 at 10:39
Thanks, I didn't nkow you could do this. But this would only solve this exact problem. What if I want to do an insert and then an update? Or an Update and an delete? Or... — Simon Balling, May 12 '16 at 12:34
Then you do one statement by one. If you are worried about performance then you can use transactions. But it would only make sense if you do a lot of queries (or you need the option to rollback). — Charlotte Dunois, May 12 '16 at 12:59

alpakyol · Accepted Answer · 2016-05-13T11:45:02.857

2

Your transction must rollback on any error.

Using SET XACT_ABORT ON command at the beginning of your transaction and then adding BEGIN TRANSACTION command should solve your problem.

Usage on the SMSS side

SET XACT_ABORT ON;

BEGIN TRANSACTION
  -- your queries here
COMMIT TRANSACTION

Usage on the PHP side

...
$sql = "SET XACT_ABORT ON; " .
       "BEGIN TRANSACTION " .
       "insert into testDB values(?,?) insert into testDB values(?,?)" .
       "COMMIT TRANSACTION";
...

edited May 13 '16 at 11:45

answered May 13 '16 at 06:05

alpakyol

2,402
2
29
35

Thanks. This does solve my rollback problem. It still does not return an error if it rolls back. Is there a way to check for one? – Simon Balling May 13 '16 at 07:42
In this scenario, SQL Server returns you two results: 1-) 1 rows affected message for the first query (assume as a succes) 2-) Error message for the second query (assume as a failure). You can check each result with `sqlsrv_next_result($stmt)` or add `SET NOCOUNT ON;` at the beginning of your query and you will only get true or false on result. Both have pros-cons, but in my opinion, consider using stored procedures and check each insert-update-delete with `@@ROWCOUNT` on SQL Server side. – alpakyol May 13 '16 at 08:09
1

I needed to add an "COMMIT TRANSACTION" at the bottom of my querys. it wouldnt work otherwise. I think it works now how I want it to work. Thanks – Simon Balling May 13 '16 at 09:16

PHP How to handle errors if multiple sqlsrv-querys are sent simultaneously

My Problem

My Questions:

1 Answers1