RE: SQL UDF problem -- RPG400-L

Chuck,

I'll try dropping/creating the function with "parameter style general", and see if that helps.

Thanks!

Met vriendelijke groeten / Best regards,

Peter Colpaert
Software Engineer - PLM Development Team
IT Operations Cluster Benelux, Philips IT

Philips Consumer Luminaires
Industrieterrein Satenrozen 11, 2550 Kontich, Belgium
Tel: (+32) 3/459 13 17
Email: Peter.Colpaert@xxxxxxxxxxx

Working from home on Wednesdays

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: maandag 9 december 2013 11:40
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL UDF problem

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(r) 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(r) 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.
..."

--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.

________________________________
The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.