title: Turning IDENTITY_INSERT ON on a table to load it with DB Unit
body: <p>I try to load a table, that have an identity column, with DB Unit. I want to be able to set the id value myself (I don't want the database generate it for me). </p>
  
  <p>Here is a minimal definition of my table</p>
  
  <pre><code>create table X (
   id numeric(10,0) IDENTITY PRIMARY KEY NOT NULL
  )
  </code></pre>
  
  <p>To insert a line in X, I execute the following SQL  </p>
  
  <pre><code>set INDENTITY_INSERT X ON
  insert into X(id) VALUES(666)
  </code></pre>
  
  <p>No problem. But when I try to load this table with the following db unit XML dataset (RS_7_10_minimal_ini.xml)</p>
  
  <pre><code>&lt;dataset&gt;
   &lt;X id="666"/&gt;
  &lt;/dataset&gt;
  </code></pre>
  
  <p>using the following minimal JUnit (DBTestCase) test case :</p>
  
  <pre><code>package lms.lp.functionnal_config;
  
  import java.io.FileInputStream;
  import org.dbunit.DBTestCase;
  import org.dbunit.PropertiesBasedJdbcDatabaseTester;
  import org.dbunit.dataset.IDataSet;
  import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
  import lms.DBUnitConfig;
  import org.junit.Test;
  
  public class SampleTest extends DBTestCase
  
  {
  
  public SampleTest(String name)
  
  {
      super( name );
      System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, DBUnitConfig.DBUNIT_DRIVER_CLASS );
      System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, DBUnitConfig.DBUNIT_CONNECTION_URL );
      System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, DBUnitConfig.DBUNIT_USERNAME );
      System.setProperty( PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, DBUnitConfig.DBUNIT_PASSWORD );
  
  }
  
  protected IDataSet getDataSet() throws Exception
  
  {
      return new FlatXmlDataSetBuilder().build(new FileInputStream("src/test/resources/RS_7_10_minimal_ini.xml"));
  }
  
  
  @Test
  public void testXXX() {
          // ...
  }
  }
  </code></pre>
  
  <p>It fails with the following exception</p>
  
  <pre><code>com.sybase.jdbc3.jdbc.SybSQLException: Explicit value specified for identity field in table 'X' when 'SET IDENTITY_INSERT' is OFF.
  </code></pre>
  
  <p>It seems DB Unit does not turn identity ON before inserting a row for which the value of the identity column is specified.</p>
  
  <p>I already tried to execute myself on the connection retrieved from the JdbcDataBaseTester but no luck. Probably a new connection or not the same connection used to push the data into de DB.</p>
  
  <p>Any idea?</p>
  
  <p>Thanks a lot for your help all !</p>
  
  <p>Octave</p>