SELECT Execution

When a SELECT query is issued, the driver executes the GET method of the schema. The GET method invokes the driver'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 driver 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 driver 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.

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

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

If needed, set the XPath attribute to the XPath of the data that constitutes an individual row. By default, the driver scans the document to detect the rows (see Parsing Hierarchical Data).
```
<api:set  attr="XPath" value="/feed/entry/" /> 
```
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>
```

You can extend the resulting script to add support for processing requests server side.

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:

Add a Location input parameter to the column definitions in the api:info block. (Location is required; if it is not specified, driver returns an error.)
```
<api:info>
  ...
  <input  name="Location"                 required="true"/>
</api:info>
```

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"/>

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." />

Note that making this an input parameter instead of an attr parameter prevents it from showing up in column listings. It you want to turn off the driver's internal paging mechanism, set the 'EnablePaging' attribute to FALSE.

<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" />

You can also specify a starting page for pageoffsetparam and pagenumberparam. For example:

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

results in

URI?offset=0<OtherParams>

Setting the offset value to 1:

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

results in

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" />

If has_more is true, it passes the token at /data/token to the next query: ?nextpagetoken=<token>

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 driver 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" />

Provided the value is set, you can then modify the URL where the request is made. Use the api:check element to first check if the Rows@Next input has a value. The Rows@Next input can be accessed as an attribute of the _input item:

<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 driver:


query	The SQL query. For example: SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50;
selectcolumns	A comma-separated list containing the columns specified in the SELECT statement. For example, the Id and Name columns.
table	The table name specified in the SELECT statement. For example, Accounts.
criteria	The WHERE clause of the statement. City LIKE '%New%' AND COUNTRY = 'US'
orderby	The columns specified in the ORDER BY clause. For example, Name.
groupby	The GROUP BY clause in the SELECT statement. For example, CreatedDate.
limit	The limit specified in the LIMIT or TOP clauses of the SELECT statement. For example, 50.
offset	The offset specified in the LIMIT or TOP clauses of the SELECT statement. For example, 10.
isjoin	Whether the query is a join.
jointable	The table to be joined.
isschemaonly	Whether the query retrieves only schema information.

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:

ODBC Driver for REST