Geek Answers Handbook

Include IDENTITY_INSERT in a table to load it using a DB block

I am trying to load a table that has an identity column with a DB block. I want to be able to set the id value myself (I don't want the database to 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 string in X, I execute the following SQL

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

No problems. But when I try to load this table using the following XML db dataset (RS_7_10_minimal_ini.xml)

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

using the following minimal JUnit test case (DBTestCase):

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

Unable to execute 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 the DB Unit does not include the identifier ON before inserting a row for which the identifier column value is specified.

, JdbcDataBaseTester, . , , DB.

?

!

+5
2

, FAQ DBUnit

DbUnit IDENTITY auto-increment?

RDBMS IDENTITY auto-increment . DbUnit . , MS SQL Server Sybase, . .     DbUnit MS SQL Server InsertIdentityOperation.

MS SQL Server, Sybase. db

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

Et voilà.

rawheiser.

+9

DBUnit, ; .

dbcc checkident
+1

More articles:


All Articles