Hi Nicholas,
I don't have anything smarter to recommend than the tried-and-true
design pattern of a portability library. Here you would have an
interface which abstracts out the differences between the databases
which you have to support. In your case you would have two
implementations of this interface, an Oracle and a Derby implementation.
Armed with these implementations, you would then write code like the
following:
conn.prepareCall
( "call " + portabilityLibrary.procName(
"custom.ipa_post_message.ipa_post_axioss_response" ) + "( ?, ?, ?, ? )" );
For this particular problem, you really do seem to be wedged on the
difference between Oracle's 3-tiered namespace and Derby's 2-tiered
namespace.
Hope this helps,
-Rick
Hammonds, Nicholas wrote:
Hello all,
I have a third party stored procedure I need to call,
custom.ipa_post_message.ipa_post_axioss_response
I'm using apache derby as a in memory database for the purposes of unit
testing.
In order to call the procedure on apache derby you must put double
quotes around the procedure name as follows
addMessage = (CallableStatement)connection.prepareCall(
"{call
\"custom.ipa_post_message.ipa_post_axioss_response\"(?,?,?,?,?)}");
Without the double quotes apache derby complains about a syntax error at
the second the '.'.
Now Oracle 9i wants things the other way round
If I call the procedure on oracle9i as follows
addMessage = (CallableStatement)connection.prepareCall(
"{call
\"custom.ipa_post_message.ipa_post_axioss_response\"(?,?,?,?,?)}");
It barfs with ' caught SQLException: ORA-06550: line 1, column 7:
PLS-00114: identifier 'custom.ipa_post_message.ipa_po' too long'
The way to get around that oracle exception is to remove the double
quotes around the stored procedure name, in otherwords:
addMessage = (CallableStatement)connection.prepareCall(
"{call
custom.ipa_post_message.ipa_post_axioss_response(?,?,?,?,?)}");
This is obviously not ideal for unit testing as I want my code that
speaks to the DB to be the same whether it is speaking to apache derby
oracle 9i.
Anyone know of a way so I can call that stored procedure in the same way
on apache derby and oracle.
Many Thanks
Nic
