Escaping reserved database keywords with spring-test-dbunit

Question

How do I escape SQL Server keywords using Spring-test-dbunit framework?

In my @DatabaseSetup("sampleData.xml") file, I have a table called File which is a reserved keyword in SQL Server. In order for the queries to run successfully on SQL Server, the reserved keyword would need to be encapsulated with square brackets ([File]).

From Expoting Dataset to a xml file giving error in DBunit, I see that this can be done in dbunit by setting a pattern escape config. I don't know where or how to put this config when using Spring-test-dbunit.

Where/how do I tell the spring-test-dbunit framework to properly escape database keywords when inserting the test data into the db using the provided xml feed?

If you would like to see code, let me know what excerpts I should post and i would be happy to do so.

score 0 · Answer 1 · answered Jul 10 '13 at 01:57

0

I think, this might help you

public class DatabaseExport
{
    public static void main(String[] args) throws Exception
    {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
        Connection jdbcConn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=def_config","dbuser","dbpasswd");
        IDatabaseConnection iconn = new DatabaseConnection( jdbcConn );
        iconn.getConfig().setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN , "[?]");
        IDataSet fullDataSet = iconn.createDataSet();
        FlatXmlDataSet.write(fullDataSet, new FileOutputStream("X:/fullDataSet.xml"));
    }
}

answered Jul 10 '13 at 01:57

sreeprasad

3,242
3
27
33

Since I am using the Spring framework, I do not use the above database setup code. Instead I rely on Spring to setup my db connections and I provide the connection details in the XML config. The problem is that I don't know **how** or **if** I can specify this config in the XML config: `DatabaseConfig.PROPERTY_ESCAPE_PATTERN , "[?]"` – nmc Aug 22 '13 at 14:59
2

@nmc have you look at the "Advanced configuration of the DbUnitTestExecutionListener" or "Advanced configuration of the DbUnitRule" or "Custom IDatabaseConnections" section in [spring-test-dbunit] (http://springtestdbunit.github.io/spring-test-dbunit/)? Maybe it can help you config your connections. – Yugang Zhou Sep 08 '13 at 01:14
@Hippoom I think the "Custom IDatabaseConnections" is just what I was looking for. If you'd like to post a completed answer, I'd be happy to accept it. I have no way of testing it at the moment as I've renamed by tables to get around this problem. – nmc Sep 09 '13 at 17:35
@nmc Thank you but don't bother, I just gave half of the answer and it was inspired by sreeprasad-govindankutty. So I suggest an edit on this answer then accept this one :) – Yugang Zhou Sep 10 '13 at 01:05
You can create a testContext.xml like this one http://stackoverflow.com/questions/22796147/spring-test-dbunit-and-table-schema-name/24206114#24206114 – Gilberto Jun 13 '14 at 13:12

score 0 · Answer 2 · edited May 23 '17 at 12:15

To do this with spring-test-dbunit, you need to follow the "Custom IDatabaseConnections" section at their documentation page: https://springtestdbunit.github.io/spring-test-dbunit/

For the Java Config variant (and as a backup reference), see the answers of this StackOverflow question: Spring Test DBunit Warning

In your case, using the dbUnitDatabaseConfig bean, set the escapePattern property to "[?]". As in:

<bean id="dbUnitDatabaseConfig" class="com.github.springtestdbunit.bean.DatabaseConfigBean">
    <property name="escapePattern" value="[?]"/>
</bean>

Side note, if JPA/Hibernate is used to generate the unit test schema at test-runtime, escaping for Hibernate is needed as well. Refer to this answer for that: https://stackoverflow.com/a/3463189/1034436

Escaping reserved database keywords with spring-test-dbunit

2 Answers2