Some ideas as I would investigate the failure, in order:
For the STRSQL report, run in debug mode, what is the "data
mapping error"? Is the error a date underflow\overflow, some other
bad data for a date, or a NULL condition error? If the latter, then
probably the null indicator is not being properly set whereas for
one of the other cases the SQL result value is probably in error.
What explicitly is meant by "the null indicator is set to zero"?
Specifically, what is the variable declaration and the data
presented for the display of the variable in debug? "Each indicator
is defined as a two-byte signed integer" so a x'0000' versus x'F0F0'
or x'000F' would be required for a non-null result.
Review the parameter list again, against the PARAMETER STYLE SQL
documentation. Errors in how parameters are passed can often appear
to be functional, when in fact they are functional only by chance
due to layout of [residual] memory in the automatic storage of the
call [stack frames].
What explicitly is meant by "returns a date"? Specifically, what
is the variable declaration and the data presented for the display
of the variable in debug? What are the date format & date separator
defined for the STRSQL session testing the function? What were the
date format & date separator defined for the RUNSQLSTM used to
create the function?
The RUNSQLSTM defaults to DATFMT(*JOB) and DATSEP(*JOB), so if
the function should correctly process the SQL-result as a character
string, is that string being generated in the date format that is
established during run-time [and thus possibly incorrect] or the
date format that was established at definition [i.e. CREATE
FUNCTION] time?
Note that the internal data type DATE is an integer versus any of
the visual string representation(s) available for display; e.g. the
second argument of CHAR(date-arg1,arg2). Does the RPG module form a
character string of digits and separators, perhaps in *ISO date
format? If so, consider that the SQL-result "is passed by address,
the address is of the storage where the return value should be
placed" and the "database provides as much storage as needed for the
return value as defined on the CREATE FUNCTION statement" which
means that for DATE there is room for an integer versus a character
string. I have never created an external scalar user defined
function that RETURNS DATE, but I would expect that I might have to
use the "RETURNS DATE CAST FROM CHAR(10)" such that for example, the
string '2010-01-24' as set in the SQL-result would be known by the
SQL to be a standard\recognizable string representation [in this
example, *ISO] of a date. Previous releases [to 6.1] even show that
CAST as an example for "data-type3 CAST FROM data-type4", and v6r1
shows a DOUBLE\DECIMAL example. If this were the source of a
problem, then that should have been a problem in prior releases.
Regards, Chuck
Lennon_s_j@xxxxxxxxxxx wrote:
I have an RPG UDF that returns a date. It worked on V5R3
and V5R4. We went to V6R1 a a couple of months ago and it
no longer works.
It is registered like this using RUNSQLSTM:
DROP FUNCTION SLENNON/DATE_MDY
;
CREATE FUNCTION SLENNON/DATE_MDY
(INDATE DECIMAL(8,0) )
RETURNS DATE
PARAMETER STYLE SQL
LANGUAGE RPGLE
NO SQL
DETERMINISTIC
EXTERNAL NAME 'SLENNON/DATE_SQL(DATE_MDY)'
CALLED ON NULL INPUT
NO EXTERNAL ACTION
I can watch the RPG code in the debugger and it returns a date
and the null indicator is set to zero, but STRSQL (green screen)
always shows the replacement value (+s). SQuirreL (JBDC) always
shows nulls.
What am I missing? Something seems to have changed in V6R1...
midrange.com
