AW: Embedded SQL date in DS array problem

Hi,

FYI,
SQL does not force to use date format *ISO, SQL always uses the 4 Byte
binary value of the date. The date format is only used to make the date
readeable. In this way it is for example possible to use date format *MDY
(with a 2 digit year) in interactive active SQL (STRSQL) and insert a row
into a table with the date '0001-01-01', even though it is out of valid
range for a 2 digit date format.

Contrary in RPG a date is always converted into the character representation
and reconverted before calulating with the date or writing/updateing a
record to a file. Because of these conversions dates must be always compared
with the valid range and a date value out of valid range causes an error. If
a field with 2 digit date format is initialized with '0001-01-01' the
program will fail with either RNX0112 or RNX0103.

Because of this coversions Paul may not have had any problems when defining
the date as character.

With embedded SQL for each host variable used in a SQL statement an
additional field (SQL00001 to SQLxxxxx) is created. If dates are used in an
SQL statement the date format specified in the compile command or set option
statement is used, while date formats specified in the D- or H-Specs are
ignored. In this way an SQL program may fail, because a 4 digit year format
(*ISO, *USA, *EUR, *JIS) is used for the RPG variable, but the default date
format (*JOB) in the compile option is used. In this way the SQL variable
gets a date format with a 2 digit year. The program will fail, as soon a the
RPG variable is initialized for example with *LOVAL ('0001-01-01') and moved
to the additional SQL-Variable SQLxxxxx.

Normally all problems can be cirumvented by using any of the 4 digit year
date formats for the SQL variable, i.e. specifying it in either the compile
command or in a SET OPTION statement.

When I tested Paul's code yesterday, an array data structure, a MODS and a
single data structure with a date field were always defined my souce code,
but only one of them was used in the fetch statement. The defintion of these
data structures in the compile list were correct.

BTW I also tried fixed and free embedded SQL.

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-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von CRPence
Gesendet: Sunday, January 20, 2008 01:40
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: Embedded SQL date in DS array problem


I asked because I did copy/paste to try the compile :-) and I wanted
to be sure I was not missing something. I had figured the reference was
to both the DS field and the DB field, but I was not sure. For some
reason my brain was stuck on the word /variable/ for the DS field. For
a column it is moot, as SQL DDL always creates the date column with *ISO
attribute. However there was no indication that I could see, why the
RPG field in the DS would have been defined as *ISO; understand, I am
only somewhat literate in RPG. I ignored the SET OPTION because I was
thinking that was /just the SQL/; I see the implication now... The
expectation that the pre-compiler would force the *ISO definition in the
DS [or whatever it needs to do to function without error], given the SET
OPTION was coded as the first SQL statement.

So with my less experienced perspective, for lack of seeing the Date
Format coming from an External definition, I inferred the internal
definition must be defaulting to *JOB. Thus I changed the source to add
the DatFmt(*ISO) on the DS field and *it compiled*! But apparently it
compiled only because I have additionally specified the DATFMT(*ISO) on
the compile. That is...

The slightly modified source shown below actually does compile fine
for me on v5r3 when specifying DATFMT(*ISO) on the compile [which is my
default]. I also experienced that the unchanged source _does not_
compile with the default DATFMT(*JOB); neither does the modified source
The changed source per CMPPFM:

I - D MyDate d DatFmt(*ISO)
D - D MyDate d

Sometimes it pays to be somewhat confused or even clueless, to
circumvent a problem. ;-) At least I hope my unwitting changes prove
functional [for compile; not tested functionally for run-time] for you also.

Regards, Chuck