CODEWITHSUNDEEP
mysqlspring-boothsqldb
RogerParkinson
RogerParkinson

Reputation: 569

how to use hsqldb and SET DATABASE SQL REGULAR NAMES

I have the usual need to mock a production database with an in memory one. Production is mysql and I'm trying to use hsqldb to mock it, using Spring Boot to handle the substitution. All that works nicely, including finding my flyway scripts and running them. The problem I have comes during that run of the flyway scripts. Someone in the past decided to call one of the mysql tables "_show", and mysql seems happy enough with that. Hsqldb is not happy. I found references to some settings that claim to solve this but they don't seem to make any difference. This is what my script looks like:

SET DATABASE SQL NAMES FALSE
SET DATABASE SQL REGULAR NAMES FALSE

CREATE TABLE _show (
  id bigint(20) NOT NULL,
...

With this in place I get:

org.hsqldb.HsqlException: unexpected token: _SHOW

when flyway tries to execute the script. I tried putting double quotes around the table name and got a different error this time about the open bracket, and I tried experimenting with backslashes to quote the underbar. No luck. I think I must be misusing the SET DATABASE... flags somehow but I can't find any examples of their use, just docs which suggest I am doing this right.

I'm using org.hsqldb:hsqldb:2.5.0

Thanks for any help

Upvotes: 0

Views: 211

Answers (1)

fredt
fredt

Reputation: 24352

The SET statements work fine in version 2.5.0 and allow the _SHOW table creation. The BIGINT(20) declaration is not compatible and should be BIGINT for HSQLDB.

You can use the HSQLDB Database Manager and execute the SQL manually, one statemant at a time, in a mem: database to see what is causing the issues. If the statements you reported are executed in one chunk, the SET command does not take effect before the CREATE is compiled.

Upvotes: 1

Related Questions