Skip site navigation (1) Skip section navigation (2)

libpq, ecpg and the bytea data type

From: Mark Richardson <markmapo(at)yahoo(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: libpq, ecpg and the bytea data type
Date: 2005-09-14 23:20:13
Message-ID: (view raw or whole thread)
Lists: 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;
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...
char myByteaField_[3][8];
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 MullaneDate: 2005-09-14 23:52:57
Subject: Re: Beta of DBD::Pg 1.44 available
Previous:From: Omar LakkisDate: 2005-09-14 14:29:36
Subject: dbAppendTable return

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group