limitation in stored procedure names

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

I am using apache derby in my unit testing.  Consequently I need to
create some stored procedures that will basically do the minimum and
behave as expected.  Therefore I am restricted to using exactly the same
parameters and name for my stored procedures.

One such procedure I am trying to create is as follows

stat = con.createStatement();
stat.execute("CREATE PROCEDURE
custom.ipa_post_message.ipa_post_axioss_response(IN pin_activity_id INT,
IN pis_message_id INT, IN pis_response VARCHAR(50), IN pis_status
VARCHAR(50), OUT pis_return VARCHAR(50)) parameter style java language
java external name
'net.thus.unittest.DBHelper.storedProc_ipa_post_axioss_response'");

It complains about a syntax error at the position of the second '.' .
If I replace the second '.' with a '_' everything works fine.  As I say
I have no influence of the procedure names is this DB is run and
developed by a third party.  Anybody know of a fix/workaround for this?

Many Thanks
Nic

Re: limitation in stored procedure names

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

Hi Nic,

The "." is used to separate identifiers in valid SQL names. For a schema 
object like a procedure, the name can have only one dot in it. The piece 
before the dot identifies a  unique schema in the database and the piece 
after the dot identifies a unique procedure name inside that schema. The 
compiler doesn't know what to do with the second dot. The compiler 
doesn't know if you are trying to create a procedure named 
"CUSTOM.IPAPOST_MESSAGE.IPA_POST_AXIOSS_RESPONSE" in the current schema 
or a procedure named "IPA_POST_AXIOSS_RESPONSE" in the 
"CUSTOM.IPA.POST_MESSAGE" schema or a procedure named 
"IPA_POST_MESSAGE.IPA_POST_AXIOSS_RES" in the "CUSTOM" schema. If you 
double quote the whole name, then the compiler will not be confused:

CREATE PROCEDURE
"custom.ipa_post_message.ipa_post_axioss_response" ...

If you do this, then whenever you invoke the procedure, you must invoke 
it using the double quoted name. E.g.,:

CALL "custom.ipa_post_message.ipa_post_axioss_response" (...)

Hope this helps,
-Rick




Hammonds, Nicholas wrote:
> I am using apache derby in my unit testing.  Consequently I need to
> create some stored procedures that will basically do the minimum and
> behave as expected.  Therefore I am restricted to using exactly the same
> parameters and name for my stored procedures.
>
> One such procedure I am trying to create is as follows
>
> stat = con.createStatement();
> stat.execute("CREATE PROCEDURE
> custom.ipa_post_message.ipa_post_axioss_response(IN pin_activity_id INT,
> IN pis_message_id INT, IN pis_response VARCHAR(50), IN pis_status
> VARCHAR(50), OUT pis_return VARCHAR(50)) parameter style java language
> java external name
> 'net.thus.unittest.DBHelper.storedProc_ipa_post_axioss_response'");
>
> It complains about a syntax error at the position of the second '.' .
> If I replace the second '.' with a '_' everything works fine.  As I say
> I have no influence of the procedure names is this DB is run and
> developed by a third party.  Anybody know of a fix/workaround for this?
>
> Many Thanks
> Nic
>
>
>   

RE: limitation in stored procedure names

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

Brilliant that has solved my problems, and thanks for the good
explanation.

Nic ;)

-----Original Message-----
From: Hammonds, Nicholas 
Sent: Monday, April 21, 2008 5:04 PM
To: 'derby-user@db.apache.org'
Subject: limitation in stored procedure names


I am using apache derby in my unit testing.  Consequently I need to
create some stored procedures that will basically do the minimum and
behave as expected.  Therefore I am restricted to using exactly the same
parameters and name for my stored procedures.


One such procedure I am trying to create is as follows

stat = con.createStatement();
stat.execute("CREATE PROCEDURE
custom.ipa_post_message.ipa_post_axioss_response(IN pin_activity_id INT,
IN pis_message_id INT, IN pis_response VARCHAR(50), IN pis_status
VARCHAR(50), OUT pis_return VARCHAR(50)) parameter style java language
java external name
'net.thus.unittest.DBHelper.storedProc_ipa_post_axioss_response'");

It complains about a syntax error at the position of the second '.' .
If I replace the second '.' with a '_' everything works fine.  As I say
I have no influence of the procedure names is this DB is run and
developed by a third party.  Anybody know of a fix/workaround for this?

Many Thanks
Nic