db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Problem with stored procedure names
Date Thu, 01 May 2008 21:24:40 GMT
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
>   


Mime
View raw message