Referencing a Derby database column with a reserved name using DBUnit

DBUnit is a pretty good tool to achieve reproducible results when unit testing your code. It puts your database in a known state before each test run.
In my case I use JPA with Hibernate as implementation to create the DB schema before each test run. Furthermore I use an embedded in-memory Derby database.

I ran into problems when inserting dbunit datasets into the created schema. A column was named interval which is a reserved word in Derby.

No problem I thought, when escaping the column name (using ' or " ticks) the problem would be solved. Well it turns out to be not that simple :-(

The solution is to set a DBUnit related property/feature to tell DBUnit how to config and use the underlying database. Here is the relevant code snippet

   IDatabaseConnection memDbConnection = new DatabaseDataSourceConnection(  
       (InitialContext) context, DEFAULT_DATASOURCE);  
   DatabaseConfig dbConfig = memDbConnection.getConfig();  
   dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new DefaultDataTypeFactory());  
   dbConfig.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "\"?\"");  

The line

dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new DefaultDataTypeFactory());

tells DBUnit to use the default data type factory which works well with Derby. You don't have to set this property explicitly when using Derby.

The second line

dbConfig.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "\"?\"");  

solved my issue. You have to explicitly declare an escape pattern. In my case this was "\"?"".
With default settings DBUnit uses no escape pattern.

Using this solution, a clean insert into the database with a column that uses a reserved name works as expected.