3

How can I get PDO to throw an exception when executing multiple queries?

If I run the erroneous sql by itself:

$execute($ddl_partial);
$execute($insert);

Then I get the expected error:

PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 207 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'other'. (SQLExecute[207] at /build/php7.0-41GaEn/php7.0-7.0.8/ext/pdo_odbc/odbc_stmt.c:260)

However, if I run some good SQL first and follow it up with the bad, then it just swallows the error and all appears fine. But looking at the database afterwards confirms all queries fail after the bad statement.

$execute($ddl_full);
$execute($insert);

$execute($drop);
$execute($ddl_partial);
$execute($insert);

I'm iterating over all the returned rowsets with while ($statement->nextRowset()) as is demonstrated in this answer, but it only prints this exactly 8 times:

array(4) {
  [0] =>
  string(5) "00000"
  [1] =>
  int(0)
  [2] =>
  string(24) " ((null)[0] at (null):0)"
  [3] =>
  string(0) ""
}

which adds up:

  • 1 - first drop
  • 1 - full ddl
  • 3 - first inserts
  • 1 - second drop
  • 1 - partial ddl
  • 1 - first statement from insert (second one is erroneous, so third never executes)

Why am I not getting the error message from the bad statement?

<?php

$hostname = 'microsoftsql.example.com';
$database = 'mydb';
$username = 'user';
$password = 'P@55w0rd';
// https://www.microsoft.com/en-us/download/details.aspx?id=50419
$driver   = 'ODBC Driver 13 for SQL Server';

$pdo = new PDO("odbc:Driver=$driver;
    Server=$hostname;
    Database=$database",
    $username,
    $password
);
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false  );

$drop = "DROP TABLE testerino;";

$ddl_full = "
    CREATE TABLE testerino (
        value VARCHAR(10),
        other VARCHAR(10)
    );
";

$ddl_partial = "
    CREATE TABLE testerino (
        value VARCHAR(10)
    );
";

$insert = "
    INSERT INTO testerino (value)
    VALUES ('first');

    INSERT INTO testerino (value, other)
    VALUES ('second', 'another');

    INSERT INTO testerino (value)
    VALUES ('third');
";

$execute = function (String $sql) use ($pdo) {
    $pdo->beginTransaction();
    try {
        $statement = $pdo->prepare($sql);
        $statement->execute();
        do {
            /* https://bugs.php.net/bug.php?id=61613 */
            var_dump($statement->errorInfo());
        } while ($statement->nextRowset());
        $pdo->commit();
    } catch (PDOException $e) {
        $pdo->rollBack();
        throw $e;
    } finally {
        $statement->closeCursor();
    }
};

$execute($drop); // not needed first time
$execute($ddl_full);
$execute($insert);

$execute($drop);
$execute($ddl_partial);
$execute($insert);

This is a big problem for me because an exception is never thrown, so the transaction doesn't roll back and I end up with only 1/3 records inserted. I need it to be all or nothing.

I'm running Ubuntu Linux 16.04.1 with PHP 7.0.8-0ubuntu0.16.04.3 (cli) ( NTS ) connecting to Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) using the Microsoft® ODBC Driver 13 (Preview) for SQL Server®

Community
  • 1
  • 1
Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
  • Why do you think this code would insert more than 1 record? – david strachan Oct 18 '16 at 19:43
  • @davidstrachan my query has 3 insert statements. When running it with the full ddl, then it properly inserts all 3. When running it with the partial, it errors out (as it should) and rolls back the transaction (no records inserted). But when running both, it inserts 3, drops table, inserts 1, then ends. – Jeff Puckett Oct 18 '16 at 19:45
  • Then post the actual code. – david strachan Oct 18 '16 at 19:49
  • @davidstrachan I'm sorry I don't understand your comment. If you look at the posted value for `$insert` then you should see it contains 3 insert statements. – Jeff Puckett Oct 18 '16 at 19:50
  • 1
    @JeffPuckettII interesting, haven't seen this problem before - [but others appearantly have](http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd). I'd just suggest adding the values to an array and then looping over them to execute - which will result in a separate execute for each insert. – JimL Oct 18 '16 at 19:58
  • @JimL yes thanks, I have actually encountered those answers during this plight, but notice that they are resolved with `PDO::ERRMODE_EXCEPTION` which I have, or by iterating `while ($statement->nextRowset())` which does not work for me either. Also, running the queries in batch is needed for performance. Executing statements one by one works fine, but takes way too long for some purposes. – Jeff Puckett Oct 18 '16 at 20:12
  • 1
    The simple fact of the matter is the Microsoft SQL drivers for PHP have never been very good simply because they don't get much use so bugs in them go undiscovered, and nobody bothers investigating them when they are discovered and reported, again because they don't get much use. I don't know if MSSQL supports ODBC but if it does maybe that's a better option? – GordonM Feb 22 '17 at 09:47

0 Answers0