SET options are not effective over multiple queries in the same connection #385
Description
When you issue SET queries like SET IDENTITY_INSERT table ON OR SET QUOTED_IDENTIFIER OFF I expect those settings to be of effect for the entire duration of that specific connection but they aren't.
Details
- Driver Version: 4.1.7-preview (manually compiled off master)
- OS Version and Distro: Linux/CentOS 7
- Can you connect to SQL Server via sqlcmd: yes
- Environment details: PHP 7.1 64bit NTS with pdo_sqlsrv
Steps to Reproduce:
- Allow values for the identity column on mytable to be inserted:
SET IDENTITY_INSERT mytable ON - Issue query setting a value for an identity column:
INSERT INTO mytable (id) VALUES (1)
Expected result:
mytable now has a row with IDENTITY column id with value 1.
Actual result:
Error is thrown that inserting into IDENTITY column is only allowed with IDENTITY_INSERT set to ON.
Steps to Reproduce II:
- Disable quoted identifiers
SET QUOTED_IDENTIFIERS OFF - Issue query with a quoted identifier:
SELECT * FROM "mytable"
Expected result:
Database error reporting a syntax error due to a quoted identifier while we've disabled it on the connection.
Actual result:
No error.
Repro Script
<?php
/*
CREATE TABLE mytable (
id int IDENTITY(1,1)
);
*/
// Should work, but errors out:
$connection = new PDO('sqlsrv:DSN here', 'user', 'password');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->query("SET IDENTITY_INSERT mytable ON");
$connection->query("INSERT INTO mytable(id) VALUES(1)");
// PHP Fatal error: Uncaught PDOException: SQLSTATE[23000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'mytable' when IDENTITY_INSERT is set to OFF.
// This actually does work:
$connection->query("SET IDENTITY_INSERT mytable ON; INSERT INTO mytable(id) VALUES(1)");
?>Repro Script 2
<?php
$connection = new PDO('sqlsrv:DSN here', 'user', 'password');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Should throw exception, but works:
$connection->query('SET QUOTED_IDENTIFIER OFF');
$connection->query('SELECT * FROM "mytable"');
// Actually throws expected exception:
$connection->query('SET QUOTED_IDENTIFIER OFF; SELECT * FROM "mytable"');
// PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'mytable'.
?>