Re: A problem with dates in RPGLE and Embedded SQL

Hi Marvin,
Instead of using :today, how about using current_date.

Yours truly,
Glenn Gundermannglenn.gundermann@gmail.comWork: (905) 482-1162 ext. 239Cell: (416) 317-3144

-------- Original message --------From: Marvin Radding <marvin.radding@xxxxxxxxxxxxxxxxxxxxx> Date: 2017-08-17 6:46 PM (GMT-05:00) To: "'RPG programming on the IBM i (AS/400 and iSeries)'" <rpg400-l@xxxxxxxxxxxx> Subject: A problem with dates in RPGLE and Embedded SQL
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).