pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to use BYTEA type?
4+ messages / 4 participants
[nested] [flat]
* How to use BYTEA type?
@ 2001-11-01 08:40 Christopher Sawtell <csawtell@paradise.net.nz>
0 siblings, 2 replies; 4+ messages in thread
From: Christopher Sawtell @ 2001-11-01 08:40 UTC (permalink / raw)
To: pgsql-sql
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?
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?
Now, how do I get my picture out again?
TNX 10^6
Sincerely etc.
Christopher Sawtell.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to use BYTEA type?
@ 2001-11-01 10:15 Radu-Adrian Popescu <radu.popescu@www.aldratech.com>
parent: Christopher Sawtell <csawtell@paradise.net.nz>
1 sibling, 1 reply; 4+ messages in thread
From: Radu-Adrian Popescu @ 2001-11-01 10:15 UTC (permalink / raw)
To: csawtell@paradise.net.nz; +Cc: pgsql-sql
Doing
template1=> SELECT proname from pg_proc ;
proname
------------------------
boolin
boolout
byteain
byteaout
you can see there is a byteaout function also. However, these are
undocumented in the interactive docs, which is a shame indeed. I only
learned of byteain/out after reading your email.
If some of the postgresql guys could point the location of the
documentation of undocumented functions :-) that'd be great.
Radu-Adrian Popescu
DBA/programmer at www.aldratech.com
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?
>
>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?
>
>Now, how do I get my picture out again?
>
>TNX 10^6
>
>Sincerely etc.
>
>Christopher Sawtell.
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to use BYTEA type?
@ 2001-11-01 16:22 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Radu-Adrian Popescu <radu.popescu@www.aldratech.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-11-01 16:22 UTC (permalink / raw)
To: Radu-Adrian Popescu <radu.popescu@www.aldratech.com>; +Cc: csawtell@paradise.net.nz; pgsql-sql
On Thu, 1 Nov 2001, Radu-Adrian Popescu wrote:
> Doing
>
> template1=> SELECT proname from pg_proc ;
> proname
> ------------------------
> boolin
> boolout
> byteain
> byteaout
>
> you can see there is a byteaout function also. However, these are
> undocumented in the interactive docs, which is a shame indeed. I only
> learned of byteain/out after reading your email.
>
> If some of the postgresql guys could point the location of the
> documentation of undocumented functions :-) that'd be great.
AFAIK, the in and out functions are *not* meant to be called by
users. They're used by the system and I think they technically
take pointer values. To use bytea correctly, I believe you are
supposed to do some escaping and place the value into the
insert statement.
insert into table values ('\011\005\\000afkajdd\011');
(note the double backslash on the null byte). And you should
get the value back from a straight select on the column I believe.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to use BYTEA type?
@ 2001-11-01 18:08 Joe Conway <joseph.conway@home.com>
parent: Christopher Sawtell <csawtell@paradise.net.nz>
1 sibling, 0 replies; 4+ messages in thread
From: Joe Conway @ 2001-11-01 18:08 UTC (permalink / raw)
To: csawtell@paradise.net.nz; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-11-01 18:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-01 08:40 How to use BYTEA type? Christopher Sawtell <csawtell@paradise.net.nz>
2001-11-01 10:15 ` Radu-Adrian Popescu <radu.popescu@www.aldratech.com>
2001-11-01 16:22 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-01 18:08 ` Joe Conway <joseph.conway@home.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox