使用SimpleJdbcTestUtils.executeSqlScript()时，HSQLDB触发语句错误 [英] HSQLDB Trigger Statement ERROR when using SimpleJdbcTestUtils.executeSqlScript()

问题描述

我当前正在尝试加载SQL脚本来创建HSQL数据库. 这是通过使用以下代码完成的:

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);

该脚本包含触发器的create语句:

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)

我正在使用Spring 3.0.5，而HSQLDB(驱动程序，...)的版本是1.8.0.10.

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?

(我还尝试将所有内容放在一行上，将sql放在一个单独的文件中，删除了分号，...)

(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!

温迪.

推荐答案

触发器的定义不完全正确. 解决方案混合了给定的答案:

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

按照@fredt的建议将HSQLDB的版本更新为2.2.8. 我稍微修改了创建脚本:

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.

感谢@Adi和@fredt的帮助！

Thanx for the help @Adi and @fredt!

温迪

这篇关于使用SimpleJdbcTestUtils.executeSqlScript()时，HSQLDB触发语句错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！