Trouble with timestamp in PQprepare/PQexecPrepared

From: Viktor Pavlenko <vvp(at)cogeco(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Trouble with timestamp in PQprepare/PQexecPrepared
Date: 2010-05-31 16:40:34
Message-ID: 19459.59010.420803.65040@hetman.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-05-31 16:47:24 Re: Libpq Asynchronous Command Processing
Previous Message Alonso 2010-05-31 16:31:27 Re: Libpq Asynchronous Command Processing