SAP SQL Anywhere
Page tree
Skip to end of metadata
Go to start of metadata

Purpose

The purpose of this wiki is to show how to use a SQL Anywhere database server as a generic HTTP server to serve files over the Internet using a web service with a directory access remote server on the local machine.

Overview

Since the introduction of SQL Anywhere 10, SQL Anywhere has been able to retrieve a generic file listing from the file system using the Directory Access Remote Server ability.

Combining this feature with the RAW Web service type, we can turn the database server into a simple web server to host generic text and binary files off of a file system similar to other HTTP web servers that customers are accustomed to like Microsoft Internet Information Services (IIS) or Apache HTTPD server.

Below is a walk-through tutorial of how to create the web service.

1. Create and start SQL Anywhere database with a web listener on HTTP port 80

Assuming SQL Anywhere 16 has been installed, from the Command Prompt execute the following commands, enabling the web service with the -xs database server switch:

    dbinit webserver.db
    dbsrv16 -xs http(port=80) webserver.db

2: Set up the file service

Connect to the webserver database with Interactive SQL (dbisql):

    dbisql –c "uid=dba;pwd=sql;Servername=webserver"

 and execute the following SQL:

    --
    -- Create File Service
    --
    CREATE SERVER www_filehost
    CLASS 'directory'
    USING 'root=c:\\inetpub\\wwwroot;SUBDIRS=10';
    CREATE EXTERNLOGIN DBA TO www_filehost;
    CREATE EXISTING TABLE www_files AT 'www_filehost;;;.';

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. You can specify an alternate directory with an alternate level of indexing if you wish.

3: Set up the Web (RAW) service

Continue using Interactive SQL (dbisql) and create a new web service with the following SQL CREATE SERVICE command:

    --
    -- Create WWW Service
    --
    CREATE SERVICE root
    TYPE 'RAW'
    AUTHORIZATION OFF
    SECURE OFF
    URL ON
    USER DBA
    AS CALL www_get_page(:url);

Note: A 'simple' security scheme will be used for this tutorial - if advanced security with authorization is required, it is possible to be defined here.

4. Set up the supported MIME types for the Web Service

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, there still needs to be 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 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 HTTPD Server).

Here is the current MIME mapping for the Apache HTTPD server: http://svn.apache.org/repos/asf/httpd/httpd/trunk/docs/conf/mime.types

To import the mime types into SQL Anywhere, you can duplicate the mine.types information from the Apache site into a text file and then using a text editor, search and replace all of the double tabs with single tabs until there are no more double tabs. This is needed in order to import the data into a database table.

This can also be done via a script with the sed utility. e.g.:

   sed -e "s/\t\t\t\t\t/\t/g" -e "s/\t\t\t\t/\t/g" -e "s/\t\t\t/\t/g" -e "s/\t\t/\t/g" mime.types > mime.types2

Back in Interactive SQL (dbisql), create the www_mine_types table and load the data:

    CREATE TABLE www_mime_types (
    mimetype VARCHAR(256) UNIQUE,
    extensions VARCHAR(1024)
    );
 
    LOAD TABLE www_mime_types (mimetype,extensions)
    FROM 'mime.types2'
    DELIMITED BY '\x09'
    COMMENTS INTRODUCED BY '#';

Next, create a stored procedure to return pages and set HTTP headers

    CREATE OR REPLACE PROCEDURE www_get_page( @url LONG VARCHAR )
    BEGIN
    DECLARE @url_file_suffix LONG VARCHAR;    
    DECLARE @url_suffix LONG VARCHAR;
    DECLARE @mime_target VARCHAR(256);
    SET @url_file_suffix = REGEXP_SUBSTR(@url,'\..+$'); -- Extract extension
    SET @url_suffix = RIGHT(@url_file_suffix, LENGTH(@url_file_suffix) - 1);
    -- Exact match
    SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions = @url_suffix;
    IF @mime_target IS NULL THEN
    -- First match 
    SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions LIKE @url_suffix || ' %';
    IF @mime_target IS NULL THEN
    -- Middle match 
    SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions LIKE '% ' || @url_suffix || ' %';
    IF @mime_target IS NULL THEN
    -- End match    
    SELECT mimetype INTO @mime_target FROM www_mime_types WHERE extensions LIKE '% ' || @url_suffix;          
    IF @mime_target IS NULL THEN
    SET @mime_target = 'text/html'; -- Default
    END IF;
    END IF;
    END IF;
    END IF;
    CALL sa_set_http_header( 'Content-Type', @mime_target );
    -- Cast binary data as LONG VARCHAR in 'text' situations, otherwise return LONG BINARY
    IF ( LEFT(@mime_target, 4) = 'text' ) THEN
    SELECT CAST(contents AS LONG VARCHAR) FROM www_files WHERE file_name = @url;
    ELSE
    SELECT contents FROM www_files WHERE file_name = @url;
    END IF;
    END;

The stored procedure extracts out the file extension of the web request, looks up that value in the www_mime_types table, and then set that value as the 'Content-Type' field. It then reads the file off of the file server and returns it to the HTTP client.

5. Test your new SQL Anywhere web server HTTP RAW service

You should now be able to test your web client.
Create a simple hello.htm file:

    <html>
    <body bgcolor = "#ffffcc" text = "#000000" align = center>
    <h1>Using SQL Anywhere as a simple webserver</h1>
    </body>
    </html>

Copy hello.htm to your root directory (c:\\inetpub\\wwwroot) defined in your file service, and from any browser test your page with :
http://localhost:80/hello.htm

Enjoy using a simple web HTTP service for documents, powered by SQL Anywhere

  • No labels