X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 78AE5D8F18 for ; Wed, 14 Sep 2005 20:20:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72381-06 for ; Wed, 14 Sep 2005 23:20:18 +0000 (GMT) Received: from web53302.mail.yahoo.com (web53302.mail.yahoo.com [206.190.49.92]) by svr1.postgresql.org (Postfix) with SMTP id B401BD8E7D for ; Wed, 14 Sep 2005 20:20:17 -0300 (ADT) Received: (qmail 31310 invoked by uid 60001); 14 Sep 2005 23:20:13 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=HlLM1ThY/3doZXlMLrvTQzwKE4+L4QnRZ+lMSVH8jKXh3+m7fE0Yt3p8SiRk5hbuhlOvUh62SQhSwjiCk1AC1dlfGE/tUuj3QNPSJIs1exOSv1EavD9MGw80tEl9AiKVjkcmIjQUjzQlQnnj+k5y+FIN8lIBHhBKC3oXVS8Ve1s= ; Message-ID: <20050914232013.31307.qmail@web53302.mail.yahoo.com> Received: from [204.27.176.5] by web53302.mail.yahoo.com via HTTP; Wed, 14 Sep 2005 16:20:13 PDT Date: Wed, 14 Sep 2005 16:20:13 -0700 (PDT) From: Mark Richardson Subject: libpq, ecpg and the bytea data type To: pgsql-interfaces@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1662275861-1126740013=:30694" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.506 required=5 tests=[AWL=0.075, DNS_FROM_RFC_ABUSE=0.374, HTML_30_40=0.056, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200509/21 X-Sequence-Number: 5744 --0-1662275861-1126740013=:30694 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1662275861-1126740013=:30694 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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. --0-1662275861-1126740013=:30694--