Re: SQL Function Returning a DATE failing on V6R1

Thanks Chuck. I had gone through most of the points you made while banging my head against the wall.

I have figured it out. Gotta confess--I goofed when I specified PARAMETER STYLE SQL. It should have been GENERAL WITH NULLS. Funny thing is that it worked in most test conditions under V5R3 but always failed under V6R1.

(I was recreating some code from a former life and I had to research the registration code and didn't do that well.)

Sorry everyone.

Sam

On 1/27/2010 1:06 AM, CRPence wrote:

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...