From: Mark Richardson <markmapo@yahoo.com>
To: pgsql-interfaces@postgresql.org
Subject: libpq, ecpg and the bytea data type
Date: Wed, 14 Sep 2005 16:20:13 -0700 (PDT)
Message-ID: <20050914232013.31307.qmail@web53302.mail.yahoo.com> (raw)
I can't get the bytea type to behave as I would expect it to. I've tried a number of things and this is what I've found (I should also say that I'm still using 7.4 - I plan on switching to 8.0.3 soon).
If I have a table defined...
CREATE TABLE myTable (myByteaField bytea NOT NULL);
then populate it...
COPY myTable (myByteaField) FROM stdin;
\000\000\000\000\000\000\000\000
\011\101\206\155\136\035\071\135
\012\000\162\047\105\223\322\121
\.
Then query (using psql)
SELECT length(myByteaField) FROM stdin;
Length
--------
0
8
1
I'm assuming that this is because of the length function (seeing the '\0' causes strlen to be shorter than the actual data).
So I try to use the libpq interface, and when I execute the code
PQgetLength(pgresult, 0, 0)
the result is 0 (for record 0, field )
PQgetLength(pgresult, 2, 0)
The result is 1 (for record 2, field 0). So this leads me to believe that the values are stored in the database as a string, and they are being terminated during the copy (I also tried inserts, with the same results).
On a last ditch effort, I used ECPG with the following code...
EXEC SQL BEGIN DECLARE SECTION;
char myByteaField_[3][8];
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT myByteaField INTO :myByteaField_ FROM myTable;
for (int i=0; i<3; i++)
for (int j=0; j<8; j++)
std::cout << myByteaField_[i][j] << "(" << int(myByteaField_[i][j] << ").";
std::cout << std::endl;
I get the following....
(0).(0).(0).(0).(0).(0).(0).(0). <- this is expected
\(92).0(48).1(49).1(49).A(65).\(92).2(50).0(48) <- why escape sequence?
\(92).0(48).1(49).2(50).(0).(0).(0).(0) <- dropped the last 6 bytes
So I'm pretty sure that the data is stored as a string, and it is being terminated at the '\0'. ECPG not only has the same behavior, but the conversion of the data to a char array seems incorrect (maybe the ECPG pre-compiler is thinking that I want to_char() of the value of the byte array).
So my question is, how would you be able to store any data in a bytea if it contained any bytes with the value of 0 (I'm sure there are images stored as bytea and they have 0 byte values). I don't need to store an image, just these 8 bytes.
Thanks in advance for any help that you can offer (I've run out of things to try).
---------------------------------
Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.
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: markmapo@yahoo.com
Subject: Re: libpq, ecpg and the bytea data type
In-Reply-To: <20050914232013.31307.qmail@web53302.mail.yahoo.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