Re: AW: SQL Date Type SQLRPGLE Program Error

[rob@xxxxxxxxx]

Would SQL's equivalent of the H spec help?
     C/EXEC SQL
     C+ Set Option
     C+     Naming    = *Sys,
     C+     Commit    = *None,
     C+     UsrPrf    = *User,
     C+     DynUsrPrf = *User,
     C+     Datfmt    = *iso,
     C+     CloSqlCsr = *EndMod
     C/END-EXEC

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





"HauserSSS" <Hauser@xxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/23/2005 11:44 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


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

Subject
AW: SQL Date Type SQLRPGLE Program Error






Hi,

you have a very common problem!
Your date fields in RPG are defined with date format *ISO.
The valid range for this date format between '0001-01-01' and '9999-12-31'

SQL do not care about the date formats specified in the D- or H-specs.
The SQL precompiler comments the SQL statements out and replaces them
with API calls. For all host variables used in these SQL statements
work fields are created. If date fields are used, they do not adopt the
date format from the original field, but the format specified in the
Compile command CRTSQLRPGI option DATFMT or in a SET OPTION statement in
your program.

The default value for the date format is *JOB, and the job date format 
uses
normally only a 2 digit year. This means the valid range for these
dates is between 1940-01-01 and 2039-12-31.

If in your file are date fields that contain '0001-01-01', this date
will be out of valid range and cause this error.

For more information look at the following Redbook:
Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open
Chapter 7:    Embedded SQL
Chapter 9.3.: Date calculation with RPG and SQL

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Mike Wills
Gesendet: Mittwoch, 23. März 2005 19:53
An: Midrange_RPG400_L
Betreff: SQL Date Type SQLRPGLE Program Error


I am hoping someone can help me understand why my code isn't working.
On my file, there is some SQL date data types defined. For example:

D gResultDs     E DS                  EXTNAME(DBAPAPI)
75=D DAPICMPNY                      4B 0
76=D DAPIVENDOR                     9A
77=D DAPIINVC                      22A
78=D DAPISUFFIX                     4B 0
79=D DAPICNCLSQ                     4B 0
80=D DAPICNCLDT                    10D   DATFMT (*ISO-)
81=D DAPIBTCHNM                     9B 0
82=D DAPIBTCHDT                    10D   DATFMT (*ISO-)
83=D DAPIVCHNBR                    10A
84=D DAPIATHCD                      3A
85=D DAPIPRCLVL                     5A
86=D DAPIACCRCD                     4A
87=D DAPIINVOTY                     1A
88=D DAPIINVCRR                     5A
89=D DAPIPYCRRN                     5A
90=D DAPIINSTIN                    10D   DATFMT (*ISO-)
91=D DAPIPRFRLC                     4A
92=D DAPIPNMBR                     14A

I am doing a "select *" and putting the results in gResultDs. I am
getting a SQLSTT of 22008 and SQLCOD of -183. The text for the error
is: Datetime field overflow occured; for example an arithmetic
operation on a date or timestamp has a result that is not within the
valid range of dates.

Using UPDDTA, I don't see any invalid dates. There are a couple that
are "0" (0001-01-01), all of the rest are within this last year or so.
Any ideas for me?

--
Mike Wills
Midrange Programmer/Lawson Administrator
koldark@xxxxxxxxx
http://www.mikewills.name
Want Gmail? Email koldark+gmail@xxxxxxxxx to get on my waiting list.
--
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.