While browsing around the internet, your browser makes many requests for many different types of content. The content that is returned may be an image, a sound file, a SWF file, or hundreds of other things. The browser relies on the web server that’s returning the data to tell it what it’s receiving, and how it should be interpreted and displayed.
This meta information is called the document’s MIME type (Multipurpose Internet Mail Extensions ). The standard was first developed for e-mail (hence the word ‘Mail’ in the acronym), but has since extended to HTTP. However with browsers, it is often called the Content Type. This is because the
Content-TypeHTTP header is the vehicle used to convey the MIME type to the browser. In Firefox if you right-click on this page is choose ‘View Page Info’, you will see that the Content-Type of this page is
This explains why in our first example, we had to specifically set the
Content-Typeto let the browser know what we were sending it. This may seem odd if you have worked with other web servers such as Apache. You may have used Apache to host lots of types of content and never had to set any MIME types. This is because Apache is doing all the MIME work for you. If you look in your Apache configuration directory, you will find a file called mime.types that contains entries that look like this:
image/bmp bmp image/gif gif image/jpeg jpeg jpg jpe ...
This is Apache’s master list to convert a URL’s extension into a MIME type. If you want to change a mapping you can either edit this file directly, or use the mod_mime module. All Apache is doing under the covers is extracting the extension, and looking it up in mime.types. The MIME table in Microsoft IIS works in a similar way. Looking up things in a table is just what databases do well, so let’s build an automatic
Content-Typesetter for SQL Anywhere web services.
First, we need a table to hold the extensions dictionary:
CREATE TABLE MIMEType ( "Extension" CHAR(255) NOT NULL PRIMARY KEY, "Type" CHAR(255) NOT NULL );
Second, we need to make a procedure that extracts the extension from the URL, looks it up in the table, and sets the Content-Type header
CREATE PROCEDURE AutoSetMIMEType() BEGIN DECLARE loc INTEGER; DECLARE ext CHAR(255); DECLARE mimetype CHAR(255); -- Find the location of the last period in the URI SET loc = LOCATE(HTTP_Header('@HttpURI'), '.', -1); -- If no period exists, default to ‘text/plain’ IF loc > 0 THEN -- Extract the extension and convert to lower case SET ext = LOWER(SUBSTR(HTTP_Header('@HttpURI'), loc + 1)); -- If the extension exists, use the corresponding type -- Otherwise default to ‘text/plain’ IF EXISTS(SELECT Extension FROM MIMEType where Extension = ext) THEN SELECT Type INTO mimetype FROM MIMEType where Extension = ext; CALL sa_set_http_header('Content-Type', mimetype); ELSE CALL sa_set_http_header('Content-Type', 'text/plain'); END IF; ELSE CALL sa_set_http_header('Content-Type', 'text/plain'); END IF; END;
Now all that is left to do is add Extension/Type pairs to the table like this:
INSERT INTO MIMEType VALUES('bmp', 'image/bmp'); INSERT INTO MIMEType VALUES('gif', 'image/gif'); INSERT INTO MIMEType VALUES('jpeg', 'image/jpeg'); INSERT INTO MIMEType VALUES('jpg', 'image/jpeg'); INSERT INTO MIMEType VALUES('jpe', 'image/jpeg'); ...
I have compiled a list of about 600 common MIME Types (based off mime.types) that you can cut and paste into Interactive SQL. You can get that SQL file here.
To use the function, all we have to do is add
Somewhere in our web service handler, and the MIME type will be automatically set from the extension, just like it is in Apache and IIS.
As a side note, there are actually some more sophisticated ways to determine the MIME type. One example is the mod_mime_magic module for Apache that looks at the first few bytes of the content and makes a guess on what the type is (for example, does it look like a BMP header?, an XML header?, etc…). While useful, it is recommended that this should only be used as a “second line of defense“, and not the primary strategy.