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;
Then query (using psql)
SELECT length(myByteaField) FROM stdin;
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;
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.
pgsql-interfaces by date
|Next:||From: Greg Sabino Mullane||Date: 2005-09-14 23:52:57|
|Subject: Re: Beta of DBD::Pg 1.44 available|
|Previous:||From: Omar Lakkis||Date: 2005-09-14 14:29:36|
|Subject: dbAppendTable return|