Search code examples
javasqljdbch2

h2 database: avoid usage of backticks in queries in version 2.0.202

I have just updated my com.h2database version 1.4.200 -> 2.0.202. In previous version I could execute statements like this:

private static final String CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS %s (" +
        "name varchar(255) NOT NULL," +
        "value varchar(255) NOT NULL," +
        "PRIMARY KEY (name)" +
        ") ENGINE=InnoDB";

But newest version complains on:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement

if I run it like this.

To fix syntax error I had to wrap all field names into backpacks like this:

private static final String CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS %s (" +
        "`name` varchar(255) NOT NULL," +
        "`value` varchar(255) NOT NULL," +
        "PRIMARY KEY (`name`)" +
        ") ENGINE=InnoDB";

I am using h2 for mySQL mode. My JDBC connect string looks like:

"jdbc:h2:mem:database;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;DATABASE_TO_UPPER=false;MODE=MySQL";

Is it possible to avoid usage of `` (backtick) symbols in every query against h2? And make queries compile without them?

Solution

  • Most likely name and/or value is a reserved keyword. There are hundreds, maybe thousands of such keywords. Just pick any other column name. Start trying namex and valuex just to check that's the problem, then think of a name that isn't a keyword.