MySQL and Blobs

I spent a long time trying to work out how I can insert binary data, such as images, into a standard mysql database using perl and the DBI interface.

A BLOB is a Binary Large Object, and can be any data at all, but in this example, it is an image.

In the end, it was quite simple, but here is a set of steps you need:

Open the file
Read the entire file into a scalar
Prepare a statement, leaving a question mark where you would put the value
Use an execute function, with the paramater or the variable containing the binary file

Sounds easy. Here is some perl:

#!/usr/bin/perl -w
# Use -w for extra help

#Load the DBI database interface
use DBI;

# Lets be strict
use strict;

# Which database and which file from the filesystem?
my $myfile = "picture.png";
my $dbname = "MyDatabase";

# Make a connection to your database
my $dbh = DBI->connect("dbi:mysql:database=$dbname") || die "Cannot open db";

# Open the file
open MYFILE, $myfile  or die "Cannot open file";
my $data;

# Read in the contents
while (<MYFILE>) {
        $data .= $_;
        }

close MYFILE;

my $sql = "INSERT INTO Table (ImageColumn) VALUES (?)";
my $sth = $dbh->prepare($sql);
my $numrows = $sth->execute($data);

# We are done with the statement handle
$sth->finish;
# I am finished with this connection to the database
$dbh->disconnect;

Now to read this back, try this bit of perl:

$sql = "SELECT ImageColumn from TABLE";
$sth = $dbh->prepare($sql);
$numrows = $sth->execute;

open OUTPUT, ">output.png";
my $ref = $sth->fetchrow_hashref;
my $newdata = $$ref{'ImageColumn'};
print OUTPUT $newdata;
close OUTPUT;
$sth->finish;
$dbh->disconnect;

... which will put the image into a file called output.png.

My application for this is to store images against a product. Some people would say to store just a filename reference to somewhere else on the filesystem, in place of the actual contents of the file, but in storing the contents I place complete control in serving this data out (via Apache) in my perl - I can check cookies and do other lookups to determine if this image should be sent out.

Happy perl-db-ing.