CREATE SERVICE statement [HTTP web service]

CREATE SERVICE service-name
TYPE { 'RAW' | 'HTML' | 'JSON' | 'XML' }
[ URL [PATH] { ON | OFF | ELEMENTS } ]
[ common-attributes ]
[ AS { statement | NULL } ]

common-attributes :
[ AUTHORIZATION { ON | OFF } ]
[ ENABLE | DISABLE ]
[ METHODS 'method,...' ]
[ SECURE { ON | OFF } ]
[ USER { user-name | NULL } ]

method :
DEFAULT
| POST
| GET
| HEAD
| PUT
| DELETE
| NONE
| *

service-name Web service names can be any sequence of alphanumeric characters or slash (/), hyphen (-), underscore (_), period (.), exclamation mark (!), tilde (~), asterisk (*), apostrophe ('), left parenthesis ((), or right parenthesis ()), except that the service name must not begin or end with a slash (/) or contain two or more consecutive slashes (for example, //).
You can name your service root, but this name has a special function.
TYPE clause Identifies the type of the service where each service defines a specific response format. The type must be one of the listed service types. There is no default value.
- 'RAW' The result set is sent to the client without any formatting. Utilization of this service requires that all content markup is explicitly provided. Complex dynamic content containing current content with markup, JavaScript and images can be generated on demand. The media type may be specified by setting the Content-Type response header using the sa_set_http_header procedure. Setting the Content-Type header to 'text/html' is good practice when generating HTML markup to ensure that all browsers display the markup as HTML and not text/plain.
- 'HTML' The result set is returned as an HTML representation of a table or view.
- 'JSON' The result set is returned in JavaScript Object Notation (JSON). A JSON service does not automatically process JSON input. It only presents data (in the response) in JSON format. JSON accepts POST/PUT methods where application/x-www-form-urlencoded is supported. If for a POST/PUT METHOD, Content-Type: application/json is specified, then the application may use http_variable('body') to retrieve the JSON (request) content. SQL Anywhere does not parse the JSON input automatically. It is up to the application to parse it. For more information about JSON, see http://www.json.org/.
- 'XML' The result set is returned as XML. If the result set is already XML, no additional formatting is applied. Otherwise, it is automatically formatted as XML. As an alternative approach, a RAW service could return a select using the FOR XML RAW clause having set a valid Content-Type such as text/xml using sa_set_http_header procedure.

URL clause Determines whether URL paths are accepted and, if so, how they are processed. Specifying URL PATH has the same effect as URL.

OFF Indicates that the service name in a URL request must not be followed by a path. OFF is the default setting. For example, the following form is disallowed due to the path elements /aaa/bbb/ccc.

http://host-name/service-name/aaa/bbb/ccc

Suppose that CREATE SERVICE echo URL PATH OFF was specified when creating the web service. A URL similar to http://localhost/echo?id=1 produces the following values:

HTTP_VARIABLE('id') == 1,
HTTP_HEADER('@HTTPQUERYSTRING') == id=1

ON Indicates that the service name in a URL request can be followed by a path. The path value is returned by querying a dedicated HTTP variable named URL. A service can be defined to explicitly provide the URL parameter or it may be retrieved using the HTTP_VARIABLE function. For example, the following form is allowed:

http://host-name/service-name/aaa/bbb/ccc

Suppose that CREATE SERVICE echo URL PATH ON was specified when creating the web service. A URL similar to http://localhost/echo/one/two?id=1 produces the following values:

HTTP_VARIABLE('id') == 1,
HTTP_VARIABLE('URL') == one/two,
HTTP_HEADER('@HTTPQUERYSTRING') == id=1

ELEMENTS Indicates that the service name in a URL request may be followed by a path. The path is obtained in segments by specifying a single parameter keyword URL1, URL2, and so on. Each parameter may be retrieved using the HTTP_VARIABLE or NEXT_HTTP_VARIABLE functions. These iterator functions can be used in applications where a variable number of path elements can be provided. For example, the following form is allowed:

http://host-name/service-name/aaa/bbb/ccc

Suppose that CREATE SERVICE echo URL PATH ELEMENTS was specified when creating the web service. A URL similar to http://localhost/echo/one/two?id=1 produces the following values:

HTTP_VARIABLE('id') == 1,
HTTP_VARIABLE('URL1') == one,
HTTP_VARIABLE('URL2') == two,
HTTP_HEADER('@HTTPQUERYSTRING') == id=1

Up to 10 elements can be obtained. A NULL value is returned if the corresponding element is not supplied. In the above example, HTTP_VARIABLE('URL3') returns NULL because no corresponding element was supplied.

For more information about URLs, see Browsing an HTTP web server and Accessing client-supplied HTTP variables and headers.

AUTHORIZATION clause Determines whether users must specify a user name and password through basic HTTP authorization when connecting to the service. The default value is ON. If authorization is OFF, the AS clause is required for all services and a user must be specified with 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 can limit the users that are permitted to use the service by providing a user or group name with the USER clause. If the user name is NULL, all known users can access the service. The AUTHORIZATION clause allows your web services to use database authorization and permissions to control access to the data in your database.
When the authorization value is ON, an HTTP client connecting to a web service uses basic authentication (RFC 2617) that obfuscates the user and password information using base-64 encoding. It is recommended that you use the HTTPS protocol for increased security.
ENABLE and DISABLE clauses Determines whether the service is available for use. By default, when a service is created, it is enabled. When creating or altering a service, you may include an ENABLE or DISABLE clause. Disabling a service effectively takes the service off line. Later, it can be enabled using ALTER SERVICE with the ENABLE clause. An HTTP request made to a disabled service typically returns a 404 Not Found HTTP status.

METHODS clause Specifies the HTTP methods that are supported by the service. Valid values are DEFAULT, POST, GET, HEAD, PUT, DELETE, and NONE. An asterisk (*) may be used as a short form to represent the POST, GET, and HEAD methods which are default request types for the RAW, HTML, and XML service types. Not all HTTP methods are valid for all the service types. The following table summarizes the valid HTTP methods that can be applied to each service type:

Method value	Applies to service	Description
DEFAULT	all	Use DEFAULT to reset the set of default HTTP methods for the given service type. It cannot be included in a list with other method values.
POST	RAW, HTML, JSON, XML	Enabled by default.
GET	RAW, HTML, JSON, XML	Enabled by default.
HEAD	RAW, HTML, JSON, XML	Enabled by default.
PUT	RAW, HTML, JSON, XML	Not enabled by default.
DELETE	RAW, HTML, JSON, XML	Not enabled by default.
NONE	all	Use NONE to disable access to a service.
*	RAW, HTML, JSON, XML	Same as specifying 'POST,GET,HEAD'.

For example, you can use either of the following clauses to specify that a service supports all HTTP method types:

METHODS '*,PUT,DELETE'
METHODS 'POST,GET,HEAD,PUT,DELETE'

To reset the list of request types for any service type to its default, you can use the following clause:

METHODS 'DEFAULT'

SECURE clause Specifies whether the service should be accessible on a secure or non-secure listener. ON indicates that only HTTPS connections are accepted, and that connections received on the HTTP port are automatically redirected to the HTTPS port. OFF indicates that both HTTP and HTTPS connections are accepted, provided that the necessary ports are specified when starting the web server. The default value is OFF.
USER clause Specifies a database user, or group of users, with permissions to execute the web service request. A USER clause must be specified when the service is configured with AUTHORIZATION OFF and should be specified with AUTHORIZATION ON. (the default) An HTTP request made to a service requiring authorization results in a 401 Authorization Required HTTP response status. Based on this response, the web browser prompts for a user ID and password.

Caution

It is strongly recommended that you specify a USER clause when authorization is enabled (default). Otherwise, authorization is granted to all users.

The USER clause controls which database user accounts can be used to process service requests. Database access permissions are restricted to the privileges assigned to the user of the service.

statement Specifies a command, such as a stored procedure call, to invoke when the service is accessed.

An HTTP request to a non-DISH service with no statement specifies the SQL expression to execute within its URL. Although authorization is required, this capability should not be used in production systems because it makes the server vulnerable to SQL injections. When a statement is defined within the service, the specified SQL statement is the only statement that can be executed through the service.

In a typical web service application, you use statement to call a function or procedure. You can pass host variables as parameters to access client-supplied HTTP variables.

The following statement demonstrates a procedure call that passes two host variables to a procedure named AuthenticateUser. This call presumes that a web client supplies the user_name and user_password variables:

CALL AuthenticateUser ( :user_name, :user_password );

For more information about passing host variables to a function or procedure, see Accessing client-supplied HTTP variables and headers.

Service definitions are stored within the ISYSWEBSERVICE table and can be examined from the SYSWEBSERVICE view.

DBA authority.

None.

SQL/2008 Vendor extension.
Transact-SQL CREATE SERVICE is supported by Adaptive Server Enterprise, for XML and RAW types only.

The following example demonstrates how to create a JSON service.

Start a database server with the -xs (http or https) option and then execute the following SQL statements to set up the service:

CREATE PROCEDURE ListEmployees()
RESULT (
 EmployeeID            integer,
 Surname               person_name_t,
 GivenName             person_name_t,
 StartDate             date,
 TerminationDate       date )
BEGIN
    SELECT EmployeeID, Surname, GivenName, StartDate, TerminationDate 
    FROM Employees 
END;

CREATE SERVICE "jsonEmployeeList"
    TYPE 'JSON'
    AUTHORIZATION OFF
    SECURE OFF
    USER DBA
    AS CALL ListEmployees();

The JSON service provides data for easy consumption by an AJAX call back.

Execute the following SQL statement to create an HTML service that provides the service in a readable form:

CREATE SERVICE "EmployeeList"
    TYPE 'HTML'
    AUTHORIZATION OFF
    SECURE OFF
    USER DBA
    AS CALL ListEmployees();

Use a web browser to access the service using an URL similar to http://localhost/EmployeeList.

CREATE SERVICE statement [HTTP web service]

Syntax

Parameters

Caution

Remarks

Permissions

Side effects

See also

Standards and compatibility

Examples