Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 607
Octave Avatar asked Nov 17 '10 14:11

Octave


People also ask

How do you on IDENTITY_INSERT is set to ON?

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value. The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

What is set IDENTITY_INSERT on SQL?

The set identity_insert command in SQL Server, as the name implies, allows the user to insert explicit values into the identity column of a table.

Do I need to set IDENTITY_INSERT off?

Set IDENTITY_INSERT on for all tables You will get an error that the other table is using this option if you try to set this option to ON in more than one table. It will also tell you the name of the table which is using this option. Therefore, you should always set this option to OFF after inserting the rows.

What does IDENTITY_INSERT mean?

IDENTITY_INSERT is a table property that allows you to insert explicit values into the column of table identifiers, i.e. into the column with IDENTITY. The value of the inserted identifier can be either less than the current value or more, for example, to skip a certain interval of values.


2 Answers

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.

like image 77
Octave Avatar answered Nov 15 '22 11:11

Octave


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 
like image 33
Rawheiser Avatar answered Nov 15 '22 10:11

Rawheiser