This information is in the sql reference.
Paul
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Paul
Therrien
Sent: Thursday, August 17, 2017 8:50 PM
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
<rpg400-l@xxxxxxxxxxxx>
Subject: RE: A problem with dates in RPGLE and Embedded SQL
Check 'set options' and date format options documentation.
Paul
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Marvin
Radding
Sent: Thursday, August 17, 2017 6:47 PM
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).
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
