|
midrange.com
Hi Peter,
Sorry, but it works (at least on release V5R2M0)!
Here is an example:
1. A simple RPG-Function to center a text
P Center B Export
D Center PI like(Text)
D ParText like(Text) const
D ParLen 5I 0 const Options(*NoPass)
D LenParText C const(%Size(ParText))
D UsedLength S 5I 0
D RetText S like(Text)
D Start S 3U 0
//--------------------------------------------------------------------
/Free
If %Parms >= 2 and ParLen > *Zeros;
UsedLength = ParLen;
Else;
UsedLength = LenParText;
EndIf;
Select;
When ParText = *Blanks;
Return *Blanks;
When %Len(%Trim(ParText)) = UsedLength;
Return ParText;
Other;
Start = %Int((UsedLength - %Len(%Trim(ParText))) / 2) + 1;
%Subst(RetText: Start) = %Trim(ParText);
Return RetText;
EndSl;
/End-Free
Center E
2. With the following SQL script I created and called this RPG function
successfully with only a single and with both parameters.
Note: Both function need different specific Names!
-- 1. CenterText with only 1 Parameter
Drop Function MYSCHEMA/CENTERTEXT (Char(20));
CREATE FUNCTION MYSCHEMA/CENTERTEXT (
PARTEXT CHAR(20) )
RETURNS CHAR(20)
LANGUAGE RPGLE
SPECIFIC MYSCHEMA/CENTER1
DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
DISALLOW PARALLEL
NOT FENCED
EXTERNAL NAME 'MYSCHEMA/CENTER(CENTER)'
PARAMETER STYLE SQL ;
COMMENT ON SPECIFIC FUNCTION MYSCHEMA/CENTER1
IS 'Center Text' ;
-- 2. CenterText with 2 Parameters
Drop Function ITSO4710/CENTERTEXT (Char(20), SMALLINT);
CREATE FUNCTION MYSCHEMA/CENTERTEXT (
PARTEXT CHAR(20),
PARLEN SMALLINT)
RETURNS CHAR(20)
LANGUAGE RPGLE
SPECIFIC MYSCHEMA/CENTER2
DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
DISALLOW PARALLEL
NOT FENCED
EXTERNAL NAME 'MYSCHEMA/CENTER(CENTER)'
PARAMETER STYLE SQL ;
COMMENT ON SPECIFIC FUNCTION MYSCHEMA/CENTER2
IS 'Center Text' ;
Select centerText(MyFld1), centerText(MyFld2),
cast(Length(MyFld2) as SmallInt)))
from MYSCHEMA/MYTABLE;
Select centerText(Cast('ABC' as Char(20))),
centerText(Cast('XXX' as Char(15)), cast(15 as SmallInt))
from SYSIBM/SYSDUMMY1;
Birgitta
"If you think education is expensive, try ignorance"
(Derek Bok)
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Peter Dow (ML)
Gesendet: Mittwoch, 5. April 2006 21:00
An: Midrange Systems Technical Discussion
Betreff: Re: AW: Specify null parameter for SQL UDF
Hi Birgitta,
Unfortunately, it appears that SQL does not pass a minimal operational
descriptor, so %parms is "unreliable" (the quote is from the RPGLE
Reference manual) -- actually debug showed it to be reliably -1 <grin>.
I ended up having two uniquely named procedures in the service program,
but the same name for the UDF.
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /
Hauser, Birgitta wrote:
> Hi Peter,
>
> you have to overload your UDF.
> That means you have to create a second UDF with the same name in the same
> library, but without parameters.
>
> create function UTLIB/ACCTGMONTH ()
> returns integer
> language RPGLE
> parameter style GENERAL WITH NULLS
> not deterministic
> no SQL
> called on null input
> no external action
> external name 'UTLIB/SVCBUS(ACCTGMONTH)'
>
> The new procedure gets its own signature and now can be called with or
> without parameter.
>
> Passing a NULL Parameter, does not mean the parameter is not passed, but a
> NULL pointer is passed.
>
> In RPG you'll use *OMIT to pass a NULL-Pointer-Parameter to an other
> procedure.
> In RPG you can check passed NULL-Pointers with ParmXYZ = *NULL.
> If the parameter is optional and not passed, no pointer gets passed.
> Checking a parameter that is not passed with = *NULL will cause a MCH3601
> failure.
> To check the number of parameter passed you'll use %Parms.
> Note: Ommitted parameter are counted as passed!
>
> Just an information for your RPG procedure.
> I'd add OPTIONS(*NOPASS) to all optional parameters.
> This allows you to check the number of passed parameters using %PARMS.
>
> d AcctgMonth...
> d pr 10i 0
> d amDate d const Options(*NoPass)
> d amDateNull 10i 0 Options(*NoPass)
> d amResultNull 10i 0 Options(*NoPass)
>
> Birgitta
>
> "If you think education is expensive, try ignorance"
> (Derek Bok)
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Peter Dow (ML)
> Gesendet: Dienstag, 4. April 2006 21:35
> An: MIDRANGE-L@xxxxxxxxxxxx
> Betreff: Specify null parameter for SQL UDF
>
> Hi Everyone,
>
> How do I call a UDF with a null parameter? I have a UDF created to
> return the accounting month for a given date (the only parameter), but
> if no parameter is given, it will return the current accounting month.
>
> It works fine when I pass it a date, e.g.
>
> SELECT ACCTGMONTH(CURRENT_DATE) FROM ANYFILE
>
> but complains (says it cannot find ACCTGMONTH) if I use it like this:
>
> SELECT ACCTGMONTH() FROM ANYFILE
>
> I understand that it identifies a UDF based on the name and the input
> parameter types, but I don't understand how to tell it a particular
> parameter may be optional.
>
> I created it with the following:
>
> create function UTLIB/ACCTGMONTH (date) returns integer
> language RPGLE
> parameter style GENERAL WITH NULLS
> not deterministic
> no SQL
> called on null input
> no external action
> external name 'UTLIB/SVCBUS(ACCTGMONTH)'
>
> ACCTGMONTH is a procedure within a service program, with prototype like
> this:
>
> d AcctgMonth...
> d pr 10i 0
> d amDate d const
> d amDateNull 10i 0
> d amResultNull 10i 0
>
> *Peter Dow* /
> Dow Software Services, Inc.
> 909 793-9050
> pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /
>
>
>
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
