4

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:

  1. How can check if the Query failed at any time?
  2. How can I make sure that a query is either executed whole or not at all?
Simon Balling
  • 481
  • 1
  • 5
  • 14
  • 1
    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

1 Answers1

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";
    ...
    
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