#358 Invalid syntax error when INSERTing into a table that contains a column named after a reserved keyword

[Simone]

Invalid syntax error when INSERTing into a table that contains a column named "order". I guess this is happening because order is an SQL keyword. Enclosing the column name in backticks (`) should solve the issue

[Jeff Jensen]

Hi,

Do you have tests that show the issue and code changes that fix it?

[Simone]

Part of the test class:

protected DBI getDBI() throws Exception {
    IDatabaseConnection connection = this.getTestConnection(this.getDataSet("datasets/dataset.xml"));
    return new TestDBI(new TestConnectionFactory(connection));
}

protected IDataSet getDataSet(String datasetFile) throws Exception {
    URL resource = getClass().getClassLoader().getResource(datasetFile);
    assert resource != null;
    File file = new File(resource.getFile());
    return new FlatXmlDataSetBuilder().build(file);
}

The dataset contains entries for a table that has a column named "order". Changing the column to "timestamp" in our database fixed the issue.

Code for TestConnectionFactory

public class TestConnectionFactory implements ConnectionFactory {

    private final IDatabaseConnection testConnection;
    private Connection connection;

    public TestConnectionFactory(IDatabaseConnection testConnection) {
        this.testConnection = testConnection;
    }

    @Override
    public Connection openConnection() throws SQLException {
        if(connection == null) {
            connection = this.testConnection.getConnection();
        }
        return connection;
    }
}

The following change to org.dbunit.operation.InsertOperation should fix it.

In the method

public OperationData getOperationData(ITableMetaData metaData,
        BitSet ignoreMapping, IDatabaseConnection connection) throws DataSetException

Change the line

sqlBuffer.append(columnName);

to

sqlBuffer.append("`" + columnName + "`");

Basically, enclose the column name in backticks, or make it configurable in case you're working with a SQL database that doesn't recognise this character.

Example of broken query:
INSERT INTO `test_table` (order, label) VALUES ('0', 'test_value');

Fixed query:
INSERT INTO `test_table` (`order`, `label`) VALUES ('0', 'test_value');

[Jeff Jensen]

Thanks for the info.

Which DB are you using?

As you mentioned, I also think we need to make make the column name "quotes" configurable. I hesitate to commit this simple change without it!

Do you have time and interest to complete this change? e.g.:
- make a test class for this
- make a config for the "column name quotes" (default to nothing as is now)
- implement the change

I hope you are able to!

[Simone]

Hi Jeff, this is embarassing but looking at the code I realised there is already a configuration option for this: DatabaseConfig.PROPERTY_ESCAPE_PATTERN

There's also a test case for it: InsertOperationIT.testExecuteWithEscapedNames()

I suppose we can close this ticket :)

Thank you

[Jeff Jensen]

Haha, embarrassing for two of us!

[Jeff Jensen]

Feature already exists.