ASA SQL Reference
SQL Statements
Use this statement to permit a database server to act as a web server.
CREATE SERVICE service-name TYPE service-type-string [ attributes ] [ AS statement ]
attributes:
[ AUTHORIZATION { ON | OFF } ]
[ SECURE { ON | OFF } ]
[ USER { user-name | NULL } ]
[ URL [ PATH ] { ON | OFF | ELEMENTS } ]
[ USING { SOAP-prefix | NULL } ]
service-type-string:
{ 'RAW' | 'HTML' | 'XML' | 'SOAP' | 'DISH' }
service-name Web service names may be any sequence of alpha-numeric characters or "/", "-", "_", ".", "!", "~", "*", "'", "(", or "")", except that the first character must not begin with a slash (/) and the name must not contain two or more consecutive slash characters.
service-type-string Identifies the type of the serivce. The type must be one of the listed service types. There is no default value.
AUTHORIZATION clause Determines whether users must specify a user name and password when connecting to the service. If authorization is OFF, the AS clause is required and a single user must be identified by the USER clause. All requests are run using that user's account and permissions.
If authorization is ON, all users must provide a user name and password. Optionally, you may limit the users that are permitted to use the service by providing a user or group name using the USER clause. If the user name is NULL, all known users can access the service.
The default value is ON. It is recommended that production systems be run with authorization turned on and that you grant permission to use the service by adding users to a group.
SECURE clause Indicates whether unsecure connections are accepted. ON indicates that only HTTPS connections are to be accepted. Service requests received on the HTTP port are automatically redirected to the HTTPS port. If set to OFF, both HTTP and HTTPS connections are accepted. The default value is OFF.
USER clause If authorization is disabled, this parameter becomes mandatory and specifies the user id used to execute all service requests. If authorization is enabled (the default), this optional clause identified the user or group permitted access to the service. The default value is NULL, which grants access to all users.
URL clause Determines whether URI paths are accepted and, if so, how they are processed. OFF indicates that nothing must follow the service name in a URI request. ON indicates that the remainder of the URI is interpreted as the value of a variable named url. ELEMENTS indicates that the remainder of the URI path is to be split at the slash characters into a list of up to 10 elements. The values are assigned to variables named url plus a numeric suffix of between 1 and 10; for example, the first three variable names are url1, url2, and url3. If fewer than 10 values are supplied, the remaining variables are set to NULL. If the service name ends with the character /, then URL must be set to OFF. The default value is OFF.
USING clause This clause applies only to DISH services. The parameter specifies a name prefix. Only SOAP services whose names begin with this prefix are handled.
statement If the statement is NULL, the URI must specify the statement to be executed. Otherwise, the specified SQL statement is the only one that can be executed through the service. The statement is mandatory for SOAP services, and ignored for DISH services. The default value is NULL.
It is strongly recommended that all services run in production systems define a statement. The statement can be NULL only if authorization is enabled.
RAW The result set is sent to the client without any further formatting. You can produce formatted documents by generating the required tags explicitly within your procedure, as demonstrated in an example, below.
HTML The result set of a statement or procedure are automatically formatted into an HTML document that contains a table.
XML The result set is assumed to be in XML format. If it is not already so, it is automatically converted to XML RAW format.
SOAP The request must be a valid Simple Object Access Protocol, or SOAP, request. The result set is automatically formatted as a SOAP response. For more information about the SOAP standards, see 
www.w3.org/TR/SOAP.
DISH A DetermIne SOAP Handler, or DISH, service acts as a proxy for one or more SOAP services. In use, it acts as a container that holds and provides access to a number of soap services. A Web Services Description Language (WSDL) file is automatically generated for each of the included SOAP services. The included SOAP services are identified by a common prefix, which must be specified in the USING clause.
The create service statement causes the database server to act as a web server. A new entry is created in the SYSWEBSERVICE system table.
Must have DBA authority.
None.
ALTER SERVICE statement, DROP SERVICE statement
To quickly set up a web server, start a database server with the -xs switch, then execute the following statement:
CREATE SERVICE tables TYPE 'HTML'
AUTHORIZATION OFF
USER DBA
AS SELECT *
FROM SYS.SYSTABLEAfter executing this statement, use any web browser to open the URL http://localhost/tables.
The following example demonstrates how to write a Hello World program.
CREATE PROCEDURE hello_world_proc
RESULT (html_doc long varchar)
BEGIN
CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
SELECT '<html>\n'
|| '<head><title>Hello World</title></head>\n'
|| '<body>\n'
|| '<h1>Hello World!</h1>\n'
|| '</body>\n'
|| '</html>\n';
END;CREATE SERVICE hello_world TYPE 'RAW' AUTHORIZATION OFF USER DBA AS CALL hello_world_proc;