Using procedures that provide HTML documents

Generally, it is easiest to write a procedure that handles the requests sent to a particular service. Such a procedure should return a web page. Optionally the procedure can accept arguments, passed as part of the URL, to customize its output.

The following example, however, is much simpler. It demonstrates how simple a service can be. This web service simply returns the phrase "Hello world!".

CREATE SERVICE hello 
TYPE 'RAW'
AUTHORIZATION OFF 
USER DBA
AS SELECT 'Hello world!';

Start a database server with the -xs option to enable handling of web requests, and then request the URL [external link] http://localhost/hello from any web browser. The words Hello world! appear on an otherwise plain page.

HTML pages

The above page appears in your browser in plain text. This happens because the default HTTP Content-Type is text/plain. To create a more normal web page, formatted in HTML, you must do two things:

  • Set the HTTP Content-Type header field to text/html so that the browsers expect HTML.

  • Include HTML tags in the output.

You can write tags to the output in two ways. One way is to use the phrase TYPE 'HTML' in the CREATE SERVICE statement. Doing so instructs the SQL Anywhere database server to add HTML tags for you. This can work quite well if, for example, you are returning a table.

The other way is to use TYPE 'RAW' and write out all the necessary tags yourself. This second method provides the most control over the output. Note that specifying type RAW does not necessarily mean the output is not in HTML or XML format. It only tells SQL Anywhere that it can pass the return value directly to the client without adding tags itself.

The following procedure generates a fancier version of Hello world. For convenience, the body of the work is done in the following procedure, which formats the web page.

The built-in procedure sa_set_http_header is used to set the HTTP header type so browsers interpret the result correctly. If you omit this statement, your browser displays all the HTML codes, rather than using them to format the document.

CREATE PROCEDURE hello_pretty_world ()
RESULT (html_doc XML)
BEGIN
  CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
  SELECT HTML_DECODE(
    XMLCONCAT(
      '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">',
      XMLELEMENT('HTML',  
      XMLELEMENT('HEAD',
          XMLELEMENT('TITLE', 'Hello Pretty World')
        ),
        XMLELEMENT('BODY',
          XMLELEMENT('H1', 'Hello Pretty World!'),
          XMLELEMENT('P',
            '(If you see the tags in your browser, check that '
            || 'the Content-Type header is set to text/html.)'
          )
        )
      )
    )
  );
END

The following statement creates a service that uses this procedure. The statement calls the above procedure, which generates the Hello Pretty World web page.

CREATE SERVICE hello_pretty_world 
TYPE 'RAW'
AUTHORIZATION OFF 
USER DBA
AS CALL hello_pretty_world();

Once you have created the procedure and the service, you are ready to access the web page. Ensure that your database server was started with the correct -xs option values, and then open the URL [external link] http://localhost/hello_pretty_world in a web browser.

You see the results formatted in a simple HTML page, with the title Hello Pretty World. You can make the web page as elaborate as you want by including more content, using more tags, using style sheets, or including scripts that run in the browser. In all cases, you create the necessary services to handle the browser's requests.

For more information about built-in stored procedures, see Alphabetical list of system procedures.

Root services

When no service name is included in a URL, SQL Anywhere looks for a web service named root. The role of root pages is analogous to the role of index.html pages in many traditional web servers.

Root services are handy for creating home pages because they can handle URL requests that contain only the address of your web site. They are also handy for handling URL paths that do not exist. For example, the following procedure and service implement a simple web page that appears when you browse to the URL [external link] http://localhost. It also handles the case where you browse to a page that does not exist.

CREATE PROCEDURE HomePage( IN url LONG VARCHAR )
RESULT (html_doc XML)
BEGIN
  CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
  IF url IS NULL THEN
    SELECT HTML_DECODE(
      XMLCONCAT(
        '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">',
        XMLELEMENT('HTML',
          XMLELEMENT('HEAD',
            XMLELEMENT('TITLE', 'My Home Page')
          ),
          XMLELEMENT('BODY',
            XMLELEMENT('H1', 'My home on the web'),
            XMLELEMENT('P',
              'Thank you for visiting my web site!'
            )
          )
        )
      )
    )
  ELSE    
    CALL dbo.sa_set_http_header('Status','404'); 
    SELECT '<H1>Status 404 - Document ' || url || ' not found</H1>'  
  END IF
END

Now create a service that uses this procedure:

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

You can access this web page by browsing to the URL [external link] http://localhost, as long as you do not specify that database names are mandatory when you start the database server. See Starting a database server that listens for web requests. By specifying URL PATH ON, you ensure that non-existent URL paths are directed to this service.

Examples

More extensive examples are included in the samples-dir\SQLAnywhere\HTTP directory.