Articles in this section

See more

QuerySurge and Web Services: SOAP API Queries

QuerySurge users know QuerySurge's strengths in connecting to a broad variety of back-end data systems, from traditional databases, to Big Data products, to Flat Files and XMLs. However, users are increasingly interested in using QuerySurge to test data from Web Services. This article describes a worked example of calling into a SOAP API from QuerySurge to compare data obtained from the webservice call to other up- or down-stream data stores.

In this example, we use a custom function plugged into QuerySurge's Flat File JDBC driver to issue a SOAP API request and to write the results to a CSV file. The resulting Flat File is then queried using the standard approach as part of a QueryPair. 

The basics of setting up a Flat File for QuerySurge are discussed in these Knowledge Base articles: Configuring Connections Delimited Flat Files. This example produces a simple comma-delimited file, for which this setup is appropriate. A worked example for writing a Custom Function for the Flat File driver is available in this article.

SOAP Client

For a SOAP webservice client, we use the membrane-soa client, which is part of an open source SOA offering from Predic8. Details of its use are available on the membrane-soa website; we don't discuss client usage deeply here beyond the illustrations in the sample code (see the download in the Resources section at the end of this article).

SOAP Webservice

For the worked example shown here, we use a public SOAP webservice from WebserviceX.NET. WebserviceX.NET documentation is available here.

The specific query that we'll use to illustrate using a SOAP webservice with QuerySurge has the following parameters:

WSDL: http://www.webservicex.net/uszip.asmx?WSDL
serviceName: USZip
portTypeName: USZipSoap
operationName: GetInfoByState
bindingName: USZipSoap
argXPath: xpath:/GetInfoByState/USState
value = ME

This query returns US postal codes and related information for a specified US State (Maine in our example).

The style of response returned by this query is a standard SOAP message, with the following body:

<GetInfoByStateResponse xmlns="http://www.webserviceX.NET">
  <GetInfoByStateResult>
    <NewDataSet xmlns="">
      <Table>
        <CITY>Whiting</CITY>
        <STATE>ME</STATE>
        <ZIP>04691</ZIP>
        <AREA_CODE>207</AREA_CODE>
        <TIME_ZONE>E</TIME_ZONE>
      </Table>
      <Table>
        <CITY>Wilton</CITY>
        <STATE>ME</STATE>
        <ZIP>04294</ZIP>
        <AREA_CODE>207</AREA_CODE>
        <TIME_ZONE>E</TIME_ZONE>
      </Table>
      ...
    </NewDataSet>
  </GetInfoByStateResult>
</GetInfoByStateResponse>

Create a Custom Function to call a Webservice and Process the Response

Note: The JDK level used to compile the jar containing your custom function file must match the JDK level used by QuerySurge. You can find your QuerySurge Java Version in Administration > Server Properties > Java Version.

Create a custom Java method to return the SOAP message converted to a CSV file. As illustrated here, custom methods must always be public and static. Our method takes 10 arguments:

  • WSDL - the URL of the WSDL for the service
  • serviceName - the SOAP service name
  • portTypeName - the SOAP port type
  • operationName - the SOAP operation
  • bindingName - the SOAP binding name
  • argXPath - an XPath specifier for each tag in the request message body that specifies an argument to the SOAP method
  • value - the value of the argument to the SOAP method
  • csvFilePath - the file path (including the file name) that the resulting csv file is written to.
  • responseXPath - an XPath statement specifying the parent node of each data grouping in the SOAP response message. In the example above, this is the <Table> node.
  • verbose - a 'true' value dumps the response json to System.out; this is for debugging outside of QuerySurge

  public static void soapQuery(String wsdlUrl, String serviceName, 
      String portTypeName, String operationName, String bindingName, 
      String argXPath, String value, String csvFilePath, 
      String responseXPath, boolean verbose) throws 
      UnsupportedOperationException, SOAPException, IOException {
      // build form params - values for the request message
      HashMap<String, String> formParams = new HashMap<String, String>();
      formParams.put(argXPath, value);
      // create a SOAP handler and get the response
      SoapHandler soap = new SoapHandler(csvFilePath, responseXPath);
      SOAPMessage soapResponse = soap.getSoapResponse(wsdlUrl, serviceName, 
      portTypeName, operationName, bindingName, formParams);
      SOAPBody soapBody = soapResponse.getSOAPBody();
      Document soapDoc = soapBody.extractContentAsDocument();
      // verbose = print the SOAP body
      if (verbose) {
        XmlToolkit tool = new XmlToolkit();
        System.out.println( 
          tool.prettyPrint(
            tool.serializeXmlToString(soapDoc)));
      }
      // convert the SOAP body to a csv file
      soap.soapToCsv(soapDoc);
  }

It is important to note that this code creates a SoapHandler object, and calls SoapHandler's getRestResponse() method. The SoapHandler is where the SOAP client specifics for this example are found. This is quite a simple implementation, designed only to illustrate the basics of calling into a SOAP webservice.

Note: For the full set of classes for this example, see the download in the Resources section at the end of this article.

Once your custom function is exported as a jar file, you're ready to set it up on your QuerySurge Agent.

Custom Function Set-Up on the Agent

There are two main steps required to set up your custom function on your Agent. The Agent service must be shut down while performing these steps, and restarted after the steps are completed.

  1. Copy the jar file to your QuerySurge Agent; it should be copied to the <QuerySurge Install Dir>\QuerySurge\agent\jdbc directory. Also, be sure to include any external dependencies on the classpath.

  2. The next step is to edit the agentconfiguration.xml so that your Agent can "see" the custom function. The file is found in the <QuerySurge Install Dir>\QuerySurge\agent\config directory. Make a copy of the file before you modify it, and modify with care.

    Find the <connectionProps> tag, and add the following <driverProp> child tag:

    <connectionProps>
    ...

    <driverProp driver="jstels.jdbc.csv.CsvDriver2" prop="function:soapQuery" 
    type="void" value="com.rttsweb.querysurge.soap.SoapClient.soapQuery" />

    ...
    </connectionProps>

After you've made these changes, re-start your Agent. You Agent should be ready to query the SOAP webservice, and compare the results to another data store in your environment.

A Query Against the SOAP Service

Create a QueryPair, and following the connection instructions for Flat Files, create a Flat File connection for the file that your SOAP data will be written to. Your Source (or Target) SOAP "query" consists of two statements:

  • a call into the custom function
  • a SQL query to query the resulting Flat File

The "query" is shown below. The call into the SOAPQUERY function, takes ten arguments. Arguments 2 - 5 are the standard SOAP arguments (the SOAP service, port type, operation, and binding). Argument 6 is an XPath statement for processing the SOAP request method body, so that arguments to the method are handled properly. In the sample shown here, the form of the request message is:

<soap:Envelope xmlns:xsi=...>
  <soap:Body>
    <GetInfoByState xmlns="http://www.webserviceX.NET">
      <USState>string</USState>
    </GetInfoByState>
  </soap:Body>
</soap:Envelope>

The argument to the method is the <USState>, specified by the two-letter abbreviation for the State (we use Maine, abbreviated 'ME', in the example below). Therefore, the XPath search used is: '/GetInfoByState/USState', which specifies the tag containing the argument in the message body (argument 7 to  SOAPQUERY. Argument 8 is the output file path for the csv containing the SOAP message data, and argument 9 is an XPath statement specifying the tag containing the response "rows" - this is the <Table> tag in the example shown here. The child tags of <Table> are the column names; we can use these names in our SQL, not because they children of <Table>, but because we have set up the Flat File Connection to use these headers. (The file that is written by SOAPQUERY does not have headers.) Finally, argument 10 should be false when the method is called in QuerySurge.

CALL SOAPQUERY(
'http://www.webservicex.net/uszip.asmx?WSDL',
'USZip',
'USZipSoap',
'GetInfoByState',
'USZipSoap',
'xpath:/GetInfoByState/USState',
'ME',
'C:\some\directory\soap.csv',
'descendant::Table',
false);

SELECT s.city
,s.state
,s.zip
,s.areacode
,s.timezone
FROM soap s;

The SQL here has to follow the regular Flat File syntax. If you're familiar with the syntax, if follows the H2 grammar. Finally, note that each of the two statements in the "query" needs to be semi-colon terminated.

Building This Worked Example

You can build this worked example as described here with the files in the Resources section below. In order to compile the code, you'll need the following maven dependencies:

<dependency>
     <groupId>com.predic8</groupId>
     <artifactId>soa-model-core</artifactId>
     <version>1.6.0</version>
</dependency>

Resources

Related articles