RE: SQL loval date/timestamp

[rob@xxxxxxxxx]

Joel,

Use the SET OPTION!  Use that you will not have to change your compile 
prompt.

As far as your pet peeves, I hear the precompiler team now has a pulse and 
that we will probably see some of the changes in the release after V5R2.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




Joel Cochran <jrc@xxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/14/2003 11:09 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: SQL loval date/timestamp






On Tue, 2003-10-14 at 16:09, Dave Boettcher wrote:
> Joel, 
> I am not sure I have seen all the e-mails on this subject, however, in
> SQLRPGLE I remember having to specifically use the DATFMT(*ISO) in the
> header for at least one program.  If there is a way you can look at the 
file
> you should be able to see what is in the date fields.  Any date should 
fit
> into *ISO since its year range is 0001 to 9999. 
> MDY is a different story.  So see what the actual date is in the file. 
JMHO.

That's right Dave, 

The original problem came about because I have Date fields in the
database that do not always get populated: as a result, many of those
dates are '0001-01-01', a valid date, but not if the SQL program thinks
the format is MDY.

I've recompiled the SQLRPGLE module using DATFMT(*ISO) and now it is
working fine.  Unfortunately I now have to either change my command
default or remember this little tidbit every stinkin' time, because this
will need to be done for every program that uses SQL to access file with
a Date field.  I can't have this blowing up just because a date field
was never set.  That's like a program not working because a numeric
field's value is 0 or a character value is blank.

<soapbox>
This is more and more becoming my pet peeve: the SQL pre-compiler and
the RPGIV teams should be joined at the hip.  I would never have had
this problem using traditional file access, but as soon as I want to add
a couple of SQL statements I have to remember a new set of rules on top
of the actual SQL.  On my peeve list now are:

1) No nested /copy statements.
2) Don't forget COMMIT(*NONE).
3) Don't get the prepare, declare, open, fetch, and close statements
physically out of order.
4) No local variables in SQL statements.
5) DATFMT(*ISO) if your DB files have "real" Date fields.
</soapbox>

Sorry for the rant but this should be easier.  I'm not saying it's easy
for the compiler team(s), but it should be easier for the programmers.

Thanks to everyone who responded.

Joel
http://www.rpgnext.com


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