Turning IDENTITY_INSERT ON on a table to load it with DB Unit

Question

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).

Here is a minimal definition of my table

create table X (
 id numeric(10,0) IDENTITY PRIMARY KEY NOT NULL
)

To insert a line in X, I execute the following SQL

set INDENTITY_INSERT X ON
insert into X(id) VALUES(666)

No problem. But when I try to load this table with the following db unit XML dataset (RS_7_10_minimal_ini.xml)

<dataset>
 <X id="666"/>
</dataset>

using the following minimal JUnit (DBTestCase) test case :

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() {
        // ...
}
}

It fails with the following exception

com.sybase.jdbc3.jdbc.SybSQLException: Explicit value specified for identity field in table 'X' when 'SET IDENTITY_INSERT' is OFF.

It seems DB Unit does not turn identity ON before inserting a row for which the value of the identity column is specified.

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.

Any idea?

Thanks a lot for your help all !

Octave

Leeds

Answer 1

Yes, found the solution in the DBUnit FAQ actually

Can I use DbUnit with IDENTITY or auto-increment columns?

Many RDBMSes allow IDENTITY and auto-increment columns to be implicitly overwritten with client values. DbUnit can be used with these RDBMS natively. Some databases, like MS SQL Server and Sybase, need to explicitly activate client values writing. The way to activate this feature is vendor-specific. DbUnit provides this functionality for MS SQL Server with the InsertIdentityOperation class.

Although it is written for the MS SQL Server, is also works for Sybase. So I push my data set to db with

    new InsertIndentityOperation(DatabaseOperation.CLEAN_INSERT).execute(connection,initialDataSet); 

Et voilà.

Thanks for your answer rawheiser.

Leeds

Is that a typo or did the name change to InsertIdentityOperation? In my getSetUpOperation() I used return new InsertIdentityOperation(DatabaseOperation.CLEAN_INSERT); – Jiles

Answer 2

Not familar enough with DBUnit to help you with the specifics; but I have used a table truncate and reseeding the identity value in similar situations.

dbcc checkident 

Brewery