|
midrange.com
Bruce,
Make sure you add the following to your code,
C/EXEC SQL
C+ SET OPTION DATFMT = *ISO
C/END-EXEC
Your program and the SQL processor need to see dates in the same format.
By default the SQLRPGLE program dates are defined as *MDY (for me
anyway).
Thanks, Matt
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bruce Collins
Sent: Tuesday, May 02, 2006 2:50 PM
To: RPG programming on the AS400 / iSeries
Subject: Embedded SQL Insert Problem
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
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.
