SELECT Execution

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

Execute Selects to CSV

When a SELECT query is issued, the connector 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 connector'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 connector; 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 connector, 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.

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

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 connector'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 will prevent it from showing up in column listings. You will also need to set the EnablePaging attribute to TRUE to turn off the driver's internal paging mechanism.

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

When the Rows@Next value is set in the output, the connector will automatically call the method again with the Rows@Next value in the input after it is finished returning results for this page. 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, 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>

Access Components of SELECT Statements

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