Problem with stored procedure names

["Hammonds, Nicholas" <Ni...@thus.net>]

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

Re: Problem with stored procedure names

[Rick Hillegas <Ri...@Sun.COM>]

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
>