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

Re: Inserting a image into a database

From: Jacinta Richardson <jarich(at)perltraining(dot)com(dot)au>
To: Andrew Boag <andrew(dot)boag(at)catalyst(dot)net(dot)nz>
Cc: rterry(at)internode(dot)on(dot)net, sydpug(at)postgresql(dot)org
Subject: Re: Inserting a image into a database
Date: 2008-08-29 11:07:33
Message-ID: 48B7D875.90601@perltraining.com.au (view raw or flat)
Thread:
Lists: sydpug
G'day folk,

Andrew's given you some great starting points, but I'd like to just make a few
stylistic corrections to the Perl code.  So building on what Andrew wrote:

#!/usr/bin/perl -w
use strict;
use DBI ;
use DBD::Pg qw(:pg_types); # Required for us to use the *bytea* column
use Fatal qw(open close);  # Throw exception if we fail to open or close

# You'll need to change these
my $dbname   = "name of your database";
my $username = "your database username, or empty";
my $password = "your database password, or empty";
my $id       = "id of image for insert";

my $passed_file = $ARGV[0];  # Assuming it was passed in on the command line

# Read in the contents of the raw file
my $contents = "";
open (my $in_fh,"<",$passed_file);  # 3 arg open and scalar filehandles

# Tell Perl not to translate newline characters
binmode($in_fh);

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

# sanity check writing file back to system (to make sure you get the same
# one)  Not essential, but a good test.
my $file_copy = $passed_file.".FILECOPY";
open (my $out_fh,">", $file_copy);
binmode($out_fh);
print {$out_fh} $contents;    # curlies not needed, but visually helpful
close $out_fh;

# Connect to the database
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $username, $password,
	{
		AutoCommit => 0,
		RaiseError => 1
	}
);

# now the insert (you'll have to update the $id value above)
# INSERT THE Binary DATA into the table.
my $insert = $dbh->prepare("
        INSERT INTO binary_image ( image_id , contents )
        VALUES ( ? , ? )
");  # Raise Error will check for failures

# bind_param the index starts from 1 (PG_BYTEA as per the documentation)
$insert->bind_param(2, undef, { pg_type => PG_BYTEA });
$insert->execute($id , $contents);

# And it's inserted!
# now the select

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

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

# Now lets write it
my $new_file = $passed_file.".DBCOPY";
open (my $new_fh,">", $new_file);
binmode($new_fh);
print {$new_fh} $content;  # curlies not needed, but visually helpful
close $new_fh;


-- 
   ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
    `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
    (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
  _..`--'_..-_/  /--'_.' ,'           | contact(at)perltraining(dot)com(dot)au |
 (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |

In response to

sydpug by date

Next:From: richard terryDate: 2008-08-31 01:08:12
Subject: Re: Inserting a image into a database
Previous:From: Andrew BoagDate: 2008-08-28 22:49:50
Subject: Re: Inserting a image into a database

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