I am currently trying to create a testing environment using an in-memory HSQLDB instance, created using Spring, thanks to its embedded databases support:
Creation of the "data source" for my unit tests:
db = new EmbeddedDatabaseBuilder() .addDefaultScripts() .addScript("stored_procedure.sql") .build();
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
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)
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:
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.
Has someone ever seen this before? And came up with a solution?
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 ?} ?
Could this effectively happen because of ResourceDatabasePopulator ? Is ResourceDatabasePopulator still causing troubles in Spring 3.1.1 ?
Any other pointer / hint?
Thank you very much in advance for your help.
M.
EDIT:
Problems:
ResourceDatabasePopulator processes semicolons as query delimiters, which is not compliant with HSQLDB's syntax.
{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:
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.
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.
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.
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 :-).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With