PQfformat question and retrieving bytea data in C

From: Jason Armstrong <ja(at)riverdrums(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PQfformat question and retrieving bytea data in C
Date: 2012-08-29 12:30:13
Message-ID: CAF2ce0oNopjWxUOOTXsNBC7ovrM_O6EtMDd8r+Babbz9R3jhdg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a question regarding the return value of PQfformat()

I have a 'data' column in my table, type bytea (postgresql 9.1.5).

In postgresql.conf:
bytea_output = 'escape'

When I execute the query:
PGresult *res = PQexec(db, "SELECT data::bytea FROM data_table WHERE id='xxx'")

And I run through the results:

int i, j;
for (i = 0; i < PQntuples(res); i++) {
for (j = 0; j < PQnfields(res); j++) {
printf("Format %d: %d\n", j, PQfformat(res, j));
printf("Type %d: %d\n", j, PQftype(res, j));
}
}

This prints that the format is type 0, and the type is 17.

Shouldn't the format be 1 (binary data)?

I am getting a discrepancy between data that I put into the table and
data I retrieve.
When I dump the data, using:

int di;
char *val = PQgetvalue(res, i, j);
for (di = 0; di < 16; di++) fprintf(stderr, "%2x ", val[di]);

I see the following:
30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30

But when I look at the same data in the database:

psql> select encode(substr(data, 0, 16), 'hex') from data_table where id='xxx';
encode
--------------------------------
30da00090132420520203137323030

This is the data I'm expecting to get back. Is the '00' (third byte)
causing the problem?

The data looks the same at a certain place (ie it starts with the same
byte 30, then the C code has 22 bytes whereas the db hex dump has 7
bytes, then the data is the same again. The 7/22 number of bytes isn't
always the same, across the different data values).

--
Jason Armstrong

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dmitriy Igrishin 2012-08-29 13:05:49 Re: PQfformat question and retrieving bytea data in C
Previous Message Bartosz Dmytrak 2012-08-29 12:15:02 Re: Dropping all foreign keys for a column in a table