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
>
>
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 |