Damo Damo - 4 months ago 61
Java Question

Hsqldb Stored Procedure

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

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.

Comments