0

I am developing Java Vert.x 3 application. I use HSQLDB for testing with in-memory DB and MySQL 8.0.20 for runtime. When the vertx verticle is deployed, it initializes the db and tables. Since this is a common code and there are differing SQL syntax between HSQLDB and MySQL and more ridiculously, the HSQLDB capitalizes all the property names and I have to double-quote the properties to use lower-case. I wonder how to achieve this. Here are my questions:

(1) HSQLDB uses "IDENTITY" keyword for creating the in-memory database table. This results in runtime error in MySQL DB as "IDENTITY" is not valid keyword. This poses a challenge that I am facing now.

(2) If it is not possible to have a common SQL syntax which satisfies both MySQL and HSQLDB, what's the best approach to split this common execution path based on the java application runtime profile since this DB initialization is done in the start function of the verticle which is the core of the application?

Any advice and insight is appreciated.

Kok How Teh
  • 3,298
  • 6
  • 47
  • 85

2 Answers2

1

You can create the HSQLDB database with MySQL compatibility mode (append ;sql.syntax_mys=true to the JDBC URL. In this mode, you can do the following:

Use MySQL syntax for CREATE TABLE. HSQLDB understands MySQL's AUTO_INCREMENT keyword as an alias for IDENTITY. It also understands all other MySQL-specific syntax.

The capitalization of column names is really not a problem. With the column names capitalized, you can use any case without quoting in your SELECT statements. This means you can run the same query that you use for MySQL on HSQLDB.

See http://hsqldb.org/doc/2.0/guide/compatibility-chapt.html#coc_compatibility_mysql

fredt
  • 24,044
  • 3
  • 40
  • 61
  • I don't think so. How do you deserialize the `select` statement result set to a list of JOCO? CAPITALIZE COLUMN NAMES IS A PROBLEM! – Kok How Teh Jul 03 '20 at 10:53
  • OK, if you need the non-capitalized names in the returned ResultSetMetaData then you do need to use double-quotes for the names. – fredt Jul 03 '20 at 12:21
0

Solution: Ditch HSQLDB and use H2 with database_to_upper=false option.

Kok How Teh
  • 3,298
  • 6
  • 47
  • 85