PDO::lastInsertId($name) doesn't work with a case-sensitive collation

[morozov]

PHP Driver version or file name

pdo_sqlsrv 5.9.0

SQL Server version

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)
Nov 30 2018 12:57:58
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)

Client operating system

Linux

PHP version

8.0.3

Microsoft ODBC Driver version

17.7.2.1

Problem description

PDO::lastInsertId($name) called on a server with a case-sensitive collation doesn't return the sequence value

Expected behavior and actual behavior

The API works regardless of the collation

Repro code or steps to reproduce

1. Run a SQL Server instance with a case-sensitive collation:

   docker run --rm \
       -p 1433:1433 \
       -e ACCEPT_EULA=Y \
       -e SA_PASSWORD=Passw0rd \
       -e MSSQL_COLLATION=Latin1_General_100_CS_AS \
        microsoft/mssql-server-linux:2017-latest

2. Run the following script:

   <?php
   
   $conn = new PDO('sqlsrv:Server=127.0.0.1', 'sa', 'Passw0rd');
   
   if (!$conn) {
       exit(1);
   }
   
   $conn->lastInsertId('test');
   var_dump($conn->errorInfo());

3. Observe the output:

   array(3) {
     [0] =>
     string(5) "42S02"
     [1] =>
     int(208)
     [2] =>
     string(90) "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name \'SYS.SEQUENCES\'."
   

Note that the sequence test doesn't exist at this point but it's irrelevant because the query executed within the driver is invalid.

[yitam]

Closing this issue now, @morozov , as your pull request #1245 has already been merged.

[morozov]

Thanks, @yitam!