Geek Answers Handbook

Hsqldb stored procedure

I am trying to set up a stored procedure in my Hsqldb database in memory for testing purposes. The saved proc process I'm working with is developed in MySql, so I want to configure it using HSqlDb to fit into my test suite

I am trying to create a simplified version of the procedure, but so far I have no joy.

Procedure

     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 get while 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: ;
+4
source share
3 answers

You do not need a semicolon after END IFand END. The error message is a little misleading.

CREATE PROCEDURE..., Spring , .

0

- stackoverflow HSQL Create Procedure

  • hsqldb sql 2 . 1, ,

  • <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;
/;
+3

This is the same as stated in one @Damo, but avoids the XML configuration.

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
+1
source

More articles:


All Articles