CODEWITHSUNDEEP
javah2
nantitv
nantitv

Reputation: 3733

H2 database complaints about sql syntax error with a valid mysql query

I created an in-memory H2 database (http://www.h2database.com/html/features.html) as following

public static HikariConfig createHikariConfigH2(String schema, String pathToSchemaSql) {
    LOGGER.info("Creating schema {} and execute sql {}", schema, pathToSchemaSql);
    HikariConfig config = new HikariConfig();

    config.setJdbcUrl("jdbc:h2:mem:" + schema + ";"

            + "MODE=MySQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE"

            + ";DB_CLOSE_DELAY=-1" + ";INIT= drop schema if exists " + schema + " CASCADE\\;create schema " + schema
            + "\\;SET SCHEMA " + schema + "\\;"

            + "runscript from 'classpath:/" + pathToSchemaSql + "'");
    config.setUsername("sa");
    config.setPassword("");
    return config;
}

Then I try to run an sql query as follows 

HikariConfig config = createHikariConfigH2(“schema”, “a.sql”);
        try (HikariDataSource master = new HikariDataSource(config);
                Connection conn = master.getConnection();
                PreparedStatement stmt = conn.prepareStatement(
                        "DELETE mt FROM tableA AS mt,tableB AS et WHERE mt.id = et.id AND et._iud = 'D'")) {
            try (ResultSet result = stmt.executeQuery()) {
                List<String> tables = new ArrayList<>();
                while (result.next()) {
                    String tableName = result.getString(1);
                    tables.add(tableName);
                }
            }
        }

But it complaints about a syntax error but the same query works fine on normal mysql db.

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "delete mt from tableA as mt,[*]tableB as et where mt.id = et.id and et._iud = 'D' "; SQL statement:
DELETE mt FROM tableA AS mt,tableB AS et WHERE mt.id = et.id AND et._iud = 'D' [42000-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:451)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
    at org.h2.message.DbException.get(DbException.java:205)
    at org.h2.message.DbException.get(DbException.java:181)
    at org.h2.message.DbException.getSyntaxError(DbException.java:229)
    at org.h2.command.Parser.getSyntaxError(Parser.java:989)
    at org.h2.command.Parser.prepareCommand(Parser.java:686)
    at org.h2.engine.Session.prepareLocal(Session.java:627)

In the mysql compatibility section (http://www.h2database.com/html/features.html#compatibility) , I could not find anything related to this.

Could someone please help

Upvotes: 0

Views: 1944

Answers (1)

Karol Dowbecki
Karol Dowbecki

Reputation: 44960

It looks like H2 doesn't support syntax for deleting from more than one table, which is supported by MySQL. Take a look at this answer which shows how to do this with a subquery in DELETE. It should be something like:

DELETE FROM tableA 
WHERE id IN (
    SELECT id 
    FROM tableB 
    WHERE _iud = 'D'
)

Upvotes: 4

Related Questions