java – Hsqldb存储过程

我正在尝试在内存Hsqldb数据库中设置存储过程以进行测试.我正在使用的存储过程是在MySql中开发的,所以我想用HSqlDb设置它以适应我的测试套件我正在尝试创建一个简化版本的程序,但尚未获得任何欢乐.程序是CREATE PR...

我正在尝试在内存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: ;

解决方法:

END IF和END后不需要分号.错误消息有点误导.

编辑
您可能还需要在CREATE PROCEDURE …文本中加倍分号,因为Spring本身也将分号视为语句分隔符.