Create a stored procedure in HSQLDB with Spring embedded databases API

Question

I am currently trying to create a testing environment using an in-memory HSQLDB instance, created using Spring, thanks to its embedded databases support:

Current set up

1. Creation of the "data source" for my unit tests:

   db = new EmbeddedDatabaseBuilder()
            .addDefaultScripts()
            .addScript("stored_procedure.sql")
            .build();
   

2. Content of "stored_procedure.sql":

   -- Mock of a more complex stored procedure in production environment
   CREATE PROCEDURE GetFooById(IN fooId VARCHAR(12))
     READS SQL DATA DYNAMIC RESULT SETS 1
     BEGIN ATOMIC
         DECLARE resultSet CURSOR WITHOUT HOLD WITH RETURN FOR SELECT name, value FROM Foos WHERE id = fooId;
         OPEN resultSet; 
     END
   

Problem

I am able to initialize my schema and insert my testing data from the "default scripts", without any issue.

However, when creating the procedure, I am seeing errors like the one below, even after different versions of the above SQL, with/without delimiters, and with delimiters in various positions:

java.sql.SQLSyntaxErrorException: unexpected end of statement:  required: ;
  at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
  at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
  at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
  at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
  at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:169)

Resources and hints

I am using Spring 3.1.1 which provides HSQLDB 2.2.4, and based on the documentation, I would think I am doing the right thing.

However, discussions like:

• This one
• Spring's JIRA ticket SPR-8817

make me think it may be an incompatibility between HSQLDB and Spring / an error caused by the way delimiters are processed.

But I am a beginner at both Spring and HSQLDB, hence my below questions.

Questions

1. Has someone ever seen this before? And came up with a solution?

2. Is there an equivalent way of returning a result set using a select-from-where query in HSQLDB, given that the final stored procedure is called using {call GetFooById ?} ?

3. Could this effectively happen because of ResourceDatabasePopulator ? Is ResourceDatabasePopulator still causing troubles in Spring 3.1.1 ?

4. Any other pointer / hint?

Thank you very much in advance for your help.

M.

---

EDIT:

Conclusion & Solutions

Problems:

1. ResourceDatabasePopulator processes semicolons as query delimiters, which is not compliant with HSQLDB's syntax.

2. {call GetFooById ?} is a valid syntax for Sybase (my production database) but not for HSQLDB, which expects {call GetFooById(?)}. And of course the HSQLDB syntax isn't compatible with Sybase's one either. Besides, Spring's JdbcTemplate doesn't abstract away these differences.

Solutions:

1. Using Java stored procedures instead of SQL stored procedures can be a workaround as the query is written on the Java side and no semi-colon delimiters are involved. Alternatively, I guess ResourceDatabasePopulator's logic could be changed and used to set up the embedded database, but I haven't tried this so far.

2. Spring's StoredProcedure class seems much more portable and can be used with both Sybase and HSQLDB, even if it is a bit more verbose than JdbcTemplate.

Source code: Available on my GitHub repository.

Answer 1

1. for stored procedures and object types you take care about JDBC driver of your database. For stored procedure, also for testing or other, create a package inside your database and call this for future test phase.

2. I used stored procedures and customization input/output object, using only Java SQL Stored procedure API, and customization where needed.

If you want explain your context and case, we can help you to find your best solution :-).