Postgres Pro
Downloads
Home   >   mailing lists

Thread: Copying bytea data out via pgsql

Copying bytea data out via pgsql

From
"John Wells"
Date:
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

Re: Copying bytea data out via pgsql

From
Leonel Nunez
Date:
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

Re: Copying bytea data out via pgsql

From
"John Wells"
Date:
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

Re: Copying bytea data out via pgsql

From
Edmund
Date:
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