Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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");
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");
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


sydpug by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group