With a URI and Column Definitions specified, the add-in processes SELECT statements client-side, in memory, through SupportEnhancedSQL. 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>

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 = '90210') 

Follow the steps below to implement this query. The following procedure defines a pseudo column, an input that can only be used in the WHERE clause, and maps the pseudo column to an API request.

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

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

2. Reference the Location pseudo-column 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"/> 

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

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

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

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

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

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

Access Components of SELECT Statements

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

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'