Exporting Data with Web Services

Slate supports the ability to create custom web services using the Query Builder. Any query can be routinely called as a web service or scheduled to push data into another system.

Calling a Slate Query as a Web Service

  Required Permissions

Administrator (All Access) Role

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

  1. Select Queries / Reports on the 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="variable_name" type="data_type" label="My Variable:"/>
        • The parameter id can be used in a formula by typing @variable_name. (If you are using a Local or Slate Template Library query, you must use a Custom SQL snippet.)

     Parameter Settings

    • id - the variable name used in the query string, and 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 the user in the query tool user interface
  5. Authentication is required for web services. Select Edit Permissions in the right-side pane and Add Grantee to set it up. You can use a User Token or a Username and Password. However, if your query exposes sensitive data, you must use a Username and Password to keep your data secure.
    User Token:
    mceclip2.png
    • Give it a descriptive name. This will appear in the Edit Permissions window alongside any other custom query permissions.
    • When calling this web service, provide the Token using the "h" query string parameter.
    • If you want to limit the IP addresses that can call this web service, enter individual IP addresses or CIDR subnets.
    • Make sure the Web Service check box is selected before saving.

    Username:
    mceclip3.png

    • Give it a descriptive name. This will appear in the Edit Permissions window alongside any other custom query permissions.
    • Enter a Username and a Password. These must be provided when the web service is called.
    • When calling this web service, provide the Token using the "h" query string parameter.
    • If you want to limit the IP addresses that can call this web service, enter individual IP addresses or CIDR subnets.
    • Make sure the Web Service check box is selected before saving.
  6. 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 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.

  7. The query may use the parameters by using the @ symbol with the parameter id. This can be done using a direct filter:
    mceclip0.png
    However, more commonly, the parameters will be used in a formula subquery:
    mceclip1.png

    If you are using a Local or Slate Template Library query, you will need to use a Custom SQL snippet. We strongly recommend the use of Configurable Joins for web services queries.

  8. 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 adding 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 choose 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 can opt to receive notifications for success, failure, and late delivery. You can also choose a format, either JSON or XML. You will then select one of the available delivery windows. The query will run at the time of the delivery window 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?
4 out of 11 found this helpful