Re: Inserting a image into a database

From: Andrew Boag <andrew(dot)boag(at)catalyst(dot)net(dot)nz>
To: rterry(at)internode(dot)on(dot)net
Cc: sydpug(at)postgresql(dot)org
Subject: Re: Inserting a image into a database
Date: 2008-08-28 22:49:50
Message-ID: 48B72B8E.70204@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: sydpug

Hear your pain, this was all done on debian linux. binmode was a gotcha

I've done it with a bytea column like this

use DBI ;
use DBD::Pg qw(:pg_types); #Required for us to use the *bytea* column

reading in file from system (we need binmode here)

#Now we'll read in the contents of the raw file
my $contents = "";

open ( F,"< $passed_file");
binmode (F); #Our friend binmode

while ( read F, my $buf, 16384) {$contents .= $buf ;}
close ( F );

sanity check writing file back to system (to make sure you get the same one)

my $new_file = $passed_file.".FILECOPY";
open ( F,"> $new_file");
binmode(F);
print F $contents ;
close (F);

now the insert (you'll have to update the $id value as this had meaning)

#INSERT THE Binary DATA into the table.
$sth = $dbh->prepare( " INSERT INTO binary_image ( image_id , contents ) VALUES ( ? , ? ) " ) or die "PREPARE FAILED";

#bind_param the index starts from 1
$sth->bind_param(2, undef, { pg_type => DBD::Pg::PG_*BYTEA* });
$sth->execute($id , $contents) or die "EXECUTE FAILED";

now the select

#Now let's get the blob and write it to a file ...
$sth = $dbh->prepare( " SELECT contents from binary_image WHERE image_id = ? " );

my $content = $sth->fetchrow_hashref->{'contents'} ;

#Now lets write the
$new_file = $passed_file.".DBCOPY";
open ( F,"> $new_file");
binmode(F);
print F $content ;
close (F);

richard terry wrote:
> Hi
>
> I wondered if anyone could give me a few lines of sample code on basic,
> showing how to insert a file you have on your hard drive, into a data field
> in postgres. I'm totally stumped (seems that using the bytea field is the way
> to go from reading the doc's.
>
> Thanks in advance.
>
> Richard
>
>

In response to

Responses

Browse sydpug by date

  From Date Subject
Next Message Jacinta Richardson 2008-08-29 11:07:33 Re: Inserting a image into a database
Previous Message richard terry 2008-08-28 21:45:00 Inserting a image into a database