Search code examples
hsqldbflyway

Stored procedure can not be parsed during migration

I'm using the api of flyway 1.7 for migrating a hsql database. I have some problems with stored procedures. The following script works fine with hsql database manager:

CREATE TABLE CUSTOMER (
  firstname VARCHAR(50),
  lastname VARCHAR(50),
  mod_ts TIMESTAMP
);

CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50), address VARCHAR(100))
  MODIFIES SQL DATA
    BEGIN ATOMIC
    INSERT INTO CUSTOMER VALUES (firstname, lastname, CURRENT_TIMESTAMP);
  END

But I think during a flyway migration the script is not parsed correctly (it looks like flyway believes the second statement ends after the semicolon).

Here is the corresponding log output:

DEBUG 2012-11-22 14:16:00,232 [main] (SqlScript.java:161) - Found statement at line 1: create table CUSTOMER (
firstname varchar(50),
lastname varchar(50),
mod_ts timestamp
);
DEBUG 2012-11-22 14:16:00,232 [main] (SqlScript.java:161) - Found statement at line 7: CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50), address VARCHAR(100))
MODIFIES SQL DATA
BEGIN ATOMIC
INSERT INTO CUSTOMER VALUES (firstname, lastname, CURRENT_TIMESTAMP);
DEBUG 2012-11-22 14:16:00,232 [main] (SqlStatement.java:75) - Executing SQL: create table CUSTOMER (
firstname varchar(50),
lastname varchar(50),
mod_ts timestamp
)
DEBUG 2012-11-22 14:16:00,232 [main] (SqlStatement.java:75) - Executing SQL: CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50), address VARCHAR(100))
MODIFIES SQL DATA
BEGIN ATOMIC
INSERT INTO CUSTOMER VALUES (firstname, lastname, CURRENT_TIMESTAMP)
ERROR 2012-11-22 14:16:00,232 [main] (DbMigrator.java:231) - com.googlecode.flyway.core.exception.FlywayException: Error executing statement at line 7: CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50), address VARCHAR(100))
MODIFIES SQL DATA
BEGIN ATOMIC
INSERT INTO CUSTOMER VALUES (firstname, lastname, CURRENT_TIMESTAMP)
ERROR 2012-11-22 14:16:00,232 [main] (DbMigrator.java:236) - Caused by org.hsqldb.HsqlException: unexpected end of statement:  required: ; : line: 4

Is there a way to solve this problem?

EDIT I just made an interesting observation. If I use the flyway command-line program, then it works perfectly.

Solution

  • Upgrade to Flyway 2.0. It added support for BEGIN ATOMIC blocks in Hsql.