RE: Problem with date zone in SQL fetch -- RPG400-L

Rob,

If I had to guess, I'd say this wasn't a case of IBM explicitly intending to validate dates regardless
of choice of DDL or DDS used to define the table. Instead, I'd guess it's more of a side effect of
the fact that the data is stored in a different format internally.

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, May 27, 2008 9:25 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Problem with date zone in SQL fetch

That was interesting news to me. Using the CPYF technique, as outlined in
http://www-
03.ibm.com/servers/eserver/iseries/db2/pdf/Performance_DDS_SQL.pdf
normally has no problem putting bad data into a DDS defined file from a
"flat" file. However, as you pointed out, a date field in a DDS defined
file still stops the bad data. Apparently there is some checking upon
writes. I wonder if that is because IBM started out fresh with date
fields and could do them right. However, if they started enforcing no bad
data into numerics (on a DDS defined file) thousands of applications out
there would start failing.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

David FOXWELL <David.FOXWELL@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
05/26/2008 03:49 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>

To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Problem with date zone in SQL fetch

Thanks Rob,

The data in my file was typed by me with DFU for testing. I wasn't aware
that the data could have been badly entered. However, I've tried your test
which works fine.
Any other ideas ?

PS, just trying to put corrupted data into the file. By flat file, I think
you mean without DDS. Well, I've created a file that I'm copying to CLTFSC
with *NOCHK. I can see that I can put what I want into the numeric zones.
But, I can't put corrupted data into the date zone, FMAJDL : I get CPF5035
: incorrect date format.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
De la part de rob@xxxxxxxxx
Envoyé : vendredi 23 mai 2008 16:11
À : RPG programming on the AS400 / iSeries
Objet : Re: Problem with date zone in SQL fetch

Another test for corrupted data is to use STRSQL, iNav's Run SQL Scripts
or whatever and try SELECT FMAJDL, days(fmajdl) as testdatetodays, FUSRID
FROM CLTFSC WHERE FCLTNO = 125; If it can't calculate the days from the
date field, it's your data, if it can, then suspect your RPG.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

David FOXWELL <David.FOXWELL@xxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
05/23/2008 09:53 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>

To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> cc

Subject
Problem with date zone in SQL fetch

Hi

I'm getting a SQL0180 from when I execute SELECT FMAJDL, FUSRID FROM
CLTFSC WHERE FCLTNO = 125

CLTFSC is a DDS PF and FMAJDL is described as a date with format *EUR.

My DS to receive the select is

D gHst DS QUALIFIED

D MajDt LIKE ( DFN_MAJDL )

D UsrId LIKE ( DFN_USRID )

DFN_MAJDL is also a date zone with format *EUR

When I look at the compiler list, I can see that gHst.MajDt is also the
same format.

As a temporary fix I've changed to
SELECT CHAR(FMAJDL), FUSRID
FROM CLTFSC WHERE FCLTNO = 125

What's happenin'?
--
This is the RPG programming on the AS400 / 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.

--
This is the RPG programming on the AS400 / 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.

--
This is the RPG programming on the AS400 / 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.

--
This is the RPG programming on the AS400 / 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.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.