|
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 > > >
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.
midrange.com
