Alex Rivera | Logout

How to load mysql dump to hsqldb database?

Asked 2010-09-28T10:46:20.350
16

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:
mysql groovy migration hsqldb
Edit
Report

1 Answer

13
  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();
answered 2010-09-28T13:26:38.437

Your Answer