Thread: Trouble with timestamp in PQprepare/PQexecPrepared

Hello,

I'm using PG 8.1.11 on linux and having trouble inserting a timestamp
value (for starters :)) from a C++ program.

The table:

CREATE TABLE test_tbl (ts TIMESTAMP (6) NOT NULL);

Insert works from psql:

INSERT INTO test_tbl VALUES (to_timestamp('20100527101705806216', 'YYYYMMDDHHMISSUS'));

C++ code:

---------------------------------------------------------------------->8

#define TIMESTAMPOID 1114

//conn is a connection

const char* stmt_name = "test_insert";
const char* stmt = "INSERT INTO test_tbl VALUES (to_timestamp($1, 'YYYYMMDDHHMISSUS'))";

Oid param_types[1];
param_types[0] = TIMESTAMPOID;

PGresult* res = PQprepare(conn, stmt_name, stmt, 1, param_types);

ExecStatusType res_status = PQresultStatus(res);
if (res_status != PGRES_COMMAND_OK) {
    string msg = string("prepare ") + stmt_name + " failed with status " +
        PQresStatus(res_status) + ", error \"" + PQresultErrorMessage(res) + "\"";
    //print msg, exit
}

const char* ts = "20100527101705806216";

const char* param_values[1];
param_values[0] = ts;

int param_lengths[1];
param_lengths[0] = strlen(ts);

int param_formats[1];
param_formats[0] = 0;

res = PQexecPrepared(conn, stmt_name, 1, param_values, param_lengths,
                     param_formats, 0);

res_status = PQresultStatus(res);
if (res_status != PGRES_COMMAND_OK) {
    string msg = string(stmt_name) + " failed with status " +
        PQresStatus(res_status) + ", error \"" + PQresultErrorMessage(res) + "\"";
    //print msg, exit
}

---------------------------------------------------------------------->8

This fails with msg:

test_insert failed with status PGRES_FATAL_ERROR, error "ERROR: 22007:
invalid input syntax for type timestamp: "20100527101705806216"
LOCATION:  DateTimeParseError, datetime.c:3423
"

Please let me know what I'm doing wrong.

Thank you.

--
Viktor

Viktor Pavlenko <vvp@cogeco.ca> writes:
> I'm using PG 8.1.11 on linux and having trouble inserting a timestamp
> value (for starters :)) from a C++ program.

The first argument of to_timestamp is not a timestamp ...

            regards, tom lane

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    TL> Viktor Pavlenko <vvp@cogeco.ca> writes:
    >> I'm using PG 8.1.11 on linux and having trouble inserting a
    >> timestamp value (for starters :)) from a C++ program.

    TL> The first argument of to_timestamp is not a timestamp ...

    TL>             regards, tom lane

Indeed! This is the fix:

#define VARCHAROID 1043
param_types[0] = VARCHAROID;

Thanks a lot for prompt reply.

--
Viktor