pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedConvertion of date/time binary format to text format
3+ messages / 2 participants
[nested] [flat]
* Convertion of date/time binary format to text format
@ 2005-12-23 06:55 Mavinakuli, Prasanna (STSD) <prasanna.b-m@hp.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mavinakuli, Prasanna (STSD) @ 2005-12-23 06:55 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
I am getting data in binary mode..(schema contains bytea so we need to
get result in binary format)
But we have timestamp in schema.this would be also in
Binary format.
So how can I get the ASCII format/Text format from that binary format.
Please get me the steps to convert timestamp column which is of
****binary mode to TEXT/ASCII mode****..
Thanking You,
Prasanna.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Convertion of date/time binary format to text format
@ 2005-12-27 18:53 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Mavinakuli, Prasanna (STSD) <prasanna.b-m@hp.com>
0 siblings, 0 replies; 3+ messages in thread
From: Volkan YAZICI @ 2005-12-27 18:53 UTC (permalink / raw)
To: Mavinakuli, Prasanna (STSD) <prasanna.b-m@hp.com>; +Cc: pgsql-interfaces
On Dec 23 12:25, Mavinakuli, Prasanna (STSD) wrote:
> So how can I get the ASCII format/Text format from that binary
> format.
>
> Please get me the steps to convert timestamp column which is of
> binary mode to TEXT/ASCII mode
I attached an example program which answers your question. Hope
this helps.
(I skipped byte order conversions in my example for simplicity. Don't
forget 'em in your own program for portability.)
Regards.
--
"We are the middle children of history, raised by television to believe
that someday we'll be millionaires and movie stars and rock stars, but
we won't. And we're just learning this fact," Tyler said. "So don't
fuck with us."
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
void
SmartQuit(PGconn *conn)
{
int stat = 0;
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "C> %s", PQerrorMessage(conn));
stat = 1;
}
PQfinish(conn);
exit(stat);
}
int
main(void)
{
PGconn *conn;
PGresult *res;
char *vcol0, *vcol1;
int lcol0, lcol1;
conn = PQconnectdb("dbname=test");
if (PQstatus(conn) != CONNECTION_OK)
SmartQuit(conn);
PQclear(PQexec(conn,
"BEGIN;"
"CREATE TEMP TABLE t_tmp (col0 text, col1 date);"
"INSERT INTO t_tmp VALUES ('example text', now()::date)"));
/* Give attention to ::text casting for date column. */
res = PQexecParams(conn, "SELECT col0, col1::text FROM t_tmp", 0, NULL, NULL, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "R> %s", PQresultErrorMessage(res));
PQclear(res);
SmartQuit(conn);
}
vcol0 = PQgetvalue(res, 0, 0);
lcol0 = PQgetlength(res, 0, 0);
vcol1 = PQgetvalue(res, 0, 1);
lcol1 = PQgetlength(res, 0, 1);
printf("(%d bytes) '%s'\n", lcol0, vcol0);
printf("(%d bytes) '%s'\n", lcol1, vcol1);
/*
* (12 bytes) 'example text'
* (10 bytes) '2005-12-27'
*/
PQclear(res);
PQclear(PQexec(conn, "ROLLBACK"));
PQfinish(conn);
return 0;
}
Attachments:
[text/plain] libpq_binary_date.c (1.2K, ../../20051227185351.GA546@alamut/2-libpq_binary_date.c)
download | inline:
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
void
SmartQuit(PGconn *conn)
{
int stat = 0;
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "C> %s", PQerrorMessage(conn));
stat = 1;
}
PQfinish(conn);
exit(stat);
}
int
main(void)
{
PGconn *conn;
PGresult *res;
char *vcol0, *vcol1;
int lcol0, lcol1;
conn = PQconnectdb("dbname=test");
if (PQstatus(conn) != CONNECTION_OK)
SmartQuit(conn);
PQclear(PQexec(conn,
"BEGIN;"
"CREATE TEMP TABLE t_tmp (col0 text, col1 date);"
"INSERT INTO t_tmp VALUES ('example text', now()::date)"));
/* Give attention to ::text casting for date column. */
res = PQexecParams(conn, "SELECT col0, col1::text FROM t_tmp", 0, NULL, NULL, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "R> %s", PQresultErrorMessage(res));
PQclear(res);
SmartQuit(conn);
}
vcol0 = PQgetvalue(res, 0, 0);
lcol0 = PQgetlength(res, 0, 0);
vcol1 = PQgetvalue(res, 0, 1);
lcol1 = PQgetlength(res, 0, 1);
printf("(%d bytes) '%s'\n", lcol0, vcol0);
printf("(%d bytes) '%s'\n", lcol1, vcol1);
/*
* (12 bytes) 'example text'
* (10 bytes) '2005-12-27'
*/
PQclear(res);
PQclear(PQexec(conn, "ROLLBACK"));
PQfinish(conn);
return 0;
}
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Convertion of date/time binary format to text format
@ 2006-01-02 14:49 Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 0 replies; 3+ messages in thread
From: Volkan YAZICI @ 2006-01-02 14:49 UTC (permalink / raw)
To: Mavinakuli, Prasanna (STSD) <prasanna.b-m@hp.com>; +Cc: pgsql-interfaces
On Jan 02 09:26, Mavinakuli, Prasanna (STSD) wrote:
> Could u give me one more favour..
> That is can u please elobarate on bytea order conversion...?
You can find lots of information about "byte order coersion" on the
internet. I'd suggest reading these links:
http://www.cs.umass.edu/~verts/cs32/endian.html
http://www.netrino.com/Publications/Glossary/Endianness.html
But for a "quick & dirty" answer:
o. You don't have to make any byte order coersion on types smaller
than 0xFF = 256 bytes - like char.
o. You _should_ make byte order coersion on types bigger than
256 bytes - like int. From PostgreSQL documentation's
libpq-example.html:
char *iptr;
int ival;
iptr = PQgetvalue(res, i, i_fnum);
/*
* The binary representation of INT4 is in network byte order,
* which we'd better coerce to the local byte order.
*/
ival = ntohl(*((uint32_t *) iptr));
Otherwise, you'll encounter different results in data transfers
on different architectures with different endianness.
Regards.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-02 14:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-23 06:55 Convertion of date/time binary format to text format Mavinakuli, Prasanna (STSD) <prasanna.b-m@hp.com>
2005-12-27 18:53 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-01-02 14:49 Re: Convertion of date/time binary format to text format Volkan YAZICI <yazicivo@ttnet.net.tr>
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