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