Msphpsql: Wrong error message after switching database context

[Agares]

PHP Driver version or file name

pdo_sqlsrv 5.3.0

SQL Server version

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 16299: ) (Hypervisor)

Client operating system

Windows 10 Enterprise 1709

PHP version

PHP 7.2.12 (cli) (built: Nov 8 2018 05:47:36) ( ZTS MSVC15 (Visual C++ 2017) x64 )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies

Microsoft ODBC Driver version

10.0.16299.15

Table schema

CREATE DATABASE test;
GO

CREATE DATABASE test2;
GO

Tables are irrelevant.

Problem description

A non-error message that was emitted previously is shown instead of the actual error message.

Expected behavior and actual behavior

Expected: An error message about division by zero is shown.
Actual:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[01000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'test2'.

Repro code or steps to reproduce

Fill in the DSN to work with your database, but make sure you're not connecting to test2 (so the USE statement will emit a message).

<?php
    $pdo = new \PDO('sqlsrv:Server=localhost;Database=test');
    $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $statement = $pdo->prepare('SET NOCOUNT ON; USE test2; SELECT 1/0 AS col1');
    $statement->execute();

    var_dump($statement->fetchColumn());

[david-puglielli]

Thank you for reporting this @Agares, I can reproduce the error. We will investigate and get back to you.

[david-puglielli]

@Agares Interestingly, it appears that the same problem affects pdo_odbc, suggesting that the issue lies with PDO's error handling - it apparently fails to properly traverse the error array returned by the underlying ODBC driver (the ODBC driver is not the problem, as I have verified that the full error array is properly handled by the sqlsrv driver). We may investigate this further in future and look for a workaround, but it seems like something you may want to raise as bug in PHP.

[Agares]

Okay, I raised a bug in PHP's bugtracker: https://bugs.php.net/bug.php?id=77572

[yitam]

@Agares thank you for bringing this to our attention.

FYI, while investigating this problem further, we filed another bug report to PHP. In light of this, the documentation of both PDO::errorInfo and PDOStatement::errorInfo have been updated, which means that the errorInfo array will always contain at least 3 elements.

Thus, we will modify pdo_sqlsrv driver such that if there are more ODBC errors, they will be appended to the errorInfo array. Given your example script above, the exception message still displays the first error, but you will see something different if you have implemented exception handling, like this:

<?php
try {
    $pdo = new \PDO('sqlsrv:Server=localhost;Database=test');
    $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $statement = $pdo->prepare('SET NOCOUNT ON; USE test2; SELECT 1/0 AS col1');
    $statement->execute();

    var_dump($statement->fetchColumn());
} catch (PDOException $e) {
    var_dump($e->errorInfo);
}
?>

With the new change the errorInfo will be (using ODBC 17):

array(6) {
  [0]=>
  string(5) "01000"
  [1]=>
  int(5701)
  [2]=>
  string(91) "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'test2'."
  [3]=>
  string(5) "22012"
  [4]=>
  int(8134)
  [5]=>
  string(87) "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Divide by zero error encountered."
}

[Agares]

Nice! Thanks, this will make my (and hopefully other people's too) life easier :)

[yitam]

Hi @Agares , 5.9.0-preview1 is just released, which contains the aforementioned change.

FYI, please check PDO ErrorInfo extended to report additional ODBC messages

I will now close this issue. Please feel free to reopen if necessary.