The SET OPTION statement is never executed but only used at compile time.
You can interpret the SET OPTION statement as the H-Specs for embedded SQL.
Like the H-Specs the SET OPTION statement must be the first SQL statement in
your source code.
Just add it immediately before the first C-spec in a source with a main
procedure (Program) or before the first P-Statement/DCL-PROC in a NOMAIN
source.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Stefan
Tageson
Gesendet: Sunday, 01.5 2016 00:54
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: RE: Embedded sql, multirow fetch and date column
Hi Chuck,
This is the local procedure SetSqlOpt, placed first in my sql-module.
The failing parts being discussed is about 12 inch below in the same source
member.
P SetSqlOpt B Export
D SetSqlOpt PI 1n
*---------------------------------------------------------------------
*---------------------------------------------------------------
* Local work areas and prototypes *
*---------------------------------------------------------------
D thisp C 'SetSqlOpt'
D Done s 1n inz(*off) static
/free
if done;
return *off;
endif;
EXEC SQL
Set Option
DatFmt = *ISO,
Naming = *Sys,
Commit = *None,
UsrPrf = *User,
DynUsrPrf = *User,
CloSqlCsr = *EndActGrp;
Done = *on;
Return *off;
/end-free
PSetSqlOpt E
Is there anything else you think I should change?
Best regards
stefan.tageson@xxxxxxxx
M +46 732 369934
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: den 30 april 2016 18:35
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Embedded sql, multirow fetch and date column
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.
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
