Exporting Data with Web Services

The core of Slate data integration leverages the Query Builder and Upload Dataset tools to exchange information between systems.

For data integration via web services, any query can be routinely called as a web service or scheduled to push data into another system, while Upload Dataset can accept a push or pull batched data from another service.

The Query Builder tool is the primary method for exporting data. Query results may be delivered in several ways, including web services.

Calling a Slate Query as a Web Service

The following are the steps to generate an endpoint that can be called via web services. 

  1. Select Queries / Reports on the top navigation bar.
  2. Select New Query and configure your query appropriately.
  3. Select Edit Web Service in the right-side pane.
  4. Enter the following Web Service configurations:
    • Service Type - Select the appropriate Service Type.
      • XML
      • JSON
      • JSONP (JSON with callback)
      • Raw
    • Parameters - If you wish to pass in values through the query string, you do so by adding to the Parameters field using the following format:
      • <param id="dtstart" type="date" label="Date (##/##/####)"/>
        • use in query sql as @dtstart
      • <param id="user" type="varchar" label="User ID:"/>
        • use in query sql as @user

     Parameter Settings

    • id - variable name used in query string, and in sql query with @

      The following parameter IDs are reserved and should never be used: callback, base, run, query, user, identity, id, h, and output. You will notice the use of id, h, and output in the service endpoints. These parameters should be static and represent the following:
       
      • id - the query ID
      • h - a salted hash of the query ID and an additional authentication check
      • output - the service type.
    • type - parameter data type (Acceptable Parameter Types)
      • date
      • datetime
      • varchar
      • int
      • real
      • uniqueidentifier
      • bit
    • label - value display to user in query tool user interface
    • Password Protection If you wish to expose sensitive data through a web service, you will want to create a username/password pair for the service in the Edit Web Service window. This will secure the web service behind basic authentication. (The web service itself will be called over HTTPS, so the username and password remain in cipher text even when sent with basic authentication.)

      We recommend setting a username and password for basic authentication.
  5. To pass in query string parameters, such as dtstart and dtend, you would append a string such as "&dtstart=1/1/2000&dtend=1/1/2099" to the service.

    If you would prefer to export the data via JSON with a callback (also known as JSONP), you can pass a callback function name to the 'callback' query string parameter.

  6. The query may make use of the parameters by including a Custom SQL "Snippet", for example:
          (a.[submitted] between @dtstart and @dtend)

    As constructed, this web service will be publicly accessible, so you should only pull publicly-available information.

  7. Service endpoint - To locate the Service endpoint, navigate to the Edit Query page and identify the Web Services link. Click the link to view the appropriate service endpoint.

Call Rate Limit- Each database is rate limited to 300 seconds of web service request processing within any 5-minute interval.

 Default Encoding

If you need to change the default encoding when calling a Slate query as a web service to something other than UTF-8, this can be done by add the encoding parameter to the call URL in the following format:

 &encoding=iso-8859-1
Pushing Data to Remote Web Services
    1. Select Queries / Reports on the top navigation bar.
    2. Select the Query and select Edit Query
    3. Select Schedule Export.
    4. Select Web Service (Remote) in the Server setting of the popup window. 
    5. Enter the endpoint in the Endpoint setting.

You may opt to receive notifications for success, failure, and late delivery. You may also choose a format, either JSON or XML.  You will then select one of the available delivery windows.  At the time of the delivery window the query will run, and push the data to the endpoint.

Custom headers may also be defined for the web service. In the Headers configuration setting, the placeholder text displays the format in which a header can be added, i.e.:

 <t name="HEADER_NAME">HEADER_VALUE</t>

Multiple headers can be added by repeating the format for each header name/value pair.

Was this article helpful?
3 out of 7 found this helpful