RE: SQL UDF - hardcoded parameters

[Kurt Anderson <kurt.anderson@xxxxxxxxxxxxxx>]

Hi Chuck,

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

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

Thanks for the feedback,
Kurt


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Sunday, April 22, 2012 5:49 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL UDF - hardcoded parameters

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