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

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (A-D)

 

CREATE PROCEDURE statement (web services)

Use this statement to create a web services client procedure which makes an HTTP or SOAP request. To create a SQL procedure, see CREATE PROCEDURE statement.

CREATE [ OR REPLACE ] PROCEDURE [ owner.]procedure-name ( [ parameter, ... ] )
URL url-string
[ TYPE { http-type-spec-string | soap-type-spec-string } ]
[ HEADER header-string ]
[ CERTIFICATE certificate-string ]
[ CLIENTPORT clientport-string ]
[ PROXY proxy-string ]
[ SET protocol-option-string ]
[ SOAPHEADER soap-header-string ]
[ NAMESPACE namespace-string ]
http-type-spec-string :
HTTP[: { GET 
  | POST[:MIME-type ] 
  | PUT[:MIME-type ] 
  | DELETE 
  | HEAD } ] 
soap-type-spec-string :
SOAP[:{ RPC | DOC }
parameter :
  parameter-mode parameter-name data-type [ DEFAULT expression ]
parameter-mode : 
IN 
| OUT 
| INOUT
url-string :
{ HTTP | HTTPS | HTTPS_FIPS }://[user:password@]hostname[:port][/path]
protocol-option-string
[ http-option-list]
[, soap-option-list ]
http-option-list : 
HTTP(
[ CH[UNK]={ ON | OFF | AUTO } ]
[; VER[SION]={ 1.0 | 1.1 } ]
)
 soap-option-list: 
SOAP(OP[ERATION]=soap-operation-name)
Parameters
  • CREATE PROCEDURE   You can create or replace a web services client procedure. You can use PROC as a synonym for PROCEDURE.

    For SOAP requests, the procedure name is used as the SOAP operation name by default. See the SET clause below for more information.

    Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type. For a list of valid data types, see SQL data types. Only SOAP requests support the transmission of typed data such as FLOAT, INT, and so on. HTTP requests support the transmission of strings only, so you are limited to CHAR types. For more information about supported SOAP types, see Working with data types and Working with structured data types.

    Parameters can be prefixed with one of the keywords IN, OUT, or INOUT. If you do not specify one of these values, parameters are INOUT by default. The keywords have the following meanings:

    • IN   The parameter is an expression that provides a value to the procedure.

    • OUT   The parameter is a variable that could be given a value by the procedure.

    • INOUT   The parameter is a variable that provides a value to the procedure, and could be given a new value by the procedure.

    When procedures are executed using the CALL statement, not all parameters need to be specified. If a default value is provided in the CREATE PROCEDURE statement, missing parameters are assigned the default values. If an argument is not provided in the CALL statement, and no default is set, an error is given.

    Specifying OR REPLACE (CREATE OR REPLACE PROCEDURE) creates a new procedure, or replaces an existing procedure with the same name. This clause changes the definition of the procedure, but preserves existing permissions. An error is returned if you attempt to replace a procedure that is already in use.

    You cannot create TEMPORARY web services procedures.

  • URL clause   Specifies the URI of the web service. The optional user name and password parameters provide a means of supplying the credentials needed for HTTP basic authentication. HTTP basic authentication base-64 encodes the user and password information and passes it in the Authentication header of the HTTP request. When specified in this way, the user name and password are passed unencrypted, as part of the URL.

    Specifying HTTPS as the URI scheme configures the procedure for secure communication over Secure Socket Layer. Such a URI requires an appropriate CERTIFICATE clause to authenticate the server and establish a secure data channel.

    Specifying HTTPS_FIPS forces the system to use the FIPS libraries. If HTTPS_FIPS is specified, but no FIPS libraries are present, non-FIPS libraries are used instead.

    When specified in this way, the user name and password are passed unencrypted, as part of the URL.

  • TYPE clause   Used to specify the format used when making the web service request. If SOAP is specified or no type clause is included, the default type SOAP:RPC is used. HTTP implies HTTP:POST. Since SOAP requests are always sent as XML documents, HTTP:POST is always used to send SOAP requests. See Creating web service client functions and procedures.

  • HEADER clause   When creating HTTP web service client procedures, use this clause to add, modify, or delete HTTP request header entries. The specification of headers closely resembles the format specified in RFC2616 Hypertext Transfer Protocol — HTTP/1.1, and RFC822 Standard for ARPA Internet Text Messages, including the fact that only printable ASCII characters can be specified for HTTP headers, and they are case-insensitive.

    For more information about using HTTP headers, see Working with HTTP headers.

  • CERTIFICATE clause   To make a secure (HTTPS) request, a client must have access to the certificate used by the HTTPS server. The necessary information is specified in a string of semicolon-separated key/value pairs. The certificate can be placed in a file and the name of the file provided using the file key, or the whole certificate can be placed in a string, but not both. The following keys are available:

    Key Abbreviation Description
    file The file name of the certificate.
    certificate cert The certificate itself.
    company co The company specified in the certificate.
    unit The company unit specified in the certificate.
    name The common name specified in the certificate.

    Certificates are required only for requests that are either directed to an HTTPS server, or can be redirected from a non-secure to a secure server.

  • CLIENTPORT clause   Identifies the port number on which the HTTP client procedure communicates using TCP/IP. It is provided for and recommended only for connections through firewalls that filter “outgoing” TCP/IP connections. You can specify a single port number, ranges of port numbers, or a combination of both; for example, CLIENTPORT '85,90-97'. See ClientPort protocol option [CPORT].

  • PROXY clause   Specifies the URI of a proxy server. For use when the client must access the network through a proxy. Indicates that the procedure is to connect to the proxy server and send the request to the web service through it.

  • SET clause   Specifies protocol-specific behavior options for HTTP and SOAP. The following list describes the supported SET options. CHUNK and VERSION apply to the HTTP protocol, and OPERATION applies to the SOAP protocol. Parameter substitution is supported for this clause.

    • 'HTTP(CH[UNK]=option)'   (HTTP or SOAP) This option allows you to specify whether to use chunking. Chunking allows HTTP messages to be broken up into several parts. Possible values are ON (always chunk), OFF (never chunk), and AUTO (chunk only if the contents, excluding auto-generated markup, exceeds 8196 bytes). For example, the following SET clause enables chunking:
      SET 'HTTP(CHUNK=ON)'

      If the CHUNK option is not specified, the default behavior is AUTO. If a chunked request fails in AUTO mode with a status of 505 HTTP Version Not Supported, or with 501 Not Implemented, or with 411 Length Required, the client retries the request without chunked transfer-coding.

      Set the CHUNK option to OFF (never chunk) if the HTTP server does not support chunked transfer-coded requests.

      Since CHUNK mode is a transfer encoding supported starting in HTTP version 1.1, setting CHUNK to ON requires that the version (VER) be set to 1.1, or not be set at all, in which case 1.1 is used as the default version.

    • ' HTTP(VER[SION]=ver)'   (HTTP or SOAP) This option allows you to specify the version of HTTP protocol that is used for the format of the HTTP message. For example, the following SET clause sets the HTTP version to 1.1:
      SET 'HTTP(VERSION=1.1)'

      Possible values are 1.0 and 1.1. If VERSION is not specified:

      • if CHUNK is set to ON, 1.1 is used as the HTTP version

      • if CHUNK is set to OFF, 1.0 is used as the HTTP version

      • if CHUNK is set to AUTO, either 1.0 or 1.1 is used, depending on whether the client is sending in CHUNK mode

    • ' SOAP(OP[ERATION]=soap-operation-name)   (SOAP only) This option allows you to specify the name of the SOAP operation, if it is different from the name of the procedure you are creating. The value of OPERATION is analogous to the name of a remote procedure call. For example, if you wanted to create a procedure called accounts_login that calls a SOAP operation called login, you would specify something like the following:
      CREATE PROCEDURE accounts_login( 
            name LONG VARCHAR, 
            pwd LONG VARCHAR )
         SET 'SOAP(OPERATION=login)'

      If the OPERATION option is not specified, the name of the SOAP operation must match the name of the procedure you are creating.

    The following statement shows how several protocol-option settings are combined in the same SET clause:

    CREATE PROCEDURE accounts_login( 
          name LONG VARCHAR, 
          pwd LONG VARCHAR )
       SET 'HTTP ( CHUNK=ON; VERSION=1.1 ), SOAP( OPERATION=login )' 
       ...

  • SOAPHEADER clause   (SOAP format only) When declaring a SOAP web service as a procedure, use this clause to specify one or more SOAP request header entries. A SOAP header can be declared as a static constant, or can be dynamically set using the parameter substitution mechanism (declaring IN, OUT, or INOUT parameters for hd1, hd2, and so on). A web service procedure can define one or more IN mode substitution parameters, and a single INOUT or OUT substitution parameter.

    The following example illustrates how a client can specify the sending of several header entries with parameters and receiving the response SOAP header data:

    CREATE PROCEDURE soap_client
      (INOUT hd1 LONG VARCHAR, IN hd2 LONG VARCHAR, IN hd3 LONG VARCHAR) 
      URL 'localhost/some_endpoint' 
      SOAPHEADER '!hd1!hd2!hd3';

    For more information about using SOAP headers, see Working with SOAP headers.

  • NAMESPACE clause   (SOAP format only) This clause identifies the method namespace usually required for both SOAP:RPC and SOAP:DOC requests. The SOAP server handling the request uses this namespace to interpret the names of the entities in the SOAP request message body. The namespace can be obtained from the WSDL (Web Services Description Language) of the SOAP service available from the web service server. The default value is the procedure's URL, up to but not including the optional path component. For more information about using SOAP namespaces, see Working with structured data types.

For more information about creating web services, including examples, see SQL Anywhere web services.

Remarks

Parameter values are passed as part of the request. The syntax used depends on the type of request. For HTTP:GET, the parameters are passed as part of the URL; for HTTP:POST requests, the values are placed in the body of the request. Parameters to SOAP requests are always bundled in the request body.

Permissions

Must have RESOURCE authority.

Must have DBA authority to create a procedure for another user.

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Persistent Stored Module feature. The syntax extensions for Java result sets are as specified in the optional J621 feature.

Example

The following example creates a web services client procedure named FtoC.

CREATE PROCEDURE FtoC( IN temperature FLOAT,
    INOUT inoutheader LONG VARCHAR,
    IN inheader LONG VARCHAR )
  URL 'http://localhost:8082/FtoCService'
  TYPE 'SOAP:DOC'
  SOAPHEADER '!inoutheader!inheader';