|
midrange.com
Why did I even doubt the others? Their correct, you need to set the datfmt
option.
D MyDate s d DATFMT(*ISO) inz(d'0001-01-01')
C/EXEC SQL
C+ SET OPTION DatFmt = *ISO
C/END-EXEC
C/EXEC SQL
C+ INSERT INTO TESTDATE VALUES( :MyDate )
C/END-EXEC
Michael Schutte
"Bruce Collins"
<bruce.collins@aa
acooper.com> To
Sent by: "RPG programming on the AS400 /
rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
05/02/2006 05:54 RE: Embedded SQL Insert Problem
PM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
TEST_DATE is a table with the column of tst_date.
Here is what I entered in while using interactive SQL
CONNECT TO ACTSASDB USER DB2INST1 USING ''
Current connection is to relational database ACTSASDB.
select * from actsasor.test_date
SELECT statement run complete.
insert into actsasor.test_date (tst_date) values('0001-01-01')
INSERT statement completed.
commit
Commit completed.
This works but in the RPG program it does not work.
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
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Michael_Schutte@xxxxxxxxxxxx
> Sent: Tuesday, May 02, 2006 3:57 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: Embedded SQL Insert Problem
>
> Is TEST_DATE a table or a field? I cannot tell by what you've given
us...
> When I read it, I see TEST_DATE as a field and not the table.
>
> Michael Schutte
>
>
>
>
> "Bruce Collins"
> <bruce.collins@aa
> acooper.com>
To
> Sent by: "RPG programming on the AS400 /
> rpg400-l-bounces@ iSeries"
<rpg400-l@xxxxxxxxxxxx>
> midrange.com
cc
>
>
Subject
> 05/02/2006 04:50 Embedded SQL Insert Problem
> PM
>
>
> Please respond to
> RPG programming
> on the AS400 /
> iSeries
> <rpg400-l@midrang
> e.com>
>
>
>
>
>
>
> 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
>
'00000000000000000000000002000000000000000000000000000000000000000000000
> 000000000'X
> 41
>
'00000000000000000000000F00000000000000000000000000000000000000000000000
> 000000000'X
> 81
>
'00000000000000000000000000000000000000000000000000000000000000000000000
> 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
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 41
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 81
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 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
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 41
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
>
> Page
> ILE RPG/400 FORMATTED DUMP
>
> 81
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 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
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 41
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 81
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 121
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 161
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 040404040'X
> 201
>
'40404040404040404040404040404040404040404040404040404040404040404040404
> 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
>
'C1C3E3E2C1E2C4C240404040404040404040E2D8D3F0F8F0F2F1C4C2F2C9D5E2E3F1404
> 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
>
'C1C3E3E2C1E2C4C240404040404040404040E2D8D3F0F8F0F2F1C4C2F2C9D5E2E3F1404
> 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.
--
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.
