× 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.



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