Can I overload stored procedures based on param type?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aj - 24 May 2006 17:08 GMT

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

Reply to this Message

Brian Tkatch - 24 May 2006 17:30 GMT

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.

Reply to this Message

aj - 24 May 2006 17:49 GMT

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

thanks

aj

> The documentation <URL:http://tinyurl.com/zm653> explicity states:
>
[quoted text clipped - 11 lines]
>
> B.

Reply to this Message

Mehmet Baserdem - 24 May 2006 19:17 GMT

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

Reply to this Message

Serge Rielau - 25 May 2006 10:57 GMT

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

aj - 25 May 2006 15:20 GMT

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

> AJ,
>
[quoted text clipped - 6 lines]
> Cheers
> Serge

Reply to this Message

Serge Rielau - 25 May 2006 16:34 GMT

> Hi Serge
>
[quoted text clipped - 23 lines]
> 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 ;-)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message