How to correctly use Laravel with SQL Server inste

问题:

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');
    }
}

回答1:

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.

回答2:

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

回答3:

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();
}

回答4:

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);
        }
    });

回答5:

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!