0

JDBC sql server: set IDENTITY_INSERT ON: no effect.

PreparedStatement stmt = connection.prepareStatement("SET IDENTITY_INSERT Table_FOO ON");
stmt.execute();
stmt.close();


PreparedStatement stmt2 = connection.prepareStatement("insert into Table_FOO (id, name) values (100, 'abc')");
stmt2.execute();
stmt2.close();

Error: still complain that DENTITY_INSERT is OFF.

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'Table_FOO' when IDENTITY_INSERT is set to OFF.

Dale K
  • 25,246
  • 15
  • 42
  • 71
eastwater
  • 4,624
  • 9
  • 49
  • 118

1 Answers1

0

You could try combining the statements into 1 string and terminate statements with semicolon(s)

PreparedStatement stmt = connection.prepareStatement("SET IDENTITY_INSERT Table_FOO ON; " +
                                                     "insert into Table_FOO (id, name) values (100, 'abc');" +
                                                     "SET IDENTITY_INSERT Table_FOO OFF;");
stmt.execute();
stmt.close();
Dale K
  • 25,246
  • 15
  • 42
  • 71
SteveC
  • 5,955
  • 2
  • 11
  • 24
  • I will try this. Do not know why the two statements not working. Thanks. – eastwater Sep 08 '20 at 02:29
  • @Sunnyday, the driver executes prepared statements using `sp_executesql` so the `SET IDENTITY_INSERT ON` goes out of scope after the statement is executed, reverting to `OFF`. The best approach, IMHO, is to include all in the same batch like SteveC suggested. An alternative is to use `Statement` instead of `PrepartedStatement` for `SET IDENTITY_INSERT ON;`. That will execute the query as an ad-hoc batch and avoid the error because the subsequent `PreparedStatement` is in the inner scope. – Dan Guzman Sep 08 '20 at 10:19