When a SELECT query is issued, the add-in executes the GET method of the schema. The GET method invokes the add-in's built-in operations to process REST. Invoking the GET method gives you control over the request for data.

The following procedures show several ways to exert control over a request for data, including:

• using SELECT WHERE to search the remote server-side data;
• using LIMIT to limit the results returned by the server; and
• implementing paging.

Query Processing

By default, the add-in processes the query client-side. All you need to set to execute any SELECT statement locally are the XPath and URI connection properties.

(It is also possible for the add-in to offload supported queries to the server while processing the rest of the query client side. Pushing these filters to the server helps optimize "later" stages of the query, such as LIMIT, JOIN, GROUP BY, and ORDER BY.)

Execute Selects to REST

The following steps describe how to build a script that enables you to execute SQL-92 queries, processed client side. Before you invoke a data processing operation, you must provide the URI and, optionally, the XPath. Use the api:set keyword to declare these attributes.

1. Set the URI attribute to a local file or an HTTP-accessible address.
   

   <api:set  attr="uri"                      value="NorthwindOData.xml" /> 

2. If needed, set the XPath attribute to the XPath of the data that constitutes an individual row. By default, the add-in scans the document to detect the rows (see Parsing Hierarchical Data).
   

   <api:set  attr="XPath" value="/feed/entry/" /> 

3. Invoke the operation in the GET method. Inside the script block, use the api:push keyword to invoke the operation. Specify the operation with the op parameter. This keyword pushes the results of processing to the schema's output.
   

   <api:script method="GET">
     <api:set attr="method" value="GET"/>
     <api:set attr="uri" value="[uri]?$format=atom"/>
     <api:call op="xmlproviderGet">
       <api:push />
     </api:call>
   </api:script>

Process SELECT WHERE on the Server

This section shows how to translate a SELECT WHERE statement into a search request to REST APIs. The procedure uses the following statement:

SELECT * 
FROM <table> 
WHERE modifedAt < '2017-10-10' AND modifedAt > '2017-09-01'

If the server supports this filter via query parameters, you can use the api:info collumn definition's other:filter property to specify the desired mapping. For the above query, we can use this property to map the modifiedAt < '<date>' filter to the query parameter that returns results that were modifed before a given date, and use the modifedAt > '<date>' filter to the query parameter that filters results that were modifed after that date.

The other:filter is a semicolon separated list with the format: <parameter name>:<operator list>, where:

• <parameter name> is the name of the query parameter, and
• <operator list> is a comma-separated list of operators used for the mapping.

Valid operators are <, <=, =, >, >=, and LIKE.

To perform this mapping, we use the following markup for the modifedAt column definition:

    <attr name="modifiedAt"           xs:type="datetime" readonly="false"   other:xPath="content/properties/modifiedAt" other:filter="modifiedBefore:<;modifiedSince:>" />
    

This query results in the following request:

[url]?modifedBefore=2017-10-10&modifedSince=2017-09-01

If your API filter is not passed in a query parameter, you must pass it in the script. For example, consider an API that filters by name by querying the /persons/{name}/data endpoint:

SELECT *
FROM Persons 
WHERE (Name = 'Fran Wilson') 

In the GET method of the schema, use the attributes of the _input item, one of the Items in API Script, to access the search criteria and build the HTTP data retrieval request. The corresponding script below builds the request.

The api:check element is useful for checking the existence of an attribute before attempting to access its value. A variety of Value Formatters are available to do transformations, like URL-encoding a string.

<api:script method="GET">
  <api:check attr="_input.Name">
    <api:set attr="uri" value="[uri]/[_input.name|urlencode]/data"/>
  </api:check>
</api:script>

Search with Pseudo Columns

If you want to build search criteria using inputs other than the columns returned in the results, you can specify a pseudo column in the WHERE clause.

For example, the Weather Underground API supports returning a forecast for a specified location. The Location itself is not part of the forecast data; it is specified in the request URI, as in the request below:

http://api.wunderground.com/api/{MyAPIKey}/hourly/q/{MyLocation}.xml 

For example, the following SQL query obtains a forcast for the zip code 27516:

SELECT * FROM Hourly WHERE Location="27516"

To implement the above query, add a Location pseudo columns as follows:

1. Add a Location input parameter to the column definitions in the api:info block. (Location is required; if it is not specified, add-in returns an error.)
   

   <api:info>
     ...
     <input  name="Location"                 required="true"/>
   </api:info>

2. To build the URI, reference the _input item's Location attribute:
   

   <api:set attr='uri' value="http://api.wunderground.com/api/[_connection.APIKey]/hourly/q/[_input.Location].xml"/>

3. To make the request and process the response, invoke the operation:
   

   <api:script method="GET" >
     <api:push op="xmlproviderGet"/>
   </api:script> 

Implement Paging

To support automatic paging, add the 'Rows@Next' input to the list of columns in the api:info block.

<input name="rows@next" desc="Identifier for the next page of results. Do not set this value manually." />

<api:set attr="EnablePaging" value="TRUE" />

The driver supports four types of paging implementations automatically:

• Where the URL of the next page is returned in the response;
• When the query includes a parameter to specify your current page offset;
• When you send a page token in the query parameter of the next request; or
• When the query includes a parameter to specify the current page number.

We describe these implementations in the next few subsections.

Paging Via Next Page URL

When the service returns the URL for the next page in the response body or header, set the 'pageurlpath' attribute to the location of this data. If a value is present at this location, it is used to set the URL of the next request.

If the next page URL is passed in the response body, set 'pageurlpath' to the XPath of the element.

<api:set  attr="pageurlpath"                             value="/data/nextPage" /> 

If the next page URL is passed in the response header with a 'Link' header, you can prefix 'pageurlpath' with header: to denote its location.

<api:set  attr="pageurlpath"                             value="header:Link" /> 

Paging Via Record Offset

If the service provides a record offset query parameter to control paging, you can set the name of the offset query parameter, the name of the page size query parameter, and the page size to be passed. Note that if there is no parameter to control page size, the page size parameter does not need to be set. However, in that case, you must set pagesize to the default page size.

<api:set  attr="pageoffsetparam"                      value="offset" /> 
<api:set  attr="pagesizeparam"                        value="limit" /> 
<api:set  attr="pagesize"                             value="100" /> 

<api:set  attr="pageoffsetparam"  value="offset" />

URI?offset=0<OtherParams>

<api:set  attr="pageoffsetparam"   value="offset;1" />

URI?offset=1<OtherParams>

Paging Via Page Number

Similar to record offset, if the service provides a query parameter that sets the page number, you can set the name of the page number query parameter, the name of the page size query parameter, and the page size to be passed. Note that if there is no parameter to control page size, the page size parameter does not need to be set. However, in that case, you must set pagesize to the default page size.

<api:set  attr="pagenumberparam"                      value="page" /> 
<api:set  attr="pagesizeparam"                        value="pagesize" /> 
<api:set  attr="pagesize"                             value="100" /> 

Paging Via Token

In cases where a token is returned in the response body, the token should be passed to a paging parameter in the subsequent request via the 'pagetokenparam' and 'pagetokenpath' attributes. 'pagetokenpath' should be set to the XPath of the element.

Some services also send a variable that denotes whether or not there are more pages, if that is the case, you can also set the 'hasmorepath' attribute to its XPath.

If the page token is passed in a query parameter, set 'pagetokenparam' to the name of the parameter.

<api:set  attr="pagetokenpath"                        value="/data/token" /> 
<api:set  attr="hasmorepath"                          value="/data/has_more" /> 
<api:set  attr="pagetokenparam"                       value="nextpagetoken" /> 

In cases where the token needs to be passed in the request body, set 'pagetokenparam' to its XPath.

<api:set  attr="pagetokenpath"                        value="/request/nextpagetoken" /> 

Other Paging Types

If your API does not follow any of the paging pattersn just discussed, you need a custom paging implementation. Set the information needed from the first page in the 'Rows@Next' attribute. When the 'Rows@Next' value is set in the output, the add-in automatically calls the method again with the 'Rows@Next' value in the input, after all results for this page are returned.

You can use the value of this input to modify the request on the next pass, to get the next page of data. Set the Rows@Next input to any information needed to make the request for the next page of data.

For example, if your API returns the next page's URL in the response, you can obtain that value by providing the XPath to the URL:

<api:set  attr="elementmappath#"  value="/next_page" />
<api:set  attr="elementmapname#"  value="rows@next" /> 

<api:check attr="_input.rows@next">
  <api:set  attr="uri"  value="[_input.rows@next]" />
  <api:else>
    <api:set  attr="uri"  value="<first page's URL>" />
  </api:else>
<api:check> 

Process Other SELECT Statements Server Side

You can build any HTTP request in the GET method. Use the _query item to access other components of the SELECT query. The following table shows the attributes of the GET method _query item that describe the query that was issued to the add-in:

SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50;

City LIKE '%New%' AND COUNTRY = 'US'

Process LIMIT on the Server

If your API supports it, you can implement the LIMIT clause to restrict the number of results that need to be retrieved from the server.

When you build the API request, reference the value of the _query item's limit attribute. In the OData API, you can specify a limit using the $top query string parameter, shown below.

http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$top=10

Below is the corresponding script:

<api:check attr="_query.limit">
  <api:set  attr="uri"      value="http://services.odata.org/V3/Northwind/Northwind.svc/Customers?$top=[_query.limit]" />
</api:check> 

Keyword Reference

For more information on the keywords used in this section, see the API Script Reference:

• api:script
• api:call
• api:push
• api:info
• api:check
• api:set