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