Serving Graphics to Website from a Database?

Yussef ElSirgany writes "Does anyone one out there know how to dynamicaly serve graphics from fields in a database which are being accessed from a perl script? I know how to access the database and generate the html just not how to serve the graphics. ODBC(Text+Graphics) --> Perl --> Dynamic HTML Thanks!" Let's make this one interesting and cover not just ODBC, but Oracle, PostgreSQL, mSQL and mySQL as well. What databases worh the best for serving images to the web?

Comments

(Just use the file system) || (try PHP)

[Anonymous Coward]

You might want to reconsider your decision to store graphics in a database. It might just be easier to store the graphics in a directory structure, and store just the path in the database. File systems have been optimized for years in the storage of lots of files of any size. Databases (including MySQL) seem to work best with small (read, not 50+K graphics) row sizes. Also, file systems have nice management features. For example, you can FTP directly into this directory structure and you can use commands like grep and find. You're rebuilding the wheel when you don't use the file system.

Of course if you're like me, I ignored the above advice and stuck 1000's of 4K-50K images in my MySQL database. It works perfectly. For performance, you will have to index on some sort of id # on the rows. I found that the time to brute-force search through a database with large rows (large because of the images) was pretty slow with both MySQL and PostgreSQL. I decided it was just easier to use PHP to do this small trick than to do it in Perl. I placed the images into the database (have you thought about how you're going to do that yet?) using HTTP-upload, which in PHP is very easy to use. PHP places the file into a temp file, and it gives you the path. Then you can copy it or read it to place it in the db. PHP includes a nice function called base64encode() that I used to escape-out any bad characters in the binary image, before placing it in the SQL command. You might can get away with just escaping-out the SQL "special" characters (backslash (\), null (0x0), and single-quote (')), but I haven't tried it. I prefer the base64 encoded method, because I often do selects on the database using a terminal, and I didn't want the control characters to mess-up my terminal.

To display the image, I simply read it from the db, base64 decode it, and setup the HTTP headers to let the web browser know I'm sending an image. The code (I appoligize for the proportional font, isn't allowed here):

Header("Content-type: image/gif");
$row = mysql_fetch_array($result);
echo base64_decode($row["fp"]);

Nice and simple! Also, what are you going to do with the images. If you need to modify the images, for example resize them or place text on them (like for a button), then you might want to look into the GD library. PHP includes support for it, and in less than a day, I was creating graphs with uploaded backgrounds, and placing text on my graphs.

MySQL vs. PostgreSQL for large objects

[Anonymous Coward]

I found that MySQL is a couple of orders of magnitude simplier to use to store and retrieve large objects. I had to document both systems for a company manual about a year ago. It took me 2 hours of work with MySQL to read/write large objects (BLOB's in their documentation) versus 4 days of hard work with PostgreSQL to read/write large objects (inversion large objects in their documentation).

MySQL: You simply place the large object in the INSERT or UPDATE statement. The only trick is to make sure that you don't have any "funny" (like null's or single quotes) characters in your query. You can URL or Base64 encode your file to overcome this.

PostgreSQL: Don't introduce this interface to your kids; it might give them nightmares. You don't place the data in the SQL command. Instead, you call a PosgreSQL-specific function that references an existing file on import and a (hopefully) non-existant file on output. This is a major problem for most people. The file has to be on the database server for you to read/write it. Where I used to work, everyone had r-command access to the database server(!!) so that they could copy images and word processor documents into the database. This was not only awkward and insecure, but it was error-prone. If you have an environment with a single machine (program that reads/writes these files, web server and database server on a single machine), then this might be acceptable. Otherwise, getting the files to/from your database server is going to cause a problem.

Performance: I haven't compared the performance of the two systems. Both were fast-enough for my application.

Example on how to use PostgreSQL for large objects

[Anonymous Coward]

I forgot to include an example of how to use PostgreSQL with large objects in my previous post. Seeing an example like the below would have saved me a huge amount of time. When I figured-out how to use it, no one on the PostgreSQL mailing list was answering questions on how to use it, so I had to do it myself. Here is how you create a table with a large object:

CREATE TABLE TEST2 (name varchar(80), file oid);

The field named file stores an index to a file that is stored on your file system. Note, these files are not deleted when the row is deleted, so you will have to write a program to look through the db for the oid #'s in use and then delete the files by hand from the database directory. To place a binary data in the database, you can do something like so:

insert into test2 (name, file) values ('George', lo_import('/tmp/washington.gif'));

Very awkward, a file containing the large object has to be placed on the database server. Example of how to retrieve the saved image:

select lo_export(file, '/tmp/test.gif') from test2 WHERE name='George';

Will place the contents of the field into the specified file. Now, you have to do something with the file. For example, with web graphics you would have to somehow copy the file to your web server, send the contents to a web browser, then delete the file on both servers. With a word processing document, you would need to get your user to FTP to the database server (or use r-commands if you're not concerned about security) to download the file, then delete it by hand. Yuck.

Hello moderators??

[Anonymous Coward]

Old, rehashed jokes are moderated up, while technical comments like the above are moderated down to a zero. Something's wrong. Hello moderators, this is a technical site, not a comedy club. Stop punishing people for not posting a joke.

Hello Moderators! (Re:Why store the image...)

[Anonymous Coward]

Rehash-boy above's comments are scored higher than the previous comments. He simply repeated what was said earlier, and his repeat is a 1 while the original comment is a 0. What are the moderators smoking?

Data or file name

[b1ng0]

Do the fields in the database contain the file name of the graphic or the actual data?

Why store the image? Store the link

[doc_brown]

Why not use both the file system and the database?

Do you need to store the image in the database? If not why not just store the path to the file in the database and have the program grab the files accoring to their stored locations from the database

It's Easy

[Ledge Kindred]

Just write an app that reads data from the database, then streams it raw across the connection with a content header like so:

Content-type: image/jpeg

[image data...........]

You should be able to find examples all over the 'net. Look for CGI-related sites and hunt for graphics-related examples. I've found examples in Perl and Java quite easily before.

The harder part is getting the raw image data into the database in the first place, but only barely harder. (Hint: prepared statements.)