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
