Trouble with timestamp in PQprepare/PQexecPrepared

[Viktor Pavlenko <vvp@xxxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general