Copying bytea data out via pgsql

John Wells wrote:

Guys,

I have a number of jpegs and tiffs that are stored in a bytea field in a
PostgreSQL database by a Java program using Hibernate.

I need to copy these out to a flat file via pgsql for viewing, etc. I've
tried

psql -c 'select binarydata_field from my_image_table where id=1' mydb >
flatfile

but although that seems to provide the data it does not seem to pull it in
a valid image format. Is there some special handling I need to do here?

Thanks!
John

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

You have on the flatfile info about the query
let's say

leonel=> select count (*) from image;
count
-------
1
(1 row)

you got fieldname, -------, the image and ( 1 row ) on the
flatfile file

you need to make a script to put the image on that flatfile
just read it as any query and put the contents of that field on the file

I use this little Perl Script

#!/usr/bin/perl

use strict;
use DBI qw (:sql_types);
use DBD::Pg qw ( :pg_types);

my $dbh = DBI->connect ( 'DBI:Pg:dbname=mydb;' , 'myuser','mypass');

my $sth=$dbh->prepare ("select image from image where id=1");
$sth->execute();
my @row = $sth->fetchrow_array ;

open ( OUT, ">imagefile.png");
print OUT $row[0];

close (OUT);

and that get me imagefile.png

I hope this helps

Leonel

lnunez@enelserver.com (Leonel Nunez) writes:

John Wells wrote:

Guys,

I have a number of jpegs and tiffs that are stored in a bytea field in a
PostgreSQL database by a Java program using Hibernate.

I need to copy these out to a flat file via pgsql for viewing, etc. I've
tried

psql -c 'select binarydata_field from my_image_table where id=1' mydb >
flatfile

but although that seems to provide the data it does not seem to pull it in
a valid image format. Is there some special handling I need to do here?

You have on the flatfile info about the query
let's say leonel=> select count (*) from image;
count
-------
1
(1 row)

you got fieldname, -------, the image and ( 1 row ) on
the flatfile file

you need to make a script to put the image on that flatfile
just read it as any query and put the contents of that field on the file

I use this little Perl Script

or you could use some of the other options to psql,
e.g

psql -A -t -c "select ..."

From the man page:
-A

--no-align
Switches to unaligned output mode. (The default output mode is
otherwise aligned.)

-t

--tuples-only
Turn off printing of column names and result row count footers,
etc. This is equivalent to the \t command.

You might also want to consider the -o filename option

Leonel Nunez said:

I hope this helps

Leonel,

Worked perfectly...thanks. I'll do the same thing via Java for my future
use (can't guarantee Perl will be available on all of our target
machines).

I appreciate the help!

John