|
midrange.com
It is an RPG-Problem!
When embedding SQL for each host variable used in the SQL statements an
additional variable gets created.
In the case of date fields, the new variables are defined with the date
format specified in either the compile option DATFMT or in a SET OPTION
statement. The date format specified for the variable in the D-Specs or in
the H-Specs gets NOT considered.
The default value for DATFMT in the compile format is *JOB and the job date
format normally is defined with a two digit year.
When moving '0001-01-01' to an date field in RPG with a 2 digit year date
format, you'll get an RPG-error (RNX0103), because this date is out of the
valid range for this date ('1940-01-01' - 2039-12-31').
It's not neccessary to set the DATFMT in the compile option DATFMT or SET
OPTION to *ISO, but it's neccessary to use a date format with a 4 digit year
(*USA, *EUR, *JIS or *ISO).
SQL itself does not consider the date format when inserting or updating a
date field in a row, because only the binary value of the date is inserted.
Date formats are only a way to make the binary representation of a date
readable.
In contrary in RPG all dates get converted into character strings and
reconverted as soon as they get written.
Mit freundlichen Gru?en / Best regards
Birgitta
"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)
-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+hauser=sss-software.de@xxxxxxxxxxxx]Im Auftrag
von Holden Tommy
Gesendet: Dienstag, 2. Mai 2006 23:06
An: RPG programming on the AS400 / iSeries
Betreff: RE: Embedded SQL Insert Problem
Or maybe it IS an SQL problem...
RPG datfmt only applies to the RPG portion of the program. To ensure
that SQL is using the correct datfmt you can do this:
c/exec sql
C+ Set option DatFmt = *ISO
c/end-exec
Thanks,
Tommy Holden
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams
Sent: Tuesday, May 02, 2006 3:58 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Embedded SQL Insert Problem
Bruce,
I'm not an SQL guru, but I think that 0001-01-01 is sort of like a null
date. It's the value, too, even if you didn't use INZ( ) in the D-spec.
I think if you changed it to, say, 2006-01-01 you might get better
results. I don't think it's an SQL problem.
* Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
615.995.7024
fax
615.995.1201
email
jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>
Bruce Collins wrote:
>Would some of you SQL Guru explain the following?
>
>I have created a connection to our AIX Partition and using STRSQL I can
>insert a record to a date field. If I try it from an RPGSQL program I
>get "The year portion of a Date or Timestamp value is not in the
correct
>range"
>
>The database on the remote AIX partition is DB2 UDB.
>
>The File on the iSeries has the field as DATE *ISO
>The Table on the AIX DB2 UDB is also DATE *ISO.
>
>Here is the program.
>
> D MyDate s d DATFMT(*ISO)
>inz(d'0001-01-01')
> D
> D
> C
> C/EXEC SQL
> C+ CONNECT TO ACTSASDB USER :USERNAME USING :PWD
> C/END-EXEC
> C
> C/EXEC SQL
> C+ INSERT INTO TEST_DATE VALUES( :MyDate )
> C/END-EXEC
> C
> C/EXEC SQL
> C+ COMMIT
> C/END-EXEC
>
>Below is the Program Dump.
>
>I hope you can help me.
>
>NAME ATTRIBUTES VALUE
>
> DS
>
> SQL_00000 BIN(4,0) 0128. '0080'X
>
> SQL_00001 BIN(4,0) 0002. '0002'X
>
> SQL_00002 BIN(9,0) 000000024. '00000018'X
>
> SQL_00003 CHAR(1) '3' 'F3'X
>
> SQL_00004 CHAR(118) '
>'
> 81 '
>'
> VALUE IN HEX
>'0000000000000000000000000200000000000000000000000000000000000000000000
0
>000000000'X
> 41
>'00000000000000000000000F0000000000000000000000000000000000000000000000
0
>000000000'X
> 81
>'0000000000000000000000000000000000000000000000000000000000000000000000
0
>00000'X
> SQL_00005 CHAR(1) ' ' '00'X
>
> SQL_00006 BIN(4,0) 0128. '0080'X
>
> SQL_00007 BIN(4,0) 0004. '0004'X
>
> SQL_00008 BIN(9,0) 000000000. '00000000'X
>
> SQL_00009 CHAR(1) '0' 'F0'X
>
> SQL_00010 CHAR(118) '
>'
> 81 '
>'
> VALUE IN HEX
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 41
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 81
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>04040'X
> SQL_00011 DATE(8) ' '
>'4040404040404040'X
> SQL_00012 BIN(4,0) 0128. '0080'X
>
> SQL_00013 BIN(4,0) 0005. '0005'X
>
> SQL_00014 BIN(9,0) 000000000. '00000000'X
>
> SQL_00015 CHAR(1) '3' 'F3'X
>
> SQL_00016 CHAR(118) '
>'
> 81 '
>'
> VALUE IN HEX
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 41
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
>
>Page
>ILE RPG/400 FORMATTED DUMP
>
> 81
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>04040'X
> SQL_00017 CHAR(1) ' ' '40'X
>
>MYDATE DATE(10) '0001-01-01'
>'F0F0F0F160F0F160F0F1'X
>PWD CHAR(10) ' '
>'8482F28995A2A3F14040'X
>SQL CHAR(256) '
>'
> 81 '
>'
> 161 '
>'
> 241 ' '
>
> VALUE IN HEX
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 41
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 81
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 121
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 161
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 201
>'4040404040404040404040404040404040404040404040404040404040404040404040
4
>040404040'X
> 241
>'40404040404040404040404040404040'X
>
> SQLCA DS
>
> SQLABC BIN(9,0) 000000136.
'00000088'X
>
> SQLAID CHAR(8) 'SQLCA '
>'E2D8D3C3C1404040'X
> SQLCABC INT(10) 136
'00000088'X
>
> SQLCAID CHAR(8) 'SQLCA '
>'E2D8D3C3C1404040'X
> SQLCOD BIN(9,0) 000000000.
'00000000'X
>
> SQLCODE INT(10) 0
'00000000'X
>
> SQLERL BIN(4,0) 0058. '003A'X
>
> SQLERM CHAR(70) 'ACTSASDB
>SQL08021DB2INST1 *DUW QDB2/AIX64 '
> VALUE IN HEX
>'C1C3E3E2C1E2C4C240404040404040404040E2D8D3F0F8F0F2F1C4C2F2C9D5E2E3F140
4
>05CC4E4E6'X
>
> 41
>'404040404040D8C4C2F261C1C9E7F6F40003000000000000000000000000'X
>
> SQLERP CHAR(8) 'SQL08021'
>'E2D8D3F0F8F0F2F1'X
> SQLERR CHAR(24) ' 7971'
>
> VALUE IN HEX
>'0000000000000000000000000000000300000001F7F9F7F1'X
>
> SQLERRD INT(10) DIM(6)
>
> (1-3) 0
'00000000'X
>
> (4) 3
'00000003'X
>
> (5) 1
'00000001'X
>
> (6) -134613007
'F7F9F7F1'X
>
> SQLERRMC CHAR(70) 'ACTSASDB
>SQL08021DB2INST1 *DUW QDB2/AIX64 '
> VALUE IN HEX
>'C1C3E3E2C1E2C4C240404040404040404040E2D8D3F0F8F0F2F1C4C2F2C9D5E2E3F140
4
>05CC4E4E6'X
> 41
>'404040404040D8C4C2F261C1C9E7F6F40003000000000000000000000000'X
>
> SQLERRML INT(5) 58 '003A'X
>
> SQLERRP CHAR(8) 'SQL08021'
>'E2D8D3F0F8F0F2F1'X
> SQLER1 BIN(9,0) 000000000.
'00000000'X
>
> SQLER2 BIN(9,0) 000000000.
'00000000'X
>
> SQLER3 BIN(9,0) 000000000.
'00000000'X
>
> SQLER4 BIN(9,0) 000000003.
'00000003'X
>
> SQLER5 BIN(9,0) 000000001.
'00000001'X
>
>
> SQLER6 BIN(9,0) -134613007.
'F7F9F7F1'X
>
> SQLSTATE CHAR(5) '00000'
>'F0F0F0F0F0'X
> SQLSTT CHAR(5) '00000'
>'F0F0F0F0F0'X
> SQLWARN CHAR(1) DIM(11)
>
> (1-11) ' ' '40'X
>
> SQLWNA CHAR(1) ' ' '40'X
>
> SQLWN0 CHAR(1) ' ' '40'X
>
> SQLWN1 CHAR(1) ' ' '40'X
>
> SQLWN2 CHAR(1) ' ' '40'X
>
> SQLWN3 CHAR(1) ' ' '40'X
>
> SQLWN4 CHAR(1) ' ' '40'X
>
> SQLWN5 CHAR(1) ' ' '40'X
>
> SQLWN6 CHAR(1) ' ' '40'X
>
> SQLWN7 CHAR(1) ' ' '40'X
>
>
>
> SQLWN8 CHAR(1) ' ' '40'X
>
> SQLWN9 CHAR(1) ' ' '40'X
>
> SQLWRN CHAR(11) ' '
>
> VALUE IN HEX '4040404040404040404040'X
>
> SQLRESULT DS
>
> IBMHCLOT ZONED(4,0) .
'40404040'X
>
> IBMHCLO8 ZONED(8,0) .
>'4040404040404040'X
> IBMHDT ZONED(3,0) . '404040'X
>
> IBMHDV1N CHAR(30) '
>'
> VALUE IN HEX
>'404040404040404040404040404040404040404040404040404040404040'X
>
> IBMHNT ZONED(3,0) . '404040'X
>
> IBMHOT ZONED(3,0) . '404040'X
>
> IBMHRT ZONED(3,0) . '404040'X
>
> IBMHSEQ ZONED(5,0) .
>'4040404040'X
> IBMHSTAT CHAR(3) ' ' '404040'X
>
> IBMHTRAL CHAR(15) ' '
>
> VALUE IN HEX
>'404040404040404040404040404040'X
>
> USERNAME CHAR(10) 'DB2INST1 '
>'C4C2F2C9D5E2E3F14040'X
> * * * * * E N D O F R P G D U M P * * * * *
>
>
>
>
>
>Thanks
>
>
>Bruce "Hoss" Collins
>IBM Certified Specialist - eServer i5 iSeries System Administrator V5R3
>Cisco Certified Network Associate
>AAA Cooper Transportation
>Dothan, AL 36303
>(334)793-2284 x2434
>
>
>
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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.
