Native SQL Insert of Timestamp Variable

[Former Member]

Hello all,

I have been struggling with this for a couple days now, seems straightforward, but clearly is not because I've been struggling with it. I'm writing entries to a SQL database on the iSeries. Pretty simple. This next statement works fine.

                EXEC SQL.

                   INSERT INTO SAP_TO_VEEVA_FILE_ATTACHMENT_UPLOAD (WABARCODE, WAKEY, WAORDERID, WAFNAM, WAFILEPATH, ZZGMTCRTM)

                       VALUES (:wa_veeva_loc-wabarcode, :wa_veeva_loc-wakey, :wa_veeva_loc-waorderid, :wa_veeva_loc-wafnam,

                       :wa_veeva_loc-wafilepath, TIMESTAMP('2013-03-15 18:30:00'))

                       WITH NC

                 ENDEXEC.

However, the TIMESTAMP static entry does me no good. I need that to be a variable. When I try to do that it fails. For instance, this code fails with the error message "Use of parameter marker not valid." Which I'm not even sure what that means.

               DATA: tmp_date(19) VALUE '2013-03-15 18:30:00'.

                 EXEC SQL.

                   INSERT INTO SAP_TO_VEEVA_FILE_ATTACHMENT_UPLOAD (WABARCODE, WAKEY, WAORDERID, WAFNAM, WAFILEPATH, ZZGMTCRTM)

                       VALUES (:wa_veeva_loc-wabarcode, :wa_veeva_loc-wakey, :wa_veeva_loc-waorderid, :wa_veeva_loc-wafnam,

                       :wa_veeva_loc-wafilepath, TIMESTAMP(:tmp_date))

                       WITH NC

                 ENDEXEC.

Any help would be so greatly appreciated. I'm still working on it so if I find the solution I'll post it.

Thanks,

Greg

[Former Member · Accepted solution]

I think this posted multiple times. I apologize I kept getting errors and tried again.

Of course I always post and then figure it out. Here is what I found if anyone has a similar issue in the future.

Although they say string and preceeding zeros can be put into TIMESTAMP I found that not to be true. I had to explicitly have a character 19 field with the exact format of YYYY-MM-DD HH:MM:SS. Even a character 20 field failed. Seems odd with a Cast.

Also, one would believe that TIMESTAMP as a command would explicitly CAST. I found this also not to be true. I had to explicitly  cast.

This statement worked for me.

...TIMESTAMP( CAST(:tmp_date AS TIMESTAMP))  seems redundant.

Now I think that just ... CAST(:tmp_date AS TIMESTAMP) would work. The previous works for me so I'm not messing around with it anymore.

Thanks

Greg