RE: SQL loval date/timestamp -- RPG400-L

This was also said in another reply. Rob Berendt said
<SNIP>
Then go with the flow.  Have you tried:
     C/EXEC SQL
     C+ Set Option
     C+     DatFmt    = *Iso,
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC

This is the precompiler's version of the H spec.
<SNIP>

however, I now noticed that I have these three lines in every SQLRPGLE
program. (I clone a lot.)


     C/exec sql
     C+ set option commit = *none,  CloSQLCsr = *EndMod, datfmt=*ISO
     C/end-exec

Either one takes care of several items on your list. There may be other
options you could use as well.  iSeries Network showed 11 articles on "SQL
Set" search.  
And I know I have seen answers regarding SET on other mailing lists. 

Dave Boettcher

-----Original Message-----
From: Joel Cochran [mailto:jrc@xxxxxxxxxx]
Sent: Tuesday, October 14, 2003 11:09 AM
To: RPG programming on the AS400 / iSeries
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.