Help with SQL INSERT & date fields

[gwilburn]

I'm writing a trigger pgm and using SQL to insert/update/delete data in another file.

I'm inserting a new row into a table containing several date fields... the date field (rec.DEL_DATE) in my ILE RPG program contains '0001-01-01' according to debug. I do not have a value for that date field yet - a user will enter it via another application.

I'm gettting the error below. The DB file is defined using DDS, and date fields are data type "L".

Additional Message Information

Message ID . . . . . . : RNX0114 Severity . . . . . . . : 99
Message type . . . . . : Escape
Date sent . . . . . . : 03/03/16 Time sent . . . . . . : 17:01:23

Message . . . . : The year portion of a Date or Timestamp value is not in
the correct range.
Cause . . . . . : A conversion was requested to a Date value, from a
Timestamp or a Date value, and the year of the source Timestamp or Date is
not in the allowable range of the target.
-- The range for a 2-digit year is 1940-2039.
-- The range for a 3-digit year is 1900-2899.
-- The range for a 4-digit year is 0001-9999.


Here is my statement:

       exec sql insert into ASTHHOBJ.OECMPLNCE
                                     (COM_NBR, WHS_NBR, ORD_NBR, REL_NBR,
                                      ENT_NBR, ENT_NAME, PO_NBR, PROD_AMT,
                                      REQ_DATE, SHP_DATE, CAN_DATE, DEL_DATE,
                                      SBM_DATE, PROD_RDY, RDY_DATE, RTE_DATE,
                                      SHIP_VIA, SCAC, CARRIER, RTNG_ID, NOTES,
                                      LINES, QTY, CTNS, CTN_LBLS, PROD_LBLS,
                                      JOB_CAT, JOB_DESC, EST_TIME, CSR_TIME,
                                      FRK_TIME, WHS_TIME)
                       values(:rec.COM_NBR, :rec.WHS_NBR, :rec.ORD_NBR,
                              :rec.REL_NBR, :rec.ENT_NBR, :rec.ENT_NAME,
                              :rec.PO_NBR, :rec.PROD_AMT, :rec.REQ_DATE,
                              :rec.SHP_DATE, :rec.CAN_DATE, :rec.DEL_DATE,
                              :rec.SBM_DATE, :rec.PROD_RDY, :rec.RDY_DATE,
                              :rec.RTE_DATE, :rec.SHIP_VIA, :rec.SCAC,
                              :rec.CARRIER, :rec.RTNG_ID, :rec.NOTES,
                              :rec.LINES, :rec.QTY, :rec.CTNS,
                              :rec.CTN_LBLS, :rec.PROD_LBLS, :rec.JOB_CAT,
                              :rec.JOB_DESC, :rec.EST_TIME, :rec.CSR_TIME,
                              :rec.FRK_TIME, :rec.WHS_TIME); 

[Scott M]

Re: Help with SQL INSERT & date fields

You need a Set option to set a data format that allows 4-digit years. I usually have

exec sql set option datfmt=*ISO; if I have the syntax correct.

There are several ways you can set the data format including an h-spec and I think compile options. I prefer the set option - it has to be the first sql statement in your program. If you don't specify, a lot of systems are setup so that the default that ends up being used only allows the 2-digit year range so '0001-01-01' will give you an error.

[B.Hauser]

Re: Help with SQL INSERT & date fields

The SET OPTION statement is the way to go!
And the SET OPTION statemenbt must be the first SQL-Statement in your source. Just add it immediately after the global D-Specs.
It is also possible to change the DATFMT in the compile command, but you may have forgotten it when compiling the next time.

H-Specs are not considered by SQL, so changing the date format there does not affect the definition of the additional variables the SQL precompiler adds.

Birgitta

[DeadManWalks]

Re: Help with SQL INSERT & date fields

On a side note, it is bad form to hard code the library.

[gwilburn]

Re: Help with SQL INSERT & date fields

On a side note, it is bad form to hard code the library.

I thought so too... but this is only the second trigger program I've written. The libraries containing the tables are not in our system library list - we use job descriptions to set the users library list instead.

I had a note in my previous pgm that trigger pgms only use the system lib list.

Is there a better way (short of using addlible command in RPG)?

[Scott M]

Re: Help with SQL INSERT & date fields

I had a note in my previous pgm that trigger pgms only use the system lib list.

I am pretty sure this is not true. We have several trigger programs and none of them specify a library anywhere and none of the program and data libraries are in the system library list. Triggers use the library list of the job that does the change to the file as far as I know.

[gwilburn]

Re: Help with SQL INSERT & date fields

I am pretty sure this is not true. We have several trigger programs and none of them specify a library anywhere and none of the program and data libraries are in the system library list. Triggers use the library list of the job that does the change to the file as far as I know.

I believe you are correct. I removed the references to the library and it seems to work just fine (as does the SET OPTION).

Thank you both for your help!

[Michael Jones]

Re: Help with SQL INSERT & date fields

Triggers use the library list of the job that does the change to the file as far as I know.

For EXTERNAL triggers, that is true.

For SQL triggers, that is unfortunately not true. Even if you do not hard code library qualifiers for table references, and even if you compile the SQL trigger using SET OPTION DFTRDBCOL = *NONE, the compiler will insert hard coded library names into the compiled code that were fetched / resolved at compile time. So, if you create an SQL trigger, and save / restore the library it is in to a new library name, the restored SQL trigger will contain hard coded library name(s) pointing back to the old / saved library name. For a lot of reasons, I prefer SQL triggers over EXTERNAL ones, with the exception of this behavior. You can confirm this behavior easily by creating an SQL trigger, and then using Generate SQL over it from Navigator. The generated SQL will show hard coded library names, even though you specified none .

For SQL functions and procedures, use of SET OPTION DFTRDBCOL = *NONE, coupled with omitting hard coded libraries, will result in a compiled object that truly honors the library list at run time.

Mike

[gwilburn]

Re: Help with SQL INSERT & date fields

Mike,
That's great information!

I've since moved the SQL actions (add, delete, etc.) to a service program. So they are ILE RPG procedures (functions) within their own service program. Do you know if the RPG functions/procedures behave similar to the SQL functions/procedures, or do they still hard-code the library names?

Thanks,
Greg

[Michael Jones]

Re: Help with SQL INSERT & date fields

Hi Greg,

ILE RPG procedures and functions won't receive hard-coded library names as a result of a compile. However, if you save a table with an external RPG trigger attached, and restore the table to a library with a different name, the external trigger on the table will still be pointed at the original library location of the RPG trigger program. Re-pointing external triggers to a different program library name is a manual process (IIRC via RMVPFTRG and ADDPFTRG).

Triggers are great but do come with administrative overhead.

Mike