SELECT Execution

With a URI and Column Definitions specified, the add-in processes SELECT statements client-side, in memory. The following sections show how to use the add-in's built-in operations to customize how the add-in requests and returns data from the server.

Execute Selects to CSV

When a SELECT query is issued, the add-in executes the GET method of the schema. In this method you can process CSV. To see this schema in a complete example, refer to Modeling CSV Data.

Retrieving Data

The following line maps the schema to a URI:

<api:set  attr="uri"                      value="ftp://somewebsite/NorthwindOData.csv" />

Invoke the operation to retrieve the data in the GET method. Specify the operation with the api:push keyword. The following lines push the results of processing to the schema's output.

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

You can then execute WHERE clause searches, JOIN queries, and SQL aggregate functions.

Customizing Data Retrieval

The add-in's operations give you high level control over the request sent to the server. You can set a variety of inputs to control authentication and other aspects of the request. See Operations for the available inputs.

You can also build the request by injecting inputs from the SQL statement. As an example, the following sections show how to use the WHERE clause to change the request dynamically. Note that other filters specified in the WHERE clause are processed client-side by the add-in; you can search on any column returned in the response.

Consider a weather forecast API that returns a location's forecast in CSV. You specify the location you want in the URI. Using the add-in, you could get the forecast with a query like the following:

SELECT *
FROM Forecasts 
WHERE (Location = '27712')

The procedure defines a pseudocolumn (an input that can only be used in the WHERE clause) and maps it to an API request. To implement this query, perform the following steps.

Add a Location input parameter to the column definitions in the api:info block.

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

Reference the Location pseudocolumn value. In the following example, the Location is the name of the CSV resource, set in the URI.
```
<api:set attr='uri' value="http://api.wunderground.com/api/MyAPIKey/hourly/q/[_input.Location].csv"/> 
```

Invoke the operation to make the request and process the response:

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

Customizing Paging

To override the add-in's internal paging mechanism, 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." />

Note that making this an input parameter instead of an attr parameter prevents it from showing up in column listings. It also requires you to turn off the driver's internal paging mechanism, which means setting the EnablePaging attribute to True.

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

When the Rows@Next value is set in the output, after the add-in finishes returning results for this page, it automatically calls the method again with the Rows@Next value in the input. You can use the value of this input to modify the request on the next pass to get the next page of data. To make the request for the next page of data, set the Rows@Next input to any information needed from that page.

For example, your API may return the next page's URL in the response. You can obtain this value by providing the XPath to the URL:

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

You can then modify the URL where the request is made, provided the value is set. The api:check element is useful for checking the existence of a required input before attempting to access its 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>

Accessing Components of SELECT Statements

You can use the _query item to access any component of the SELECT statement in the schema.