在表上打开 IDENTITY_INSERT 以使用 DB 单元加载它

发布于 2024-10-02 13:46:03 字数 1899 浏览 11 评论 0原文

我尝试使用数据库单元加载一个具有标识列的表。我希望能够自己设置 id 值（我不希望数据库为我生成它）。

这是我的表的最小定义

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

要在 X 中插入一行，我执行以下 SQL

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

没问题。数据库单元 XML 数据集 (RS_7_10_minimal_ini.xml) 加载此表时

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

但是，当我尝试使用以下最小 JUnit (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() {
        // ...
}
}

：它失败并出现以下异常

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

似乎数据库单元在插入行之前不会打开标识为其指定标识列的值。

我已经尝试在从 JdbcDataBaseTester 检索到的连接上执行自己，但没有成功。可能是一个新连接，或者与用于将数据推送到数据库的连接不同。

有什么想法吗？

非常感谢大家的帮助！

八度

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人│生佛魔见 2024-10-09 13:46:03

是的，实际上在 DBUnit FAQ 中找到了解决方案

我可以将 DbUnit 与 IDENTITY 或自动增量列一起使用吗？
许多 RDBMS 允许用客户端值隐式覆盖 IDENTITY 和自动增量列。 DbUnit 本身可以与这些 RDBMS 一起使用。某些数据库（例如 MS SQL Server 和 Sybase）需要显式激活客户端值写入。激活此功能的方法是特定于供应商的。
DbUnit 通过 InsertIdentityOperation 类为 MS SQL Server 提供此功能。

虽然它是为 MS SQL Server 编写的，但也适用于 Sybase。将数据集推送到数据库

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

因此，我使用Et voilà

。感谢您的回答。

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.

回复收藏 0 原文

野却迷人 2024-10-09 13:46:03

对 DBUnit 不够熟悉，无法帮助您了解具体细节；但我在类似的情况下使用了表截断和重新播种标识值。

dbcc checkident

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.