CREATE FUNCTION statement [Web service]

CREATE [ OR REPLACE ] FUNCTION [ owner.]function-name ( [ parameter, ... ] )
RETURNS data-type
URL url-string
[ HEADER header-string ]
[ SOAPHEADER soap-header-string ]
[ TYPE { 
  'HTTP[ :{ GET | POST[:MIME-type ] | PUT[:MIME-type ] | DELETE | HEAD } ]' | 
  'SOAP[:{ RPC | DOC } ]' } ]
[ NAMESPACE namespace-string ]
[ CERTIFICATE certificate-string ]
[ CLIENTPORT clientport-string ]
[ PROXY proxy-string ]
[ SET protocol-option-string ]

url-string :
' { HTTP | HTTPS | HTTPS_FIPS }://[user:password@]hostname[:port][/path]'

parameter :
   [ IN ] parameter-name data-type [ DEFAULT expression ]

Parameter names must conform to the rules for database identifiers. They must have a valid SQL data type, and must be prefixed by the keyword IN, signifying that the argument is an expression that provides a value to the function. However, function parameters are IN by default.

When functions are executed, not all parameters need to be specified. If a DEFAULT value is provided in the CREATE FUNCTION statement, missing parameters are assigned the default values. If an argument is not provided by the caller and no default is set, an error is given.

OR REPLACE clause Specifying OR REPLACE creates a new function, or replaces an existing function with the same name. This clause changes the definition of the function, but preserves existing permissions. You cannot use the OR REPLACE clause with temporary functions.
RETURNS clause Specify one of the following to define the return type for the SOAP or HTTP function:
- CHAR
- VARCHAR
- LONG VARCHAR
- TEXT
- NCHAR
- NVARCHAR
- LONG NVARCHAR
- NTEXT
- XML
- BINARY
- VARBINARY
- LONG BINARY
The value returned is the body of the HTTP response. No HTTP header information is included. If more information is required, such as status information, use a procedure instead of a function.

The data type does not affect how the HTTP response is processed.
URL clause Use the URL clause only when defining an HTTP or SOAP web services client function. The 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.
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.

For functions 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
HEADER clause When creating HTTP web service client functions, use this clause to add or modify HTTP request header entries. Only printable ASCII characters can be specified for HTTP headers, and they are case-insensitive.
SOAPHEADER clause When declaring a SOAP web service as a function, 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 function can define one or more IN mode substitution parameters, but cannot define an INOUT or OUT substitution parameter.

TYPE clause The 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:GET, HTTP:POST, and HTTP:PUT types. 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 function call after parameter substitutions have been processed. Calling a web service function with a NULL value or no parameter (after substitutions) results in a request with no body and a content-length of zero. Parameter names and values (multiple parameters are permitted) are URL encoded within the body of the HTTP request.

Some typical MIME-types include:

text/plain
text/html
text/xml

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.

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 SQL Anywhere server running the put_data.sql sample:

ALTER 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 SQL Anywhere server running the put_data.sql sample:

ALTER PROCEDURE CDEL(resnm LONG VARCHAR, mediatype LONG VARCHAR)
    URL 'http://localhost/resource/!resnm'
    TYPE 'HTTP:DELETE:!mediatype';

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.

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

SOAP:RPC This type sets the Content-Type to 'text/xml'. SOAP operations and parameters are encapsulated in SOAP envelope XML documents.
SOAP:DOC This type sets the Content-Type 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.

NAMESPACE clause Applies to SOAP client functions 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 function's URL, up to but not including, the optional path component.

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. You can use the file key to specify the file name of the certificate, or you can use the certificate key to specify the server certificate in a string. You cannot specify a file and certificate key together. 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 directed to an HTTPS server, or for requests that 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 function communicates using TCP/IP. It is provided for and recommended only for connections across firewalls, as firewalls filter according to the TCP/UDP port. 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. This clause indicates that the function 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

'REDIR(COUNT=count, STATUS=status-list)' (HTTP or SOAP) The HTTP response status codes such as 302 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 option allows 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 (SQLCODE -983).

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 (SQLCODE -983). 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 handles 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 includes these parameters.

Location: alternate_service?a=1&b=2

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

'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 FUNCTION 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 FUNCTION accounts_login( 
      name LONG VARCHAR, 
      pwd LONG VARCHAR )
   SET 'HTTP ( CHUNK=ON; VERSION=1.1 ), SOAP( OPERATION=login )' 
   ...

The CREATE FUNCTION statement creates a web services function in the database. A function can be created for another user by specifying an owner name.

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.

RESOURCE authority.

DBA authority for external functions, including Java functions.

Automatic commit.

SQL/2008 Vendor extension.
Transact-SQL Not supported by Adaptive Server Enterprise.

The following statement creates a function named cli_test1 that returns images from the get_picture service running on localhost:

CREATE FUNCTION cli_test1( image LONG VARCHAR )
RETURNS LONG BINARY
URL 'http://localhost/get_picture' 
TYPE 'HTTP:GET';

The following statement issues an HTTP request with the URL http://localhost/get_picture?image=widget:

SELECT cli_test1( 'widget' );

The following statement uses a substitution parameter to allow the request URL to be passed as an input parameter. The SET clause is used to turn off CHUNK mode transfer-encoding.

CREATE FUNCTION cli_test2( image LONG VARCHAR, myurl LONG VARCHAR )
RETURNS LONG BINARY
URL '!myurl' 
TYPE 'HTTP:GET'
SET 'HTTP(CH=OFF)'
HEADER 'ASA-ID';

The following statement issues an HTTP request with the URL http://localhost/get_picture?image=widget:

CREATE VARIABLE a_binary LONG BINARY
a_binary = cli_test2('widget', 'http://localhost/get_picture');
SELECT a_binary;

CREATE FUNCTION statement [Web service]

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Examples