CODEWITHSUNDEEP
javadatabasehsqldb
FrozenHeart
FrozenHeart

Reputation: 20746

HyperSQL - unexpected token ON

I'm trying to use HyperSQL in my Java application in the following way:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Main {

    static Connection conn;
    static Statement stat;

    public static void main(String[] args) {

        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver" );
        } catch (Exception ex) {
            System.out.println("An error occurred while loading HSQLDB JDBC driver: " + ex.getMessage());
            return;
        }

        try {

            conn = DriverManager.getConnection(
                    "jdbc:hsqldb:file:helper_db;sql.syntax_mys=true");

            stat = conn.createStatement();

            stat.executeUpdate(
                "CREATE TABLE IF NOT EXISTS some_table " +
                     "(" +
                        "foo TEXT PRIMARY KEY, " +
                        "bar TEXT" +
                    ");"
            );

            stat.executeUpdate(
                "INSERT INTO some_table VALUES" +
                        "('foo', 'bar') " +
                        "ON DUPLICATE KEY UPDATE some_table = VALUES" +
                        "('foo', 'bar');"
            );

        } catch (Exception ex) {

            System.out.println("An error occurred: " + ex.getMessage());
            return;

        }

    }
}

This code gives me the following output:

An error occurred: unexpected token: ON

What am I doing wrong? How to resolve this issue?

Upvotes: 0

Views: 2718

Answers (2)

Saurabh
Saurabh

Reputation: 139

The updated version of HSQL now supports ON DUPLICATE KEY UPDATE feature of MySQL.

Refer: http://hsqldb.org/doc/guide/guide.html#coc_compatibility_mysql

Upvotes: 0

user330315
user330315

Reputation:

HSQLDB does not support the ON DUPLICATE syntax (which is clearly documente in the manual).

You need to use MERGE instead assuming that there is at least one column in your values clause that is a unique key:

MERGE INTO some_table ut
USING (
  VALUES
    ('foo', 'bar')
) AS md (foo_column, bar_column) ON (ut.foo_column = md.foo_column)
WHEN MATCHED THEN UPDATE
     SET ut.bar_column = md.bar_column
WHEN NOT MATCHED THEN
  INSERT (foo_column, bar_column)
  VALUES (md.foo_column, md.bar_column);

Please check the manual for more details: http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_merge_statement

Upvotes: 2

Related Questions