db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Regarding Stored Procedures
Date Tue, 16 Feb 2010 15:49:25 GMT
Hi Shazin,

Derby does not support SQL Routine overloading. The first unique index 
on SYSALIASES prevents you from declaring more than one function with a 
given name in a given schema (similarly for procedures).

However, it does appear that the code may have supported routine 
overloading in the past. I say this because CreateAliasConstantAction, 
in looking for name collisions, bothers to check the number of 
parameters in routine signatures. If you declare the following function...

create function addInts( arg1 int ) returns int
language java parameter style java no sql deterministic external name 
'z.addInts';

...then the following two declarations raise exceptions at different points:

-- Raises an exception inside CreateAliasConstantAction itself:
create function addInts( arg1 varchar( 10 ) ) returns int
language java parameter style java no sql deterministic external name 
'z.addInts';

-- Raises an exception when the unique index rejects a conflicting tuple:
create function addInts( arg1 int, arg2 int ) returns int
language java parameter style java no sql deterministic external name 
'z.addInts';

In addition, SYSALIASES has a SPECIFICNAME column, suggesting that there 
was once support for the SPECIFIC clause of the SQL Standard CREATE 
FUNCTION/PROCEDURE statements. The SPECIFIC clause is used to 
distinguish overloaded routine names when the database is confused. 
Today, SPECIFICNAME seems to always be stuffed with a system-generated 
value.

I worked on Cloudscape many years ago when routines were declared via a 
Cloudscape-specific CREATE METHOD ALIAS statement. Looking at the 
Cloudscape 3.5 reference manual, I see that back then the SYSALIASES 
table did not have a SPECIFICNAME column. Support for the SQL Standard 
CREATE FUNCTION/PROCEDURE statements was added after I left. Maybe 
someone who remained on Cloudscape can comment on whether routine 
overloading was added at some point and then removed later.

The details of the SQL Standard language for routine overloading are 
very involved. However, there's a readable summary in part 2, section 
4.27 (<SQL-invoked routines>):

"Different SQL-invoked routines can have equivalent <routine name>s. No 
two SQL-invoked functions in the
same schema are allowed to have the same signature. No two SQL-invoked 
procedures in the same schema are
allowed to have the same name and the same number of parameters. Subject 
routine determination is the process
for choosing the subject routine for a given <routine invocation> given 
a <routine name> and an <SQL argument
list>. Subject routine determination for SQL-invoked functions considers 
the most specific types of all of the
arguments (that is, all of the arguments that are not <dynamic parameter 
specification>s whose types are not
known at the time of subject routine determination) to the invocation of 
the SQL-invoked function in order
from left to right. Where there is not an exact match between the most 
specific types of the arguments and the
declared types of the parameters, type precedence lists are used to 
determine the closest match. See Subclause 9.4,
“Subject routine determination”."

Hope this helps,
-Rick

Shazin Sadakath wrote:
> Hi,
>
> Can anyone tell me whether derby supports SQL Routine overloading? If 
> not any methods have been taken
> to implement that feature, if so what is it?
>
> Thanks in advance,
> Shazin


Mime
View raw message