HsqlDB trigger syntax: expected;

Question

I use hsqldb for my unit tests. My production uses Oracle 11G Db. When I ran 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 really am an example of a trigger in HSQL docs .

I see this post : But its solution does not work for me, or I do not understand this.

I always have 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 ';', he continues to fail.

Here is my dependency on HSQL:

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

any ideas?

Answer 1

Solution in HSQL Create Procedure link The syntax does not seem to match the documentation found in this configuration line:

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

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