db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shazin Sadakath <shazin.sadak...@gmail.com>
Subject Re: Regarding Stored Procedures
Date Wed, 17 Feb 2010 04:37:48 GMT
Thanks a lot for that great explanation. It sure did help

On Tue, Feb 16, 2010 at 9:19 PM, Rick Hillegas <Richard.Hillegas@sun.com>wrote:

> 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

View raw message