The problem is you are fetching inserting the whole record and do not list
the values.
The data structure of the record is used. In RPG (and in those data
structures), dates are converted into a character representation.
Because you are using different date formats, these date fields are
converted into the character representation based on the format in DDS.
SQL on the other hand does not convert, but can handle character
representations in any 4 digit year date format correctly.
But cannot handle character representations with 2 digit years (except the
current job's date format).
IMHO the only solution is, to list the fields when inserting them.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Thomas Garvey
Gesendet: Monday, 09.7 2012 23:07
An: 'Midrange Systems Technical Discussion'
Betreff: RE: SQL0180 on SQL insert
I am not testing the program in the library it's compiled into. I'm copying
it into another library for testing. And it's in debug mode so I know I'm
getting the correct version of the object.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Monday, July 09, 2012 3:59 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL0180 on SQL insert
Did you reclaim your activation group after the last compile? You may still
be running/testing a older copy of this program...
The datfmt on the H-spec is strictly for RPG date context, and is NOT the
same as the datfmt in the SQL options block. It is perfectly acceptable to
define both, and even best when they're all the same format (*ISO).
-Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Thomas Garvey
Sent: Monday, July 09, 2012 3:43 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL0180 on SQL insert
OK, still not understanding, and still getting the SQL0180.
My H spec has datfmt(*ISO) [it always did] and I inserted the following SQL
Option statement at the beginning of my code (before any other SQL
statements)...
exec sql
Set Option
Naming = *Sys,
Commit = *None,
UsrPrf = *User,
DynUsrPrf = *User,
Datfmt = *iso,
DatSep = *dash,
CloSqlCsr = *EndMod;
Here's the PRTSQLINF results (so I know the Set Option worked)...
Object name...............NHP09/NHBLDCLS
Object type...............*PGM
CRTSQLRPGI
OBJ(QTEMP/NHBLDCLS)
SRCFILE(NHP09/QRPGLESRC)
SRCMBR(NHBLDCLS)
COMMIT(*NONE)
OPTION(*SYS *NOEXTIND *PERIOD)
TGTRLS(V7R1M0)
ALWCPYDTA(*OPTIMIZE)
CLOSQLCSR(*ENDMOD)
RDB(*LOCAL)
DATFMT(*ISO)
DATSEP('-')
TIMFMT(*HMS)
TIMSEP(':')
DFTRDBCOL(*NONE)
DYNDFTCOL(*NO)
SQLPKG(NHP09/NHBLDCLS)
MONITOR(*USER)
SQLCURRULE(*DB2)
ALWBLK(*ALLREAD)
DLYPRP(*NO)
DYNUSRPRF(*USER)
USRPRF(*USER)
SRTSEQ(*HEX)
LANGID(ENU)
RDBCNNMTH(*DUW)
TEXT('Build Class for flds in file (added date handling)')
SQLPATH(*LIBL)
DECRESULT(31 31 0)
DECFLTRND(*HALFEVEN)
CONACC(*DFT)
STATEMENT TEXT CCSID(37)
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, July 09, 2012 3:10 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL0180 on SQL insert
I pretty much have a standard sql options block in my skeleton rpg program I
use to generate new programs.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 07/09/2012 03:35 PM
Subject: RE: SQL0180 on SQL insert
Sent by: midrange-l-bounces@xxxxxxxxxxxx
I would think so... Easy enough to find out.
You could add the sql options block, or just recompile the program with
DATFMT(*ISO) and see if that does the trick... The Options DatFmt value
overrides the compile command option...
Like I said before, I honestly can't remember the last time I needed
anything other than DATFMT(*ISO).
-Eric
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Thomas Garvey
Sent: Monday, July 09, 2012 2:28 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL0180 on SQL insert
I think I understand but wonder why this didn't happen until I added two
date fields with DATFMT(*ISO) in the DDS. There were already two date
fields with DATFMT(*USA) and I didn't get the SQL0180.
So, if I use the SET OPTION SQL command, all should be well?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Monday, July 09, 2012 2:19 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL0180 on SQL insert
Thomas,
The program date format is defined by either the COMPILEOPT keyword in the
CRTSQLRPGI command or by the H-SPECS to the same effect. If nothing is
specified it defaults, IIRC, to the job's date format.
The SQL date format is defined, as Rob posted before, using the SET OPTION
sentence.
So, as I see it, the ideal would be for you to define SQL's date format
as:
exec sql DATFMT = *ISO;
and RPG's date format as:
H DATFMT(*ISO)
That way you can be assured of your date fields being consistent in their
format.
BTW, internally the actual date data is not defined in any particular
format. It is defined as a 4-byte string containing an integer (a Scaliger
Number) that represents the date. So, if I get your file ("defined" as
*MDY) in my system, I would "see" it as *DMY, just because that is my job's
default date format.
Hope this makes sense....
Regards,
Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
midrange.com
