A SQL Anywhere database can provide and consume web services. These can be standard web services available over the Internet, or these can be provided by SQL Anywhere databases, as long as the web service is not in the same database as the client procedure or function.

SQL Anywhere can act as both HTTP and SOAP web service clients. This functionality is provided through stored functions and stored procedures.

Client functions and procedures are created and manipulated using the following SQL statements:

• CREATE FUNCTION statement (web services)
• CREATE PROCEDURE statement (web services)
• ALTER FUNCTION statement
• ALTER PROCEDURE statement
• DROP FUNCTION statement
• DROP PROCEDURE statement

For example, the syntax of the CREATE FUNCTION and CREATE PROCEDURE statements, as used to create web service client functions, are as follows:

CREATE FUNCTION [ owner.]procedure-name ( [ parameter, ... ] )
RETURNS data-type
URL url-string
[ proc-attributes ]

CREATE PROCEDURE [ owner.]procedure-name ( [ parameter, ... ] )
URL url-string
[ proc-attributes ]

Key to this syntax is the URL clause, which is used to provide the URL of the web service that you want the procedure to access. The basic syntax of the URL clause is as follows:

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

The optional user and password information permits you to access web services that require authentication. The hostname can be the name or the IP address of the computer providing the web service.

The port number is required only if the server is listening on a port number other than the default. The default port numbers are 80 for HTTP services and 443 for HTTPS services.

The path identifies the resource or web service on the server.

A request can be sent to any web service, whether it is provided by another SQL Anywhere database or available over the Internet. If the web service is provided by the same database server, it cannot reside in the same database as the client function. Attempting to access a web service in the same database results in the error 403 Forbidden.

Because it is used for parameter substitution, exclamation marks that appear in strings anywhere in the procedure definition must be escaped. See Escaping the ! character.

Common clauses

The additional clauses that enable you to supply more details about the procedure call are as follows:

proc-attributes :
[ 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]

The TYPE clause is important because it tells SQL Anywhere how to format the request to the web service provider. Standard SOAP types RPC and DOC are available. The standard HTTP methods of GET and POST are available, specified as HTTP:GET and HTTP:POST, respectively. Specifying HTTP implies HTTP:POST.

If type SOAP is chosen, SQL Anywhere automatically formats the request as an XML document in the standard format necessary for SOAP requests. Since a SOAP request is always an XML document, an HTTP POST request is always implicitly used to send the SOAP request document to the server when type SOAP is chosen. Specifying SOAP implies SOAP:RPC.

Names for web service client functions and procedures

The procedure name is used as the SOAP operation name when building the outgoing SOAP request. In addition, the names of any parameters also appear in tagnames in the SOAP request envelope. Since the SOAP server expects to see these names, specifying them correctly is an important part of defining a SOAP stored procedure. This fact places restrictions on the name of SOAP procedures and functions, beyond the general rules that apply to procedure and function names in SQL Anywhere.

The following statement creates a SOAP stored procedure named MyOperation:

CREATE PROCEDURE MyOperation ( a INTEGER, b CHAR(128) )
URL 'HTTP://localhost'
TYPE 'SOAP:DOC';

When this procedure is called, such as by the following statement, a SOAP request is generated:

CALL MyOperation( 123, 'abc' );

The procedure name appears in the <m:MyOperation> tag within the request body. The two parameters to the procedure, a and b, become <m:a> and <m:b>, respectively.

<SOAP-ENV:Envelope
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:m="http://localhost">
  <SOAP-ENV:Body>
    <m:MyOperation>
      <m:a>123</m:a>
      <m:b>abc</m:b>
    </m:MyOperation>
  </SOAP-ENV:Body>
 </SOAP-ENV:Envelope>

Namespace URIs

All SOAP requests require a method namespace URI. The SOAP processor on the server side uses this URI to understand the names of the various entities in the message body of the request.

When creating a SOAP function or procedure, of either SOAP:DOC or SOAP:RPC, you may be required to specify a namespace URI before the call succeeds. You can obtain the required namespace value from the WSDL description document, or from other available documentation for the service. The NAMESPACE clause applies only to SOAP functions and procedures. The default namespace value is the procedure's URI, up to, but not including, the optional path component and excluding any user and password values.

HTTPS requests

To issue a secure HTTPS request, the client must have access to the server's certificate, or the certificate used to sign the server's certificate. This certificate tells SQL Anywhere how to encrypt the request. Certificate values are also required when a request directed to an insecure server might be redirected to a secure one.

There are two ways to provide the certificate information. You can either place the certificate in a file and provide the file name, or you can provide the entire certificate as a string value. You cannot do both.

The certificate attributes are supplied as a string value constructed as key=value pairs separated by semicolons:

certificate-string :
{ file=filename | certificate=string } ; company=company ; unit=company-unit ; name= common-name

The following keys are available:

For example, the following statement creates a procedure that makes a secure request to a web service located on the same computer as the client:

CREATE PROCEDURE test()
URL 'HTTPS://localhost/myservice'
CERTIFICATE 'file=C:\srv_cert.id;co=iAnywhere;
             unit=SA;name=JohnSmith';

Since no TYPE clause was provided, the request is assumed to be of type SOAP:RPC. The server's public certificate is located in the file C:\srv_cert.id.

Client ports

When accessing web services through a firewall, it is sometimes necessary to tell SQL Anywhere which ports to use when opening a connection to the server. Ordinarily, port numbers are obtained dynamically, and you should rely on the default behavior unless your firewall restricts access to a particular range of ports.

The ClientPort option designates the port number on which the client application communicates using TCP/IP. You can specify a single port number, or a combination of individual port numbers, and ranges of port numbers, as demonstrated in the following example:

CREATE PROCEDURE test ()
URL 'HTTPS://localhost/myservice'
CLIENTPORT '5040,5050-5060,5070';

It is best to specify a list or a range of port numbers. If you specify a single port number, then only one connection is maintained at a time. In fact, even after closing the one connection, there is a timeout period of several minutes during which no new connection can be made to the same remote server and port. When you specify a list and/or range of port numbers, the application keeps trying port numbers until it finds one to which it can successfully bind.

This feature is similar to the ClientPort network protocol option. See ClientPort protocol option [CPORT].

Using proxies

Some web service requests may need to be made through a proxy server. When this is the case, the URL of the proxy server must be supplied using the PROXY clause.

The format of the value is the same as for the URL clause, although any user, password, or path values are ignored:

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

When a proxy server is specified, SQL Anywhere formats the request and sends it to the proxy server using the supplied proxy URL. The proxy server forwards the request to the final destination, obtains the response, and forwards the response back to SQL Anywhere.

Logging web service client procedures

Information from web service clients, including HTTP requests and transport data, can be logged to the web service client log file. You enable logging to this file by starting the database server with the -zoc server option or by setting the WebClientLogging server property using the sa_server_option system procedure. See -zoc server option, and sa_server_option system procedure.