pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Sebastien FLAESCH <sf@4js.com>
To: pgsql-interfaces@postgresql.org
Subject: INTERVAL data type and libpq - what format?
Date: Tue, 19 May 2009 10:12:20 +0200
Message-ID: <4A1269E4.1060708@4js.com> (raw)
Hello,
I try to use the new 8.4 INTERVAL type with libpq, but get crazy with
the input formatting rules...
I use PQprepare() / PQexecPrepared() with parameter list, binding the
INTERVAL values with the 1186 pg_type and passing a string buffer with
values like:
"12345" for an INTERVAL YEAR
The INSERT works without error, but when selecting rows from the table
in psql, I get "00:00:00" values ?!?!
When inserting the value "12345" from the psql command tool it works...
I must be doing something wrong, but I could not find any documentation
on using INTERVAL in libpq...
Can someone from the hackers just tell me if it's supposed to work and
if yes what format is expected by the client library?
Attached, you have a test case to reproduce the problem.
Thanks a lot!
Seb
/*
Version: 8.4.beta1
Created by: sf@4js.com
Problem with INTERVAL input format
----------------------------------
After executing this program, 2 rows are present in the table.
Only the first has the expected values...
Why does the second insert fail to insert "123 11" in INTERVAL DAY TO HOUR?
Diagnostic info:
SQL State: 22007
Message : invalid input syntax for type interval: " 123 11"
Why does the third row show "00:00:00" in first INTERVAL YEAR column?
[sf@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type "help" for help.
test1=> select * from t1;
k | i1 | i2
---+--------------+-------------------
1 | -12345 years | 123 days 11:00:00
3 | 00:00:00 | 123 days 11:00:00
(2 rows)
When inserting rows with psql, the format used by the C program are supported:
test1=> insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1=> select * from t1 where k=4;
k | i1 | i2
---+--------------+-------------------
4 | -12345 years | 123 days 11:00:00
(1 row)
So what am I doing wrong here?
*/
#include <stdio.h>
#include <libpq-fe.h>
static int checkResult(PGresult * r)
{
if (r == NULL)
return 0;
switch (PQresultStatus(r)) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
return 1;
default:
return 0;
}
}
static void getErrorInfo(PGresult * r)
{
if (r == NULL)
return;
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", PQresultErrorField(r, PG_DIAG_SQLSTATE));
fprintf(stderr, " Message : %s\n", PQresultErrorField(r, PG_DIAG_MESSAGE_PRIMARY));
}
int main(int argc, char **argv)
{
PGresult *r;
PGconn *c;
Oid paramTypes[10];
const char *paramValues[10];
fprintf(stdout,"++ Connecting...\n");
c = PQconnectdb("dbname='test1' user='pgsuser' password='fourjs'");
if (c == NULL) {
fprintf(stderr,">> Could not connect.\n");
exit(1);
}
fprintf(stdout,"++ Creating table t1 ...\n");
r = PQexec(c, "DROP TABLE t1");
PQclear(r);
r = PQexec(c, "CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY TO HOUR)");
if (!checkResult(r)) {
fprintf(stderr,">> Could not create table 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
fprintf(stdout,"++ Preparing INSERT ...\n");
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
r = PQprepare(c, "s1",
"INSERT INTO t1 VALUES ( $1, $2, $3 )",
3, (const Oid *) paramTypes);
if (!checkResult(r)) {
fprintf(stderr,">> Could not prepare stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is working */
fprintf(stdout,"++ Executing INSERT (1) ...\n");
paramValues[0] = "1";
paramValues[1] = "-12345 years";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (2) ...\n");
paramValues[0] = "2";
paramValues[1] = "-12345";
paramValues[2] = " 123 11";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 2.\n");
getErrorInfo(r);
/*exit(1);*/
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (3) ...\n");
paramValues[0] = "3";
paramValues[1] = "-12345";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 3.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
PQfinish(c);
}
Attachments:
[text/plain] libpqtest1.c (3.8K, ../4A1269E4.1060708@4js.com/2-libpqtest1.c)
download | inline:
/*
Version: 8.4.beta1
Created by: sf@4js.com
Problem with INTERVAL input format
----------------------------------
After executing this program, 2 rows are present in the table.
Only the first has the expected values...
Why does the second insert fail to insert "123 11" in INTERVAL DAY TO HOUR?
Diagnostic info:
SQL State: 22007
Message : invalid input syntax for type interval: " 123 11"
Why does the third row show "00:00:00" in first INTERVAL YEAR column?
[sf@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type "help" for help.
test1=> select * from t1;
k | i1 | i2
---+--------------+-------------------
1 | -12345 years | 123 days 11:00:00
3 | 00:00:00 | 123 days 11:00:00
(2 rows)
When inserting rows with psql, the format used by the C program are supported:
test1=> insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1=> select * from t1 where k=4;
k | i1 | i2
---+--------------+-------------------
4 | -12345 years | 123 days 11:00:00
(1 row)
So what am I doing wrong here?
*/
#include <stdio.h>
#include <libpq-fe.h>
static int checkResult(PGresult * r)
{
if (r == NULL)
return 0;
switch (PQresultStatus(r)) {
case PGRES_COMMAND_OK:
case PGRES_TUPLES_OK:
return 1;
default:
return 0;
}
}
static void getErrorInfo(PGresult * r)
{
if (r == NULL)
return;
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", PQresultErrorField(r, PG_DIAG_SQLSTATE));
fprintf(stderr, " Message : %s\n", PQresultErrorField(r, PG_DIAG_MESSAGE_PRIMARY));
}
int main(int argc, char **argv)
{
PGresult *r;
PGconn *c;
Oid paramTypes[10];
const char *paramValues[10];
fprintf(stdout,"++ Connecting...\n");
c = PQconnectdb("dbname='test1' user='pgsuser' password='fourjs'");
if (c == NULL) {
fprintf(stderr,">> Could not connect.\n");
exit(1);
}
fprintf(stdout,"++ Creating table t1 ...\n");
r = PQexec(c, "DROP TABLE t1");
PQclear(r);
r = PQexec(c, "CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY TO HOUR)");
if (!checkResult(r)) {
fprintf(stderr,">> Could not create table 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
fprintf(stdout,"++ Preparing INSERT ...\n");
paramTypes[0] = 23; /* INT4 */
paramTypes[1] = 1186; /* INTERVAL */
paramTypes[2] = 1186; /* INTERVAL */
r = PQprepare(c, "s1",
"INSERT INTO t1 VALUES ( $1, $2, $3 )",
3, (const Oid *) paramTypes);
if (!checkResult(r)) {
fprintf(stderr,">> Could not prepare stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is working */
fprintf(stdout,"++ Executing INSERT (1) ...\n");
paramValues[0] = "1";
paramValues[1] = "-12345 years";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 1.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (2) ...\n");
paramValues[0] = "2";
paramValues[1] = "-12345";
paramValues[2] = " 123 11";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 2.\n");
getErrorInfo(r);
/*exit(1);*/
}
PQclear(r);
/* This is NOT working */
fprintf(stdout,"++ Executing INSERT (3) ...\n");
paramValues[0] = "3";
paramValues[1] = "-12345";
paramValues[2] = " 123 11:00";
r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
if (!checkResult(r)) {
fprintf(stderr,">> Could not exec stmt 3.\n");
getErrorInfo(r);
exit(1);
}
PQclear(r);
PQfinish(c);
}
Message-ID: <4A1269E4.1060708@4js.com>
Permalink: ../4A1269E4.1060708@4js.com/
Also on: postgresql.org/message-id/4A1269E4.1060708@4js.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: sf@4js.com
Subject: Re: INTERVAL data type and libpq - what format?
In-Reply-To: <4A1269E4.1060708@4js.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox