× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. May 2006

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 
'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 thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.