AW: SQLRPGLE create table

SQL tables do not support any date format.
A date in either DDS described tables as in SQL defined tables is stored as
binary value representing the number of days since X (scaliger no).
A date format is only used to make this binary date values readable.
In DDS described tables the date format was only used to make the binary
value readable with WRKF.

Just try the following:
Take a DDS described file with a date where a date format was specified and
open it with WRKF you'll see the date in the specified format.
Then do a STRSQL and execute a SELECT * from yourFile). If the specified
date format is the same as your job's date format, you'll see the date in
the expected format.
After change the date format (F13 --> 1 --> Option Date format (DATFMT) to
any other date format and rerun your query.
You'll detect the date is returned in the new date format.

You also may check the Hex value for the date by converting the column with
the SQL Scalar function.
You'll detect the hex value has nothing to do with any date representation.

Also in your embedded SQL programs do NOT change the date format to any date
format with a 2 digit year (as suggested before).
You may get problems. The SQL precompiler generates additional workfields
for all host variables. For date fields the format specified in either the
compile command or in the SET OPTION statement is used to generate the
workfields (date formats specified either in the D or H specs are ignored).
In this way you may get problems when initializing a date host variable that
is used as host variable with the low value. (RPG will crash!!!).

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 Raul A. Jager W.
Gesendet: Friday, 21. October 2011 17:42
An: RPG programming on the IBM i / System i; Midrange Systems Technical
Discussion
Betreff: SQLRPGLE create table

Actual version 7.1
I want to re-create a table using SQL, and I want to be able to use the
programs that where compiled before the change. The old table was
created in the 90's using dds.

Mi problem is that a DATE field was defined in the *DMY format, and SQL
insist in creating *ISO. I tryed "set option datfmt=*dmy" I tryed the
parameter in the RUNSQLSTM, the SYSVAL is DMY, in an interactive SQL I
used f13 to set DMY, but "create table" ignores all. I even tryed
"create table like old..."

The reason why I whant to re-create the table now is that when I tryed
to set a constraint I got a message "The table is in use" even when I
had it allocated *EXCL. Digging into messages I found "5 -Object is
damaged" and in the actions it recomends to delete and restore or create.

Is there a way to create a DMY format date using SQL?
Shoul I make the change and re-compile everything?