CODEWITHSUNDEEP
mysqlgroovymigrationhsqldb
Skarab
Skarab

Reputation: 7141

How to load mysql dump to hsqldb database?

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

Upvotes: 16

Views: 13019

Answers (5)

narko
narko

Reputation: 3875

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.

Upvotes: 0

Florian Lopes
Florian Lopes

Reputation: 1289

Solved this problem using IntelliJ IDEA :

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

Upvotes: 0

Yemi Kudaisi
Yemi Kudaisi

Reputation: 163

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.

Upvotes: 0

Skarab
Skarab

Reputation: 7141

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

  7. 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();

Upvotes: 13

Antoine
Antoine

Reputation: 4734

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 ,

Upvotes: 7

Related Questions