RE: Problem With External UDF

Are the parms the same on each call?  What happens is you use IS
DETERMINISTIC to have the results cached from the first call.  The
additional calls would not actually execute the UDF again....

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of
Alexander.Brand@xxxxxxxxx
Sent: Wednesday, June 08, 2005 10:01 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Problem With External UDF


Rob wrote:
> Many people still use a dummy select to do things that could better be 
> done with 
> SET :NewNumber = GETNEWNUMBER(:PARAM1, :PARAM2)
We need a dummy select because we are using RPG and Client/Server 
applications:
The database driver doesn't accept SET in a Query.


Charles wrote:
> See what happens if you add NO FINAL CALL like so:
> 
>  LANGUAGE RPGLE 
>  SPECIFIC GETNEWNUMBER
>  NOT DETERMINISTIC 
>  MODIFIES SQL DATA 
>  NO FINAL CALL
>  DISALLOW PARALLEL 
>  EXTERNAL NAME 'RGSTDOBJ/S_ALL(FGETNR)';

Only DB2SQL parameter style allows to use the FINAL CALL parameter
( PARAMETER STYLE DB2SQL )
The supported set of parameters for this parameter style are:
externalUDF( IN parameter (repeated),
OUT result,
IN parameter null indicator (repeated),
OUT result null indicator,
OUT sqlstate,
IN function name,
IN specific name,
OUT diagnostic message,
INOUT scratchpad,
IN call type,
IN dbinfo)
I had to change the parameter list...
Now it is called only one time.


BUT sometimes we are using JOINS in the select clause
and then GETNEWNUMBER UDF is called four times!!!!!!!!!!

SELECT GETNEWNUMBER(T1.FIELD, T2.FIELD) 
FROM TABLE1 T1
JOIN TABLE2 T2 on T1.id=T2.id
WHERE
T1.FIELD = xxx

Charles wrote:
> Also, see how many calls are made when the table has say 5 rows.
17 calls were made....


regards,
Alexander