RE: Embedded SQL date in DS array problem

I'm sorry that I was unavailable to respond to this earlier. I
want to explain something about the blocked fetch statement. As you
know when the precompiler finds an SQL statement, it generates RPG code.
For almost all statements, a temporary work variable is declared and
then assigned to the host variable.
Example:
select col1 into :myvar from mytable
There would be an
EVAL MYVAR = SQL_0000x

Even when a data structure is used in an SQL statement, the precompiler
generates an EVAL for every subfield.

However, for the blocked fetch statement, the precompiler does not
use a temporary work variable. For this reason, the precompiler
must make sure that it has the exact same memory layout as the compiler.
If there is a reason that the precompiler does not have the same layout,
the blocked fetch will get an error. Some reasons why the precompiler
will have a different layout are: OVERLAY, subfield with no name,
precompiler
couldn't resolve the LIKE. Another reason would be that the DATE or TIME
format for SQL is not the same as the compiler. The precompiler
requires the format to be in an H spec or in a keyword on the variable
declaration. That must match what is specified for the SQL date format.

You found a bug where the precompiler checks the date format before the
set option had been applied.

I'm sorry for all the time that the precompiler has wasted you and others.

Gina

date: Sun, 20 Jan 2008 09:37:47 -0000
from: "Paul Tuohy" <tuohyp@xxxxxxxxxxxxxxxxx>
subject: RE: Embedded SQL date in DS array problem

Hi Chuck,

Thank you, you hit on a solution - it is for all the wrong reasons and
should not be so but it is a solution <g>

When you have a date field in DS array (or MODS) host variable it looks as
if the pre-compiler ignores the SET OPTION DATFMT = *ISO directive and the
default DATFMT(*ISO) on the H Spec.

(Chuck, in RPG IV the default format for date fields defined in a program

is

specified using the DATFMT keyword on the H SPEC. If the DATFMT keyword is
not present it defaults to *ISO. This is not the same as the old UDATE and
*DATE stuff that defaults to *JOB).

The program compiles if

1. (As Chuck discovered) I specify DatFmt(*ISO) on the Date field and
compile with DATFMT(*JOB)
2. I specify DatFmt(*ISO) on the H Spec and compile with DATFMT(*JOB)

I can honestly say that over the years the pre-compiler has wasted more of
my time than anything else on the system :-(

One more to the list of "this sucks".

Regards

Paul Tuohy
ComCon
www.comconadvisor.com
www.systemideveloper.com