On 09-Dec-2013 00:42 -0800, Colpaert, Peter wrote:
Yesterday evening we migrated (finally) from our V5R3 system to a
brand new enterprise system at V7R1.
All seemed well until I tried to use a UDF in SQL this morning. It
takes in a date in numeric ISO format, and should return a date in
excel format (# of days since 01/01/1900).
However, it always returns zero.
I re-created the function using the following syntax:
create function colpaerp.isotoexcel(decimal(8, 0))
returns numeric(8, 0)
external name 'SRVPGMLIB/SQLUDF(ISOTOEXCEL)'
language rpgle
parameter style db2sql
no sql
deterministic
no external action
returns null on null input
allow parallel;
This is the source of the function in the service program:
P IsoToexcel b Export
d IsoToExcel pi 8s 0
d IsoDate 8p 0 const
D Xls_StartDate S D inz(d'1900-01-01')
D retVal s 8s 0 inz
/free
Reset retVal;
test(de) *iso IsoDate;
If %error;
Else;
If IsoDate> *zeros;
retVal = %diff(%date(IsoDate:*iso):xls_startdate:*d) + 2;
EndIf;
Endif;
Return %dec(retVal:10:0);
/end-free
p IsoToExcel e
When I debugged the function, the retVal contained the correct value,
but in the SQL result it was zero.
Is there a difference between V5R3 and V7R1 when creating the
function?
FWIW, the V5 system was first restored on a new V6 system, which was
subsequently upgraded to V7.
Any help would be greatly appreciated.
Not specific to the RPG, however... For sure...
The PARAMETER STYLE specified for the external scalar User Defined
Function, does not match the invoked external program that is invoked.
Prior to v6r1m0 [DB2 for i SQL 6.1] the SQL had a better chance at
returning [what appeared to be] correct output when something other than
the *proper* PARAMETER STYLE GENERAL had been coded on the CREATE
FUNCTION ... EXTERNAL NAME. Since V6R1 the results are more likely to
be Incorrect Output (kwd INCORROUT) or if possible [as more desirable
effect] to be diagnosed as improperly coded due to error msg CPF426A
RC8. The PARAMETER STYLE DB2SQL requires that the return value be
returned via a /parameter/ rather than a procedure return-value.
http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/rzaq9/rzaq9.pdf
v6r1m0 Memo to users (MTU)
_Changes for user-defined external functions that call a service_ _program_
http://www.ibm.com/support/docview.wss?uid=nas248b2f6f111f6b497862575fc003ca9b2
Software version: V6R1M0
Reference #: SE38412
OSP-DB-OTHER-F/QDBIOERRQO-T/QQURB-RC17-MSGCPF5035 EXTERNAL UDF
(RPGLE) FAILS AT R610 WITH MSGCPF5035 OR CPD4019 RC6
"...
_Problem Conclusion_
In v6r1m0, the possibility of a defect was identified when improper uses
of the UDF's Parameter Style were allowed to work when they should not
have. While this often worked correctly, the potential for returning
invalid data existed. ...
...
... UDF is actually defined incorrectly given how they are attempting to
use it, and this use is why the UDF is now returning hex zeros (which
result in the mapping error when viewed as a date).
There are two options they can take to fix this.
1) Change the Parameter Style to be PARAMETER STYLE GENERAL which
supports return values, and re-create the SQL Function.
or
2) PARAMETER STYLE SQL does not return a value. Instead, a return
parameter pointer must be provided on the parameter list and the return
value assigned to that pointer.
..."
And this presumably is the APAR for which the above alluded
change\correction came, since which the functions are less likely to
effect seemingly correct output:
http://www-912.ibm.com/n_dir/nas4apar.nsf/ALLAPARS/SE34712
The following doc links describe some of the parameter styles used
for defining the parameter passing of the arguments and return values
for a FUNCTION defined as a routine to the SQL:
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafypassargumentsproc.htm>
IBM i 7.1 Information Center -> Database -> Programming -> SQL
programming -> Routines -> Using user-defined functions -> Writing UDFs
as external functions
_i Passing arguments from DB2 to external functions i_
"DB2® provides storage for all parameters that are passed to a
user-defined function (UDF). Therefore, parameters are passed to an
external function by address.
This is the normal parameter passing method for programs. For service
programs, ensure that the parameters are defined correctly in the
function code.
When defining and using the parameters in the UDF, care should be taken
to ensure that no more storage is referenced for a given parameter than
is defined for that parameter. The parameters are all stored in the same
space and exceeding a given parameter's storage space can overwrite
another parameter's value. This, in turn, can cause the function to see
invalid input data or cause the value returned to the database to be
invalid.
There are several supported parameter styles available to external UDFs.
For the most part, the styles differ in how many parameters are passed
to the external program or service program.
_Parameter style SQL_
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyparamsql.htm>
The SQL parameter style conforms to the industry standard SQL. This
parameter style can be used only with scalar user-defined functions (UDFs).
_Parameter style DB2SQL_
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyparamdb2sql.htm>
With the DB2SQL parameter style, the same parameters and the same
order of parameters are passed to an external program or a service
program as with the SQL parameter style. However, DB2SQL allows
additional optional parameters to be passed as well.
_Parameter style GENERAL_
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyparamgen.htm>
With the GENERAL parameter style, the parameters are passed to an
external service program just as they are specified in the CREATE
FUNCTION statement. This parameter style can be used only with scalar
user-defined functions (UDFs).
..."
<
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyparamgen.htm>
IBM i 7.1 Information Center -> Database -> Programming -> SQL
programming -> Routines -> Using user-defined functions -> Writing UDFs
as external functions -> Passing arguments from DB2 to external functions
_i Parameter style GENERAL i_
"With the GENERAL parameter style, the parameters are passed to an
external service program just as they are specified in the CREATE
FUNCTION statement. This parameter style can be used only with scalar
user-defined functions (UDFs).
The format is:
Read syntax diagramSkip visual syntax diagram
.------------------.
V |
>>-SQL-result = func--(----+--------------+-+--)---------------><
'-SQL-argument-'
_SQL-argument_
This argument is set by DB2® before calling the UDF. This value
repeats n times, where n is the number of arguments specified in the
function reference. The value of each of these arguments is taken from
the expression specified in the function invocation. It is expressed in
the data type of the defined parameter in the CREATE FUNCTION statement.
Note: These parameters are treated as input only; any changes to the
parameter values made by the UDF are ignored by DB2.
_SQL-result_
This value is returned by the UDF. DB2 copies the value into
database storage. In order to return the value correctly, the function
code must be a value-returning function. The database copies only as
much of the value as defined for the return value as specified on the
CREATE FUNCTION statement. If the CAST FROM clause is used in the CREATE
FUNCTION statement, DB2 assumes the UDF returns the value as defined in
the CAST FROM clause, otherwise DB2 assumes the UDF returns the value as
defined in the RETURNS clause.
Because of the requirement that the function code be a
value-returning function, any function code used for parameter style
GENERAL must be created into a service program.
..."
midrange.com
