Mike Wither

Greenhorn
+ Follow
since Mar 25, 2010
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Mike Wither

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?


Thanks!
14 years ago
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
14 years ago