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