CREATE PROCEDURE statement [Web service]

• OR REPLACE clause

  Specifying 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 privileges. An error is returned if you attempt to replace a procedure that is already in use.

• procedure-name

  The name of the procedure.

• parameter-name

  Parameter names must conform to the rules for other database identifiers such as column names. They must have a valid SQL data type.

  If a parameter has a default value, it need not be specified. Parameters with no default value must be specified.

  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.

• datatype

  The data type of the parameter. Set the data type explicitly, or specify the %TYPE or %ROWTYPE attribute to set the data type to the data type of another object in the database. Use %TYPE to set it to the data type of a column in a table or view. Use %ROWTYPE to set the data type to a composite data type derived from a row in a table or view. However, defining the data type using a %ROWTYPE that is set to a table reference variable (TABLE REF (table-reference-variable) %ROWTYPE is not allowed.

  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.

• RESULT clause

  The RESULT clause is required to use the procedure in a SELECT statement. The RESULT clause must return two columns. The first column contains HTTP response header, status, and response body attributes, while the second column contains the values for these attributes. The RESULT clause must specify two character data types. For example, VARCHAR or LONG VARCHAR. If the RESULT clause is not specified, the default column names are Attribute and Value and their data types are LONG VARCHAR.

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

  For procedures of type HTTP:GET, query parameters can be specified within the URL clause in addition to being automatically generated from parameters passed to a procedure.

  URL 'http://localhost/service?parm=1

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

  To use a certificate from the operating system certificate store, specify a URL beginning with https://.

• TYPE clause

  Specifies the format used when making the web service request. SOAP:RPC is used when SOAP is specified or no TYPE clause is included. HTTP:POST is used when HTTP is specified.

  The TYPE clause allows the specification of a MIME-type for HTTP:POST and HTTP:PUT types. When HTTP:PUT is used, then a MIME-type must be specified.The MIME-type specification is used to set the Content-Type request header and set the mode of operation to allow only a single call parameter to populate the body of the request. Only zero or one parameter may remain when making a web service stored procedure call after parameter substitutions have been processed. Calling a web service procedure with a NULL value or no parameter (after substitutions) results in a request with no body and a content-length of zero. When a MIME-type is specified then the single body parameter is sent in the request as is, so the application must ensure that the content is formatted to match the MIME-type.

  Some typical MIME-types include:

  • text/plain
  • text/html
  • text/xml

  When no MIME-type is specified, parameter names and values (multiple parameters are permitted) are URL encoded within the body of the HTTP request.

  The keywords for the TYPE clause have the following meanings:

  • HTTP:GET

    By default, this type uses the application/x-www-form-urlencoded MIME-type for encoding parameters specified in the URL.

    For example, the following request is produced when a client submits a request from the URL http://localhost/WebServiceName?arg1=param1&arg2=param2:

    GET /WebServiceName?arg1=param1&arg2=param2 HTTP/1.1
    // <End of Request - NO BODY>

  • HTTP:POST

    By default, this type uses the application/x-www-form-urlencoded MIME-type for encoding parameters specified in the body of a POST request. URL parameters are stored in the body of the request.

    For example, the following request is produced when a client submits a request from the URL http://localhost/WebServiceName?arg1=param1&arg2=param2:

    POST /WebServiceName HTTP/1.1
    Content-Type: application/x-www-form-urlencoded
    Content-Length: 19
    arg1=param1&arg2=param2
    // <End of Request>

  • HTTP:PUT

    HTTP:PUT is similar to HTTP:POST, but the HTTP:PUT type does not have a default media type.

    The following example demonstrates how to configure a general purpose client procedure that uploads data to a database server running the %SQLANYSAMP17%\SQLAnywhere\HTTP\put_data.sql sample:

    CREATE OR REPLACE PROCEDURE CPUT([data] LONG VARCHAR, resnm LONG VARCHAR, mediatype LONG VARCHAR)
        URL 'http://localhost/resource/!resnm'
        TYPE 'HTTP:PUT:!mediatype';
    
    CALL CPUT('hello world', 'hello', 'text/plain' );

  • HTTP:DELETE

    A web service client procedure can be configured to delete a resource located on a server. Specifying the media type is optional.

    The following example demonstrates how to configure a general purpose client procedure that deletes a resource from a database server running the put_data.sql sample:

    CREATE OR REPLACE PROCEDURE CDEL(resnm LONG VARCHAR)
        URL 'http://localhost/resource/!resnm'
        TYPE 'HTTP:DELETE';
    
    CALL CDEL('hello', 'text/plain' );

  • HTTP:HEAD

    The HEAD method is identical to a GET method but the server does not return a body. A media type can be specified.

    CREATE OR REPLACE PROCEDURE CHEAD(resnm LONG VARCHAR)
        URL 'http://localhost/resource/!resnm'
        TYPE 'HTTP:HEAD';
    
    CALL CHEAD( 'hello' );

  • HTTP:OPTIONS

    The OPTIONS method is identical to a GET method but the server does not return a body. A media type can be specified. This method allows Cross-Origin Resource Sharing (CORS).

  • SOAP:RPC

    This type sets the Content-Type header to 'text/xml'. SOAP operations and parameters are encapsulated in SOAP envelope XML documents.

  • SOAP:DOC

    This type sets the Content-Type header to 'text/xml'. It is similar to the SOAP:RPC type but allows you to send richer data types. SOAP operations and parameters are encapsulated in SOAP envelope XML documents.

  Specifying a MIME-type for the TYPE clause automatically sets the Content-Type header to that MIME-type.

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

  Headers can be defined as header-name:value-name pairs. Each header must be delimited from its value with a colon ( : ) and therefore cannot contain a colon. You can define multiple headers by delimiting each pair with \n, \x0d\n, <LF> (line feed), or <CR><LF>. (carriage return followed by a line feed)

  Multiple contiguous white spaces within the header are converted to a single white space.

• CERTIFICATE clause

  To make a secure (HTTPS) request, a client must have access to the certificate used to sign the HTTP server's certificate (or any certificate higher in the signing chain). The necessary information is specified in a string of semicolon-separated keyword=value pairs. The following keywords are available:

  Keyword	Abbreviation	Description
  file		The file name of the certificate or specify * to use a certificate from the operating system certificate store. Cannot be specified if either the certificate or certificate_name keyword is specified.
  certificate	cert	The certificate itself. Cannot be specified if either the file or certificate_name keyword is specified.
  certificate_name	cert_name	The name of a certificate stored in the database. Cannot be specified if either the file or certificate keyword is specified.
  company	co	The company specified in the certificate.
  unit		The company unit specified in the certificate.
  name		The common name specified in the certificate.
  skip_certificate_name_check		Controls whether the client library skips the check of the server host name against the database server certificate host names.
  allow_expired_certs		Controls whether the client libraries accept a root certificate and database server certificate that have either expired or are not yet valid.

  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. Only PEM formatted certificates are supported.

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

• PROXY clause
  Specifies the URI of a proxy server. For use when the client must access the network through a proxy. The proxy-string is usually an HTTP or HTTPS url-string. This is site specific information that you usually need to obtain from your network administrator. This clause indicates that the procedure is to connect to the proxy server and send the request to the web service through it. For an example, the following PROXY clause sets the proxy server to proxy.example.com:

  PROXY http://proxy.example.com

• SET clause

  Specifies protocol-specific behavior options for HTTP, SOAP, and REDIR (redirects). Only one SET clause is permitted. The following list describes the supported SET options. CHUNK, EXCEPTIONS, VERSION, and KTIMEOUT apply to the HTTP protocol, OPERATION applies to the SOAP protocol, and COUNT and STATUS apply to the REDIR option. REDIR options can be included with either HTTP or SOAP protocol options.

  • CHUNK={ ON | OFF | AUTO }

    (short form CH) This HTTP 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.

  • EXCEPTIONS={ ON | OFF | AUTO }

    (short form EX) This HTTP option allows you to control status code handling. The default is ON.

    When set to ON or AUTO, HTTP client procedures will return a result set for HTTP success status codes (1XX and 2XX) and all codes will raise the exception SQLE_HTTP_REQUEST_FAILED.

    SET 'HTTP(EXCEPTIONS=AUTO)'

    When set to OFF, HTTP client procedures will always return a result set, independent of the HTTP status code. The result row with the word Status in the attribute column contains the HTTP status code in the value column.

    Exceptions that are not related to the HTTP status code (for example, SQLE_UNABLE_TO_CONNECT_TO_HOST) will be raised when appropriate regardless of the EXCEPTIONS setting.

  • VERSION={ 1.0 | 1.1 }

    (short form VER) This HTTP option allows you to specify the version of the 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

  • KTIMEOUT=number-of-seconds

    (short form KTO) This HTTP option allows you to specify the keep-alive timeout criteria, permitting a web client procedure to instantiate and cache a keep-alive HTTP/HTTPS connection for a period of time. To cache an HTTP keep-alive connection, the HTTP version must be set to 1.1 and KTIMEOUT set to a non-zero value. KTIMEOUT may be useful for HTTPS connections particularly, if you notice a significant performance difference between HTTP and HTTPS connections. A database connection can only cache a single keep-alive HTTP connection. Subsequent calls to a web client procedure using the same URI reuse the keep-alive connection. Therefore, the executing web client call must have a URI whose scheme, destination host and port match that of the cached URI, and the HEADER clause must not specify Connection: close. When KTIMEOUT is not specified, or is set to zero, HTTP/HTTPS connections are not cached.

  • OPERATION=soap-operation-name

    (short form OP) This SOAP 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.

  • COUNT=count

    (short form CNT) This REDIR option allows you to control redirects. See STATUS below.

  • STATUS=status-list

    (short form STAT) This REDIR option allows you to control redirects. HTTP response status codes such as302 Found and 303 See Other are used to redirect web applications to a new URI, particularly after an HTTP POST has been performed. For example, a client request could be:

    GET /people/alice HTTP/1.1
    Host: www.example.com
    Accept: text/html, application/xhtml+xml
    Accept-Language: en, de

    The web server response could be:

    HTTP/1.1 302 Found
    Location: http://www.example.com/people/alice.en.html

    In response, the client would send another HTTP request to the new URI. The REDIR options allow you to control the maximum number of redirections allowed and which HTTP response status codes to automatically redirect.

    For example, SET 'REDIR(COUNT=3; STATUS=301,307)' allows a maximum limit of 3 re-directions and permits redirection for 301 and 307 statuses. If one of the other redirection status codes such as 302 or 303 is received, an error is issued (SQLE_HTTP_REQUEST_FAILED).

    The default redirection limit count is 5. By default, an HTTP client procedure will automatically redirect in response to all HTTP redirection status codes (301, 302, 303, 307). To disallow all redirection status codes, use SET 'REDIR(COUNT=0)'. In this mode, a redirection response does not result in an error (SQLE_HTTP_REQUEST_FAILED). Instead, a result set is returned with the HTTP status and response headers. This permits a caller to conditionally reissue the request based on the URI contained in the Location header.

    A web service procedure specifying a POST HTTP method which receives a 303 See Other status issues a redirect request using the GET HTTP method.

    The Location header can contain either an absolute path or a relative path. The HTTP client procedure will handle either. The header can also include query parameters and these are forwarded to the redirected location. For example, if the header contained parameters such as the following, the subsequent GET or a POST will include these parameters.

    Location: alternate_service?a=1&b=2

    In the above example, the query parameters are a=1&b=2.

  The following example shows how several 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 ), REDIR(COUNT=5;STATUS=302,303)' 
     ...

  The following example shows the use of short forms with uppercase and lowercase letters.

  CREATE PROCEDURE accounts_login( name LONG VARCHAR, pwd LONG VARCHAR )
     SET 'HTTP( CH=ON; Ver=1.1 ), REDIR(CNT=5;Stat=302,303)' 
     ...

• 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 using parameter substitution 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';

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

  You can specify a variable name for namespace-string. If the variable is NULL, the namespace property is ignored.

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.

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. For more information, see the SET clause.

You cannot create TEMPORARY web services procedures.

For required parameters that accept variable names, an error is returned if one of the following conditions is true:

• The variable does not exist
• The contents of the variable are NULL
• The variable exceeds the length allowed by the parameter
• The data type of the variable does not match that required by the parameter

You must have the CREATE PROCEDURE system privilege to create procedures owned by you.

You must have the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege to create procedures owned by others.

To replace an existing procedure, you must own the procedure or have one of the following:

• CREATE ANY PROCEDURE and DROP ANY PROCEDURE system privileges.

• CREATE ANY OBJECT and DROP ANY OBJECT system privileges.

• ALTER ANY OBJECT or ALTER ANY PROCEDURE system privileges.

Automatic commit.

• ANSI/ISO SQL Standard

  Not in the standard.

• Transact-SQL

  Not supported by Adaptive Server Enterprise.