我正在尝试在内存 Hsqldb 数据库中设置一个存储过程以进行测试。我正在使用的存储过程是在 MySql 中开发的,因此我想使用 HSqlDb 设置它以适应我的测试套件
我正在尝试创建该过程的简化版本,但目前还没有任何乐趣。
程序是
CREATE PROCEDURE p_recordTaskExecution(IN userTaskId INT, IN isSuccess BOOLEAN, IN statusMessage VARCHAR(2000), IN operationsPerformed INT, INOUT procedureStatus BOOLEAN)
BEGIN ATOMIC
IF userTaskId = 1 Then
set procedureStatus = true;
ELSE
set procedureStatus = false;
END IF;
END;
运行测试时出现的错误如下
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 3 of resource class path resource [databaseTesting/inMemory/createInMemoryDatabase.sql]: CREATE PROCEDURE p_recordTaskExecution(IN userTaskId INT, IN isSuccess BOOLEAN, IN statusMessage VARCHAR(2000), IN operationsPerformed INT, INOUT procedureStatus BOOLEAN) BEGIN ATOMIC if userTaskId = 1 Then set procedureStatus = true; nested exception is java.sql.SQLSyntaxErrorException: unexpected end of statement: required: ;
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:475)
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:229)
at org.springframework.jdbc.datasource.init.CompositeDatabasePopulator.populate(CompositeDatabasePopulator.java:60)
at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48)
at org.springframework.jdbc.datasource.init.DataSourceInitializer.execute(DataSourceInitializer.java:108)
at org.springframework.jdbc.datasource.init.DataSourceInitializer.afterPropertiesSet(DataSourceInitializer.java:93)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1625)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1562)
... 40 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected end of statement: required: ;
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:460)
... 47 more
Caused by: org.hsqldb.HsqlException: unexpected end of statement: required: ;
感谢以下 stackoverflow 链接,我终于得到了答案HSQL 创建过程语法似乎与文档不匹配 https://stackoverflow.com/questions/9157027/hsql-create-procedure-syntax-doesnt-seem-to-match-the-documentation
为了完整起见,这就是我所做的
我将 hsqldb sql 代码分成 2 个文件。一个仅使用创建表和数据语法,另一个使用存储过程
-
加载此文件时,我能够指定要使用的分隔符
<jdbc:initialize-database data-source="mctDBDataSource" ignore-failures="DROPS" >
<jdbc:script location="classpath:databaseTesting/inMemory/createInMemoryDatabase.sql" separator=";"/>
<jdbc:script location="classpath:databaseTesting/inMemory/createInMemoryDatabaseProcedures.sql" separator="/;"/>
</jdbc:initialize-database>
。
正如前面指出的,Spring 对待分号;作为语句的结尾,这样我就可以为我的存储过程文件指定不同的分隔符
-
更新了我的存储过程以使用新的分隔符,现在一切都按预期工作
CREATE PROCEDURE p_recordTaskExecution(IN userTaskId BIGINT, IN isSuccess BOOLEAN, IN statusMessage VARCHAR(2000), IN operationsPerformed BIGINT, INOUT procedureStatus BOOLEAN)
BEGIN ATOMIC
IF userTaskId = 1 Then
SET procedureStatus = true;
ELSE
SET procedureStatus = false;
END IF;
END;
/;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)