IDS Function problem
Tony had two SPL functions with the same name, f_lhin_lu, one taking CHAR(2) and one VARCHAR(2), and couldn't drop either: plain DROP FUNCTION gave error 9700 (ambiguous routine), and including the parameter name gave syntax or type errors. Respondents explained this is routine overloading, not a bug: you must give only the argument data types in the signature, e.g. DROP FUNCTION f_lhin_lu(char(2)); and DROP FUNCTION f_lhin_lu(varchar(2));. Marco Greco also suggested using the SPECIFIC clause when creating/dropping routines to avoid the issue.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a function that was allowed to be saved twice with
the same name and
now cannot be dropped. It saved both versions of a function to the one
routine after a modification was made as shown below. I'm using IDS 9.40
and this is an obvious bug. Any ideas on how this function can be dropped?
CREATE FUNCTION "informix".f_lhin_lu(LHIN CHAR(2)) RETURNING CHAR(80);
DEFINE DESC1 CHAR(80);
SELECT lhin_code.lhin_desc INTO DESC1 FROM lhin_code WHERE lhin_code.lhin_cd
= LHIN;
RETURN DESC1;
END FUNCTION;
CREATE FUNCTION "informix".f_lhin_lu(LHIN VARCHAR(2)) RETURNING VARCHAR(80);
DEFINE DESCRIP VARCHAR(80);
SELECT lhin_code.lhin_desc INTO DESCRIP FROM lhin_code WHERE
lhin_code.lhin_cd = LHIN;
RETURN DESCRIP;
END FUNCTION;
Attempts to drop the function give the following errors:
drop function f_lhin_lu;Gives error:
9700: Routine (f_lhin_lu) ambiguous - more than one routine resolves togiven signature.
drop function f_lhin_lu(LHIN)Gives error:
9628: Type (lhin) not found.
drop function f_lhin_lu()Gives error:
674: Routine (f_lhin_lu) can not be resolved.
DROP FUNCTION F_LHIN_LU(LHIN CHAR(2))201: A syntax error has occurred.
DROP FUNCTION F_LHIN_LU(LHIN VARCHAR(2))201: A syntax error has occurred.
Thanks,
Tony
"Demeis, Tony" <Tony.Demeis@moh.gov.on.ca> wrote:
>
>I have a function that was allowed to be saved twice with the same name and
>now cannot be dropped. It saved both versions of a function to the one
>routine after a modification was made as shown below. I'm using IDS 9.40
>and this is an obvious bug. Any ideas on how this function can be dropped?
>
>CREATE FUNCTION "informix".f_lhin_lu(LHIN CHAR(2)) RETURNING CHAR(80);
>DEFINE DESC1 CHAR(80);
>SELECT lhin_code.lhin_desc INTO DESC1 FROM lhin_code WHERE
>lhin_code.lhin_cd
>= LHIN;
>RETURN DESC1;
>END FUNCTION;
>
>CREATE FUNCTION "informix".f_lhin_lu(LHIN VARCHAR(2)) RETURNING
>VARCHAR(80);
>DEFINE DESCRIP VARCHAR(80);
>SELECT lhin_code.lhin_desc INTO DESCRIP FROM lhin_code WHERE
>lhin_code.lhin_cd = LHIN;
>RETURN DESCRIP;
>END FUNCTION;
>
>Attempts to drop the function give the following errors: [snipped]
You will need to specify the input parameter(s) by data type. For example:
DROP FUNCTION f_lhin_lu(CHAR(2));
should work just fine.
HTH
--
June Hunt
> DROP
FUNCTION F_LHIN_LU(LHIN CHAR(2))
> 201: A syntax error has occurred.
Almost. Try: drop function f_lhin_lu(char(2));
> DROP FUNCTION F_LHIN_LU(LHIN VARCHAR(2))> 201: A syntax error has occurred.
That's an IDS / SPL feature.
Use:
drop function/procedure xxx( char(2) )
or
drop function/procedure xxx( varchar(2) )
That should work.
J.
-----Original Message-----
From: "Demeis, Tony" <Tony.Demeis@moh.gov.on.ca>
To: ids@iiug.org
Date: Fri, 3 Jun 2005 08:48:30 -0400 (EDT)
Subject: IDS Function problem [5101]
I have a function that was allowed to be saved twice with the same name and
now cannot be dropped. It saved both versions of a function to the one
routine after a modification was made as shown below. I'm using IDS 9.40
and this is an obvious bug. Any ideas on how this function can be dropped?
CREATE FUNCTION "informix".f_lhin_lu(LHIN CHAR(2)) RETURNING CHAR(80);
DEFINE DESC1 CHAR(80);
SELECT lhin_code.lhin_desc INTO DESC1 FROM lhin_code WHERE lhin_code.lhin_cd
= LHIN;
RETURN DESC1;
END FUNCTION;
CREATE FUNCTION "informix".f_lhin_lu(LHIN VARCHAR(2)) RETURNING VARCHAR(80);
DEFINE DESCRIP VARCHAR(80);
SELECT lhin_code.lhin_desc INTO DESCRIP FROM lhin_code WHERE
lhin_code.lhin_cd = LHIN;
RETURN DESCRIP;
END FUNCTION;
Attempts to drop the function give the following errors:
drop function f_lhin_lu;Gives error:
9700: Routine (f_lhin_lu) ambiguous - more than one routine resolves togiven signature.
drop function f_lhin_lu(LHIN)Gives error:
9628: Type (lhin) not found.
drop function f_lhin_lu()Gives error:
674: Routine (f_lhin_lu) can not be resolved.
DROP FUNCTION F_LHIN_LU(LHIN CHAR(2))201: A syntax error has occurred.
DROP FUNCTION F_LHIN_LU(LHIN VARCHAR(2))201: A syntax error has occurred.
Thanks,
Tony
Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com
Drop funcion f_lhin_lu(char)
Drop funcion f_lhin_lu(lvarchar)
Demeis, Tony wrote:
> I have a function that was allowed to be saved twice with the same name and
> now cannot be dropped. It saved both versions of a function to the one
> routine after a modification was made as shown below. I'm using IDS 9.40
> and this is an obvious bug. Any ideas on how this function can be dropped?
>
> CREATE FUNCTION "informix".f_lhin_lu(LHIN CHAR(2)) RETURNING CHAR(80);
> DEFINE DESC1 CHAR(80);
> SELECT lhin_code.lhin_desc INTO DESC1 FROM lhin_code WHERE lhin_code.lhin_cd
> = LHIN;
> RETURN DESC1;
> END FUNCTION;
>
> CREATE FUNCTION "informix".f_lhin_lu(LHIN VARCHAR(2)) RETURNING VARCHAR(80);
> DEFINE DESCRIP VARCHAR(80);
> SELECT lhin_code.lhin_desc INTO DESCRIP FROM lhin_code WHERE
> lhin_code.lhin_cd = LHIN;
> RETURN DESCRIP;
> END FUNCTION;
>
> Attempts to drop the function give the following errors:
>
> drop function f_lhin_lu;> Gives error:
> 9700: Routine (f_lhin_lu) ambiguous - more than one routine resolves to> given signature.
>
> drop function f_lhin_lu(LHIN)> Gives error:
> 9628: Type (lhin) not found.>
> drop function f_lhin_lu()> Gives error:
> 674: Routine (f_lhin_lu) can not be resolved.>
> DROP FUNCTION F_LHIN_LU(LHIN CHAR(2))> 201: A syntax error has occurred.>
> DROP FUNCTION F_LHIN_LU(LHIN VARCHAR(2))> 201: A syntax error has occurred.>
>
> Thanks,
> Tony
> .
>
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #
Demeis, Tony wrote:
> I have a function that was allowed to be saved twice with the same name and
> now cannot be dropped. It saved both versions of a function to the one
> routine after a modification was made as shown below. I'm using IDS 9.40
> and this is an obvious bug. Any ideas on how this function can be dropped?
No bug - a major 9.x/10 feature, rather. Notice that you have created the
functions taking two different paramenter lists? And you drop specific
routines by specifying the routine signature (owner name, routine name,
argument type list), eg
drop function something(int, int)
RTF SQL syntax M, more specifically DROP FUNCTION, DROP PROCEDURE, DROP ROUTINE
Hint -- it is much better to create & drop routines using the SPECIFIC clause
>
> CREATE FUNCTION "informix".f_lhin_lu(LHIN CHAR(2)) RETURNING CHAR(80);
> DEFINE DESC1 CHAR(80);
> SELECT lhin_code.lhin_desc INTO DESC1 FROM lhin_code WHERE lhin_code.lhin_cd
> = LHIN;
> RETURN DESC1;
> END FUNCTION;
>
> CREATE FUNCTION "informix".f_lhin_lu(LHIN VARCHAR(2)) RETURNING VARCHAR(80);
> DEFINE DESCRIP VARCHAR(80);
> SELECT lhin_code.lhin_desc INTO DESCRIP FROM lhin_code WHERE
> lhin_code.lhin_cd = LHIN;
> RETURN DESCRIP;
> END FUNCTION;
>
> Attempts to drop the function give the following errors:
>
> drop function f_lhin_lu;> Gives error:
> 9700: Routine (f_lhin_lu) ambiguous - more than one routine resolves to> given signature.
>
> drop function f_lhin_lu(LHIN)> Gives error:
> 9628: Type (lhin) not found.>
> drop function f_lhin_lu()> Gives error:
> 674: Routine (f_lhin_lu) can not be resolved.>
> DROP FUNCTION F_LHIN_LU(LHIN CHAR(2))> 201: A syntax error has occurred.>
> DROP FUNCTION F_LHIN_LU(LHIN VARCHAR(2))> 201: A syntax error has occurred.>
>
> Thanks,
> Tony
>
>
>
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Informix faq http://www.iiug.org/techinfo/faq/informix.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm