CREATE SERVICE statement

Use this statement to permit a database server to act as a web server.

Syntax 1 - DISH service 
CREATE SERVICE service-name
TYPE 'DISH'
[ GROUP { group-name | NULL } ]
[ FORMAT { 'DNET' | 'CONCRETE' [ EXPLICIT { ON | OFF } ] | 'XML' | NULL } ]
[ common-attributes ]
Syntax 2 - SOAP service 
CREATE SERVICE service-name
TYPE 'SOAP'
[ DATATYPE { ON | OFF | IN | OUT } ]
[ FORMAT { 'DNET' | 'CONCRETE' [ EXPLICIT { ON | OFF } ] | 'XML' | NULL } ]
[ common-attributes ]
AS statement
Syntax 3 - Miscellaneous services 
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
| *
Parameters

  • service-name   Web service names can be any sequence of alphanumeric 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.

    Unlike other services, you cannot specify a forward slash (/) in a DISH service name.

  • TYPE clause   Identifies the type of the service by the result set that is returned. The type must be one of the listed service types. There is no default value.

    • 'SOAP'   The result set is returned as a SOAP response. The format of the data is determined by the FORMAT clause. A request to a SOAP service must be a valid SOAP request, not just a simple HTTP request. For more information about the SOAP standards, see [external link] www.w3.org/TR/SOAP.

    • 'DISH'   A DISH service (Determine SOAP Handler) acts as a proxy for those SOAP services identified by the GROUP clause, and generates the WSDL (Web Services Description Language) for each of these SOAP services upon request.

    • '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.

    • 'HTML'   The result set of a statement or procedure is automatically formatted into an HTML document that contains a table.

    • 'JSON'   The result set is returned in JavaScript Object Notation (JSON). JSON web services are similar to XML web services in that both accept "generic" HTTP requests (that is, the requests do not have to contain a specially-formatted body like SOAP web services). For more information about JSON, visit [external link] http://www.json.org.

    • 'XML'   The result set is returned as XML. If the result set is already XML, no additional formatting is applied. If it is not already XML, it is automatically formatted as XML. The effect is similar to that of using the FOR XML RAW clause in a SELECT statement.

  • GROUP clause   Applies to DISH services only. Specifies a common prefix that controls which SOAP services the DISH service exposes. For example, specifying GROUP xyz exposes only SOAP services xyz/aaaa, xyz/bbbb, or xyz/cccc, but does not expose abc/aaaa or xyzaaaa. If no group name is specified, the DISH service exposes all the SOAP services in the database. SOAP services can be exposed by more than one DISH service. The same characters are permitted in group names as in service names.

    When creating a service, GROUP NULL is identical to not specifying a GROUP clause. However, if used in an ALTER SERVICE statement, GROUP NULL will remove any existing grouping whereas the absence of a GROUP clause will not.

  • DATATYPE clause   Applies to SOAP services only. Controls whether data typing is supported for parameter inputs and/or result set outputs (responses) for all SOAP service formats. When supported, data typing allows a SOAP toolkit to parse and cast the data to the appropriate type. Parameter data types are exposed in the schema section of the WSDL (Web Service Definition Language) generated by the DISH service. Output data types are represented as XML schema type attributes for each column of data.

    The following values are permitted for the DATATYPE clause:

    • ON   Generate data typing for input parameters and result set responses (the default).

    • OFF   Do not generate data typing of input parameters and result set responses.

    • IN   Generate data typing of input parameters only.

    • OUT   Generate data typing of result set responses only.

    For more information about SOAP services, see Using SOAP services.

  • URL clause   Determines whether URL paths are accepted and, if so, how they are processed.

    • OFF   OFF indicates that nothing must follow the service name in a URL request. Specify OFF if the remainder of the URL path is not permitted, or if the service name ends with a forward slash (/). For example, if OFF is selected and you have the URL path http://<host-name>/<service-name>/aaa/bbb/ccc, only http://<host-name>/<service-name> is permitted. The remainder of the URL path, /<aaa/bbb/ccc is not permitted.

    • ON   ON indicates that the remainder of the URL is interpreted as the value of a variable named URL. Specify ON if the remainder of the URI path is permitted and is set as a single parameter. For example, in the URL path http://<host-name>/<service-name>/aaa/bbb/ccc, the remainder of the URI path is /aaa/bbb/ccc, and is treated as a single parameter.

    • ELEMENTS   ELEMENTS indicates that the remainder of the URL path is to be split at the slash characters into a list of up to 10 elements set as multiple parameters. For example, in the URL path http://<host-name>/<service-name>/aaa/bbb/ccc, each element of the path is treated as a separate parameter. For example, url1=aaa, url2=bbb, url3=ccc, and so on. The values are assigned to variables named url plus a numeric suffix of between 1 and 10. If fewer than 10 values are supplied, the remaining variables are set to NULL. If the service name ends with the forward slash character /, then url must be set to OFF. The default value is OFF.

    For more information about URLs, see Understanding how URLs are interpreted, and Working with variables.

  • FORMAT clause   Applies to DISH and SOAP services only. Generates output formats compatible with various types of SOAP clients, such as .NET or Java JAX-RPC. If the format of a SOAP service is not specified, the format is inherited from the service's DISH service declaration. If the DISH service also does not declare a format, it defaults to DNET, which is compatible with .NET clients. A SOAP service that does not declare a format can be used with different types of SOAP clients by defining multiple DISH services, each having a different FORMAT type.

    The following formats are supported:

    • 'DNET'   Microsoft DataSet format for use with .NET SOAP clients. DNET is the default FORMAT value and was the only format available before version 9.0.2.

    • 'CONCRETE'   A platform-neutral DataSet format for use with clients such as JAX-WS, or with clients that automatically generate interfaces based on the format of the returned data structure. Specifying this format type exposes either an explicit dataset element or a SimpleDataset element within the WSDL (Web Service Definition Language). When EXPLICIT ON is specified (the default), the WSDL describes an explicit dataset element. When EXPLICIT OFF is specified, the WSDL describes the SimpleDataset element. The SimpleDataset element describes the result set as a containment hierarchy of a rowset composed of an array of rows, each of which contains an array of column elements. The explicit dataset element extends this by including the actual names and types of each of the columns.

    • 'XML'   A simple XML string format. The DataSet is returned as a string that can be passed to an XML parser. This format is the most portable between SOAP clients.

    • NULL   For SOAP services, the format will inherit from the DISH service format (if it has one) or default to DNET. For DISH services, the format will default to DNET. When creating a new service, the effect of specifying FORMAT NULL is identical to not specifying any format. However, if used in an ALTER SERVICE statement, FORMAT NULL will override any previous format whereas the absence of a FORMAT clause will not override the previous format.

  • statement   If the statement is NULL, the URL must specify the statement to be executed. Otherwise, the specified SQL statement is the only one that can be executed through the service. SOAP services must have statements; DISH services must have none. 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.

  • AUTHORIZATION clause   Determines whether users must specify a user name and password through basic HTTP authorization 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 can 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.

  • 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.

  • METHODS clause   Indicates what type of requests are supported by the service. Valid request types are DEFAULT, POST, GET, HEAD, PUT, and DELETE. An asterisk (*) may be used as a short form to represent the POST, GET and HEAD attributes which are default request types for the RAW, HTML and XML service types. The default request types for SOAP services are POST and HEAD. The default request types for DISH services are GET and HEAD. Not all request types are valid for all of the service types. The following table summarizes this.

    Request type Applies to service Description
    DEFAULT all Use DEFAULT to reset the set of request types to the default set for the given service type. It cannot be included in a list with other request types.
    POST SOAP, DISH, RAW, HTML, XML Enabled by default for SOAP, RAW, HTML and XML.
    GET DISH, RAW, HTML, XML Enabled by default for DISH, RAW, HTML and XML.
    HEAD SOAP, DISH, RAW, HTML, XML Enabled by default for SOAP, DISH, RAW, HTML and XML.
    PUT RAW, HTML, XML Not enabled by default.
    DELETE RAW, HTML, XML Not enabled by default.
    * RAW, HTML, XML Same as specifying 'POST,GET,HEAD'.

    For example, to specify the full list of request types for the RAW service type, you can use either of the following clause:

    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   Indicates whether unsecured 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 identifies the user or group permitted to access the service. The default value is NULL, which grants access to all users.

Remarks

The CREATE SERVICE statement causes the database server to act as a web server. A new entry is created in the ISYSWEBSERVICE system table.

Permissions

Must have DBA authority.

Side effects

None.

See also
Standards and compatibility

  • SQL/2003   Vendor extension.

Examples

To set up a web server quickly, start a database server with the -xs option (for example, -xs http), then execute the following statement: 

CREATE SERVICE tables TYPE 'HTML'
   AUTHORIZATION OFF
   USER DBA
   AS SELECT *
      FROM SYS.SYSTAB;

After 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;

After executing this statement, use any web browser to open the URL http://localhost/hello_world.

The following example demonstrates how to create a JSON 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 "JSON/EmployeeList"
TYPE 'JSON'
AUTHORIZATION OFF
SECURE OFF
USER DBA
AS CALL ListEmployees();

After executing this statement, use any web browser to open the URL http://localhost/JSON/EmployeeList and save the JSON response to a file so that it can be browsed using a text editor.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0