Creating and customizing a root web service

An HTTP client request that does not match any web service request is processed by the root web service if a root web service is defined.

The root web service provides you with an easy and flexible method to handle arbitrary HTTP requests whose URLs are not necessarily known at the time when you build your application, and to handle unrecognized requests.

Example

This example illustrates how to use a root web service, which is stored in a table within the database, to provide content to web browsers and other HTTP clients. It assumes that you have started a local HTTP web server on a single database and listening on port 80. All scripts are run on the web server.

Connect to the database server through Interactive SQL and run the following SQL script to create a root web service that passes the url host variable, which is supplied by the client, to a procedure named PageContent:

CREATE SERVICE root
    TYPE 'RAW'
    AUTHORIZATION OFF 
    SECURE OFF
    URL ON
    USER DBA
    AS CALL PageContent(:url);

The URL ON portion specifies that the full path component is made accessible by an HTTP variable named URL. For more information about the CREATE SERVICE statement syntax, see CREATE SERVICE statement.

Run the following SQL script to create a table for storing page content. In this example, the page content is defined by its URL, MIME-type, and the content itself.

CREATE TABLE Page_Content (
	url          VARCHAR(1024) NOT NULL PRIMARY KEY,
	content_type VARCHAR(128)  NOT NULL, 
	image        LONG VARCHAR  NOT NULL
);

Run the following SQL script to populate the table. In this example, the intent is to define the content to be provided to the HTTP client when the index.html page is requested.

INSERT INTO Page_Content 
VALUES(
    'index.html',
    'text/html',
    '<html><body><h1>Hello World</h1></body></html>'
);
COMMIT;

Run the following SQL script to implement the PageContent procedure, which accepts the url host variable that is passed through to the root web service:

CREATE PROCEDURE PageContent( IN @url LONG VARCHAR )
RESULT ( html_doc LONG VARCHAR )
BEGIN
    DECLARE @status char(3);
    DECLARE @type varchar(128);
    DECLARE @image long varchar;

    SELECT content_type, image INTO @type, @image
        FROM Page_Content
        WHERE url = @url;

    IF @image is NULL THEN
        SET @status = '404';
        SET @type = 'text/html';
        SET @image =  '<html><body><h1>404 - Page Not Found</h1>There is no content located at the URL "' || html_encode( @url ) || '" on this server.</body></html>';
    ELSE
    	SET @status = '200';
    END IF;
    CALL dbo.sa_set_http_header( '@HttpStatus', @status );
    CALL dbo.sa_set_http_header( 'Content-Type', @type );
    SELECT @image;
END;

The root web service calls the PageContent procedure when a request to the HTTP server does not match any other defined web service URL. The procedure checks if the client-supplied URL matches a url in the Page_Content table. The SELECT statement sends a response to the client. If the client-supplied URL was not found in the table, a generic 404 - Page Not Found html page is built and sent to the client.

In the error message, the HTML_ENCODE function is used to encode the special characters in the client-supplied URL. For more information, see HTML_ENCODE function [Miscellaneous].

The @HttpStatus header is used to set the status code returned with the request. A 404 status indicates a Not Found error, and a 200 status indicates OK. The 'Content-Type' header is used to set the content type returned with the request. In this example, the content (MIME) type of the index.html page is text/html. For more information, see sa_set_http_header system procedure.

Creating and customizing a root web service

Example

See also