• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
  • Tim Holloway
  • Carey Brown
  • salvin francis

Unit testing StoredProcedure with HSQLDB

Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:106) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:952) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:985) at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:117) at test.storedprocedure.GetVersionStoredProcedure.execute(GetVersionStoredProcedure.java:30) at test.storedprocedure.GetVersionStoredProcedureTest.testExecute(GetVersionStoredProcedureTest.java:19) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:618) at junit.framework.TestCase.runTest(TestCase.java:164) at junit.framework.TestCase.runBare(TestCase.java:130) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:120) at junit.framework.TestSuite.runTest(TestSuite.java:230) at junit.framework.TestSuite.run(TestSuite.java:225) at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) 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(CallableStatementCreatorFactory.java:167) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:930) ... 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.


Posts: 17344
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Mike Wither
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
We don't have time for this. We've gotta save the moon! Or check this out:
Java file APIs (DOC, XLS, PDF, and many more)
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!