title: Create a stored procedure in HSQLDB with Spring embedded databases API
body: <p>I am currently trying to create a testing environment using an in-memory HSQLDB instance, created using Spring, thanks to its embedded databases support:</p>
  
  <h2>Current set up</h2>
  
  <ol>
  <li><p>Creation of the "data source" for my unit tests:</p>
  
  <blockquote>
  <pre><code>db = new EmbeddedDatabaseBuilder()
           .addDefaultScripts()
           .addScript("stored_procedure.sql")
           .build();
  </code></pre>
  </blockquote></li>
  <li><p>Content of "stored_procedure.sql":</p>
  
  <blockquote>
  <pre><code>-- 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
  </code></pre>
  </blockquote></li>
  </ol>
  
  <h2>Problem</h2>
  
  <p>I am able to initialize my schema and insert my testing data from the "default scripts", without any issue.</p>
  
  <p>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:</p>
  
  <blockquote>
  <pre><code>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)
  </code></pre>
  </blockquote>
  
  <h2>Resources and hints</h2>
  
  <p>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.</p>
  
  <p>However, discussions like:</p>
  
  <ul>
  <li><a href="http://sourceforge.net/projects/hsqldb/forums/forum/73674/topic/4542791" rel="noreferrer">This one</a></li>
  <li>Spring's JIRA ticket SPR-8817</li>
  </ul>
  
  <p>make me think it may be an incompatibility between HSQLDB and Spring / an error caused by the way delimiters are processed.</p>
  
  <p>But I am a beginner at both Spring and HSQLDB, hence my below questions.</p>
  
  <h2>Questions</h2>
  
  <ol>
  <li><p>Has someone ever seen this before? And came up with a solution?</p></li>
  <li><p>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 <em>{call GetFooById ?}</em> ?</p></li>
  <li><p>Could this effectively happen because of ResourceDatabasePopulator ? Is ResourceDatabasePopulator still causing troubles in Spring 3.1.1 ?</p></li>
  <li><p>Any other pointer / hint?</p></li>
  </ol>
  
  <p>Thank you very much in advance for your help.</p>
  
  <p>M.</p>
  
  <hr>
  
  <p>EDIT:</p>
  
  <h2>Conclusion &amp; Solutions</h2>
  
  <p><strong>Problems</strong>:</p>
  
  <ol>
  <li><p>ResourceDatabasePopulator processes semicolons as query delimiters, which is not compliant with HSQLDB's syntax.</p></li>
  <li><p><strong>{call GetFooById ?}</strong> is a valid syntax for Sybase (my production database) but not for HSQLDB, which expects <strong>{call GetFooById(?)}</strong>. And of course the HSQLDB syntax isn't compatible with Sybase's one either. Besides, Spring's JdbcTemplate doesn't abstract away these differences.</p></li>
  </ol>
  
  <p><strong>Solutions</strong>:</p>
  
  <ol>
  <li><p>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.</p></li>
  <li><p>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.</p></li>
  </ol>
  
  <p><strong>Source code</strong>: Available on <a href="https://github.com/marccarre/spring-db-testing" rel="noreferrer">my GitHub repository</a>.</p>