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
http://thejavablog.wordpress.com/category/hsqldb/
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.
Regards,
Mike