Hi,
just for information.
Embedded SQL creates for each host variable an additional variable (SQL +
running number).
As for date variables embedded SQL does not care about the date format
specified in either D- or H-specs but uses the date format specified in
either the Creation Statement or in an SET OPTION-Statement integrated in
the source.
In a table or physical file the date is always stored as 4 byte binary value
(called Scaliger Number) representing the number of days since X.
A date format is only be used to make this 4 Byte Integer date value
readable.
RPG itself always converts the date value into a character presentation
depending on the date format either specified in the D- or H-specs (or if
neither specified it uses ISO) and reconverts it immediately before writing
into a table. That's why you won't see the binary value even in debugging an
displaying the Hex-Values and that's why RPG may get an overflow when using
date formats with a 2 digit year. The error you'll get is RNX... (and not a
negative SQL code when using embedded SQL).
SQL itself does not care about any date format because it always uses the
binary value.
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: Thursday, 06. May 2010 22:19
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: SQL null indicators when not all columns are null capable
Rick.Chevalier wrote on Thursday, April 15, 2010 08:24:
The issue was that the date in the table was a date data type in
*ISO format. The embedded SQL was using the date format for the
job when it retrieved the records which was *MDY. The result was
placing mm/dd/yy into a date expecting to receive yyyy/mm/dd.
Adding the DATFMT(*ISO) option to my SET statement corrected the
issue.
As I'm typing this I'm wondering why it matters. If the date in
the table is a date data type and the field in the result set is
a date data type why would the representations matter? Maybe it
really is a bug? Or, is the value SQL places into the result set
the same representation it would use if displaying on the screen?
Hmmm... tax day post. No wonder I did not get back to this ;-)
I agree it should not matter if the "date data type" of RPG is
used for target of the "database SQL DATE data type". The reason I
asked about what shows in the listing, is that the SQL precompiler
decides what to generate for the data type of the variables. The
original information showed a LIKE(aDateDBfield) for the declare of
the RPG variable, but if the SQL decided to generate a CHAR(8) or
CHAR(10), then I expect that the result would be the same as for the
CHAR(date-expression, LOCAL) for which the DATFMT() of the *JOB is
used to cast the date to a CHAR value represented by the date format
attribute; e.g. *MDY. That is, if the SQL decided to use CHAR
versus DATE, then the date format matters. If the SQL honored the
attempt to use the RPG Date data typed variable, then the date
format should be moot.
CRPence wrote on Wednesday, April 14, 2010 14:20:
What does DATFMT() show in PRTSQLINF? How is the variable
ReceivedBack declared according to the listing? Does the
returned data match the expected format; i.e. the SQL date
format? If the LIKE declared variable appears other than as
DATE(10), what if the variable is declared instead, explicitly
as an alphanumeric with a length of 10 bytes?
Regards, Chuck
midrange.com
