Hibernate 3.5 vs 4 IDENTITY_INSERT

Question

We are launching the Spring 3.1 / Hibernate 4 / Java 7 / Tomcat 7 / MSSQL 2008 R2 web application. We must deal with obsolete data and archived data. When extracting data from the archive, we need to use the original unique identifier so that other (unarchived) records are correctly restored. These identifiers are stored in the primary key / auto increment field.

So far, when we used Spring 3.0 / Hibernate 3.5, the following code worked so that INSERT would retrieve the record back to the corresponding table (we already have session , entity and fullTableName in the scope):

 session.doWork( new Work() { @Override public void execute(Connection connection) throws SQLException { PreparedStatement statement = null; try { statement = connection.prepareStatement(String.format("SET IDENTITY_INSERT %s ON", fullTableName)); statement.execute(); session.save(entity); statement = connection.prepareStatement(String.format("SET IDENTITY_INSERT %s OFF", fullTableName)); statement.execute(); } finally { /* close the statement */ } } }); 

As I mentioned, all this worked fine in Hibernate 3.5, but now that we have upgraded to Hibernate 4, it stops working. Is there anything with a difference between Work and IsolatedWork?

To solve the problem and avoid problems with the working interface, we tried the following:

 session.createSQLQuery(String.format("SET IDENTITY_INSERT %s ON", fullTableName)).executeUpdate(); session.save(entity); session.createSQLQuery(String.format("SET IDENTITY_INSERT %s OFF", fullTableName)).executeUpdate(); 

However, this did not work either. In particular, the resulting exception is java.sql.SQLException: Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF. However, it should be clear that we are trying to set it to ON.

We made a trace of the SQL Server Profiler situation and found something interesting. Something sets IMPLICIT_TRANSACTIONS ON in each of our transactions. Here are a few examples from Profiler tracing (I replaced our actual <schema> scheme and some large data bits with shorter labels):

 SET IMPLICIT_TRANSACTIONS ON go declare @p1 int set @p1=55 exec sp_prepare @p1 output,N'',N'SET IDENTITY_INSERT <schema>.Employee ON',1 select @p1 go exec sp_execute 55 go declare @p1 int set @p1=56 exec sp_prepare @p1 output,N'<parameters for the INSERT>',N'insert into <schema>.Employee (<all the column names>) values ( <all the parameters> )',1 select @p1 go exec sp_execute 56,<the actual values to insert> go IF @@TRANCOUNT > 0 ROLLBACK TRAN go IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF go exec sp_execute 54,N'Error writing EMPLOYEE archive record. ',<an id>,N'1' go 

Now we specifically set IMPLICIT_TRANSACTIONS to OFF, through Connection.setAutoCommit (false) in our transaction (transactions are managed through Spring @Transactional and Hibernate Transaction Manager). Obviously this does not work, but what are the alternatives besides using setAutoCommit, and why will it work in Spring3.0 / Hibernate 3.5, but not Spring 3.1 / Hibernate 4?

Thanks for any thoughts or suggestions - we are at an impasse.

Answer 1

Well, that was a subtle decision ...

Our work call used java.sql.PreparedStatement internally, and we then called the execute() method. Apparently, this tells SQL Server to complete the command in its own stored procedure, as some code examples show.

We changed using PreparedStatement to just a java.sql.Statement and called its execute() method:

 session.doWork( new Work() { @Override public void execute(Connection connection) throws SQLException { Statement statement = null; try { statement = connection.createStatement(); statement.execute(String.format("SET IDENTITY_INSERT %s ON", fullTableName)); session.save(entity); statement = connection.createStatement(); statement.execute(String.format("SET IDENTITY_INSERT %s OFF", fullTableName)); } finally { /* close the statement */ } } }); 

So what's the difference? As far as we know, PreparedStatement creates pre-compiled SQL, while Statement creates static SQL ... exactly what we need to call IDENTITY_INSERT !

Lesson: there are many hives of confusion and meanness ... we must be careful!