Using MySQL to Import and Retrieve Blobs and Display as Image Files in HTML

I received a phone call from a friend of mine who had some questions about storing image files as blobs in MySQL. He wanted to design a web site that would allow someone to upload an image, and then that image would be stored in the MySQL database for viewing later. He stated that he was going to be storing thousands of images. I mentioned that it might be better to store the images on the file system, and then to just store the location of the image in the database. But, he still wanted to know how to do it and would decide which solution he would incorporate later.

I already had a Perl script that allowed me to upload files to a web site, as I would give out this URL to people that wanted to send me large files. And, I know that you can store images (and other files) in MySQL as a blob, but I wasn’t sure how to display the blob as an image in HTML (once I had retrieved it from the MySQL database). A search on Google provide bits and pieces of the solution, but the solution would only provide the ability to retrieve one image at a time. My friend wanted to be able to display multiple images on a single web page.

I figured out that I needed five things – a database to store the blob, a web page to upload the file into the database, a Perl script to retrieve the results of a search from the database, a Perl script to go back to the database and retrieve the information and blob (image) from a query, and finally a Perl script to display the blobs where an HTML img tag could “read” it. I tried to do it all with just three scripts (ignoring the last script), but I had a problem retrieving and displaying more than one image at a time from a single query. Google just wasn’t providing the answers.

First, I created a “blobs” database to store the images. The database consists of two columns – a serial_number (int, 10) and a mediumblob. In the scripts, I do not check to make sure that the image size is smaller than what is allowed by a mediumblob (16,777,215 bytes), but this could easily be added. Here is the create table statement:

CREATE TABLE `blobs` (
`serial_number` varchar(10) NOT NULL,
`blob_file` mediumblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Next, I needed a simple web page that will allow the user to upload an image file (and the database may also be used for any other type of file). This form will take the file and place it into the blobs MySQL database. For this example, I have hard-coded a serial number (1234567890) into the web form, but you could easily generate a unique serial number or pull an auto-increment value from the database.

<HTML>
<BODY> </BODY>
<title>Uploading A File</title>
<FORM ACTION="http://192.168.1.2/cgi-bin/mysql/blob_upload.pl" METHOD="post" ENCTYPE="multipart/form-data">
<table>
<tr>
<td>
<font face=verdana size=1 color=#003366> File to Upload:  
</td>
<td>
<INPUT size=70 TYPE="file" NAME="file">
</td>
</tr>
<td>
<input type=hidden name="serial_number" value="1234567890">
</td>
<td>
<INPUT TYPE="submit" NAME="Submit" VALUE="Submit Form">
</td>
</tr>
</table>
</FORM>
</BODY>
</HTML>

The web page sends the information to a Perl script named “blob_upload.pl”, which then places the information (serial number and image file) into the database. The script also creates a directory for today’s date, and then stores a copy of the image on the server in that directory. (For more information on using Perl to connect to MySQL, especially my use of the subroutine ConnectToMySql – see this earlier post. You could also insert the location of the file into another column in the database, but I chose to bypass that functionality for this example. Here is the script:

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI;
use POSIX 'strftime'; # Need to explicitly load the functions in POSIX

# Note that if you pass no argument to localtime, it assumes the current time
$Date = strftime '%Y-%m-%d', localtime;

$database = "scripting_mysql";
$upload_dir = "$Date\_uploaded_files";

#make directory unless it already exists
mkdir "$upload_dir", 0777 unless -d "$upload_dir";

$query = new CGI;

$filename = $query->param("file");
$serial_number = $query->param("serial_number");

print $query->header ( );

$filename =~ s/.*[\/\\](.*)/$1/;
$filename =~ s/ /_/g;
$upload_filehandle = $query->upload("file");
$directory_filename = "$upload_dir/$filename";

print "$filename $serial_number";

# upload the file to the server

open UPLOADFILE, ">$directory_filename";
binmode UPLOADFILE;

while ( <$upload_filehandle> )
{
    print UPLOADFILE;
}

close UPLOADFILE;

# Open the file
open MYFILE, $directory_filename || print "Cannot open file";
my $blob_file;

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

close MYFILE;

#----------------------------------------------------------------------
# insert the values into the database
#----------------------------------------------------------------------

# invoke the ConnectToMySQL sub-routine to make the database connection
    $dbh = ConnectToMySql($database);

    # set the value of your SQL query
    $query = "insert into blobs (serial_number, blob_file) values (?, ?) ";

    $sth = $dbh->prepare($query);
    $sth->execute($serial_number, $blob_file) || print "Can't access database";

    $sth->finish;
    $dbh->disconnect;

#----------------------------------------------------------------------

print " <TITLE>File Uploaded</TITLE>";

print " <font face=verdana size=2 color=#003366>Thanks for uploading your file!<br>";
print " <font face=verdana size=2 color=#003366>File directory: $Date\_uploaded_files<br>";
print " <font face=verdana size=2 color=#003366>Your file: $filename<br>";
print " <font face=verdana size=2 color=#003366>Your serial number: $serial_number<br>";

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

my $host="192.168.1.2";

open(ACCESS_INFO, "<..\/accessBLOB") || die "Can't access login credentials";
    my $database = <ACCESS_INFO>;
    my $host = <ACCESS_INFO>;
    my $userid = <ACCESS_INFO>;
    my $passwd = <ACCESS_INFO>;

    # the chomp() function will remove any newline character from the end of a string
    chomp ($database, $host, $userid, $passwd);

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}


Now we have a web page to upload the image files and a Perl script to store the image files. We just need a script to print out the serial number and the blob – but we need to print the blob out as an image in HTML by using the img tag. This is the tricky part. To accomplish this, we create another Perl script that simply “prints” the blob out in a way that an HTML img tag can read it. But first, here is a script to go to the database and retrieve the blob based upon a serial number (which is hard coded in this example). This script is titled “blob_view.pl”. In this script, we are sending a query to the MySQL database to retrieve the image based upon the serial number. But, the script has a “while loop” so that you can bring back more than one image if you desire – and in this loop we call the fourth script – get_blob.pl – to bring each blob back one at a time and to display it inside the HTML img tag. Here is the script to retrieve the image with the same serial number from the web page (hard coded – serial number 1234567890):

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI qw(:standard);

print header;
print "<html><body>Here is your picture:<p>";

$dbh = ConnectToMySql($Database);

$query = "select serial_number from blobs where serial_number = '1234567890'";

$sth = $dbh->prepare($query);

$sth->execute();

while (@data = $sth->fetchrow_array()) {
$serial_number = $data[0];

print "<p>$serial_number <p> <img width=200 src=\"get_blob.pl?sn=$serial_number\"><br>get_picture.pl?sn=$serial_number";

}

$sth->finish;
$dbh->disconnect;

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

my $host="192.168.1.2";

open(ACCESS_INFO, "<..\/accessBLOB") || die "Can't access login credentials";
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

chomp($userid);
chomp($passwd);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

The key here is to use another script named get_blob.pl that only returns the image, which allows us to use an HTML img tag to display the image. You pass the serial number to get_blob.pl as a variable named “sn”, and it retrieves the blog so that the img tag can display it. There are other ways in Perl to display a single image without using another script, but I wanted this to be able to handle multiple images from a single query. If I changed the query to something like “$query = “select serial_number from blobs where serial_number like ‘123456789%'”;”, it would retrieve more than one row (in my case), and print each image separately. So, you will need this script as well to retrieve the images:

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI;
#use CGI qw(:standard);

$query = new CGI;

$serial_number = $query->param("sn");

     $dbh = ConnectToMySql($Database);

     $query = "select serial_number, blob_file from blobs where serial_number = '$serial_number'";
     
$sth = $dbh->prepare($query);

$sth->execute();

while (@data = $sth->fetchrow_array()) {
$serial_number = $data[0];
$blob_file = $data[1];
print STDOUT "Content-Type: image/jpeg\n";
print STDOUT "Content-length: \n\n";
binmode STDOUT;
print STDOUT $blob_file;

}

     $sth->finish;
     $dbh->disconnect;

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

my $host="192.168.1.2";

open(ACCESS_INFO, "<..\/accessBLOB") || die "Can't access login credentials";
     my $database = <ACCESS_INFO>;
     my $host = <ACCESS_INFO>;
     my $userid = <ACCESS_INFO>;
     my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

chomp($userid);
chomp($passwd);
# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

This may seem like a lot of code, and I am sure that some Perl experts will show me what I am doing wrong (for example, I didn’t use strict or warnings), or a way to do it with less code. But I like to write this code so that a novice Perl user will be able to (hopefully) figure out what I am doing and be able to incorporate it into their project. I know that Google didn’t provide me with an easy answer, so hopefully this will help someone with their next project.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
About these ads

3 Responses to Using MySQL to Import and Retrieve Blobs and Display as Image Files in HTML

  1. Hartmut Holzgraefe says:

    Images stored as Blobs in the database: Don’t go there, there be Dragons!

  2. shannon says:

    Hi Tony!, warn of memory usage with large blobs, lots of large blobs + lots of simultaneous connections pulling said blobs at the same time will use a lot of memory beyond what you have configured or expect from the my.cnf due to allocation of buffers to store the entire blob at once (and/or copies).

    See how memory is allocated:

    http://dev.mysql.com/doc/refman/5.5/en/memory-use.html

    consider disk storage, or caching them to disk, only updating when necessary, but not having them pulled from the db by every connection.

    Also queries with blob,text in result using temporary table have to go to disk as opposed to smaller of max_heap_table_size and tmp_table_size , see:

    http://dev.mysql.com/doc/refman/5.5/en/blob.html

    http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

    Might read up on blob streaming here:

    http://www.blobstreaming.org/

  3. Gord Jeoffroy says:

    Seconding Hartmut’s comment: store images in a separate directory; store pathnames to the images in the database. But thanks for the article!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 39 other followers