Re: [GENERAL] Copying bytea data out via pgsql
[EMAIL PROTECTED] (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 theflatfile info about the query > let's say leonel=> select count (*) from image; > count > --- > 1 > (1 row) > > > you gotfieldname, ---, 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Copying bytea data out via pgsql
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly You have on theflatfile info about the query let's say leonel=> select count (*) from image; count --- 1 (1 row) you gotfieldname, ---, 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Copying bytea data out via pgsql
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly