Can I overload stored procedures based on param type?

[aj]

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_N EW 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

[Brian Tkatch]

The documentation <URL:http://tinyurl.com/zm653> explicity states:

No two identically-named procedures within a schema are permitted to
have exactly the same number of parameters. A duplicate signature
raises an SQL error (SQLSTATE 42723).

For example, given the statements:

CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

the second statement will fail because the number of parameters in the
procedure is the same, even if the data types are not.
B.

[aj]

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

thanks

aj

Brian Tkatch wrote:

The documentation <URL:http://tinyurl.com/zm653> explicity states:

No two identically-named procedures within a schema are permitted to
have exactly the same number of parameters. A duplicate signature
raises an SQL error (SQLSTATE 42723).

For example, given the statements:

CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

the second statement will fail because the number of parameters in the
procedure is the same, even if the data types are not.
B.

[Mehmet Baserdem]

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_ud f_identifier" in the function creation.

smthg like :

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

It will help you to distinguish the functions.

example:

drop specific function my_unique_udf_i dentifier

Regards,

Mehmet Baserdem

[Serge Rielau]

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

[aj]

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,

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]

aj wrote:

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.

Yes, it would be easy. But the customer base has shown little interest.
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