How to serve pictures from a database
You are building a web application for a Human Resources department. One part of this application is the display of an employee profile, including a photograph. All this information is stored in a database.
How do you extract the picture of an employee for display on a web page?
Listing 1 presents the script to create the employee table on a Sybase SQL Anywhere database.
id NUMERIC(12,0) NOT NULL,
first_name VARCHAR(50) NULL,
last_name VARCHAR(50) NULL,
picture LONGVARBINARY NULL
)
Listing 2 shows how to use the img tag at our advantage to call a dynamic web page as the source of the picture. Remember that the src attribute is not limited to static files. In fact, it can accept anything as long as it returns a valid picture.
Listing 3 shows how to use the
select picture
from person
where id = #URL.id#
</cfquery>
<cfcontent type="image/x-ms-bmp" variable="#MyPicture.picture#">
This question was written by Philippe Randour.
It was last updated on May 29, 2007 at 5:26:52 PM EDT.
CFML Referenced
Categories
Comments
Comment made by Jamie Samland on May 30, 2007 at 12:28 PM
Will type="image/x-ms-bmp" have to change for jpg/gif/png?
Comment made by Philippe Randour on May 30, 2007 at 2:33 PM
Jamie,
You are right. As the 'type' attribute represents the MIME type of the content sent back to the browser, it must be specific to that content. Possible values for pictures include 'image/gif', 'image/jpeg', 'image/tiff' and 'image/png', among others. This information is there to help the browser determine what to do with the content it receives.
Comment made by Raul Riera on June 1, 2007 at 10:56 AM
Why is it useful to store the binary data of a picture instead of just the path to the picture in the DB? I may be wrong, but I don't see why this is a good approach.
Comment made by Raymond Camden on June 1, 2007 at 11:53 AM
I actually don't do this - I use a full path, but I can see where this makes things a bit simpler. No need to worry about paths, etc.
Comment made by Philippe Randour on June 4, 2007 at 4:50 PM
This approach (storing binary data instead of just a path) can also be easier if you have to replicate your data across multiple sites and don't want to manage multiple different replication engines. You also avoid any synchronization problem between the database and the file system.
Comment made by Jon Hartmann on June 18, 2007 at 3:51 PM
I recently tried a similar setup to return cfchart results, however I couldn't get the image to load at all. Is there some difference there that should matter?