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
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.
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With