Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

CREATE SERVICE statement [SOAP web service]

Creates a new SOAP over HTTP or DISH service.

  • SOAP over HTTP services
    CREATE [ OR REPLACE ] SERVICE service-name
    [ DATATYPE { ON | OFF | IN | OUT } ]
    [ FORMAT { 'DNET' | 'CONCRETE' [ EXPLICIT { ON | OFF } ] | 'XML' | NULL } ]
    [ common-attributes ]
    AS statement
    common-attributes :
    [ METHODS 'method,...' ]
    [ SECURE { ON | OFF } ]
    [ USER { user-name | NULL } ]
    method :
    | POST
    | HEAD
    | NONE
  • DISH services
    CREATE SERVICE service-name
    [ GROUP { group-name | NULL } ]
    [ FORMAT { 'DNET' | 'CONCRETE' [ EXPLICIT { ON | OFF } ] | 'XML' | NULL } ]
    [ common-attributes ]
    [ METHODS 'method,...' ]
    [ SECURE { ON | OFF } ]
    [ USER { user-name | NULL } ]
    method :
    | POST
    | GET
    | HEAD
    | 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, //).

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

    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.

    • 'SOAP'

      The result set is returned as an XML payload known as a SOAP envelope. The format of the data may be further refined using by the FORMAT clause. A request to a SOAP service must be a valid SOAP request, not just a general HTTP request.

    • 'DISH'

      A DISH service (Determine SOAP Handler) is a SOAP endpoint that references any SOAP service within its GROUP context. It also exposes the interfaces to its SOAP services by generating a WSDL (Web Services Description Language) for consumption by SOAP client toolkits.

  • GROUP clause

    A DISH service without a GROUP clause exposes all SOAP services defined within the database. By convention, the SOAP service name can be composed of a GROUP and a NAME element. The name is delimited from the group by the last slash character. For example, a SOAP service name defined as 'aaa/bbb/ccc' is 'ccc', and the group is 'aaa/bbb'. Delimiting a DISH service using this convention is invalid. Instead, a GROUP clause is applied to specify the group of SOAP services for which it is to be the SOAP endpoint.

    Note Slashes are converted to underscores within the WSDL to produce valid XML. Use caution when using a DISH service that does not specify a GROUP clause such that it exposes all SOAP services that may contain slashes. Use caution when using groups with SOAP service names that contain underscores to avoid ambiguity.
  • DATATYPE clause

    Applies to SOAP services only. When DATATYPE OFF is specified, SOAP input parameters and response data are defined as XMLSchema string types. In most cases, true data types are preferred because it negates the need for the SOAP client to cast the data prior to computation. Parameter data types are exposed in the schema section of the WSDL 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

      Generates data typing of input parameters and result set responses.

    • OFF

      All input parameters and response data are typed as XMLSchema string (default).

    • IN

      Generates true data types for input parameters only. Response data types are XMLSchema string.

    • OUT

      Generates true data types for responses only. Input parameters are typed as XMLSchema string.

  • FORMAT clause

    This clause specifies the output format when sending responses to SOAP client applications.

    The SOAP service format is dictated by the associated DISH service format specification when it is not specified by the SOAP service. The default format is DNET.

    SOAP requests should be directed to the DISH service (the SQL Anywhere SOAP endpoint) to leverage common formatting rules for a group of SOAP services (SOAP operations). A SOAP service FORMAT specification overrides that of a DISH service. The format specification of the DISH service is used when a SOAP service does not define a FORMAT clause. If no FORMAT is provided by either service then the default is 'DNET'.

    The following formats are supported:

    • 'DNET'

      The output is in a System.Data.DataSet compatible format for consumption by .NET client applications. (default)

    • 'CONCRETE'

      This output format is used to support client SOAP toolkits that are capable of generating interfaces representing arrays of row and column objects but are not able to consume the DNET format. Java and .NET clients can easily consume this output format.

      The specific format is exposed within the WSDL as an explicit dataset object or a SimpleDataset. Both dataset representations describe a data structure representing an array of rows with each row containing an array of columns. An explicit dataset object has the advantage of representing the actual shape of the result set by providing parameter names and data types for each column in the row. In contrast, the SimpleDataset exposes rows containing an unbounded number of columns of any type.

      FORMAT 'CONCRETE' EXPLICIT ON requires that the Service statement calls a stored procedure which defines a RESULT clause. Having met this condition, the SOAP service will expose an explicit dataset whose name begins with the service name appended with Dataset.

      If the condition is not met, a SimpleDataset is used.

    • 'XML'

      The output is generated in an XMLSchema string format. The response is an XML document that requires further processing by the SOAP client to extract column data. This format is suitable for SOAP clients that cannot generate intermediate interface objects that represent arrays of rows and columns.

    • NULL

      A NULL type causes the SOAP or DISH service to use the default behavior. The format type of an existing service is overwritten when using the NULL type in an ALTER SERVICE statement.


    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 SOAP services, and a user must be specified with the USER clause. All requests are run using that user's account and privileges. 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 privileges 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) which 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, and NONE. An asterisk (*) may be used as a short form to represent the POST, GET, and HEAD methods. The default method types for SOAP services are POST and HEAD. The default method types for DISH services are GET, POST, and HEAD. 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 both 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 both Enabled by default for SOAP.
    GET DISH only Enabled by default for DISH.
    HEAD both Enabled by default for SOAP and DISH.
    NONE both

    Use NONE to disable access to a service. When applied to a SOAP service, the service cannot be directly accessed by a SOAP request. This enforce exclusive access to a SOAP operation through a DISH service SOAP endpoint.

    It is recommended that you specify METHODS 'NONE' for each SOAP service.

    * DISH only Same as specifying 'POST,GET,HEAD'.

    For example, you can use the following clause to specify that a service supports all SOAP over HTTP method types:


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

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


    It is strongly recommended that you specify a USER clause when authorization is enabled (the 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.

    A DISH service is the only service that must either define a null statement, or not define a statement. A SOAP service must define a statement. Any other SERVICE can have a NULL statement, but only if it is configured with AUTHORIZATION ON.

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

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


You must have the MANAGE ANY WEB SERVICE system privilege.

Side effects


  • ANSI/ISO SQL Standard

    Not in the standard.

  • Transact-SQL

    CREATE SERVICE is supported by Adaptive Server Enterprise for SOAP types only.