Re: Creating a SQL UDF

On 26 Sep 2013 11:26, Robert Mullis wrote:

I have a service program (SYSRVUTL) with the following procedure in
it:

D Remove_Non_AlphaNumeric...
D pr 65535a varying opdesc
D inData 65535a const varying
D inCompress n const

I want to create a UDF to use this procedure in embedded SQL, but so
far I have been unsuccessful. I have tried numerous times creating
the function with slight variations and each time it appears to
create. But when I try and run it in an SQL Select, it comes back and
tells me the function is not found in *LIBL. I know it was created in
a library that is in my library list.

This was my last attempt at creating:

CREATE FUNCTION WORKLB/REMOVE_NON_ALPHANUMERIC
( INDATA VARCHAR(32740)
, COMPRESS CHAR(1)
) RETURNS VARCHAR(32740)
LANGUAGE RPGLE DETERMINISTIC NO SQL
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'WORKLB/SYSRVUTL(REMOVE_NON_ALPHANUMERIC)'
PARAMETER STYLE GENERAL

Anyone have any suggestions? I am sure it is simple, but this is my
first attempt at creating a UDF.

The specific message identifier was not noted, nor was the failing invocation. Presumably a literal 1-byte character [delimited same as a] string was specified as the second argument, and the specific error implied that the UDF by that name _with the same\compatible parameters_ was not found in *LIBL [per an invocation that was not library-qualified]. That is, perhaps the invocation was something like?: REMOVE_NON_ALPHANUMERIC(COL_VARCHAR_50,'1') If so...

While the argument can be specified as CAST('1' as CHAR(1)) [instead of the literal\constant value '1'] to avoid the error, the issue instead can be resolved by creating an overloaded function, even while allowing the RPG program to remain unchanged; i.e. the SQL will accept the literal as VARCHAR for invocation of a function by that name, but that literal will be cast to CHAR so the RPG is properly invoked by the SQL:

create function /* new UDF version allows a 1-byte char literal */
WORKLB/REMOVE_NON_ALPHANUMERIC /* same routine name as orig. */
( INDATA VARCHAR(32740) /* parm1-unchanged: same input type+len */
, COMPRESS VARCHAR(1) /* enable character constant as VARCHAR */
) RETURNS VARCHAR(32740) /* unchanged: same return data type */
specific REMOVE_NON_ALPHANUERMIC_VC1
/* optional name enables DROP by name vs by parameter typing */
source WORKLB/REMOVE_NON_ALPHANUMERIC(VARCHAR(32740), CHAR(1))
/* "source" original UDF; i.e. original parm typing\signature */