Recipe 18.7. Retrieving Images or Other Binary Data

Problem

You can store images or other binary data values in your database, using the techniques discussed in Section 18.6. But how do you get them back out?

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.

Discussion

As described in Section 18.6, it's difficult to issue a statement manually that stores a literal image value into a database, so normally you use LOAD_FILE⁠(⁠ ⁠ ⁠) or write a script that encodes the image data for insertion. However, there is no problem at all entering a statement that retrieves an image:

SELECT * FROM image WHERE id = 1; 

But binary information tends not to show up well on text-display 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, or 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, although that is more common for nonimage binary data such as PDF files. (Section 18.9 discusses downloading.)

To display an image in a web page, include an <img> tag in the 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 /usr/local/lib/mcb/images directory, you can reference it like this:

<img src="/books/2/302/1/html/2//usr/local/lib/mcb/images/iceland.jpg" /> 

If you use this approach, make sure that each image filename has an extension (such as .gif or .png) that enables the web server to determine what kind of Content-Type: header to generate when it sends the file to the client.

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://localhost/cgi-bin/display_image.pl?name=iceland.jpg http://localhost/cgi-bin/display_image.pl?name=iceland.jpg;location=fs 

The script looks like this:

#!/usr/bin/perl # display_image.pl - display image over the Web use strict; use warnings; use CGI qw(:standard escapeHTML); use FileHandle; use Cookbook; sub error { my $msg = escapeHTML ($_[0]);   print header (), start_html ("Error"), p ($msg), end_html ();   exit (0); } # ---------------------------------------------------------------------- # Default image storage directory and pathname separator # (CHANGE THESE AS NECESSARY) my $image_dir = "/usr/local/lib/mcb/images"; # The location should NOT be within the web server document tree my $path_sep = "/"; # Reset directory and pathname separator for Windows/DOS if ($^O =~ /^MSWin/i || $^O =~ /^dos/) {   $image_dir = "C:\\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;