Ok, so right away I see the DATFMT(*MDY), which explains why the SQL_xxxxxx definitions using *MDY format.
So, there could be the notorious issue with trying to cast from *ISO into *MDY, namely that the minimum valid date value for *MDY would be January 1, 1940. If your file has *ISO *loval dates (0001-01-01), or any date prior to 1940, then you have trouble... I hope I'm remembering that correctly...
Personally, I can't remember the last time that I wanted anything other than *ISO in the DATFMT parm... This is why I always code the SQL options block.
-Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Thomas Garvey
Sent: Monday, July 09, 2012 1:56 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL0180 on SQL insert
Here it is...
5770SS1 V7R1M0 100423 Print SQL information
Object name...............*LIBL/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(*MDY)
DATSEP('/')
TIMFMT(*HMS)
TIMSEP(':')
DFTRDBCOL(*NONE)
DYNDFTCOL(*NO)
SQLPKG(NHP09/NHBLDCLS)
MONITOR(*USER)
SQLCURRULE(*DB2)
ALWBLK(*ALLREAD)
DLYPRP(*NO)
DYNUSRPRF(*OWNER)
USRPRF(*OWNER)
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 DeLong, Eric
Sent: Monday, July 09, 2012 1:29 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL0180 on SQL insert
Thomas,
Could I get you to PRTSQLINF on your program object, then send us the first
part of that spool that shows the compile options?
Thanks,
-Eric DeLong
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Thomas Garvey
Sent: Monday, July 09, 2012 12:44 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL0180 on SQL insert
I have not used the SET OPTION command in the source because most of these
attributes are set on the compile command, and the date fields in the file
have multiple DATFMT attributes defined in the DDS. That is, one date field
is *ISO and another is *USA, and the time fields are *USA.
The external DS expands (in the compile listing) to include the date fields
as follows...
000015=D FCODATELO 10D DATFMT (*ISO-)
Lowest Date found
000016=D FCODATEHI 10D DATFMT (*ISO-)
Highest Value found
000018=D FCOBEGDAT 10D DATFMT (*USA/)
Date Tracking Began
000019=D FCOBEGTIM 8T TIMFMT (*USA:)
Time Tracking Began
000020=D FCOENDDAT 10D DATFMT (*USA/)
Date Tracking Ended
000021=D FCOENDTIM 8T TIMFMT (*USA:)
Time Tracking Ended
Here's how they appear in the Global Field References section of the compile
listing ...
FCODATEHI D(10*ISO-) 2000016D 4000017M 060900M
062000
ALWNULL 5000017
FCODATELO D(10*ISO-) 2000015D 4000016M 060800M
062000
ALWNULL 5000016
However, in the same compile listing I can see that the SQL data structures
that the precompiler generates are as follows...
062000 D SQL_00075 377 384D DATFMT(*MDY/)
FCODATELO 062000
062000 D SQL_00076 385 392D DATFMT(*MDY/)
FCODATEHI 062000
062000 D SQL_00078 394 401D DATFMT(*MDY/)
FCOBEGDAT 062000
062000 D SQL_00079 402 409T TIMFMT(*HMS:)
FCOBEGTIM 062000
062000 D SQL_00080 410 417D DATFMT(*MDY/)
FCOENDDAT 062000
062000 D SQL_00081 418 425T TIMFMT(*HMS:)
FCOENDTIM 062000
Note the difference in the FCODATELO and FCODATEHI DATFMT specs? How would
the SET OPTION help this? If it's defaulting to *MDY/, no matter how the
actual data is defined in the file, what's going on here?
Tom
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, July 09, 2012 12:22 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL0180 on SQL insert
There's data type, and, format. What do you have for DATFMT on:
C/EXEC SQL
C+ Set Option
C+ Naming = *Sys,
C+ Commit = *None,
C+ UsrPrf = *User,
C+ DynUsrPrf = *User,
C+ Datfmt = *iso,
C+ CloSqlCsr = *EndMod
C/END-EXEC
Can you post the data structure and the file layout?
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: "Thomas Garvey" <tgarvey@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>,
Date: 07/09/2012 01:15 PM
Subject: SQL0180 on SQL insert
Sent by: midrange-l-bounces@xxxxxxxxxxxx
I've spent too much time on this problem and need some help. I have to be
missing something obvious here.
Using embedded SQL in an RPGLE program I am getting an SQL0180 error (Syntax
of date, time, or timestamp value not valid) on the following insert
command...
exec sql
insert into TESTFILE Values(:TESTFILEDs :TESTNullInd);
The TESTFILEds is externally described (as follows)
d TESTFILEDS e ds extname(TESTFILE)
The TestNullInd is an array of null indicators for each field in TESTFILE.
The file is defined in DDS and the date data type fields have DATFMT(*ISO)
and ALWNULL attributes.
The data structure fields in the TESTFILEDS are all populated correctly
before the sql insert command. Specifically, the date fields have
'2012-07-09' [using DateField = %date(); ]. When the SQL statement is
executed, the SQL0180 appears and no records is inserted. However, I can
use DFU and interactive SQL to enter the record manually, using the same
field values, and it works fine. It makes no difference what the null
indicator values are set to (null or not null) in the RPG, the error still
occurs. I have the program in debug and have tried changing the date field
contents to every date format I can think of and it makes no difference. I
still get the error.
I am on a v7r1 system. Is there a PTF I haven't found out about? or am I
just boneheaded about something here? I am about to resort to native file
access here but really need to stay with embedded SQL.
Thanks for any advice.
Tom Garvey
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
