HSQLDB Trigger Statement ERROR when using SimpleJdbcTestUtils.executeSqlScript()

Question

I'm currently trying to load a sql script to create a HSQL database. This is done by using the following code:

Resource resource = new ClassPathResource("/create-table.sql");        
SimpleJdbcTestUtils.executeSqlScript(template, resource, Boolean.FALSE);

The script contains the create statement of a trigger:

CREATE TRIGGER t BEFORE UPDATE ON SUBJECTS 
REFERENCING NEW AS newrow OLD AS oldrow
FOR EACH ROW
BEGIN ATOMIC
    SET newrow.VERSION = oldrow.VERSION + 1;
END;

When running the tests using this code, the following error occurs:

Caused by: java.sql.SQLException: Unexpected end of command: REFERENCING in statement   
[CREATE TRIGGER t BEFORE UPDATE ON SUBJECTS REFERENCING]
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 org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:508)
at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)

I'm using Spring 3.0.5 and HSQLDB (driver,...) version is 1.8.0.10.

Has anyone ever had this problem or knows how to solve this?

(I also tried to place everything on one line, placed the sql in a separate file, removed semicolons, ...)

Any help will be much appreciated. Thanx in advance!

Wendy.

score 3 · Accepted Answer · edited Mar 22 '12 at 20:42

3

The definition of the trigger wasn't completely correct. The solution became a mix of the given answers:

Update version of HSQLDB to 2.2.8 as @fredt suggested. I changed the create script a bit:

CREATE TRIGGER t BEFORE UPDATE ON SUBJECTS 
REFERENCING NEW AS newrow OLD AS oldrow
FOR EACH ROW
    SET newrow.VERSION = oldrow.VERSION + 1;

Now there are no complaints about the semicolon etc.

Thanx for the help @Adi and @fredt!

Wendy

edited Mar 22 '12 at 20:42

Christian Conti-Vock

252
1
3
15

answered Mar 07 '12 at 08:59

JavaLady

51
1
6

yes, "referencing new" does not work in HSQL 1.8 and "early versions of 2.0" - just stumbled on this too! beware! – alexandroid Dec 13 '12 at 18:48

score 0 · Answer 2 · edited May 23 '17 at 12:06

Change

SET newrow.VERSION = oldrow.VERSION + 1;

END;

to

SET newrow.VERSION = oldrow.VERSION + 1;END;

SimpleJdbcTestUtils assumes ';' as delimiter if it is present in the script. So if you have single statement with multiple ';', you need to make sure there are no new line character(\n) after ';'.

I suggest you to abandon SimpleJdbcTestUtils if your statements have multiple ';' or if your script contains sql comments(try it for fun). For alternative check this and I prefer this approach if you do not mind adding ant library.

score 0 · Answer 3 · answered Mar 03 '12 at 11:08

0

You need to use the latest version of HSQLDB (currently 2.2.8). The older versions do not support this form of CREATE TRIGGER.

When you have changed the HSQLDB jar, follow Adi's recommendation for the issue with ';'.

answered Mar 03 '12 at 11:08

fredt

24,044
3
40
61

HSQLDB Trigger Statement ERROR when using SimpleJdbcTestUtils.executeSqlScript()

3 Answers3

Linked