That worked Chuck. I changed the second parm to a VARCHAR(1) and now it
finds the UDF. Now the UDF is failing with "Missing operational
descriptor." in the joblog. Apparently the OPDESC on my procedure is a
problem.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, September 26, 2013 3:45 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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 */
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
