Mock Stored procedures with Spring Boot and HSQLDB

Question

I am working on a Spring Boot project with HSQLDB as in-memory database for my tests. The reason why I decided to use HSQLDB over h2 is that my datasource is quite old and contains a lot of queries with multiple OUT parameters that H2 cannot handle

While I am able to run using JPA queries with multiple OUT parameters on HSQL, I need now to Mock some of the stored procedures.

In H2 is possible as you can see for instance here. Is it possible to mock stored procedures on HSQLDB? So far I could find only some really old articles (2011) and nothing in the HSQLDB documentation itself.

Answer 1

HSQLDB supports stored procedures with OUT parameters. You can write the procedure in SQL or Java. An example from the documentation below:

CREATE PROCEDURE new_customer(OUT newid INT, IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
   MODIFIES SQL DATA
   BEGIN ATOMIC
     DECLARE temp_id INTEGER;
     INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
     SET temp_id = IDENTITY();
     INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
     SET newid = temp_id;
   END

You can find the above example, as well as an example written in Java in the Guide. http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html