Skip to content

pdo_dblib throws incorrect PDOException for UNIQUE constraint violation #9463

@jnahmias

Description

@jnahmias

Description

The following code:

<?php
$pdo = new PDO("dblib:host=myserver.example.org");
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$pdo->exec('DROP TABLE IF EXISTS tempdb.dbo.myTable;');
$pdo->exec('CREATE TABLE tempdb.dbo.myTable (id int UNIQUE);');
$i = $pdo->prepare("INSERT INTO tempdb.dbo.myTable (id) VALUES (:id);");
$i->execute([':id' => 1]);
$i->execute([':id' => 1]);  // should throw exception for UNIQUE violation
exit(0);

Resulted in this output with a PDOException thrown having the SQLSTATE error code set to HY000 [General error]:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 20018 Violation of UNIQUE KEY constraint 'UQ__myTable__3213E83EB59C83C0'. Cannot insert duplicate key in object 'dbo.myTable'. The duplicate key value is (1). [20018] (severity 14) [INSERT INTO tempdb.dbo.myTable (id) VALUES ('1');] in ~/mybug.php:9
Stack trace:
#0 ~/mybug.php(9): PDOStatement->execute()
#1 {main}
  thrown in ~/mybug.php on line 9

But I expected this output instead with a PDOException thrown having the SQLSTATE error code set to 23000 [Integrity constraint violation]):

PHP Fatal error:  Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: Violation of UNIQUE KEY constraint 'UQ__myTable__3213E83EB59C83C0'. Cannot insert duplicate key in object 'dbo.myTable'. The duplicate key value is (1). (severity 14) [INSERT INTO tempdb.dbo.myTable (id) VALUES ('1');] in ~/mybug.php:9
Stack trace:
#0 ~/mybug.php(9): PDOStatement->execute()
#1 {main}
  thrown in ~/mybug.php on line 9

Ref for SQLSTATEs: https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes

Note that if you use tsql from freetds/libtds directly (without pdo_dblib), you get this output:

$ tsql -H myserver.example.org -p 1433
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> DROP TABLE IF EXISTS tempdb.dbo.myTable;
2> CREATE TABLE tempdb.dbo.myTable (id int UNIQUE);
3> INSERT INTO tempdb.dbo.myTable (id) VALUES (1);
4> GO
1> INSERT INTO tempdb.dbo.myTable (id) VALUES (1);
2> GO
Msg 2627 (severity 14, state 1) from MYSERVER Line 1:
        "Violation of UNIQUE KEY constraint 'UQ__myTable__3213E83E6D65FB8A'. Cannot insert duplicate key in object 'dbo.myTable'. The duplicate key value is (1)."
Msg 3621 (severity 0, state 0) from MYSERVER Line 1:
        "The statement has been terminated."
1> quit
$

PHP Version

PHP 8.1.7

Operating System

Debian unstable/testing/bookworm

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @jnahmias@cmb69

        Issue actions

          pdo_dblib throws incorrect PDOException for UNIQUE constraint violation · Issue #9463 · php/php-src