Web Services Connector

The Web Services connector, known by the type name ws, exposes stored procedures for calling web services backed by a CData Virtuality Server WS resource adapter. It will commonly be used with the TEXTTABLE or XMLTABLE table functions to use CSV or XML formatted data.

Basic Configuration

Before issuing queries to web services, we need to configure a connection using the SYSADMIN.createConnection procedure:

CALL SYSADMIN.createConnection('webservicealias', 'ws', '<connection properties>');;
CALL SYSADMIN.createDatasource('webservicealias', 'ws', '<model properties>', '<translator properties>');;

Setting the proper binding value on the connector is recommended as it removes the need for callers to pass an explicit value. If your service actually uses SOAP11, but the binding used SOAP12 you will receive execution failures.

Execution Properties

Execution properties are used within the '<translator properties>' section of the createDatasource command:

CALL SYSADMIN.createDatasource('webservicealias', 'ws', '', 'DefaultBinding=HTTP');;

There are ws importer settings, but it provides metadata for dynamic virtual databases.

Configuring 3-legged OAuth2

In order to set up 3-legged OAuth2 with a ws data source, it should be supplied with the authCode and redirectUri parameters. The authCode parameter should be obtained from an authorization server for a combination of certain clientId, clientSecret, and redirectUri.

CALL "SYSADMIN.createConnection"("name" => 'test_ws', "jbossCLITemplateName" => 'ws', "connectionOrResourceAdapterProperties" => 'EndPoint=<endPoint>, SecurityType=OAuth2, authCode=<authCode>, ClientId=<clientId>, ClientSecret=<clientSecret>, redirectUri=<redirectUri>');;
 
CALL "SYSADMIN.createDataSource"("name" => 'test_ws', "translator" => 'ws', "modelProperties" => '', "translatorProperties" => '');;

Configuring NTLM Authentication

In order to set up NTLM authentication with a ws data source, it should be supplied with the AuthDomain and AuthHost parameters. The SecurityType parameter should be set to the NTLM value.

CALL "SYSADMIN.createConnection"("name" => 'test_ws', "jbossCLITemplateName" => 'ws', "connectionOrResourceAdapterProperties" => 'SecurityType=NTLM, AuthDomain=<authDomain>, AuthUserName=<userName>, AuthPassword=<password>');;
 
CALL "SYSADMIN.createDataSource"("name" => 'test_ws', "translator" => 'ws', "modelProperties" => '', "translatorProperties" => '');;

NTLM authentication available since v4.1

Usage

The Web Services connector provides the invoke() and invokeHttp() procedures for accessing web services. 

Since multiple parameters are not required to have values, it is often more clear to call the procedures with named parameter syntax:

CALL ws.invoke(binding => 'HTTP', action => 'GET');;

CALL ws.invokeHttp(
    action => 'POST',
    request => '{ a: 0 }',
    requestContentType => 'application/json',
    "requestHeaders" => 'Authorization: Bearer 1234567
                         bla bla: foo
						 User-Agent: My (fake) Server Software'
);;

Invoke Procedure

Invoke allows for multiple binding, or protocol modes, including HTTP, SOAP11, and SOAP12.

invoke(
	binding in STRING
	, action in STRING
	, request in XML
	, endpoint in STRING
	, failOnHttpError in BOOLEAN
	, failed out BOOLEAN
	, httpResponseCode out INTEGER
	, result ReturnValue XML
)

Input Parameters

binding:

• possible values: HTTP, SOAP11, or SOAP12
• default: as specified via translator property DefaultBinding

action: 

• Action with a SOAP binding indicates the SOAPAction value
• Action with an HTTP binding indicates the HTTP method (GET, POST, PATCH, etc.), which defaults to POST

request: 

• The request XML should be a valid XML document or root element


endpoint: 

• The endpoint URL may be absolute or relative. If it's relative then it will be combined with the default endpoint
• default: endpoint as specified in the WS resource adapter configuration

failOnHttpError:   

• Specifies whether the query should fail or not if an HTTP error occurs during the web service call
• If FALSE is passed to the stored procedure, in the case of HTTP errors
  
  • the web service call on CData Virtuality Server will be successfully executed 
  • failed  in response from web service will be TRUE
  • result in response from web service will contain the error message sent by web service
• default: TRUE
  

Output Parameters

failed: indicates whether the web service call returned an error

httpResponseCode: the response code of the web service call

result: the result of the web service call

InvokeHTTP Procedure

invokeHttp can return the byte contents of an HTTP(S) call.

invokeHttp(
	action in STRING
	, request in OBJECT
	, endpoint in STRING
	, requestContentType in STRING
	, requestHeaders in CLOB
	, failOnHTTPError in BOOLEAN
	, contentType out STRING
	, responseHeadersArray out OBJECT
	, responseHeadersXML out CLOB
	, failed out BOOLEAN
	, httpResponseCode out INTEGER
	, result ReturnValue BLOB
)

Input Parameters

action: 

• default: POST
• indicates the HTTP method (GET, POST, PATCH, PUT, DELETE, etc.)

request:

• C an be one of SQLXML, STRING, BLOB, or CLOB. The request will be sent as the POST payload in byte form. For STRING/CLOB values this will default to the UTF-8 encoding. To control the byte encoding, see the to_bytes function


endpoint: same as for invoke()

requestContentType: 

• May be needed for a server to know what type of data a client sends in its request

requestHeaders: 

• It may be used to add additional or modify some of the request header values. This might be needed by some REST services or custom authentication mechanisms, etc
• Default headers sent by CData Virtuality Server: User-Agent, Content-Type, Host, Accept, Connection and - if request is set - Content-Length
• requestHeaders accepts a CLOB as a collection of key-value pairs 
  • Each pair must have the format <key>:<value> and they must appear on a new line, as in the original HTTP specification ( Header-Name1:Value1{Linebreak}Header-Name2:Value2{Linebreak} )
  • Linebreak may be set via \n, \r\n and \r
  • If the same key occurs more often than once, the last occurrence will be used

failOnHttpError: same as for invoke()

Output Parameters

contentType: returns the Content-Type header value from a server response.

responseHeadersArray: returns all header values from a server response represented as an array of [key, value] arrays. If a server response contains different values for the same header name all these values will be united using a comma separator to one value.

responseHeadersXML: returns all header values from a server response represented as an XML using the following template:

<headers>
   <header name="cookieName">9013ojlkjasdf</header>
   ....
   </header>
</headers>

If a server response contains different values for the same header name all these values will be united using a comma separator to one value.

failed: same as for invoke()

httpResponseCode: same as for invoke()

result: same as for invoke()

PATCH Method

The PATCH method requests that a set of changes described in the request entity be applied to the resource identified by the Request-URI. The difference between the PUT and PATCH requests is reflected in the way the server processes the enclosed entity to modify the resource identified by the Request-URI. 

In a PUT request, the enclosed entity is considered to be a modified version of the resource stored on the origin server, and the client is requesting that the stored version be replaced.

With PATCH, however, the enclosed entity contains a set of instructions describing how a resource currently residing on the origin server should be modified to produce a new version. The PATCH method affects the resource identified by the Request-URI, and it also may have side effects on other resources; i.e., new resources may be created, or existing ones modified, by the application of a PATCH.

Example:

SELECT CAST(TO_CHARS(w.result, 'utf8') AS STRING)
FROM (
    CALL dvcore3233ds.invokehttp (
        endpoint => 'CUSTOMER/3'
        ,action => 'PATCH'
        ,request => '<CUSTOMER><FIRSTNAME>Michael</FIRSTNAME><LASTNAME>Clarke</LASTNAME><STREET>542 Upland Pl.</STREET><CITY>San Francisco</CITY></CUSTOMER>'
        ,requestContentType => 'application/xml'
    )
) w;;