RE: Embedded sql, multirow fetch and date column -- RPG400-L

I've also tried specifying DATFMT(*ISO) on the CRTSQLRGPI cmd, with the same bad result as earlier, SQL5011 Host variable not defined or unusable.

Best regards

stefan.tageson@xxxxxxxx
M +46 732 369934

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Stefan Tageson
Sent: den 1 maj 2016 00:54
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: 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.