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.
midrange.com
