Thanks a lot for that great explanation. It sure did help On Tue, Feb 16, 2010 at 9:19 PM, Rick Hillegas <[email protected]>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 >> > >
