It's not a secret anymore!
The moose likes Spring and the fly likes Unit testing StoredProcedure with HSQLDB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Frameworks » Spring
Bookmark "Unit testing StoredProcedure with HSQLDB" Watch "Unit testing StoredProcedure with HSQLDB" New topic

Unit testing StoredProcedure with HSQLDB

Mike Wither

Joined: Mar 25, 2010
Posts: 2
Hi guys, wasn't sure where to put this question since it probably as much a HSQLDB question as a Spring one. Anyway,
following this article

I decided I want to unit test my StoredProcedure classes, by using an in-memory database like HSQLDB and using the "CREATE ALIAS" function to
do this. My stored procedures are nothing special, one input parameter and one output in one case or no inputs and one output in another.

However, when I create the "mock" stored procedure (a Java class) as described above and run the test against HSQL, I get the following exception:

org.springframework.dao.TransientDataAccessResourceException: CallableStatementCallback; SQL [{call GET_VERSION(?)}]; S1000 General error java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 0 in statement [ call GET_VERSION(?) ]; nested exception is java.sql.SQLException: S1000 General error java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 0 in statement [ call GET_VERSION(?) ] at at at at org.springframework.jdbc.core.JdbcTemplate.execute( at at org.springframework.jdbc.object.StoredProcedure.execute( at test.storedprocedure.GetVersionStoredProcedure.execute( at test.storedprocedure.GetVersionStoredProcedureTest.testExecute( at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke( at sun.reflect.DelegatingMethodAccessorImpl.invoke( at java.lang.reflect.Method.invoke( at junit.framework.TestCase.runTest( at junit.framework.TestCase.runBare( at junit.framework.TestResult$1.protect( at junit.framework.TestResult.runProtected( at at at junit.framework.TestSuite.runTest( at at at at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests( at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests( at at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main( Caused by: java.sql.SQLException: S1000 General error java.lang.ArrayIndexOutOfBoundsException: Array index out of range: 0 in statement [ call GET_VERSION(?) ] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcCallableStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareCall(Unknown Source) at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement( at org.springframework.jdbc.core.JdbcTemplate.execute( ... 22 more

Now if I run my code against an Oracle database with some mocked up procedures this works fine?!
The class that mocks the stored procedure just returns a string, it is called test.storedprocedure.mock.Version
so for the hsqldb testing I create an alias by saying "CREATE ALIAS GET_VERSION FOR \"test.storedprocedure.mock.Version.getVersion\""

I did some further testing, so instead of calling the StoredProcedure execute method, I decided to get the JdbcTemplate and call execute
with the command being "call GET_VERSION()". This works FINE in HSQLDB!
However, if I call "call GET_VERSION(?)" on the template (which is what happens when executing the stored proc) I get the error from above!

Is there any explanation ot this? Am I doing something wrong? Is this a HSQLDB quirk?

Sorry I can't show the code right now since I am at a different computer but if you need it (or need more information) let me know and I will get it.


Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

If I am correct HSQLDB doesn't have stored procedures. So if you even send a command to that database the looks like a SP call and not SQL that it will fail like that.

But I am not 100% positive. and I could be wrong.


Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
Mike Wither

Joined: Mar 25, 2010
Posts: 2
Hi, thanks for that.

Apparently, HSQLDB (according to the link I posted above) DOES support at least mocking stored procedures as Java classes. I did that and when I call the stored procedure via the Spring StoredProcedure API I get the exception above.

I also get the exception if I call getJdbcTemplate().execute("call GET_VERSION(?)");

BUT, I do NOT get the exception if I call getJdbcTemplate().execute("call GET_VERSION()"); --> it actually works fine!

Now, obviously, I would like to get the working behaviour, but by using the StoredProcedure API.

Anyway, if this doesn't work out, is there a way of unit testing a stored procedure class (which extends from the Spring StoredProcedure) without having to connect
to Oracle or another non-in-memory database?

Jon Luckcuck

Joined: May 12, 2010
Posts: 1
I had the same issue. It's to do with the way Spring builds-up the callable statement. When you declare your IN / OUT parameters, it adds '?' to the query string. Works fine using the pure JDBC CallableStatement. I actually ended up writing stub stored procedures, and injected them into HSQLDB before my tests started to run. Interestingly HSQLDB 2.x is moving away from calling Java classes, and their support for fully functional stored procedures is much improved in 2.x. Only thing that is lacking is the use of ResultSets and Cursors within Stored Procedures But if you're just looking to return single values (and not rows), then I'd advise moving to the latest HSQLDB 2.x and just write some stub stored procedures.
I agree. Here's the link:
subject: Unit testing StoredProcedure with HSQLDB
It's not a secret anymore!