CODEWITHSUNDEEP
phpsql-serverlaravellaravel-5.1
Junior
Junior

Reputation: 12002

How to correctly use Laravel with SQL Server instead on MySQL?

I am trying to use SQL Server for my databases with my Laravel project. I was able to connect SQL Server with Laravel 5.2. However, when I try to seed data into the table I get this error

[Illuminate\Database\QueryException] SQLSTATE[23000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cann ot insert explicit value for identity column in table 'surveys' when IDENTITY_INSERT is set to OFF. (SQL: insert into [surveys] ([id], [name]) values (10, 'Some Text'))

Note: I am trying to supply the identity value which is probably what is causing the problem.

While researching the SQL error, I learned that I need to execute the following queries.

Before seeding I need to execute

SET IDENTITY_INSERT surveys ON;

After the seeding I need to execute

SET IDENTITY_INSERT surveys OFF;

But I am not sure how can I execute these command using Laravel

How can I seed while supplying the value for the identity column without this issue?

UPDATED Here is my seeder

<?php

use Illuminate\Database\Seeder;

class FinalSurveyTS extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {   
        $myTable = 'surveys';

        DB::statement('SET IDENTITY_INSERT ' . $myTable . ' ON');


        DB::table($myTable)->truncate();


        DB::table($myTable)->insert([
            'id' => 10,
            'name' => 'some name',
        ]);

        DB::statement('SET IDENTITY_INSERT ' . $myTable . ' OFF');
    }
}

Upvotes: 8

Views: 6658

Answers (6)

JD Lien
JD Lien

Reputation: 11

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.

Upvotes: 0

rc.adhikari
rc.adhikari

Reputation: 2222

I ended up creating the following methods to control the IDENTITY_INSERT to ON/OFF for SQL table seeding process:

function setIdentityInsert($table, $onStatus = true)
{
    $status = $onStatus ? 'ON' : 'OFF';

    $this->sqlConnection->unprepared("SET IDENTITY_INSERT $table $status");
}

function insertTableData($table, $data)
{
    return $this->sqlConnection->table($table)->insert($data);
}

function seedTable($table, $hasAutoIncrementPrimaryKey = false, $data = [])
{
    if ($hasAutoIncrementPrimaryKey) {
        $this->setIdentityInsert($table);
        $response = $this->insertTableData($table, $data);
        $this->setIdentityInsert($table, false);

        return $response;
    }
    else {
        return $this->insertTableData($table, $data);
    }
}

Note: Generally, the table requires to have an auto-increment primary key to set Identity Insert to ON, that's why I have the $hasAutoIncrementPrimaryKey flag. Otherwise, seeding may throw an error as:

SQLSTATE[HY000]: General error: 544 Cannot insert explicit value for
identity column in table 'test_table_name' when IDENTITY_INSERT is set to
OFF. [544] (severity 16) [(null)]

Hope this helps!

Upvotes: 0

Laurence
Laurence

Reputation: 60058

For anyone finding this via Google - the new correct answer is to use unprepared() like this:

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

As discussed on this issue thread: https://github.com/laravel/framework/issues/27778

Upvotes: 9

Alper YAZGAN
Alper YAZGAN

Reputation: 11

Any raw statement will be executed separately with a separate connection to the database.

Because of this, you should follow the way I did for the connection; Here is the sample code; 

    DB::transaction(function () use ($reader, $dbDriver, $tableName) {
      if ($dbDriver == 'pgsql') DB::statement('ALTER TABLE ' . $tableName . ' DISABLE TRIGGER ALL');
      elseif ($dbDriver == 'sqlsrv')
        {
          $pdo = DB::connection()->getPdo();
          $pdo->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY, true);
          DB::statement('SET IDENTITY_INSERT ' . $tableName . ' ON');
        }

      foreach ($reader as $row) DB::table($tableName)->insert([$row]);

      if ($dbDriver == 'pgsql') DB::statement('ALTER TABLE ' . $tableName . ' ENABLE TRIGGER ALL');
      elseif ($dbDriver == 'sqlsrv')
        {
          DB::statement('SET IDENTITY_INSERT ' . $tableName . ' OFF');
          $pdo->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY, false);
        }
    });

Upvotes: 1

Junior
Junior

Reputation: 12002

Here is what I found.

Laravel does not support raw statement inside of a transaction.

Any raw statement will be executed separately with a separate connection to the database.

The means when executing 

DB::statement('SET IDENTITY_INSERT surveys ON;');
DB::table(...)->insert(...);DB::table(...)->insert(...);DB::table(...)->insert(...);
DB::statement('SET IDENTITY_INSERT surveys ON;');

..

Laravel will process 3 different database connection. This means that the second statement will never be aware of the first or third transaction. There for the first line will have no affect what so ever in this case.

The work around to this problem "until Laravel adds support to the raw statement to the transaction" is to create insert statement as a raw query like so

DB::statement('

SET IDENTITY_INSERT surveys ON;
INERT INTO table(id, col1, col2)
VALUES(10, 'blah','blah'), (11, 'blah','blah'), (12, 'blah','blah');
SET IDENTITY_INSERT surveys OFF;

');

I hope this post helps someone else.

Upvotes: 3

Sergio Guillen Mantilla
Sergio Guillen Mantilla

Reputation: 1468

In order to execute those commands, you can do it as raw

DB::statement('SET IDENTITY_INSERT surveys ON;');

and then

DB::statement('SET IDENTITY_INSERT surveys OFF;');

DB::statement is supposed for commands that don't return data

Edit When the seeder is executed like this:

DB::statement(...);
DB::table(...)->insert(...);
DB::statement(...);

Seems that for some reason the state of IDENTITY_INSERT variable is not kept, so a way to make it work is to wrap that code in a transaction.

try {
    DB::beginTransaction();
    DB::statement(...);
    DB::table(...)->insert(...);
    DB::statement(...);
    DB::commit();
} catch(Exception $e) {
    DB::rollback();
}

Upvotes: 3

Related Questions