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) )
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].