Re: SQL UDF - hardcoded parameters

On 16 Apr 2012 08:40, Kurt Anderson wrote:

So creating a separate function expecting VarChar that pointed to a
separate procedure that expected a Varying field did the trick.

I would think having just the one /varying/ version of the procedure and just the one corresponding VARCHAR version of the SQL function, would be better than having two versions of each.? The one procedure would be able to satisfy both the prototyped calls and the SQL invokers where each could use either string literals [aka "constants", via SQL, implicitly typed as VARCHAR] or fixed-length or varying character variables. Understandably, having existing code that depends on the original prototype makes adding new code easier than changing the existing code, but maintaining two procedures and two SQL functions might be[come] more onerous than just making the change to any existing code.?

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.

That works fine for a varying character string variable also; as I alluded above. The procedure having been prototyped expecting a varying string, a program can call with a non-varying literal or variable, and the value will be copied\cast into the temporary location [per CONST] to be passed to the procedure.

However if a separate prototype had been coded directly in a program source indicating that the parameter is varying, but the legitimate [and what should be the only copy of a] prototype and the interface as coded in the procedure source indicate that the parameter is not varying, then the same issue would occur for a CALLP as occurred when the SQL UDF argument was defined as VARCHAR(20) but the procedure expected a fixed-length CHAR instead; i.e. the first two bytes of the CHAR(20) were the 2-byte length of the VARCHAR(20), such that the string comparisons would never match. In the reverse of the same mismatched prototype definitions, the first two bytes of the CHAR(20) data would be interpreted as the 2-byte length of the varying character string for which RNX0115 "Length of varying length variable is out of range" would be the likely effect.

Thankfully SQL supports overloading, so the user can use the same
function name regardless of the file's field type.

Sure, but as noted earlier, I would not prefer that for resolution because just the one procedure with /varying/ coded should be able to function as desired for both RPG and SQL invocations.

Regards, Chuck

CRPence on Sunday, April 15, 2012 12:35 AM
On 13-Apr-2012 13:44 , Kurt Anderson wrote:

<<SNIP>>

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.

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