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
