Run the following command to create a SQL Anywhere database:

dbinit ftc_client

Start the personal database client using the following command:

dbsrv12 ftc_client.db

Connect to the database in Interactive SQL using the following command:

dbisql -c "UID=DBA;PWD=sql;SERVER=ftc_client"

Create a new stored procedure to send SOAP requests to a DISH service.

Execute the following SQL statement in Interactive SQL:

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

The http://localhost:8082/soap_endpoint string in the URL clause indicates that the web server runs on localhost and listens on port 8082. The desired DISH web service is named soap_endpoint, which serves as a SOAP endpoint.

The SET clause specifies the name of the SOAP operation or service FtoCService that is to be called.

The default format used when making a web service request is 'SOAP:RPC'. The format chosen in this example is 'SOAP:DOC', which is similar to 'SOAP:RPC' but allows for a richer set of datatypes. SOAP requests are always sent as XML documents. The mechanism for sending SOAP requests is 'HTTP:POST'.

The substitution variables in a SQL Anywhere client procedure (inoutheader, inheader) must be alpha-numeric. If the web service client is declared as a function, all its parameters are IN mode only (they cannot be assigned by the called function). Therefore, OPENXML or other string functions would have to be used to extract the SOAP response header information.

Create a wrapper procedure that builds two special SOAP request header entries, passes them to the FtoC procedure, and processes server responses.

Execute the following SQL statements in Interactive SQL:

CREATE PROCEDURE FahrenheitToCelsius( temperature FLOAT )
BEGIN
  DECLARE io_header LONG VARCHAR;
  DECLARE in_header LONG VARCHAR;
  DECLARE result LONG VARCHAR;
  DECLARE err INTEGER;
  DECLARE crsr CURSOR FOR
    CALL FtoC( temperature, io_header, in_header );
  SET io_header =
    '<Authentication xmlns="SecretAgent" ' ||
    'mustUnderstand="1">' ||
    '<userName alias="99">' ||
    '<first>Susan</first><last>Hilton</last>' ||
    '</userName>' ||
    '</Authentication>';
  SET in_header =
    '<Session xmlns="SomeSession">' ||
    '123456789' ||
    '</Session>';

  MESSAGE 'send, soapheader=' || io_header || in_header;
  OPEN crsr;
  FETCH crsr INTO result, err;
  CLOSE crsr;
  MESSAGE 'receive, soapheader=' || io_header;
  SELECT temperature, Celsius
      FROM OPENXML(result, '//tns:answer', 1, result)
      WITH ("Celsius" FLOAT 'text()');
END;

The first SET statement creates the XML representation of a SOAP header entry to inform the web server of user credentials:

<Authentication xmlns="SecretAgent" mustUnderstand="1">
  <userName alias="99">
    <first>Susan</first>
    <last>Hilton</last>
  </userName>
</Authentication>

The second SET statement creates the XML representation of a SOAP header entry to track the client session ID:

<Session xmlns="SomeSession">123456789</Session>

The OPEN statement causes the FtoC procedure to be called which sends a SOAP request to the web server and then processes the response from the web server. The response includes a header which is returned in inoutheader.