MySQL Cookbook

17.8 Retrieving Images or Other Binary Data

17.8.1 Problem

Okay, you can store images or other binary data values in your database, using the techniques discussed in Recipe 17.7. How do you get them back out?

17.8.2 Solution

You need nothing more than a SELECT statement. Of course, what you do with the information after you retrieve it might be a little more involved.

17.8.3 Discussion

As described in Recipe 17.7, it's difficult to issue a statement manually that stores a literal image value. However, there is no problem at all entering a query that retrieves an image:

mysql> SELECT * FROM image WHERE id = 1;

But binary information tends not to display well on ASCII devices, so you probably don't want to do this interactively from the mysql program unless you want your terminal window to turn into a horrible mess of gibberish (and possibly even to lock up). It's more common to use the information for display in a web page. Or you might send it to the client for downloading, though that is more common for non-image binary data such as PDF files. Downloading is discussed in Recipe 17.10.

Displaying an image in a web page is done by including an <img> tag in that page that tells the client's web browser where to get the image. If you've stored images as files in a directory that the web server has access to, you can refer to an image directly. For example, if the image file iceland.jpg is located in the directory /mcb/images under the server's document root, you can reference it like this:

<img src="/mcb/images/iceland.jpg" />

If you use this approach, make sure that each image filename has an extension (such as .gif or .png) that allows the web server to determine what kind of Content-Type: header to send in the response.

If the images are stored in a database table instead, or in a directory that is not accessible to the web server, the <img> tag can refer to a script that knows how to fetch images and send them to clients. To do this, the script should respond by sending a Content-Type: header that indicates the image format, a Content-Length: header that indicates the number of bytes of image data, a blank line, and finally the image itself as the body of the response.

The following script, display_image.pl, demonstrates how to serve images over the Web. It requires a name parameter that indicates which image to display, and allows an optional location parameter that specifies whether to retrieve the image from the image table or from the filesystem. The default is to retrieve image data from the image table. For example, the following URLs display an image from the database and from the filesystem, respectively:

http://apache.snake.net/cgi-bin/display_image.pl?name=iceland.jpg

http://apache.snake.net/cgi-bin/display_image.pl?name=iceland.jpg;location=fs

The script looks like this:

#! /usr/bin/perl -w
# display_image.pl - display image over the Web

use strict;
use lib qw(/usr/local/apache/lib/perl);
use CGI qw(:standard escapeHTML);
use FileHandle;
use Cookbook;

# Default image storage directory and pathname separator
# (CHANGE THESE AS NECESSARY)
my $image_dir = "/usr/local/apache/htdocs/mcb/images";
my $path_sep = "/";

# Reset directory and pathname separator for Windows/DOS
if ($^O =~ /^MSWin/i || $^O =~ /^dos/)
{
    $image_dir = "D:\\apache\\htdocs\\mcb\\images";
    $path_sep = "\\";
}

my $name = param ("name");
my $location = param ("location");

# make sure image name was specified
defined ($name) or error ("image name is missing");
# use default of "db" if the location is not specified or is not "db" or "fs"
(defined ($location) && $location eq "fs") or $location = "db";

my $dbh = Cookbook::connect ( );

my ($type, $data);

# If location is "db", get image data and MIME type from image table.
# If location is "fs", get MIME type from image table and read the image
# data from the filesystem.

if ($location eq "db")
{
    ($type, $data) = $dbh->selectrow_array (
                        "SELECT type, data FROM image WHERE name = ?",
                        undef,
                        $name)
                or error ("Cannot find image with name $name");
}
else
{
    $type = $dbh->selectrow_array (
                        "SELECT type FROM image WHERE name = ?",
                        undef,
                        $name)
                or error ("Cannot find image with name $name");
    my $fh = new FileHandle;
    my $image_path = $image_dir . $path_sep . $name;
    open ($fh, $image_path)
        or error ("Cannot read $image_path: $!");
    binmode ($fh);      # helpful for binary data
    my $size = (stat ($fh))[7];
    read ($fh, $data, $size) == $size
        or error ("Failed to read entire file $image_path: $!");
    $fh->close ( );
}

$dbh->disconnect ( );

# Send image to client, preceded by Content-Type: and Content-Length:
# headers.

print header (-type => $type, -Content_Length => length ($data));
print $data;

exit (0);

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

sub error
{
my ($msg) = shift;

    print header ( ), start_html ("Error"), p (escapeHTML ($msg)), end_html ( );
    exit (0);
}