Posted: Fri Apr 27, 2007 12:57 pm Post subject: create UDF, but invoke failed.
I created a function, but when i try to invoke it, i got SQLCODE = -440
it's really wierd.
e.g. The DDL:
Code:
CREATE FUNCTION MYTEST (CHAR(12), CHAR(4), CHAR(4), CHAR(8)) RETURNS
CHAR(10) SPECIFIC FT_F_GET_BK_CURR EXTERNAL NAME 'ABCTEST' LANGUAGE
C NOT DETERMINISTIC PARAMETER STYLE DB2SQL FENCED RETURNS NULL ON
NULL INPUT READS SQL DATA NO EXTERNAL ACTION SCRATCHPAD NO FINAL
CALL DISALLOW PARALLEL NO DBINFO NO collid WLM ENVIRONMENT
DBDGWLM4 ASUTIME NO LIMIT STAY RESIDENT NO PROGRAM TYPE MAIN
SECURITY DB2 STOP AFTER SYSTEM DEFAULT FAILURES INHERIT SPECIAL
REGISTERS;
the create succeed, and I can query it from sysibm.sysroutines catalog table, the schema is my ID.
But when i try invoke the function, e.g. SQL:
Code:
SELECT MYTEST('AAA','BBB','CCC','DDD') FROM SYSIBM.SYSDUMMY1;
it return:
Code:
SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME MYTEST HAVING
COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
Does anyone have idea about it? Anything wrong with the CREATE FUNCTION DDL?
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Fri Apr 27, 2007 1:40 pm Post subject:
I dunno but you define your p1st parm as CHAR(12) but only supply a CHAR(3) parm and so on for the other parameters. Just a guess, but SQL can be picky. _________________ Utility and Program control cards are NOT, repeat NOT, JCL.
I found the resean
If I change all function parameters from CHAR to VARCHAR,
it will work.
but it's really stupied why DB2 can't find the function for CHAR parameter
even I can't invoke it by
Code:
SELECT MYTEST('123456789012','BBBB',
'CCCC','12345678')
FROM SYSIBM.SYSDUMMY1;
i know dynamical SQL will read the string parameter as VARCHAR, but when I invoke the funtion in program (the parameter will be the host language variable), it will failed at BIND too.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
