Simply adding the following H specs and sql options got me past the error you were seeing. I do not explicitly specify any date formats at the field level, I always set *iso in the H specs (ctl-opt) and sql options which is the first c spec or free format code (not declaration) in my program. As far as where to put sql statements, exec sql set option is the first statement following my declarations. Not inside any procedure or subroutine. In reality, it is a declaration, so I put it right next to my declarations. If you look at the pre-compiled source, it does not directly generate any RPG (as do executable statements like select into or update). Exec sql declare ... statements are similar, and I put those immediately following set option. One exception that I have found so far is declare cursor. I put that in my sub-procedure that opens the cursor since it could contain host variables that are scoped to the open procedure. These declare cursor statements go immediately following the declarations in the open cursor sub-procedure. Once again these aren't executable. Prepared cursor declarations, and statement declarations (I guess those aren't necessary) go after set option at the head of the program.
ctl-opt NoMain DatFmt(*iso);
exec sql set option datfmt=*iso;
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----Stefan Tageson <Stefan.Tageson@xxxxxxxx> wrote: -----
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
From: Stefan Tageson <Stefan.Tageson@xxxxxxxx>
Date: 05/01/2016 06:31AM
Subject: RE: Embedded sql, multirow fetch and date column
BIG thanks Birgitta, the DatFmt(*ISO) on the subfield deifinition solved the issue! I've tried to rename the subfield but that didn't make any difference.
So what I learned by your and Chuck's reply is that explicitly define the date format is a good habit and that the Exec SQL Set Options is a compiler directive only ( I should eventually have known .... ).
Again, thank you :)
Best regards
stefan.tageson@xxxxxxxx
M +46 732 369934
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: den 1 maj 2016 11:54
To: 'RPG programming on the IBM i (AS/400 and iSeries)' <rpg400-l@xxxxxxxxxxxx>
Subject: AW: Embedded sql, multirow fetch and date column
Hmm, just an idea "date" is a reserved word in SQL and date is also a data type in free format RPG.
Try to rename the subfield date to anything else, may be ISODate.
You may also try to explicitely add the date format to the sub-field definition (fix: DatFmt(*ISO) / free: Date(*ISO) )
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 11:21
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: RE: Embedded sql, multirow fetch and date column
No, it's even worse :), the precompiler throws a SQL5011 "Host structure array ds not defined or not usable" as soon as I put the date column in the ds. If I remove the date column the compile is ok. The SQL5011 points at the Fetch statements in the source.
I've not seen this error when using single row fetch, eventually this has something to do with the multi row fetch?
P tctools_dirstat1_print...
P B Export
D tctools_dirstat1_print...
D PI 1n
*---------------------------------------------------------------
D dblib 10a const
D dbfile 10a const
D outfile 250a const
D directory 1000a const
D type 1a const
D numcols 10i 0 const
D startdate d const
D p * value options( *nopass )
*---------------------------------------------------------------
*---------------------------------------------------------------------
* Local work areas *
*---------------------------------------------------------------------
D thisp c 'tctools_dirstat1_print'
D errtxt s 1024a
D wxlib s 10a
D wxfile s 10a
D preparedsummary...
d s 1n inz( *off ) static
d sqlstmt s 2000a
D SqlEof s 1n
D SqlWarn s 1n
d numberofrows s 10i 0 inz( %elem( ds ))
d rowsreturned s 10i 0
d totalreturned s 10i 0 inz( 0 )
d i s 10i 0
D ds DS qualified dim(1000)
D id 10i 0
D date d
D obj 1000a
D size 20i 0
D count 10i 0
/free
if not preparedsummary;
sqlstmt = 'select dbid, dbdate, dbobj, dbsize, dbcount ' +
'from ' + %trimr( wxlib ) + '/' + %trimr( wxfile ) +
' where dbtype = ''*'' and dbdate >= ?, and dbobj = ?' +
' order by dbdate desc ' +
' for read only';
exec sql
declare tctools_dirstat1_print_summary scroll cursor for
tctools_dirstat1_print_summarys;
exec sql
prepare tctools_dirstat1_print_summarys
from :sqlstmt;
preparedsummary = *on;
endif;
exec sql
open tctools_dirstat1_print_summary
using :startdate
:directory;
exec sql
fetch first
from tctools_dirstat1_print_summary
for :numberofrows rows
into :ds;
rowsreturned = SQLErrd( 3 );
Best regards
stefan.tageson@xxxxxxxx
M +46 732 369934
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: den 1 maj 2016 11:01
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
<rpg400-l@xxxxxxxxxxxx>
Subject: AW: Embedded sql, multirow fetch and date column
I don't know what exactly your problem is, but I've never had any problems with (real) date fields used in embedded SQL.
SET OPTION with a date format with a 4 digit year (*ISO, *EUR, *USA or *JIS) will work correctly.
Does your RPG program crash? If so, what's the error message.
If your RPG does not crash, what's the SQL code immediately after the errornous SQL-Statement?
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 10:23
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: RE: Embedded sql, multirow fetch and date column
Then I will remove the call to SetSqlOpt, but as the Set Options command is the first statement in the SQL-module, well, something else must be the problem with the date column.
Best regards
stefan.tageson@xxxxxxxx
M +46 732 369934
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: den 1 maj 2016 10:11
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
<rpg400-l@xxxxxxxxxxxx>
Subject: AW: Embedded sql, multirow fetch and date column
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!"
--
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.
--
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
