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



On 27-Apr-2016 12:00 -0500, Stefan Tageson wrote:
On 27-Apr-2016 11:35 -0500, Stefan Tageson wrote:
<<SNIP>>
The DS looks like:

D dx DS qualified dim(1000)
D id 10i 0
D date d
D obj 1000a
D size 20i 0
D count 10i 0
<<SNIP>>

<<SNIP>> And btw the datfmt is *ISO <<SNIP>>

Define "is" ;-) Ensuring the SQL pre-compiler establishes the proper\compatible Date Format is often the crux, in avoiding the described issue. And for the compiler, either the H-spec DATFMT(*ISO-) or the DATFMT(*ISO-) specification on the above D-spec for the "date" sub-field would ensure an explicit designation.

The SQL pre-compiler in my experience, has long been somewhat schizophrenic in the treatment of the Date Format; most glaringly, the issue described by the APAR SE32840 [and its sysroute SE31665; both closed UR1, with neither identified as ever having been corrected] since v5r3, for which the pre-compiler *failed to* apply the DATFMT Option prior to testing the DatFmt of a variable both defined with the Date data type and defined in the dimensioned DS used for a "block fetch statement". The circumvention for the issue, was to specify the intended Date Format special value "on the DATFMT parameter of the CRTSQLRPGI command".

While the APARs do not reflect any correction had ever transpired, I expect that problem was eventually corrected [by IBM i 7.1] such that the "precompiler will now apply the SET OPTION before the check" of the Date Format of the date field\variable in the DS.

To ensure the most consistent and positive effect, that the "DATFMT is *ISO", ensure that the non-executable SET OPTION DATFMT=*ISO statement appears very early [i.e. physical location] in the source, and that there is also an H-spec DATFMT(*ISO-) appears in the source. Optionally, also specify DATFMT(*ISO) on the create\pre-compile request.

<<SNIP>>
SetSqlOpt();

The apparent naming of that procedure [as Set SQL Option(s)] seems to suggest an intention to invoke a SQL SET OPTION statement; e.g. by invoking an external program (EXTPGM) or external procedure (EXTPROC) that has the SET OPTION coded.? Coded in a local procedure [rather than external program or external procedure], the SET OPTION statement would be expected to fail with msg SQL0084, making more conspicuous the invalid attempt to use the statement as an executable vs merely declarative.

If indeed the above SetSqlOpt(); had been coded with the intent that an executable request to the non-executable SET OPTION statement should establish the DATFMT OPTION for the program being compiled, then remove that procedure call and instead ensure the physical location within the source for the appropriate SET OPTION statement, is at the beginning of the SQLRPGLE source being compiled.

sqlstmt = 'select dbid, dbdate, ,..
<<SNIP>>

Note: The use of variables prefixed with "sql" is the domain of [reserved for the use by] the SQL pre-compiler; though the /reserved/ aspect is not strictly enforced, and although the use of the variable plays no role in the current difficulty, the variable name should be changed.


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.