libpq, ecpg and the bytea data type - Mailing list pgsql-interfaces

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;

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).