Re: AW: SQLRPGLE create table

["Raul A. Jager W." <raul@xxxxxxxxxx>]

Thank you Birgitta for making it clear.
I was using as a reference the output of DSPPFM, even if I suspected the 
internal format is binary. My problem comes because RPG "respects" the
option selected in DATFMT, and SQL does not have the option.

I read in this list a lot of recomendations on using SQL to create the
tables. While the file was Ok I did not wanted to get into the change,
but since I need to re-create, it may be a good oportunity to medernize.

Birgitta Hauser wrote:

> 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?