pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feed(no subject)
5+ messages / 1 participants
[nested] [flat]
* (no subject)
@ 2013-05-22 01:26 Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 16:52 ` Re: - pgaql binary data date time fornats Neeraj Rai <rneeraj@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Neeraj Rai @ 2013-05-22 01:26 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
I'm trying a simple pgsql program with PQexecPrepared and have a few questions.
I have seen the example but didn't find the answer there. googled too.
http://www.postgresql.org/docs/9.2/static/libpq-example.html
Q1. what format is date set in ? what is the length ?
Q2.Q2.what is the type and length of bit wise fields
Q3. how to convert seconds since epoch to date
Q4. is int passable as time?
Q5. the paramValsVec_ is char* const char[] . Can we type cast int to char* and pass in ? and set type to INT4OID?
Below is some pseudo code to put above questions in perspective.
>>. table
CREATE TABLE t1
(curdate date,
str1 varchar(19),
tm_new timestamptz,
count int);
>>> c++ code PGconn* pgconn; // connected
char * UpsertStmt_= "INSERT INTO t1 values( $1, $2, $3, $4)";
int result=0;
PGresult* pgres = PQprepare (pgconn, "stmtname", UpsertStmt_, 4, NULL);
int result = PQresultStatus(pgres);
PQclear(pgres_);
struct mydata
{
int curtime;
char str1[32];
int mycount;
intmybitfield : 3;
},
mydata myvar;
myvar.curtime = time(NULL)
strcpy(myvar.str1, hello);
myvar.mycount = 2;
myvar.mybitfield = 2;
// set helper vars
paramLenVec_[0] = 8;// Q1. what format is date set in ? what is the length
paramLenVec_[1] = strlen(str1);
paramLenVec_[2] = sizeof(int); // type = INT4OID, sizeof (int)
paramLenVec_[3] = sizeof(int);// Q2.what is the type and length of bit wise fields
paramValsVec_[0] = (char*)&myvar.curtime; // Q3. how to convert to date
paramValsVec_[1] = myvar.str1;
paramValsVec_[2] = &myvar.curtime; // Q4. is int passable as time
paramValsVec_[3] = (char*)&myvar.mycount; // Q5.
PQexecPrepared (pgconn, "stmtname", 4,
paramValsVec_,
paramLenVec_,
NULL, 0
);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: - pgaql binary data date time fornats
2013-05-22 01:26 (no subject) Neeraj Rai <rneeraj@yahoo.com>
@ 2013-05-22 16:52 ` Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 23:52 ` - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Neeraj Rai @ 2013-05-22 16:52 UTC (permalink / raw)
To: pgsql-interfaces; Neeraj Rai <rneeraj@yahoo.com>
Hi,
I forgot to mention that I'm trying to use binary data format.
I found exanples of sending string fornatted date time. However, i am assuming there are functions to covert unix time to pgtimestamptz and may be faster
Looking for inverse of ts.epoch
I can bypass bitwise fueld issue by assigning to int.
I think Q5 can be ignored. It should work the same for execparams and execprepared.
Thanks
Neeraj
^ permalink raw reply [nested|flat] 5+ messages in thread
* - pgaql long value corrupted using htons
2013-05-22 01:26 (no subject) Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 16:52 ` Re: - pgaql binary data date time fornats Neeraj Rai <rneeraj@yahoo.com>
@ 2013-05-22 23:52 ` Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 23:55 ` Re: - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Neeraj Rai @ 2013-05-22 23:52 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
1. I am having trouble writing long values via c interface .testlibqp-dt.c is attached.
. 2 is being inserted as 8589934592.
qsf=> select * from t1;
curdate | strf | intf | tm_new | txnid
------------+--------+--------------------+------------------------+-------
1999-01-10 | hello2 |8589934592 | 1999-01-08 04:05:06-05 | 3
I tried htobe64 but that didn't help either.
Any advise would be appreciated.
2. I have switched to using string for data and timestamp and it seems to work.
I found functions like TimestampTzGetDatum,time_t_to_timestamptz in the code
But it seems they are not exposed to interface.
I have the date time in unix format (via time and gettimeofday). I would prefer to pass those values
in binary, rather than string format. Is there any option to do this ? Or is it easily addable?
thanks
Neeraj
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: - pgaql long value corrupted using htons
2013-05-22 01:26 (no subject) Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 16:52 ` Re: - pgaql binary data date time fornats Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 23:52 ` - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
@ 2013-05-22 23:55 ` Neeraj Rai <rneeraj@yahoo.com>
2013-05-23 11:53 ` - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Neeraj Rai @ 2013-05-22 23:55 UTC (permalink / raw)
To: pgsql-interfaces
testlibqp-dt.c is attached
>________________________________
> From: Neeraj Rai <rneeraj@yahoo.com>
>To: "pgsql-interfaces@postgresql.org" <pgsql-interfaces@postgresql.org>
>Sent: Wednesday, May 22, 2013 7:52 PM
>Subject: [INTERFACES]- pgaql long value corrupted using htons
>
>
>
>Hi,
>
>
>1. I am having trouble writing long values via c interface .testlibqp-dt.c is attached.
>. 2 is being inserted as 8589934592.
>
>
>
>
>qsf=> select * from t1;
> curdate | strf | intf | tm_new | txnid
>------------+--------+--------------------+------------------------+-------
>1999-01-10 | hello2 |8589934592 | 1999-01-08 04:05:06-05 | 3
>
>
>I tried htobe64 but that didn't help either.
>Any advise would be appreciated.
>
>
>
>2. I have switched to using string for data and timestamp and it seems to work.
>
>
>I found functions like TimestampTzGetDatum,time_t_to_timestamptz in the code
>But it seems they are not exposed to interface.
>I have the date time in unix format (via time and gettimeofday). I would prefer to pass those values
>in binary, rather than string format. Is there any option to do this ? Or is it easily addable?
>
>
>thanks
>Neeraj
>
>
>
>=
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
Attachments:
[text/x-csrc] testlibpq-dt.c (3.1K, ../../1369266903.47851.YahooMailNeo@web164904.mail.bf1.yahoo.com/3-testlibpq-dt.c)
download | inline:
/*
CREATE table t1
(
curdate date,
strf varchar(10),
intf bigint,
tm_new timestamptz,
txnid smallint
);
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <arpa/inet.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
typedef struct
{
int curdate;
char strf[8];
long intf;
int tm_new;
short txnid;
} t1data;
int
main(int argc, char **argv)
{
char *user;
char *pass;
char *dbname;
char *host = NULL;
char *port = NULL;
PGconn *conn;
PGresult *res;
char insertStmt[512];
int result;
const char* stmtName = "t1insert";
const char* paramValue[5];
int paramLength[5];
int paramFormat[5];
t1data mydata;
char date[16];
char tm_new[32];
int ii=0;
if (argc < 3)
{
fprintf(stderr, "usage: dbname user pass [host][port] \n");
exit(1);
}
dbname = argv[1];
user = argv[2];
pass = argv[3];
if (argc > 3) host = argv[4];
if (argc > 4) port = argv[5];
/* Make a connection to the database */
conn = PQsetdbLogin(host, port, NULL, NULL, dbname, user, pass);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
exit_nicely(conn);
}
fprintf(stdout, "Connected to database : %s %s\n", user, dbname);
// prepare insert statement with unique key
strcpy(insertStmt, "INSERT INTO t1 values ($1, $2, $3, $4, $5);");
// let the backend prepare statement (it is parsed/planned only once)
res = PQprepare (conn, stmtName, insertStmt, 1, NULL);
result = PQresultStatus(res);
if (result != PGRES_COMMAND_OK)
{
fprintf(stderr, "PQprepare failed: %s : (%d) %s\n", insertStmt, result,PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
fprintf(stdout, "PQprepare done : %s=%s\n", stmtName, insertStmt);
PQclear(res);
// maybe multiple calls to insert data
strcpy(date, "19990110");
strcpy(mydata.strf, "hello2");
mydata.intf = htonl((uint32_t)2);
strcpy(tm_new, "1999-01-08 04:05:06 EST");
mydata.txnid = htons(3);
// set values
paramValue[0] = date;
paramValue[1] = mydata.strf;
paramValue[2] = (char*)&mydata.intf;
paramValue[3] = tm_new;
paramValue[4] = (char*)&mydata.txnid;
// set lengths
paramLength[0] = 8;
paramLength[1] = 6;
paramLength[2] = sizeof(mydata.intf);
paramLength[3] = strlen(tm_new);
paramLength[4] = sizeof(mydata.txnid);
// set format
paramFormat[0] = 0;
paramFormat[1] = 0;
paramFormat[2] = 1;
paramFormat[3] = 0;
paramFormat[4] = 1;
res = PQexecPrepared(conn, stmtName, 5, paramValue, paramLength, paramFormat, 0);
result = PQresultStatus(res);
if (result != PGRES_COMMAND_OK)
{
fprintf(stderr, "exec failed: %s : (%d) %s\n", insertStmt, result,PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
fprintf(stdout, "exec done : %s, $1=(%d)\n", stmtName, ii);
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
^ permalink raw reply [nested|flat] 5+ messages in thread
* - pgaql long value corrupted using htons
2013-05-22 01:26 (no subject) Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 16:52 ` Re: - pgaql binary data date time fornats Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 23:52 ` - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 23:55 ` Re: - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
@ 2013-05-23 11:53 ` Neeraj Rai <rneeraj@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Neeraj Rai @ 2013-05-23 11:53 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
1. I am having trouble writing long values via c interface .testlibqp-dt.c is attached.
. 2 is being inserted as 8589934592.
qsf=> select * from t1;
curdate | strf | intf | tm_new | txnid
------------+--------+--------------------+------------------------+-------
1999-01-10 | hello2 |8589934592 | 1999-01-08 04:05:06-05 | 3
I tried htobe64 but that didn't help either.
Any advise would be appreciated.
2. I am using string for data and timestamp and it seems to work.
I found functions like TimestampTzGetDatum,time_t_to_timestamptz in the code
But it seems they are not exposed to interface.
I have the date time in unix format (via time and gettimeofday). I would prefer to pass those values
in binary, rather than string format. Is there any option to do this ? Or is it easily addable?
thanks
Neeraj
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Attachments:
[text/x-csrc] testlibpq-dt.c (3.1K, ../../1369310024.8114.YahooMailNeo@web164904.mail.bf1.yahoo.com/3-testlibpq-dt.c)
download | inline:
/*
CREATE table t1
(
curdate date,
strf varchar(10),
intf bigint,
tm_new timestamptz,
txnid smallint
);
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <arpa/inet.h>
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
typedef struct
{
int curdate;
char strf[8];
long intf;
int tm_new;
short txnid;
} t1data;
int
main(int argc, char **argv)
{
char *user;
char *pass;
char *dbname;
char *host = NULL;
char *port = NULL;
PGconn *conn;
PGresult *res;
char insertStmt[512];
int result;
const char* stmtName = "t1insert";
const char* paramValue[5];
int paramLength[5];
int paramFormat[5];
t1data mydata;
char date[16];
char tm_new[32];
int ii=0;
if (argc < 3)
{
fprintf(stderr, "usage: dbname user pass [host][port] \n");
exit(1);
}
dbname = argv[1];
user = argv[2];
pass = argv[3];
if (argc > 3) host = argv[4];
if (argc > 4) port = argv[5];
/* Make a connection to the database */
conn = PQsetdbLogin(host, port, NULL, NULL, dbname, user, pass);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
exit_nicely(conn);
}
fprintf(stdout, "Connected to database : %s %s\n", user, dbname);
// prepare insert statement with unique key
strcpy(insertStmt, "INSERT INTO t1 values ($1, $2, $3, $4, $5);");
// let the backend prepare statement (it is parsed/planned only once)
res = PQprepare (conn, stmtName, insertStmt, 1, NULL);
result = PQresultStatus(res);
if (result != PGRES_COMMAND_OK)
{
fprintf(stderr, "PQprepare failed: %s : (%d) %s\n", insertStmt, result,PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
fprintf(stdout, "PQprepare done : %s=%s\n", stmtName, insertStmt);
PQclear(res);
// maybe multiple calls to insert data
strcpy(date, "19990110");
strcpy(mydata.strf, "hello2");
mydata.intf = htonl((uint32_t)2);
strcpy(tm_new, "1999-01-08 04:05:06 EST");
mydata.txnid = htons(3);
// set values
paramValue[0] = date;
paramValue[1] = mydata.strf;
paramValue[2] = (char*)&mydata.intf;
paramValue[3] = tm_new;
paramValue[4] = (char*)&mydata.txnid;
// set lengths
paramLength[0] = 8;
paramLength[1] = 6;
paramLength[2] = sizeof(mydata.intf);
paramLength[3] = strlen(tm_new);
paramLength[4] = sizeof(mydata.txnid);
// set format
paramFormat[0] = 0;
paramFormat[1] = 0;
paramFormat[2] = 1;
paramFormat[3] = 0;
paramFormat[4] = 1;
res = PQexecPrepared(conn, stmtName, 5, paramValue, paramLength, paramFormat, 0);
result = PQresultStatus(res);
if (result != PGRES_COMMAND_OK)
{
fprintf(stderr, "exec failed: %s : (%d) %s\n", insertStmt, result,PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
fprintf(stdout, "exec done : %s, $1=(%d)\n", stmtName, ii);
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-05-23 11:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-22 01:26 Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 16:52 ` Re: - pgaql binary data date time fornats Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 23:52 ` - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
2013-05-22 23:55 ` Re: - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
2013-05-23 11:53 ` - pgaql long value corrupted using htons Neeraj Rai <rneeraj@yahoo.com>
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