DB2 LUW 8.1 FP11

Can't I overload SPs based on param types rather than
just number of params?

If I do:
CREATE PROCEDURE FOO(
P_DATE_OLD DATE,P_DATE_NEW DATE)
then
CREATE PROCEDURE FOO(
P_INT_OLD INTEGER,P_INT_NEW INTEGER)
then
CREATE PROCEDURE FOO(
P_TEXT_OLD VARCHAR(500),P_TEXT_NEW VARCHAR(500))

I get
SQL0454N The signature provided in the definition for
routine "FOO" matches the signature of some other routine
that already exists in the schema or for the type.
LINE NUMBER=8. SQLSTATE=42723
for the 2nd and 3rd CREATE PROCEDURE calls..

I guess its some sort of promotability issue? I've tried
switching the order of the CREATEs around, but no help.

Can I do this sort of thing?

TIA

aj

OK - guess I need a knock on the head w/ the RTFM hammer.

thanks

aj

Brian Tkatch wrote:

Aj,

It wasn't the case but just the let you know :

You can do this in UDFs. # of parameters might be same given that
their data types are different.

But then you may want to use,

SPECIFIC "some_unique_udf_identifier" in the function creation.

smthg like :

CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) SPECIFIC
my_unique_udf_identifier ..................

It will help you to distinguish the functions.

example:

drop specific function my_unique_udf_identifier

Regards,

Mehmet Baserdem

AJ,

As noted by others it's only possible for functions, not procedures.
This is, in fact, the first time I see such a question, so I'm curious
whether you are porting of another product (which one).
AFAIK no other vendor supports overloading procedures by parameter type
and very few support function overloading by type to begin with.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Hi Serge

No, not porting from another product.

Just trying to implement the audit trail from hell. My
insert/update/delete triggers CALL a SP.

The insert/delete triggers are easy - they call a SP,
passing (TABLE, PK, OPERATION, USER)

However, the update trigger is more. It calls the SP with
TABLE, PK, OPERATION, USER, COLUMN, OLDVAL, NEWVAL)
where the COLUMN type and OLD/NEW values can be DATE,
CHAR/VARCHAR, or INTEGER.

I wanted to overload the SP by type, using wrapper SPs to
call the one that actually does the audit trail work. This
seemed better than clouding my triggers up w/ a bunch of
dummy SP params, or trying to CAST(), or using different SPs.
I'm also a Java developer, so overloading by type makes
sense to me.

I wound up CALLing different SPs from the update triggers
based on type.

Why is overloading by type in SP a big deal? DB2 is so
strongly typed I would think its easy.

thanks

aj

Serge Rielau wrote:

aj wrote:
Few use function overloading. Need to follow the money....
But what you are looking for seems more to be implicit casting.
After allI take it your audit doesn't care what the source type was. You
log everything as varchar. So if DB2 would cast the integer to varchar
when passing the argument you'd be happy right?

Cheers
Serge

PS: that's the opposite of strong typing ;-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab