SQLSRV driver does not support ID inserts

[laurencei]

• Laravel Version: 5.8.0
• PHP Version: 7.2
• Database Driver & Version: MSSQL 2012 with SQLSRV driver

Description:

As part of an enterprise project, my client needs to use MSSQL. Trying to insert a seed with a specific id gives an error:

DB::table('test_table')->insert(['id' => 1, 'name' => 'example']);

SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert explicit value for identity column in table 'test_table' when IDENTITY_INSERT is set to OFF. (SQL: insert into [test_table] ([id], [name]) values (1, example))

This is expected behavior, as MSSQL works a bit differently to most other databases, and does not allow IDs to be inserted by default. The method to solve is to turn IDENTITY_INSERT to ON for the table, insert the record, then turn it OFF again:

DB::statement('SET IDENTITY_INSERT test_table ON');
DB::table('test_table')->insert(['id' => 1, 'name' => 'example']);
DB::statement('SET IDENTITY_INSERT test_table OFF');

Except this doesnt work, and MSSQL still thinks the IDENTITY_INSERT is OFF. If you run the above as native SQL queries - it works, so the problem is how Laravel is handling it.

Technically, we should be able to wrap the whole thing in a transaction, but that doesnt work either:

DB::beginTransaction();
DB::statement('SET IDENTITY_INSERT test_table ON');
DB::table('test_table')->insert(['id' => 1, 'name' => 'example']);
DB::statement('SET IDENTITY_INSERT test_table OFF');
DB::commit();

Googling around, I found this SO thread which people have the same issue. There is a manual workaround, but the answer given there is not practical for me or suitable for long term use.

I only really need the id inserting for seeding and a few edge cases, - but this seems like a bug to me?

[laurencei]

I'm wondering if we need to create some extra commands in the SQL driver - and do something like:

DB::beginTransaction();
DB::setIdentityInsertOn();
DB::table('test_table')->insert(['id' => 1, 'name' => 'example']);
DB::setIdentityInsertOff();
DB::commit();

Or even better:

DB::table('test_table')->insertWithIdentity(['id' => 1, 'name' => 'example']);

which would wrap the insert with ON and OFF

[driesvints]

I'll need someone else using SQLSRV to confirm this.

[staudenmeir]

Use unprepared() instead of statement():

DB::unprepared('SET IDENTITY_INSERT test_table ON');
DB::table('test_table')->insert(['id' => 1, 'name' => 'example']);
DB::unprepared('SET IDENTITY_INSERT test_table OFF');

The difference is that statement() executes a prepared statement, while unprepared() uses PDO::exec() instead. Every prepared statement starts a new session (inside the same database connection) and because the SET IDENTITY_INSERT query is session-specific, it doesn't affect other prepared statements like the INSERT query. PDO::exec() affects the whole database connection.

[laurencei]

@staudenmeir - you are a genius - that resolves it.

So then the next question is - how do we document this moving forward? I'll update the 2-3 forum threads/SO topics I found on Google about this issue, but should there be something in the docs? unprepared() is not mentioned at all?

edit: p.s. could you explain why that solves it?

[staudenmeir]

I've added the explanation to my previous post.

We could append the unprepared() method to https://laravel.com/docs/5.8/database > "Running A General Statement" and add bindings to the statement() example to show the difference.

[driesvints]

Feel free to send in a pr to the docs for this!

[algorhythm]

Related links
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175528(v=sql.105)
https://docs.microsoft.com/en-us/sql/connect/php/direct-statement-execution-prepared-statement-execution-pdo-sqlsrv-driver?view=sql-server-ver15

[jdlien]

If you're getting an Assertion Error, try this workaround:

DB::unprepared('SELECT 1; SET IDENTITY_INSERT test_table ON');
DB::table('articles')->insert(['id' => $request->input('article_id'), 'title' => $request->input('title'), 'body'=> $request->input('body')]);
DB::unprepared('SELECT 1; SET IDENTITY_INSERT test_table OFF');

DB::unprepared will work, although in my case I was running into an issue running this on macOS when I had the Doctrine DBAL (Database Abstraction Layer) installed, which is a dependency of Laravel Nova. It turns out that DB::unprepared would return false for this statement, which worked fine except that the DBAL would cause an Assertion Error.

 AssertionError 

  assert($result !== false)

  at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:54

I struggled with a number of approaches to work around this but ultimately what I found was that by adding a trivial SELECT before the SET IDENTITY_INSERT table_name ON, it would return true and the AssertionError would no longer be a problem.

Please let me know if there's a more elegant solution. It's possible that my particular issue was a driver quirk or DB setting, but I'm using the same Docker SQL Server 2019 configuration as on other environments that didn't have this issue.