Re: How to use BYTEA type?

Christopher Sawtell wrote:

> Greetings folks,
>
> Please could a kind soul tell be how to extract data from a BYTEA type of
> field into a file, or better still tell me where I can find some doco?

Bruce has already written some minimal documentation which is in the 7.2
beta. I'm hoping to add to that prior to the 7.2 release

>
> This is the table:-
>
> create table fax_info ( id serial, comment text, image bytea) ;
>
> This appears to work ( no erorr messages ):-
>
> chris=# insert into fax_info ( comment, image ) values
> ( 'Faking it with a computer', byteain ('picture.pgm.gz'));
> INSERT 18772 1
>
> Is this correct?

No. You'll end up with literally the text 'picture.pgm.gz' in image.

What you need to do is escape 3 special characters in your application,
and then insert the escaped string directly. How exactly you do that
will vary depending on your application layer language. For example, in
PHP, you would do something like:

$image_name = "/tmp/myimage.jpg";
$fp = fopen($image_name,"r");
$image = fread($fp, filesize($image_name));
fclose($fp);

$esc_string = bytea_esc($image);
$sql = "insert into fax_info(comment,image) ";
$sql .= "values ('my image comment','$esc_string')";
$rs = pg_exec($conn, $sql);

where bytea_esc() is the function that escapes the special characters.
The three are ASCII 0, ASCII 39 (single quote), and ASCII 92 (single
backslash). In 7.2 there is a libpq function which can be called from
your C program to do the escaping, but for now, and in other programming
environments you may have to write your own. I have seen posts
indicating that the Perl DBI library for Postgres does have this
function already.

The escaping is a little tricky, and again varies depending on your
programming environment. When the string *reaches PostgreSQL*, it needs
to be escaped like this:

ASCII 0 ==> \\000
ASCII 39 ==> \' or \\047
ASCII 92 ==> \\\\ or \\134

So an input string like 'hello<ASCII 0>world' would wind up being
inserted like (where <ASCII 0> is a single 0 byte):

insert into foo(mybytea) values('hello\\000world');

As I said, the escaped string in your programming environment may need
to be different. In PHP for example, one set of backslashes is striped
by the PHP language parser (so \\ becomes \), so the actual function I
use looks like:

function bytea_esc($ct)
{
$buf = "";
for ($i = 0; $i < strlen($ct); $i++)
{
if (ord($ct[$i]) == 0)
$buf .= "\\\\000";
else if (ord($ct[$i]) == 39)
$buf .= "\\\\047";
else if (ord($ct[$i]) == 92)
$buf .= "\\\\134";
else
$buf .= $ct[$i];
}
return $buf;
}

>
> Now, how do I get my picture out again?
>

To get it back out, you query it out the same as any other field. The
catch is that all "non-printable" characters (which is quite a few more
than the three above) are returned to you escaped, i.e. ASCII 255 will
be returned as '\377'. So again you need to unescape the returned string
using your application programming language. In PHP there is a native
function which works great: stripcslashes(). So to complete the PHP example:

$sql = "select image from fax_info ";
$sql .= "where serial = 1";
$rs = pg_exec($conn, $sql);
$image = stripcslashes(pg_result($rs,0,0));

header("content-type: image/jpeg");
echo $image;

Hope this helps,

Joe