Hsqldb存储过程

Question

我试图在我的内存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: ;

Answer 1

在END IF和END之后不需要分号。错误信息有点误导。

编辑您可能还需要在创建过程中将分号加倍 文本，因为Spring本身认为分号也是语句分隔符。

Answer 2

此配置与@Damo指定的配置相同，但避免使用XML配置。

ScriptUtils.executeSqlScript(ds.getConnection(),  new EncodedResource(
                new InputStreamResource(getClass().getClassLoader().getSystemResourceAsStream("procs.sql"))
                ), false, false, "--", "/;", "--", "--");

这是方法签名：

void org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(Connection connection, EncodedResource resource, boolean continueOnError, boolean ignoreFailedDrops, String commentPrefix, String separator, String blockCommentStartDelimiter, String blockCommentEndDelimiter) throws ScriptException

Answer 3

我终于得到了答案，感谢下面的stackoverflow链接HSQL创建过程语法似乎不匹配留档

为了完整，这就是我所做的

>

我将我的hsqldb sql代码分为两个文件。一个是创建表和数据语法，另一个是存储过程

加载此文件时，我能够指定要使用的分隔符

<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处理分号；作为语句的结尾，因此我可以为存储的procs文件指定不同的分隔符

更新了我的存储过程以使用新的分离器，现在一切按预期工作

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