RE: A problem with dates in RPGLE and Embedded SQL

Thanks, Booth, that did the trick. I now get all records processed as expected.

Thanks,

Marvin
No trees were killed in the sending of this message, but a large number of electrons were terribly inconvenienced, traumatized, triggered, and crying for their safe spaces.

From: Booth Martin [mailto:booth@xxxxxxxxxxxx]
Sent: Thursday, August 17, 2017 7:07 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>; Marvin Radding <marvin.radding@xxxxxxxxxxxxxxxxxxxxx>
Subject: Re: A problem with dates in RPGLE and Embedded SQL


I use this in my c-specs:

// The immediately following /EXEC SQL is SQL's version of RPG's H Spec
// It is never executed. Just used at compile time.
exec sql set option
DatFmt = *ISO,
Commit = *None,
SrtSeq = *LangIDShr; // allows sort & search with upper/lower

On 8/17/2017 5:46 PM, Marvin Radding wrote:

I am having a strange problem when attempting to read a file using embedded SQL.



I have a file LEXNEXHITP with 5 date fields among others. I have defined the data structure to read the record into like this:



d record e ds extname(lexnexhitp)



And my select and open statements are like this:



exec sql

declare hits cursor for

select *

from lexnexhitp

where lxrcpdate = :today

ORDER BY LXCOMPANY, LXDCDMATCH, LXPOLICY, lxrcpdate;



exec sql

open hits;

if sqlerrchk('Hits Open') = Error;

return *on;

endif;

return *off;



when I fetch the records using this (the dou loop is to skip the records that have the error):



dou sqlerr <> error;

exec sql

fetch hits into :record;

sqlerr = sqlerrchk('Hits Fetch':replymsg);

if sqlerr = EndOfData;

return *off;

endif;

enddo;



this is the long text for the error:



Message . . . . : Data mapping error on member LEXNEXHITP.

Cause . . . . . : A data mapping error occurred on field LEXNEXHITP_1.LXDOB

in record number 0, record format *FIRST, member number 1, in member

LEXNEXHITP file LEXNEXHITP in library MR0473, because of error code 18. The

error code meanings follow: 1 -- There is data in a decimal field that is

not valid. 2 -- A significant digit was truncated. 3 -- A floating point

value exceeded the maximum representable value. 4 -- A floating point value

was less than the minimum representable value. 5 -- A binary floating point

value could not be used for the attempted operation because it is not a

number, or is not valid. 6 -- A floating point value could not be mapped to

packed decimal, zoned decimal, or binary. 8 -- A binary floating point value

that is not a number is not allowed as a key value. 9 -- Division by zero.

10 -- A partial binary key field could not be mapped. The length of the key

did not include the entire binary field. 11 -- A partial key field could not

be mapped. The length of the key did not include the entire binary,

floating point, date, or timestamp field. 12 -- There is data in a DBCS

field that is not valid. 13 -- Group by results could not be determined

because no records were selected. 14 -- The length of a variable length

field is not valid or the data in a substring variable is not valid. 15 -- A

date value is greater than the maximum allowed value. 16 -- A date value is

less than the minimum allowed value. 17 -- The format of the data in a date,

time, or timestamp field is not valid. 18 -- There is data in a date, time,

or timestamp field that is not valid. 19 -- An unexpected null field was

found. 20 -- A field that is not null capable could not be set to null. 21

-- The data has been converted from one CCSID to another. However,

substitution characters were used for characters which could not be

converted. 22 -- A shift-out character was found while converting to a

single or mixed byte CCSID or a substitution character was found. The data

could not be converted. 23 -- The data could not be converted from one CCSID

to another. 24 -- The data could not be converted from an ASCII CCSID to an

EBCDIC CCSID, or could not be converted from or to a UCS-2 (Universal Coded

Character Set) CCSID. The addition of shift-out and shift-in characters or

the expansion of single-byte data to a double-byte value caused the

converted length to be larger than the maximum length the result could hold.

25 -- Hash value cannot be computed for the requested query. If the error

occurred on an attempt to read an existing record, file LEXNEXHITP in

library MR0473 identifies the name of the physical file containing the field

contributing to the mapping error. Otherwise, the error occurred on an

attempt to put or update a record in a file and the file name identifies the

open file that contains the field contributing to the mapping error. If the

field name is *N, the field name is not known or is a default value.



This error repeats for 2 other date fields. But only 3 not 5. Seems that the dates that trigger the error are dates before 1945, like 1928. As you can see by the select statement and the data structure record I am attempting to read the file into the data structure that has the same format as the file record.



Is there some setting I need to set to avoid this error? I have tried to user the DATFMT(*ISO) control option but that didn't solve the problem. What else is there?



Marvin

Notice: This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. This message may also contain Protected Health Information (PHI) and must be treated confidentially and handled in accordance with HIPAA and other federal and state privacy laws. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately and delete this e-mail (and any accompanying attachments).