December 2011 Edition
Using SQL Anywhere as a Generic HTTP Web Server
Since the introduction of SQL Anywhere 10, SQL Anywhere has been able to retrieve a generic file listing from the file system using the newly-introduced Directory Access Remote Server ability. Combining this feature with the ‘RAW SERVICE’ ability, we can turn the database server into a rudimentary web server, to host generic text/binary files off a file system (similar to other web servers that customers are used to, like IIS/Apache).
1. Create and start SQL Anywhere database with a web server listening port 80
Assuming SQL Anywhere 12 has been installed from the command prompt execute:
2. Set up the file service
Connect to the webserver database with dbisql e.g.:
In this command, we use 'c:\inetpub\wwwroot' as our main web directory (the default IIS path), and we allow 10 levels of sub-directory indexing as well.
3. Set up the Web (RAW) service
Continue using dbisql and create a new web service with:
Note: a 'simple' security scheme will be used for this test - if advanced security is required, it is possible to be defined here.
4. Set Up the supported MIME types
When a browser requests a resource from a web server, it has no expectation on what the format of the response should be (e.g. the request for an HTML document looks exactly the same as a request for an image). Unfortunately, the browser needs a "hint" when it is reading the response data to help it figure out what kind of binary file was returned. This behaviour is controlled by the Content-Type HTML header which is set by the web server and given a value of a registered media type.
When setting up a SQL Anywhere RAW service, all files binary and text will be returned with a generic content-type header - this means that any binary file sent via this service without an appropriate "Content-Type" HTML header will instead always be displayed as text.
SQL Anywhere supports changing the Content-Type header value via sa_set_http_header() - however, we still need a mechanism to detect what kind of file we're about to return to the client and set the Content-Type header's value appropriately. We can do this fairly easily by setting up a 'mapping table' of file extensions to media types, using the existing lists that are already published for other products (e.g. Apache).
Here is the latest MIME mapping for Apache servers: http://svn.apache.org/repos/asf/httpd/httpd/trunk/docs/conf/mime.types
To import the mime types into SQL Anywhere, you can just copy mine.types from Apache site into a file and then using simple text editor search/replace all double tabs with single tabs until there is no more double tabs. This is needed for importing this data into a database table.
(on Unix or Linux this can be simply done with sed e.g.:
Then from dbisql create www_mine_types table and load the data:
Next, create a stored procedure to return pages and set HTTP headers
The stored procedure is basically trying extract out the file extension of the web request, look up that value in the www_mime_types table, and then set that value as the 'Content-Type' field. It then grabs the file off of the file server and returns it to the HTTP client.
5. Test your new webserver
You should now be able to test your web client.
Enjoy using a simple webserver powered by SQL Anywhere.