Hsqldb Stored Procedure

Question

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.

The procedure is

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

Answer 1

I finally got the answer thanks to the following stackoverflow link HSQL Create Procedure Syntax doesn't seem to match the documentation

For completeness here is what I did

1. I separated out my hsqldb sql code into 2 files. 1 with just create table and data syntax and the other with stored procedures

2. When loading in this files I was able to specify the separator to use

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

   . As pointed out earlier, Spring treats the semi colon ; as the end of the statement so I am able to specify a different separator to use for my stored procs file

3. Updated my stored procedure to use the new separator and now everything works as expected

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

Answer 2

This one is same as to the one @Damo specified But avoids the XML config.

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

This is the Method signature:

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

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

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.