Hsqldb存储过程 [英] Hsqldb Stored Procedure

问题描述

我试图在内存中的Hsqldb数据库中设置存储过程以进行测试.我正在使用的存储过程是在MySql中开发的，因此我想使用HSqlDb对其进行设置以适合我的测试套件

I am trying to setup a stored procedure in my in memory Hsqldb database for testing purposes. The stored proc I am working from is developed in MySql so I want to set it up with HSqlDb to fit in with my testing suite

我正在尝试创建该过程的简化版本，但到目前为止还没有任何乐趣.

I am trying to create a simplified version of the procedure but having no joy as of yet.

过程是

     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;

运行测试时遇到的错误如下

The error I am getting when running my tests is as follows

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之后不需要分号.该错误消息有点误导人.

You don't need a semicolon after END IF and END. The error message is a little misleading.

编辑 您可能还需要将CREATE PROCEDURE...文本中的分号加倍，因为Spring本身也认为分号也是语句分隔符.

EDIT You may also need to double the semicolons in your CREATE PROCEDURE... text since Spring itself considers semicolon to be statement separator as well.

这篇关于Hsqldb存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！