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



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.