Re: SQL UDF - hardcoded parameters

On 23 Apr 2012 07:17, Kurt Anderson wrote:

I find it odd that RPG's Const keyword will allow for conversion
from non-varying to varying, but not vice-versa. But so long as I
know that, I can live with it.

I obviously failed in a prior attempt to describe :-( The CONST is working fine for prototyped invocations. However, the CONST kwd plays no role in the processing for the UDF; the UDF effectively is\defines the prototype for the SQL invocation of the procedure, irrespective of the PR\PI defined within the procedure.

Write a simple program that invokes the original isTollFree where a PR has been coded separately [not from a /copy] in the program source claiming that the EXTPROC wants a CONST VARYING 20-byte string. Notice the same effect as when the VARCHAR(20) UDF was created and invokes the original isTollFree expecting the CONST fixed-length 20-byte string.

D TF800 S 20A inz('800')
D TFind S n
D* hard-coded prototype for ISSTOLLFREE
D* the actual prototype is in the oldest quoted message
d isTollFree PR n extproc('$ISTOLLFREE')
d npa 20a const varying
c/free
TFind=isTollFree(tf800); // false: x'0003F8'<>x'F8F0F0'
*inlr = *on;

So just like the separate and dissimilar PR in the program source in the example above [different PR than in the procedure source], the VARCHAR(20) UDF effectively established an incorrect PR to the actual PI\PR [const fixed-len char(20)] declared in the procedure. And the same mis-compare would occur for the data in the invoked procedure; i.e. x'0003F8'<>x'F8F0F0', where the former value is the first three bytes of the varying data being interpreted as non-varying data, and the latter is the literal '800' used to detect if the value is one of the toll-free values.

For reference [about multiple\mismatched PR; nothing specific to varying], a discussion from 31 Jul 2000:
Forums -> Archived Forums -> General RPG
Subject: Is the linker so stupid or is it me ?
http://www.systeminetwork.com/forums/aft/15510

I appreciate knowing how I can not have to create a separate
procedure. I took your suggestion and applied it to a scenario I
hadn't mentioned - where isTollFree could be passed a numeric field.
Instead of using a separate procedure like I was doing with Char and
VarChar, this is what I'm now doing:

Create or Replace Function ccdlib/isTollFree (ANI dec(25,0))
returns char(1)
language sql
specific ccdlib/isTollFreeN
Begin
Declare rtnChar Char(1);
Set rtnChar = isTollFree(char(ANI));
Return rtnChar;
End

That should work fine, but is much /heavier handed/ than the sourced function; i.e. a separate procedure was created, just not RPG. The above request creates an actual procedure and service program to implement the LANGUAGE SQL. By contrast, a sourced function merely adds a new row to the SYSROUTINE [SYSFUNCS] catalog data; i.e. no new executable code.

I tried with Source, but wasn't sure how to cast the parameter in
that situation.

As I had alluded, IMO, the syntax for sourcing functions is not nearly as obvious for effecting nothing more than a CAST of the argument to match the expectations of the External procedure. The sourced function uses the parameter declaration to define the numeric input, and the SOURCE reference which originally defined the character input; the CAST is implied, as casting from the former to the latter.

The new CREATE FUNCTION (sourced) to enable the numeric argument, with the exception of the argument\parameter definition [i.e. the "parameter-declaration" specification] and optional SPECIFIC NAME, would be identical to the example given to enable the VarChar variant ["specific istollfreeVC"] in a prior message quoted below:

create function ccdlib/isTollFree /* same routine name */
( ANI dec(25) ) /* enable numeric constant [or variable] */
returns char(1) /* same return data type */
specific istollfreeNV /* optional naming; e.g. enabling DROP */
source ccdlib/isTollFree(char(20)) /* "source" original UDF */

Notes:

In v5r3 the above sourced UDF may give a bogus error about CCSID, presumably resolved by redefining the original CHAR(20) UDF to have parameter-declaration of CHAR(20) CCSID 37

The actual naming of the argument\parameter is optional; i.e. ANI was listed in my examples merely to be consistent with the original UDF.

The SOURCE UDF is identified by the SQL in the same way that SQL would find the function at run-time or for DROP; i.e. together, the UDF name and parameter-declaration section identify the UDF. The syntax allows instead identifying the UDF with "SPECIFIC--specific-name". The original UDF [oldest quoted message below] was given no specific-name, so I used the parameter-declaration method for identifying which isTollFree routine was the "sourced" function.

Regards, Chuck

CRPence on Sunday, April 22, 2012 5:49 PM wrote:

I am responding again, in a separate message from my prior reply,
to explain separately, an alternative means to resolve the
original scenario; i.e. allowing a literal string as the argument
for the function without having to use the CHAR cast function on
the literal. While not as good IMO as having just one procedure
using varying and just one UDF using VARCHAR to match, as I alluded
in the prior message, but...

This resolution is possible without having to create a new
procedure as well as allowing the original procedure to remain
unchanged. This can be effected with overloading the UDF, just
like with reference to a new procedure, but using an effective CAST
to allow the originally coded procedure to process both SQL UDFs.
For example, by making the new UDF function in the manner that is
hopefully conveyed by the intended effect of the following
pseudo-coding [an unavailable syntax which IMO would be much
clearer than "sourcing" existing functions]:

create function isTollFree( ANI VarChar(20) CAST TO CHAR(20) ) ...

Referring to the original scenario which remains in the quoted
message text below, the given procedure with a prototyped
fixed-length 20-byte character string remains the same, and no
additional procedure is created. The same UDF as shown and
originally created would still be created using the CHAR(20) to
match the expected input for the only argument of the UDF; i.e.
matching declaration between the SQL and the prototyped
fixed-length 20-byte character string. The next step is to create
additionally, an overloaded UDF that will still be processed by
that same external name [i.e. by the same procedure in the service
program], but enabling the literal [which in the SQL docs the term
is "constant"] value to be implicitly typed as VARCHAR by the SQL
and thus enabling the SQL /function resolution/ feature to find a
compatible function:

create function ccdlib/isTollFree /* same routine name */
( ANI varchar(20) ) /* enable character constant as VARCHAR */
returns char(1) /* same return data type */
specific istollfreeVC /* optional naming; e.g. enabling DROP */
source ccdlib/isTollFree(char(20)) /* "source" original UDF */

Having done the above additional CREATE FUNCTION, the request to
perform the SQL isTollFree('800') for which the literal\constant
value '800' is implicitly typed as VARCHAR, the compatible function
named isTollFree with the matching VARCHAR declaration [specific
named function isTollFreeVC] is located using Function Resolution
and Best Fit processing by the SQL run-time [instead of failing
with SQL0204 or SQL0440]. Having /sourced/ the original function
defined as CHAR(20), the SQL properly casts the input value from
VARCHAR(20) of the new UDF to a CHAR(20) value which is then passed
to the same procedure 'CCDLIB/ANIPROCS($ISTOLLFREE)' as noted by
the source function; i.e. the new UDF uses the same procedure used
to implement the source\original UDF.

_CREATE FUNCTION (Sourced)_
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstcfso.htm
"This CREATE FUNCTION (Sourced) statement is used to create a
user-defined function, based on another existing scalar or
aggregate function, at the current server. ..."

Regards, Chuck

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

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

Thanks for your input.

CRPence on Sunday, April 15, 2012 12:35 AM wrote:

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

<<SNIP>>