Posts

Showing posts from March, 2017

Populating stored procs into a HSQL DB

I recently encountered a problem trying to load stored procedures into a HSQL DB used for testing. The problem was caused by the script runner provided by spring which separates each statement to be executed in a script file by a semicolon. If a stored proc has statements inside it (which most do), then the proc isn't executed as a single statement. This is further compounded by each statement executed must be understandable by JDBC. For example the following stored proc causes issues: CREATE PROCEDURE MY_PROC(IN param1 VARCHAR(30), OUT out_param VARCHAR(100)) READS SQL DATA BEGIN ATOMIC SELECT the_value INTO out_param FROM my_table WHERE field = param1; END .; This problem is solved by using the script runners provided by HSQL in the "org.hsqldb:sqltool" dependency as they parse can correctly parse the scripts containing stored procedures. Here is a Spring Boot test, using an in memory database but using HSQL's script runners: @RunWith(SpringRunner.class