Inserting TIMESTAMP in SQL/MP programmatically

[Dave]

I apparently have been off using sql/mx too long and forgotten most of what I ever knew about MP. I am trying to insert a row with a column defined as TIMESTAMP. It has to be something pretty simple and any help would be appreciated.

For a start, I have no problem from mxci with a sqlmp alias:

>insert into myct.mysch.testtabl (node,upd_dtm) values ('ab', TIMESTAMP '2012-05-04 10:10:06.610000');
..

--- 1 row(s) inserted.

But when I try this from a Guardian program using:

memcpy ( rec.node, "ab", 2);
strcpy (rec.upd_dtm, "2012-05-04 10:10:06.610000");
rec.upd_dtm[26] = 0;

EXEC SQL INSERT INTO testtabl
(
node,
upd_dtm
)
VALUES
(
:rec.node,
:rec.upd_dtm indicator :rec.upd_dtm_i TYPE AS TIMESTAMP
);

I get a a sqlcode = -8426 -- bad date format.

==================================

Here is the actual test program:

#pragma nolist
#pragma wide

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <errno.h>

#pragma list

exec sql begin declare section;
exec sql invoke TESTTABL as test_def;
struct test_def rec;
exec sql end declare section;

short sqlcode;

short main (int argc,char *argv[])
{

memcpy ( rec.node, "ab", 2);
strcpy (rec.upd_dtm, "2012-05-04 10:10:06.610000");
rec.upd_dtm[26] = 0;

EXEC SQL INSERT INTO testtabl
(
node,
upd_dtm
)
VALUES
(
:rec.node,
:rec.upd_dtm indicator :rec.upd_dtm_i TYPE AS TIMESTAMP
);

return 0;
}
==================================

The sql/mp table script:

Drop Table TESTTABL;
-- SQL operation complete.
Create Table TESTTABL
(
NODE Char(2) Not Null,
UPD_DTM Timestamp Default Null,
Primary Key (
NODE
)
)
Organization Key Sequenced
Catalog $DATA.MYCAT
Blocksize 4096
Extent (10,10)
Maxextents 16
Audit
NO Clearonpurge
NO Dcompress
NO Icompress
NO Serialwrites
NO Verifiedwrites
Buffered
NO Auditcompress
Similarity Check Enable;

==================================

And the compile command:

c/in testtstc/testtst;symbols,suppress,runnable,sql
sqlcomp /in testtst/ catalog $data.mycat

[Keith Dick]

You are trying to express the date in default format and that format uses colon rather than space between the end of the date and beginning of the time:

"2012-05-04:10:10:06.610000"

The USA and European formats do use a space between the date and time parts, but the default format is different, for some reason. This is shown in the manual entry for datetime literals, but I guess you either didn't look it up or didn't notice that subtle difference.

[dave....@gmail.com]

Thanks Keith. I spent literally hours in the MP and the C for MP manual but it never occurred to me that the format of the time would be different and I certainly never noticed - probably because I was focused on the coding differences between mp and mx.

Thx.