How to load mysql dump to hsqldb database?

Question

I have a sql file that creates a database in mysql:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`machine`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`machine` (
  `id` INT NOT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) );


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Now I would like to load this file into hsqldb 2 database. What do I need to change in the mysql dump to load the data into hsqldb?

Currently I use this code (groovy) to execute the sql file:

def embeddedDbSettings = [url:'jdbc:hsqldb:file:mydb', user:'sa', password:'', driver:'org.hsqldb.jdbcDriver'];
sql =  Sql.newInstance(embeddedDb);
sql.executeInsert new File("./sql/create_database.sql").text;

and all the time I got this crypting exception:

Exception in thread "main" java.sql.SQLException: unknown token
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
    at groovy.sql.Sql.executeInsert(Sql.java:1440)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoCachedMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:229)
    at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:52)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
    at de.hpi.ecir.eval_script.Convert2Excel.main(Convert2Excel.groovy:37)
Caused by: org.hsqldb.HsqlException: unknown token
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserBase.read(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 13 more

score 13 · Accepted Answer · edited Oct 17 '12 at 15:18

Remove all SET lines
Change a line with command which creates a database to: CREATE SCHEMA mydb AUTHORIZATION DBA
Remove all if not exists - hsqldb does not support this command
Remove all commends (not neccesary but needed for the code you find in this post)
Remove all `
Replace TINYINT (mysql equivalent for boolean) by boolean

Execute each command separately:

String[] commands = new File("./sql/create_database.sql").text.split(";");

for(String command: commands)
{

 // new line is a delimiter in hsqldb

  sql.execute command.replace("\n", " ");
}

// remember to call shutdown otherwise hsqldb will not save your data
sql.execute "SHUTDOWN"
sql.close();

Afaik, TINYINT represents an 8-bit variable (range 0-255), not a boolean. — msteiger, Aug 13 '14 at 07:26

score 7 · Answer 2 · answered Oct 17 '11 at 08:45

You also have to :

replace "AUTO_INCREMENT" in CREATE_TABLE by "GENERATED BY DEFAULT AS IDENTITY"
replace "int" by "integer"
move "default" statement in column creation for example :

from this :

CT_CLIENT integer NOT NULL DEFAULT '0',

to this :

CT_CLIENT integer DEFAULT '0' NOT NULL ,

score 0 · Answer 3 · answered Feb 27 '15 at 09:06

0

Solved this problem using IntelliJ IDEA :

In the database tab, add a connection to your database (MySQL in this case)
Right-click on desired database and click on "Copy DDL".

answered Feb 27 '15 at 09:06

Florian Lopes

1,093
1
13
20

score 0 · Answer 4 · answered Oct 25 '15 at 10:05

I solved this issue by relying on RazorSQL. It is not for free, but with the evaluation version you have enough for performing the conversion from MySQL to HSQLDB. It also supports other DB conversions.

The only problem I detected during the conversion was the primary keys. So basically, the following generated code excerpt would not run for me:

CREATE TABLE items_fractions (
  id INTEGER IDENTITY NOT NULL,
  item_id INTEGER NOT NULL,
  fraction_id INTEGER NOT NULL,
  PRIMARY KEY (id)
);

I had to remove the IDENTITY bit.

score 0 · Answer 5 · edited Nov 13 '12 at 19:29

0

You don't have to run each command separately, hsqldb works fine if you run the scripts all at once, as long as all your tokens are valid.

edited Nov 13 '12 at 19:29

Baz

36,440
11
68
94

answered Aug 24 '11 at 06:56

Yemi Kudaisi

163
1
7

How to load mysql dump to hsqldb database?

5 Answers5