pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedPQgetvalue failed to return column value for non-text data in binary format
8+ messages / 3 participants
[nested] [flat]
* PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-21 04:34 Jeff Lynn <jmlynn@rogers.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Lynn @ 2007-05-21 04:34 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
I am new to PostgreSQL and tried using the libpq C library. I can read
a test table using PQgetvalue for text format, i.e. format = 0.
However, when I experiment with using binary output format, I was unable
to read anything else except result set fiend that is in char(x),
varchar(x), i.e., character string data.
What have I done wrong?
int * pId;
LPSTR pszName;
double * pSalary;
PGresult *pResult = PQexecParams(m_pConn, (LPCTSTR)strSelect, nParams,
parmTypes, paramValues, parmLengths, parmFormats, 1);
pId = (int *)PQgetvalue(pResult, row, 0);
pszName = (LPSTR)PQgetvalue(pResult, row, 1);
pSalary = (double *)PQgetvalue(pResult, row, 6);
printf("id: %d name: %s salary: %f", *pId, pszName, *pSalary);
What I got is as follow:
+ + + printing resultset, 2 rows, 8 columns
id: 167772160 name: Amy Who salary: 0.000000
id: 16777216 name: David Smith salary: 0.000000
Which supposed to return:
id: 10 name: Amy Who salary: 150000.00
id: 1 name: David Smith salary: 350000.00
Any help is greatly appreciated!
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-21 05:57 Andrew McNamara <andrewm@object-craft.com.au>
parent: Jeff Lynn <jmlynn@rogers.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andrew McNamara @ 2007-05-21 05:57 UTC (permalink / raw)
To: Jeff Lynn <jmlynn@rogers.com>; +Cc: pgsql-interfaces
>I am new to PostgreSQL and tried using the libpq C library. I can read
>a test table using PQgetvalue for text format, i.e. format = 0.
>However, when I experiment with using binary output format, I was unable
>to read anything else except result set fiend that is in char(x),
>varchar(x), i.e., character string data.
>
>What have I done wrong?
The data returned in binary mode is in a (largely undocumented)
PostgreSQL form. Types like int4, and float8 are returned in network
byte order [1], while other types such as timestamps are in more subtle
formats. The only canonical reference to these formats is the PostgreSQL
source.
In general, you should just use the text format, as it's more portable
and less likely to change over time, although the specifics of the text
format are also largely undocumented.
[1] http://en.wikipedia.org/wiki/Endianness
--
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-21 21:46 Jeff Lynn <jmlynn@rogers.com>
parent: Andrew McNamara <andrewm@object-craft.com.au>
0 siblings, 2 replies; 8+ messages in thread
From: Jeff Lynn @ 2007-05-21 21:46 UTC (permalink / raw)
To: Andrew McNamara <andrewm@object-craft.com.au>; +Cc: pgsql-interfaces
Thanks you very much for your help and clarification. As programmer in
heart, one tends to go straight the specific api. I guess once one
become so comfortable with a particular open source software, then one
can go to the source to understand what is happening there. But in my
case, I guess I am far from that level of sophistication in
understanding PostgreSQL.
This is interesting position to be taken by PostgreSQL. For simple
query, particularly with regards to reporting, one can go about using
text for all column. But a lot of time, I deal with large dataset
ingestions and extracts that tends to take many hours to perform. So I
usually prefer binary form data for performance reason. To that end, I
found there is a significant different in the time it takes to ingest or
extract data from one database to another database of totally different
vendor. Most of our company's works are done in Java nowadays, but for
large data ingestions/extract/conversion, a C program out performs Java
program fair and square, no debate there (based on actual run-time
data). For clarification, we deal with various business partners that
we have to import/export large amount of data between heterogeneous
databases that the wonderful technology of database replication does not
apply for our use.
For this kind of operations, I even found the performance gain by
creating C program out-weight a Java program for that same purpose.
I can get by using the socket function, ntohl() and ntohs() for smallint
and int4 for the conversion. But there is no such thing for the float4
and float8 as well as the date, timestamp you mentioned. I wish there
is such function or macro available for the C interface.
I do not agree with the "ease of portability" issue, because if that is
the case, one can always give up performance and use Java for that
purpose. Beside, it should not be that much more difficult to provide
win32 exe for PostgreSQL with PQgetvalue() that return int2, int4,
float4, float8, date, datetime/timestamp, etc.
Jeff,
Andrew McNamara wrote:
> The data returned in binary mode is in a (largely undocumented)
> PostgreSQL form. Types like int4, and float8 are returned in network
> byte order [1], while other types such as timestamps are in more subtle
> formats. The only canonical reference to these formats is the PostgreSQL
> source.
>
> In general, you should just use the text format, as it's more portable
> and less likely to change over time, although the specifics of the text
> format are also largely undocumented.
>
> [1] http://en.wikipedia.org/wiki/Endianness
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-21 23:39 Andrew McNamara <andrewm@object-craft.com.au>
parent: Jeff Lynn <jmlynn@rogers.com>
1 sibling, 0 replies; 8+ messages in thread
From: Andrew McNamara @ 2007-05-21 23:39 UTC (permalink / raw)
To: Jeff Lynn <jmlynn@rogers.com>; +Cc: pgsql-interfaces
>This is interesting position to be taken by PostgreSQL. For simple
>query, particularly with regards to reporting, one can go about using
>text for all column. But a lot of time, I deal with large dataset
>ingestions and extracts that tends to take many hours to perform. So I
>usually prefer binary form data for performance reason.
The performance benefits of "binary" over "text" formats are often less
than you might think. In the real world, on-the-wire binary formats
often need parsing, just like text formats, and if they don't match your
internal binary format, still more conversion is required.
>Most of our company's works are done in Java nowadays, but for
>large data ingestions/extract/conversion, a C program out performs Java
>program fair and square, no debate there (based on actual run-time
>data). For clarification, we deal with various business partners that
>we have to import/export large amount of data between heterogeneous
>databases that the wonderful technology of database replication does not
>apply for our use.
I regularly import and export millions of rows between python and
PostgreSQL - my experience is that the TCP connection to the database,
and the database itself (disk I/O) is often the limiting factor, rather
than my choice of language. Where python is the limiting factor, it's
typically the instantiation of objects representing row values that
dominates, and that would be true whether a text or binary format was
used. The remaining cases are where the processing is non-trivial, and
in those cases, I would much rather be working in a high level language
where bugs are easier to identify. It's rare indeed that I resort to
coding it in C.
>I can get by using the socket function, ntohl() and ntohs() for smallint
>and int4 for the conversion. But there is no such thing for the float4
>and float8 as well as the date, timestamp you mentioned. I wish there
>is such function or macro available for the C interface.
So do I. Serialising floats and doubles is a common enough activity,
and it defies belief that the standard C libraries don't provide a way
to do it. Having every application that wants to pass floats over the
net implement it's own scheme is just asking for bugs.
I would suggest looking at the PostgreSQL source for one example of how
to do floats. Timestamps are actually composite types made up of other
primitive binary types. The only gotcha with the time types is that they
can use ints or floats, depending on how the server was built. When
using protocol 3 with a version 8 server, there is a parameter that
informs you which was chosen.
>I do not agree with the "ease of portability" issue, because if that is
>the case, one can always give up performance and use Java for that
>purpose. Beside, it should not be that much more difficult to provide
>win32 exe for PostgreSQL with PQgetvalue() that return int2, int4,
>float4, float8, date, datetime/timestamp, etc.
Note that you're preaching to the choir - I'm just a PG user, like you,
and I find the "binary" situation less than optimal. I wouldn't have
used it if I could work out how to pass arrays as text parameters (but
now that I know how the binary types work, I suspect I could solve the
text array problem by looking at the PG source).
--
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-22 06:10 Jeroen T. Vermeulen <jtv@xs4all.nl>
parent: Jeff Lynn <jmlynn@rogers.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jeroen T. Vermeulen @ 2007-05-22 06:10 UTC (permalink / raw)
To: Jeff Lynn <jmlynn@rogers.com>; +Cc: Andrew McNamara <andrewm@object-craft.com.au>; pgsql-interfaces
On Tue, May 22, 2007 04:46, Jeff Lynn wrote:
> This is interesting position to be taken by PostgreSQL. For simple
> query, particularly with regards to reporting, one can go about using
> text for all column. But a lot of time, I deal with large dataset
> ingestions and extracts that tends to take many hours to perform. So I
> usually prefer binary form data for performance reason. To that end, I
> found there is a significant different in the time it takes to ingest or
> extract data from one database to another database of totally different
> vendor. Most of our company's works are done in Java nowadays, but for
> large data ingestions/extract/conversion, a C program out performs Java
> program fair and square, no debate there (based on actual run-time
> data). For clarification, we deal with various business partners that
> we have to import/export large amount of data between heterogeneous
> databases that the wonderful technology of database replication does not
> apply for our use.
For that kind of thing, may I suggest you try using the COPY API? That
may buy you more speed than transferring data in binary form
would—particularly for insertions.
Jeroen
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-22 18:17 Jeff Lynn <jmlynn@rogers.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Lynn @ 2007-05-22 18:17 UTC (permalink / raw)
To: pgsql-interfaces; andrewm@object-craft.com.au
Thank you for your comment about text and binary format. To use text, I
thought one will have to construct the insert sql statement on the fly,
which will incurs server parsing, syntax checking followed by
execution. With binary format, I can simply prepare a statement and
then update each binded parameter prior to execute the prepared
statement over each records.
Is there a way for me to prepare the insert statment and pass in text
instead of, say, double precision? I experimented with that the the
server reject the insert statement with "mismatched datatype" error.
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-22 18:23 Jeff Lynn <jmlynn@rogers.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jeff Lynn @ 2007-05-22 18:23 UTC (permalink / raw)
To: pgsql-interfaces; jtv@xs4all.nl
Thank you for your suggestion of COPY API. I will take a look. The
trouble is, I cannot seems to find any example that illustrate any
non-text data usage. The PostgreSQL doc is extensive, provided you
already know the details and serve well during implementation of
applications.
For beginner, there is no cohesive way to lead me read thru the doc in
proper sequence. All tutorial and examples gave me knowledge to do very
simple stuff. The extensive documentation only lead to more questions.
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Re: PQgetvalue failed to return column value for non-text data in binary format
@ 2007-05-23 09:43 Andrew McNamara <andrewm@object-craft.com.au>
parent: Jeff Lynn <jmlynn@rogers.com>
0 siblings, 0 replies; 8+ messages in thread
From: Andrew McNamara @ 2007-05-23 09:43 UTC (permalink / raw)
To: Jeff Lynn <jmlynn@rogers.com>; +Cc: pgsql-interfaces
>Thank you for your comment about text and binary format. To use text, I
>thought one will have to construct the insert sql statement on the fly,
>which will incurs server parsing, syntax checking followed by
>execution. With binary format, I can simply prepare a statement and
>then update each binded parameter prior to execute the prepared
>statement over each records.
>
>Is there a way for me to prepare the insert statment and pass in text
>instead of, say, double precision? I experimented with that the the
>server reject the insert statement with "mismatched datatype" error.
Certainly. Whether you use PQexecParams, or PQprepare and PQexecPrepared,
you can pass text or binary parameters. But I would suggest starting
with PQexecParams and getting that right before moving on to prepared
statements.
A overly simple example of PQexecParams usage:
PGresult *res;
int i;
const int l = 4;
const char *param_vals[l];
param_vals[0] = "10";
param_vals[1] = "20";
param_vals[2] = "30";
param_vals[3] = "40";
res = PQexecParams(conn, "INSERT INTO foo VALUES ($1,$2,$3,$4)",
l, NULL, param_vals, NULL, NULL, 0);
>Does someone out there has some example(s) of converting the
>PostgreSQL's internal (network byte order) representation of float4,
>flost8, date, datetime and timestamp into Win32 float, double, CTime or
>any Win32 date/time construct under Intel x86 h/w architecture?
I'd recommend against using binary args until you've got things working
seemlessly with text (and maybe not even then)... I have enough grey
hairs to prove the folly trying to use the binary interface.
--
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-05-23 09:43 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-21 04:34 PQgetvalue failed to return column value for non-text data in binary format Jeff Lynn <jmlynn@rogers.com>
2007-05-21 05:57 ` Andrew McNamara <andrewm@object-craft.com.au>
2007-05-21 21:46 ` Jeff Lynn <jmlynn@rogers.com>
2007-05-21 23:39 ` Andrew McNamara <andrewm@object-craft.com.au>
2007-05-22 06:10 ` Jeroen T. Vermeulen <jtv@xs4all.nl>
2007-05-22 18:17 Re: PQgetvalue failed to return column value for non-text data in binary format Jeff Lynn <jmlynn@rogers.com>
2007-05-23 09:43 ` Andrew McNamara <andrewm@object-craft.com.au>
2007-05-22 18:23 Re: PQgetvalue failed to return column value for non-text data in binary format Jeff Lynn <jmlynn@rogers.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