Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create a stored procedure in HSQLDB with Spring embedded databases API

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.

like image 838
Marc Carré Avatar asked Apr 26 '12 17:04

Marc Carré


1 Answers

  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 :-).

like image 52
Giorgio Desideri Avatar answered Oct 12 '22 18:10

Giorgio Desideri