|
Hi,if you define date and time fields in RPG it is not necessary to add the length.
You defined you date field as date that's ok. The date format your RPG field uses depends on the date format, that is either specified in the D- or H-specs. Because there is no definition in the D-specs, the date format specified in the H-specs. If no date format is defined in the H-Specs date format *ISO is used.
The valid range for *ISO-Dates is 0001-01-01 to 9999-12-31.The SQL precompiler generates for each host variable used in an SQL-Statements additional variables. For date and time fields the precompiler does not care about the formats specified in the H- or D-Specs. Instead it uses the date format specified in the compile command. The default value for date format in the compile command is *JOB. And the job date format normally only has 2 digits. Dates with a 2 digit years have a restricted range, from 1940-01-01 to 2039-12-31. An error occurs as soon a value in the RPG-variable is out of the valid range for the SQL-variable. To avoid this, you have to specify a 4 digit date format in the compile command.
Alternatively you can add a set option statement in your source. C/EXEC SQL Set Option DatFmt = *ISO, TimeFmt = *ISO C/END-EXECThe SET OPTION-Statement must preceed all other SQL-Statements. Before release V5R4 this was not checked, but with release V5R4 a compile error occurs, if the SET OPTION-Statement is not the first SQL-Statement.
Mit freunlichen Grüßen / Best regards Birgitta Hauser"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)----- Original Message ----- From: "Turnidge, Dave" <DTurnidge@xxxxxxxxxxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> Sent: Friday, October 27, 2006 16:49 Subject: Embedded SQL newby question...
midrange.com
I have a file defined as:
JRSYS K1 A 8 1 8 System
JRONAM A 10 9 18 Original Object Name
JRFNAM K4 A 10 19 28 Final Object name
JRCDAT K2 L 10 29 38 Creation date (YYYYMMDD)
JRCTIM K3 T 8 39 46 Creation time (HHMMSS)
JRVOL A 10 47 56 Volume
JRSEQ P 5 0 57 59 Sequence number
JROSIZ P 15 0 60 67 Original Size
JRSSIZ P 15 0 68 75 Saved Size
JRNOTE A 50 76 125 NOTE
Here is the first record in that file:
System..................: MD400
Original Object Name....: QUDRCV1321
Final Object name.......: QUDRCV1321
Creation date (YYYYMMDD): 0001-01-01
Creation time (HHMMSS)..: 00.00.00
Volume..................: AUD001
Sequence number.........: 209
Original Size...........: 205672448
Saved Size..............: 45297664
NOTE....................:
This is my data structure:
** Data Structure for SQL Fetch
d DetailStr ds inz
d jrsys 8a
d jronam 10a
d jrfnam 10a
d jrcdat 10d
d jrctim 8t
d jrvol 10a
d jrseq 5p 0
d jrosiz 15p 0
d jrssiz 15p 0
d jrnote 50a
This is my SQL statement:
** Select records from SAVHISTORY
c/exec sql
c+ Declare DtlSelect Cursor
c+ For Select jrsys,
c+ jronam,
c+ jrfnam,
c+ jrcdat,
c+ jrctim,
c+ jrvol,
c+ jrseq,
c+ jrosiz,
c+ jrssiz,
c+ jrnote
c+ From $auditstg/savhistory
c+ Order by jrsys,
c+ jrcdat,
c+ jrctim,
c+ jrfnam
c/end-exec
I am getting the following CPF5035 error:
Message . . . . : Data mapping error on member SAVHISTORY.
Cause . . . . . : A data mapping error occurred on field
DbopExprKey(QdsSortedPtl(Node_11), SAVHISTORY_1.JRCDAT[3], 1) in
record
number 0, record format *FIRST, member number 1, in member SAVHISTORY
file
SAVHISTORY in library $AUDITSTG, because of error code 18. The error
codes
and their meanings follow:
18 -- There is data in a date, time, or timestamp field that is not
valid.
The following SQL0181 error follows the previous:
Message . . . . : Value in date, time, or timestamp string not valid.
Cause . . . . . : The string representation of a date, time or
timestamp
value is not in the acceptable range. JRCDAT is either the character
string
constant that is not valid or the column or host variable that
contained the
string. If the name is *N, then the value was found in an expression
specified in the statement. If the value was found in a host
variable, then
the host variable number is 4. The proper ranges for date, time, or
timestamp values are as follows:
-- The range for years is from 0001 to 9999.
-- The range for months is from 1 to 12.
-- The range for days is from 1 - 30 for April, June, September,
and
November, from 1 - 28 for February and from 1 to 31 for all other
months.
In a leap year, the range for February can be from 1 to 29.
-- The range for days in a Julian date is from 001 to 366 for a
leap year
or 001 to 365 days for all other years.
-- The range for hours is from 0 to 24. If the hour is 24, then
the other
parts of the time values must be zeros. If the time format is USA,
then the
hour cannot be greater than 12.
-- The range for minutes is from 0 to 59.
-- The range for seconds is from 0 to 59.
-- The range for microseconds is from 0 to 999999.
Recovery . . . : Ensure that the date, time, or timestamp value
conforms to
the ranges for the data type it represents. Try the request again.
I believe my issue has to do with defining the date/time field, and I
don't know what to put where to fix it.
Please direct me to a page in a manual that describes what I have done
and how to fix it. Or, if it's simple... just respond gently... :-)
Thank you,
Dave
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
