Trigger Syntax on HsqlDB : expected ;

Question

I use hsqldb for my unit tests. My production use Oracle 11G Db. When i run my start script as above:

<jdbc:embedded-database id="dataSource" type="HSQL">
</jdbc:embedded-database>

<jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
  <jdbc:script location="classpath:/sql/init-cct-schema.sql" separator=";" />
  <jdbc:script location="classpath:/sql/init-cct-insert.sql" separator=";" />
</jdbc:initialize-database>

I am really quite the trigger example in HSQL docs.

I see this post: But his solution doesn't work for me, or I don't understand it.

I have always this error:

java.lang.IllegalStateException: Failed to load ApplicationContext
    at org.springframework.test.context.TestContext.getApplicationContext(TestContext.java:157)
    at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.injectDependencies(DependencyInjectionTestExecutionListener.java:109)
    at 
...
    ... 38 more
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 9 of resource class path resource [sql/init-cct-schema.sql]:  CREATE TRIGGER TI_TYPE_MVT BEFORE INSERT ON TYPE_MVT     REFERENCING NEW AS newrow FOR EACH ROW     BEGIN ATOMIC       IF newrow.TYPE_MVT_PK is null THEN         SET newrow.TYPE_MVT_PK = SQ_TYPE_MVT.nextval
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:199)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:132)
    at org.springframework.jdbc.datasource.init.CompositeDatabasePopulator.populate(CompositeDatabasePopulator.java:55)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:45)
    ... 41 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected end of statement:  required: ;
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:184)
    ... 44 more
Caused by: org.hsqldb.HsqlException: unexpected end of statement:  required: ;
    at org.hsqldb.error.Error.parseError(Unknown Source)

Here is my trigger:

SET DATABASE SQL SYNTAX ORA TRUE;    
CREATE TRIGGER TI_TYPE_MVT BEFORE INSERT ON TYPE_MVT
        REFERENCING NEW AS newrow FOR EACH ROW
        BEGIN ATOMIC
          IF newrow.TYPE_MVT_PK is null THEN
            SET newrow.TYPE_MVT_PK = SQ_TYPE_MVT.nextval;
          END IF;
        END;

I try without the final ';' , it's continue to fail.

Here is my dependancy on HSQL:

<dependency>
  <groupId>org.hsqldb</groupId>
  <artifactId>hsqldb</artifactId>
  <version>2.2.8</version>
</dependency>

any ideas?

Answer 1

The solution in the link HSQL Create Procedure Syntax doesn't seem to match the documentation is in this line of configuration:

<jdbc:script location="file:Artifacts/Hsql Version Scripts/install/install.sql" separator="/;"/>

By default, the separator used by the Spring script is the semicolon. This means when the first semicolon inside the trigger definition is reached, the incomplete definition is sent to HSQLDB (which results in the error). When you use the above configuration line, it changes the default separator to two characters "/;". Using the special configuration, you need to modify your script to have this separator at the end of each create trigger definition. Leave the semicolons inside the trigger definition body as they are.