So creating a separate function expecting VarChar that pointed to a separate procedure that expected a Varying field did the trick. Admittedly, I never pass Varying fields into non-Varying parameters in RPG, but I thought that the Const keyword would convert the value as it does with zoned to packed and so on. I guess that is not the case.
Thankfully SQL supports overloading, so the user can use the same function name regardless of the file's field type.
Thanks for your input.
-Kurt
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Sunday, April 15, 2012 12:35 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL UDF - hardcoded parameters
On 13-Apr-2012 13:44 , Kurt Anderson wrote:
I created a UDF to check to see if an alphanumeric phone number is
toll-free. When I hardcode a parameter such as '800' it doesn't work
(returns false, '0'). When I pass in a variable that contains '800',
it does work (returns true, '1').
Does the function invocation actually set the value to false, or perhaps was the indicator initialized to false, and the function invocation failed? Perhaps the code failed to check the SQLcode or the SQLstate after the invocation, to ensure that the invocation was done with success? That is, presumably the invocation via isTollFree('800') is failing with -440 aka SQL0440? That would also be consistent with the comment in another message, that isTollFree(char('800')) was able to resolve what was described as "doesn't work".
Here is my Function:
Create or Replace Function ccdlib/isTollFree ( ANI Char(20) )
returns Char(1)
language rpgle
no sql
external name 'CCDLIB/ANIPROCS($ISTOLLFREE)'
parameter style general
The service program procedure's prototype:
// Is the Number Toll-Free?
D $isTollFree PR n
D NPA 20a Const
Am I doing something wrong?
I see nothing wrong with that.
I tried changing the function parameter to VarChar, but that didn't
resolve the issue.
Presumably "didn't resolve" refers to the same results as described in the opening paragraph versus still seeing the alluded sqlcode -440?
Changing to define the function to have VARCHAR input would typically be done to avoid a sql0440. However changing only the UDF to have ANI as input to VARCHAR, leaving the storage required by [as defined by] the called procedure as char(20), would cause the procedure to see the two-byte varchar length as the first two bytes of the char(20) data...
which is not very helpful. In that case, expectations would be that the procedure would have been originally designed or redesigned to have NPA declared as "varying", or else probably all invocations would return false [per the two-byte variable length mis-compare problem].
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
