RE: SQL loval date/timestamp

[Joel Cochran <jrc@xxxxxxxxxx>]

Yes, I've added that to the program now and it's peachy keen.  I'm
hoping that I can put these in a /copy book to minimize the cloning
aspect, but I'm thinking that won't work if the pre-compiler processes
all of the SQL first and then opens the /copys.

Either way I should have remembered this since it has floated around
these lists on more than one occassion.

Thanks!

Joel
http://www.rpgnext.com


On Tue, 2003-10-14 at 20:41, Dave Boettcher wrote:
> 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.
> _______________________________________________
> 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.