The CData Sync App is designed for streaming REST only.

This streamed file content does not include all of the metadata associated with remotely stored REST files, such as file and folder name.

If access to both the file metadata and the actual file content is needed, then the CData Sync App must be used in tandem with the associated file system driver(s) for the service the REST files are remotely stored in.

The following file system drivers are available:

• AmazonS3
• Box
• Dropbox
• FTP
• GoogleCloudStorage
• IBLCloudObjectStorage
• OneDrive
• SFTP

See the relevant CData file system driver's documentation for a configuration guide for connecting to stored REST file metadata.

Adding a Connection to REST

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the REST icon is not available, click the Add More icon to download and install the REST connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Modeling XML/JSON/CSV Data

The DataModel property controls how your data is represented into tables.

• Document (default): Model a top-level, document view of your REST data. The Sync App returns nested object arrays as aggregated XML/JSON/CSV objects.
• FlattenedDocuments: Implicitly join nested array objects and parent objects into a single table.
• Relational: View nested object arrays as individual, related tables containing a primary key and a foreign key that links to the parent document.

Set Format to XML, JSON, or CSV in accordance with the data structuring standard used by the REST source you want to connect to. and set DataModel to more closely match the data representation to the structure of your data.

Authenticating a Connection

See AuthScheme for information on the different methods of authentication.

Next Steps

• See Modeling REST Data for information on customizing schema discovery and executing SQL to REST.
• See Fine-Tuning Data Access for more advanced connection settings: fine-tune the default data modeling settings, connect through a firewall, or troubleshoot connections.

Kerberos

To authenticate to REST with Kerberos, set AuthScheme to NEGOTIATE.

Authenticating to REST via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

The Sync App provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Kerberos Credential Cache Files:

1. Ensure that the KRB5CCNAME variable is present in your environment.
2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.
3. To obtain a ticket:
   1. Open the MIT Kerberos Ticket Manager application.
   2. Click Get Ticket.
   3. Enter your principal name and password.
   4. Click OK.

   If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The Sync App uses the cache file to obtain the Kerberos ticket to connect to REST.

Note: If you would prefer not to edit KRB5CCNAME, you can use the KerberosTicketCache property to set the file path manually. After this is set, the Sync App uses the specified cache file to obtain the Kerberos ticket to connect to REST.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with REST.

Enabling Cross-Realm Authentication

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

The CData Sync App enables the granular control useful in more complex deployments or network topologies; you can use the following connection properties to fine-tune data access, connect through a firewall, or troubleshoot connections.

Fine-Tuning Data Access

You can use the following properties to gain greater control over how the Sync App parses the XML data into rows. You can also customize the schemas detected based on the connection string.

• RowScanDepth: This property determines the number of rows that will be scanned to detect column data types when generating table metadata.
• XPath: Explicitly specify the paths to nested object arrays, instead of detecting them during the row scan.

• GenerateSchemaFiles: This property enables you to persist table metadata in static schema files that are easy to customize, to persist your changes to column data types, for example. Set this property to "OnStart" to generate schema files for all tables in your database at connection. Or, set this property to "OnUse" to generate schemas as you execute SELECT queries to tables. The resulting schemas are based on the connection properties you use to configure Automatic Schema Discovery.

  To use the resulting schema files, set the Location property to the folder containing the schemas. See Customizing Schemas for more information.

In this section we will show how to control the various schemes that the Sync App offers to bridge the gap with relational SQL and REST services. The CData Sync App provides a managed way for you to use the two prevailing techniques for dealing with nested data:

• Parsing the data structure and building a relational model based on the existing hierarchy.
• Drilling down into the nested elements using horizontal and vertical flattening.

Parsing Hierarchical Data

By default, the Sync App automatically detects the rows in a document, so that you do not need to know the structure of the underlying data to query it with SQL. Set the DataModel property to choose a basic configuration of how the Sync App models the rows into tables.

Flattening Objects and Arrays into Rows

To flatten data, you only need to be familiar with two data structures -- objects and arrays. In JSON, these are literal structures. In XML, the analogous structures are below:

• Object: Any parent element that does not repeat at the same height.
• Array: Any element that repeats at the same height.

In the following example from the people collection, maintenance is an object array, since each maintenance node has child elements.

<maintenance>
  <date>07-17-2017</date>
  <desc>oil change</desc>
</maintenance>
<maintenance>
  <date>01-03-2018</date>
  <desc>new tires</desc>
</maintenance> 

Configuring Automatic Schema Discovery

The Sync App discovers columns and data types by scanning the RowScanDepth count of objects in arrays. Set the FlattenObjects and FlattenArrays properties to configure how nested data is flattened into columns; see Automatic Schema Discovery for examples.

Executing SQL to REST

Any relation you can access through flattening you can also access with an ad-hoc SQL query. The Sync App enables you to query nested data with the following capabilities:

• Vertical Flattening: Access nested object arrays as separate tables.
• Free-Form Queries: Query any nested structure without flattening the data.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

Customizing Schemas

Customizing Schemas enables you to project your chosen relational structure on top of a document. This allows you to choose the names of columns, their data types, and the locations of their values in the document.

System Catalog

The System Tables reflect the schemas you configured, custom schemas or dynamically discovered. The Stored Procedures surface additional functionality in the Sync App's data processing operations that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE. You can find the reported stored procedures defined in .rsb files in the folder specified by Location -- if Location is not specified, the db subfolder of the installation directory.

Below is the raw data used throughout this chapter. The data includes entries for people, the cars they own, and various maintenance services performed on those cars:

 
<?xml version="1.0" encoding="UTF-8" ?>
<root>
  <rootAttr1>rootValue1</rootAttr1>
  <people>
    <personal>
      <age>20</age>
      <gender>M</gender>
      <name>
        <first>John</first>
        <last>Doe</last>
      </name>
    </personal>
    <jobs>support</jobs>
    <jobs>coding</jobs>
    <vehicles>
      <type>car</type>
      <model>Honda Civic</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>sunroof</features>
      <features>rims</features>
      <maintenance>
        <date>07-17-2017</date>
        <desc>oil change</desc>
      </maintenance>
      <maintenance>
        <date>01-03-2018</date>
        <desc>new tires</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>truck</type>
      <model>Dodge Ram</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>lift kit</features>
      <features>tow package</features>
      <maintenance>
        <date>08-27-2017</date>
        <desc>new tires</desc>
      </maintenance>
      <maintenance>
        <date>01-08-2018</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>work</type>
      <zip>12345</zip>
    </addresses>
    <addresses>
      <type>home</type>
      <zip>12357</zip>
    </addresses>
    <source>internet</source>
  </people>
  <people>
    <personal>
      <age>24</age>
      <gender>F</gender>
      <name>
        <first>Jane</first>
        <last>Roberts</last>
      </name>
    </personal>
    <jobs>sales</jobs>
    <jobs>marketing</jobs>
    <source>phone</source>
    <vehicles>
      <type>car</type>
      <model>Toyota Camry</model>
      <insurance>
        <company>Car Insurance</company>
        <policy_num>98765</policy_num>
      </insurance>
      <features>upgraded stereo</features>
      <maintenance>
        <date>05-11-2017</date>
        <desc>tires rotated</desc>
      </maintenance>
      <maintenance>
        <date>11-03-2017</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>car</type>
      <model>Honda Accord</model>
      <insurance>
        <company>Car Insurance</company>
        <policy_num>98765</policy_num>
      </insurance>
      <features>custom paint</features>
      <features>custom wheels</features>
      <maintenance>
        <date>10-07-2017</date>
        <desc>new air filter</desc>
      </maintenance>
      <maintenance>
        <date>01-13-2018</date>
        <desc>new brakes</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>home</type>
      <zip>98765</zip>
    </addresses>
    <addresses>
      <type>work</type>
      <zip>98753</zip>
    </addresses>
  </people>
  <rootAttr2>rootValue2</rootAttr2>
  <rootAttr3>rootValue3</rootAttr3>
  <rootAttr3>rootValue4</rootAttr3>
</root>

The Sync App offers three basic configurations to model nested data in XML and JSON as tables. See the following sections for examples of parsing objects and arrays.

• Flattened Documents Model: Implicitly join nested object arrays into a single table.
• Relational Model: Model object arrays as individual tables containing a primary key and a foreign key that links to the parent document.
• Top-Level Document Model: Model a top-level view of a document. Nested object arrays are returned as aggregates.

For users who simply need access to the entirety of their data, flattening the data into a single table is the best option. The Sync App will use streaming and only parses the data once per query in this mode.

Joining Object Arrays into a Single Table

With DataModel set to "FlattenedDocuments", the Sync App returns a separate table for each object array, but implicitly JOINed to the parent table. Any nested sibling XPath values (child paths at the same height) will be treated as a SQL CROSS JOIN.

Example

Below is a sample query and the results, based on the sample document in Raw Data and parsing based on the XPaths /root/people, /root/people/vehicles, and /root/people/vehicles/maintenance. This implicitly JOINs the people element with the vehicles element and implicitly JOINs the vehicles element with the maintenance element.

Connection String

Use the following connection string to query the Raw Data in this example.

URI=C:\people.txt;Format=XML;DataModel=FlattenedDocuments;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'

Query

The following query drills into the nested elements in each people element. Since the XPath property included the vehicles node, you can query an element of a vehicle explicitly.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [type],
  [model],
  [insurance.company] AS ins_company,
  [insurance.policy_num] AS ins_policy_num,
  [date] AS maint_date,
  [desc] AS maint_desc
FROM
  [people]

Results

With horizontal and vertical flattening based on the described paths, each vehicle element is implicitly JOINed to its parent people element and each maintenance element is implicitly JOINed to its parent vehicle element.

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

Using a top-level document view of the data provides ready access to top-level elements. The Sync App returns nested elements in aggregate, as single columns.

One aspect to consider is performance. You forego the time and resources to process and parse nested elements -- the Sync App parses the returned data once, using streaming to read the data. Another consideration is your need to access any data stored in nested parent elements, and the ability of your tool or application to process JSON or XML.

Modeling a Top-Level Document View

With DataModel set to "Document" (the default), the Sync App scans only a single element, the top-level element by default. The top-level elements are available as columns due to the default object flattening. Nested elements are returned as aggregates.

You can set XPath to specify an element other than the top-level one.

Example

Below is a sample query and the results, based on the sample document in Raw Data. The query results in a single "people" table based on the XPath "/root/people".

Connection String

Set the DataModel connection property to "Document" to perform the following query and see the example result set. The Sync App will scan only the XPath value below:

URI=C:\people.txt;Format=XML;DataModel=Document;XPath='/root/people';

Query

The following query pulls the top-level elements and the subelements of the vehicles element into the results.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [vehicles]
FROM
  [people]
  

Results

With a document view of the data, the personal element is flattened into 4 columns and the source and vehicles elements are returned as individual columns, resulting in a table with 6 columns.

  <vehicles><type>car</type><model>Honda Civic</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>sunroof</features><features>rims</features><maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance></vehicles><vehicles><type>truck</type><model>Dodge Ram</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>lift kit</features><features>tow package</features><maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance></vehicles>

  <vehicles><type>car</type><model>Toyota Camry</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>upgraded stereo</features><maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance></vehicles><vehicles><type>car</type><model>Honda Accord</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>custom paint</features><features>custom wheels</features><maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance></vehicles>

See Also

• Automatic Schema Discovery: Configure column discovery with horizontal flattening.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

The CData Sync App can be configured to create a relational model of the data, treating each XPath as an individual table containing a primary key and a foreign key that links to the parent document. This is particularly useful if you need to work with your data in existing BI, reporting, and ETL tools that expect a relational data model.

Joining Nested Arrays as Tables

With DataModel set to "Relational", any JOINs are controlled by the query. Any time you perform a JOIN query, the file or source will be queried once for each table included in the query.

Example

Below is a sample query against the sample document in Raw Data, using a relational model based on the XPaths "/root/people", "/root/people/vehicles", and "/root/people/vehicles/maintenance".

Connecting String

Set the DataModel connection property to "Relational" and set the XPath connection property to "/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;" to perform the following query and see the example result set.

URI=C:\people.txt;Format=XML;DataModel=Relational;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'

Query

The following query explicitly JOINs the people, vehicles, and maintenance tables.

SELECT 
  [people].[personal.age] AS age, 
  [people].[personal.gender] AS gender, 
  [people].[personal.name.first] AS first_name, 
  [people].[personal.name.last] AS last_name, 
  [people].[source], 
  [vehicles].[type], 
  [vehicles].[model], 
  [vehicles].[insurance.company] AS ins_company, 
  [vehicles].[insurance.policy_num] AS ins_policy_num, 
  [maintenance].[date] AS maint_date, 
  [maintenance].[desc] AS maint_desc
FROM 
  [people]
JOIN 
  [vehicles] 
ON 
  [people].[_id] = [vehicles].[people_id]
JOIN 
  [maintenance] 
ON 
  [vehicles].[_id] = [maintenance].[vehicles_id]

Results

In the example query, each maintenance element is JOINed to its parent vehicle element, which is JOINed to its parent people element to produce a table with 8 rows (2 maintenance entries for each of 2 vehicles each for 2 people).

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• Free-Form Queries: Use dot notation to select nested data.
• Vertical Flattening: Query nested data as separate tables.
• XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

By default, the Sync App automatically infers a relational schema by inspecting a series of REST objects in an array. This section describes the connection properties available to configure these dynamic schemas.

Discovering Tables

This section shows how to fine-tune the discovered schemas by fine-tuning the row scan. The rows detected depend on the RowScanDepth, DataModel, and XPath properties.

• RowScanDepth: This setting determines the number of object arrays to scan to find the rows and discover the columns. The process follows nested objects, counting 1 object array as 1 row.
• DataModel: Select how you want to represent the object arrays as tables. This setting also affects the objects that are scanned: the Relational and FlattenedDocuments settings will find all object arrays (including nested ones) in RowScanDepth items. The Document setting will only find the first object array.

• XPath: Set this to explicitly specify the object arrays you want to expose. Note that if DataModel is set to Document, you can only set one, top-level XPath. See Parsing Hierarchical Data for examples of accessing a sample document with different XPath values.

Discovering Columns

The columns identified during the discovery process depend on the FlattenArrays and FlattenObjects properties. If FlattenObjects is set (this is the default), nested objects will be flattened into a series of columns. For example, consider the following document:

<company>
  <id>12</id>
  <name>Lohia Manufacturers Inc.</name>
  <address>
    <street>Main Street</street>
    <city>Chapel Hill</city>
    <state>NC</state>
  </address>
  <office>Chapel Hill</office>
  <office>London</office>
  <office>New York</office>
  <annual_revenue>35,600,000</annual_revenue>
</company> 

If FlattenObjects is not set, then the address.street, address.city, and address.state columns will not be broken apart. The address column of type string will instead represent the entire object. Its value would be the following:

<address><street>Main Street</street><city>Chapel Hill</city><state>NC</state></address>

The FlattenArrays property can be used to flatten array values into columns of their own. This is only recommended for arrays that are expected to be short, for example the offices array below:

<office>London</office>
<office>Los Angeles</office>

It is best to leave other unbounded arrays as they are and piece out the data for them as needed using XML Functions.

As discussed in Automatic Schema Discovery, intuited table schemas enable SQL access to unstructured REST data. Customizing Schemas enables you to define static tables and gives you more granular control over the relational view of your data; for example, you can change the data types reported. However, you are not limited to the schema's view of your data.

You can query any nested structure without flattening the data. Any relations that you can access through Automatic Schema Discovery can also be accessed with an ad hoc SQL query.

Extended Projection Syntax

In the SELECT clause, use dot notation to specify an XPath to the data, as in the following query.

SELECT [personal.name.last], [personal.name.first], [vehicles.1.type], [vehicles.1.model] FROM people WHERE [personal.name.last] = 'Roberts' AND [personal.name.first] = 'Jane'

Note that to specify the path to a specific array element, specify the element's ordinal position. Arrays have a zero-based index, so the preceding query retrieves the second vehicle.

Example

The preceding query draws the column names from the example people document in Raw Data. Below is a person object from the array of people:

<?xml version="1.0" encoding="utf-8"?>
<root>
  <rootAttr1>rootValue1</rootAttr1>
  <people>
    <personal>
      <age>20</age>
      <gender>M</gender>
      <name>
        <first>John</first>
        <last>Doe</last>
      </name>
    </personal>
    <jobs>support</jobs>
    <jobs>coding</jobs>
    <vehicles>
      <type>car</type>
      <model>Honda Civic</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>sunroof</features>
      <features>rims</features>
      <maintenance>
        <date>07-17-2017</date>
        <desc>oil change</desc>
      </maintenance>
      <maintenance>
        <date>01-03-2018</date>
        <desc>new tires</desc>
      </maintenance>
    </vehicles>
    <vehicles>
      <type>truck</type>
      <model>Dodge Ram</model>
      <insurance>
        <company>ABC Insurance</company>
        <policy_num>12345</policy_num>
      </insurance>
      <features>lift kit</features>
      <features>tow package</features>
      <maintenance>
        <date>08-27-2017</date>
        <desc>new tires</desc>
      </maintenance>
      <maintenance>
        <date>01-08-2018</date>
        <desc>oil change</desc>
      </maintenance>
    </vehicles>
    <addresses>
      <type>work</type>
      <zip>12345</zip>
    </addresses>
    <addresses>
      <type>home</type>
      <zip>12357</zip>
    </addresses>
    <source>internet</source>
  </people>
</root> 

Connection String

With the following connection string, the Sync App will not parse nested data -- the data is processed when you execute the query. The properties of the top-level object are still flattened through the default FlattenObjects functionality. Nested data is returned as an XML aggregate.

URI=C:\people.txt;DataModel=Document;XPath='/root/people;'

Query

You can access any nested structure in the Raw Data document as a column:

SELECT [personal.name.last], [personal.name.first], [vehicles.1.type], [vehicles.1.model] FROM people WHERE [personal.name.last] = 'Roberts' AND [personal.name.first] = 'Jane'

Note that arrays have a zero-based index. The preceding query retrieves the example person's second vehicle.

Results

The preceding query returns the following results:

Vertical flattening queries enable you to retrieve a nested element as if it were a separate table.

Vertical Flattening Query Syntax

In the FROM clause, you can use dot notation to drill down to a nested element.

SELECT * FROM [people.vehicles] 

Example

Consider a single array element from the Raw Data document -- a person object from an array of people:

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<people>
  <personal>
    <age>20</age>
    <gender>M</gender>
    <name>
      <first>John</first>
      <last>Doe</last>
    </name>
  </personal>
  <vehicles>
    <type>car</type>
    <model>Honda Civic</model>
    <insurance>
      <company>ABC Insurance</company>
      <policy_num>12345</policy_num>
    </insurance>
    <maintenance>
      <date>07-17-2017</date>
      <desc>oil change</desc>
    </maintenance>
    <maintenance>
      <date>01-03-2018</date>
      <desc>new tires</desc>
    </maintenance>
  </vehicles>
  <vehicles>
    <type>truck</type>
    <model>Dodge Ram</model>
    <insurance>
      <company>ABC Insurance</company>
      <policy_num>12345</policy_num>
    </insurance>
    <maintenance>
      <date>08-27-2017</date>
      <desc>new tires</desc>
    </maintenance>
    <maintenance>
      <date>01-08-2018</date>
      <desc>oil change</desc>
    </maintenance>
  </vehicles>
  <source>internet</source>
</people>
</root> 
  

Connection String

With the following connection string, the Sync App will not parse nested data -- the data is processed when you execute the query. Due to the default FlattenObjects functionality, the properties of the top-level element are flattened. Nested data is returned as an XML aggregate.

URI=C:\people.txt;DataModel=Document;XPath='/root/people;'

Query

Vertical flattening will allow you to retrieve the vehicles element as a separate table:

SELECT * FROM [people.vehicles]

<features>sunroof</features><features>rims</features>

<maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance>

<features>lift kit</features><features>tow package</features>

<maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance>

<features>upgraded stereo</features>

<maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance>

<features>custom paint</features><features>custom wheels</features>

<maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance>

The Sync App can return XML as column values. The Sync App enables you to use SQL functions to work with these column values. The following sections provide examples; for a reference, see STRING Functions. The examples in this section use the following array (see Parsing Hierarchical Data for more information on parsing XML objects and arrays):

 
<grades>
  <student>
    <grade>A</grade>
    <score>2</score>
  </student>
  <student>
    <grade>A</grade>
    <score>6</score>
  </student>
  <student>
    <grade>A</grade>
    <score>10</score>
  </student>
  <student>
    <grade>A</grade>
    <score>9</score>
  </student>
  <student>
    <grade>B</grade>
    <score>14</score>
  </student>
</grades>

XML_EXTRACT

SELECT Name, XML_EXTRACT(grades,'[0].grade') AS Grade, XML_EXTRACT(grades,'[0].score') AS Score FROM Students;

XML_COUNT

SELECT Name, XML_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;

XML_SUM

SELECT Name, XML_SUM(score,'[x].score') AS TotalScore FROM Students;

XML_MIN

SELECT Name, XML_MIN(score,'[x].score') AS LowestScore FROM Students;

XML_MAX

SELECT Name, XML_MAX(score,'[x].score') AS HighestScore FROM Students;

The Sync App can return JSON structures as column values. The Sync App enables you to use standard SQL functions to work with these JSON structures. The examples in this section use the following array:

[
     { "grade": "A", "score": 2 },
     { "grade": "A", "score": 6 },
     { "grade": "A", "score": 10 },
     { "grade": "A", "score": 9 },
     { "grade": "B", "score": 14 }
]

JSON_EXTRACT

SELECT Name, JSON_EXTRACT(grades,'[0].grade') AS Grade, JSON_EXTRACT(grades,'[0].score') AS Score FROM Students;

JSON_COUNT

SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;

JSON_SUM

SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;

JSON_MIN

SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;

JSON_MAX

SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;

You can customize the table schemas detected through Automatic Schema Discovery to change column names and data types, enable update functionality, and more. The processing operations of the CData Sync App hide the complexity of processing data and communicating with remote data sources, but they also expose control over these layers through custom schemas.

Custom schemas are defined in configuration files. In this chapter we outline the structure of these files.

Generating Schema Files

Generating Schema Files allows you to persist and customize the dynamic schemas discovered through Automatic Schema Discovery.

Editing Schema Files

Tables and views are defined by authoring schema files in APIScript. APIScript is a simple configuration language that allows you to define the columns and the behavior of the table. It also has built-in operations that enable you to process REST. In addition to these data processing primitives, APIScript is a full-featured language with constructs for conditionals, looping, etc. However, as shown by the example schema, for most table definitions you will not need to use these features.

Example Schema

Below is a fully functional table schema that models the people document in the Raw Data example and contains all the components you will need to execute SQL to JSON/XML data sources.

The schema reflects the following connection string, which returns a single table containing the data delineated by the specified XPaths -- see Parsing Hierarchical Data for a guide to the different DataModel settings.

DataModel=FLATTENEDDOCUMENTS;URI=C:\people.xml;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance';Location=C:\myschemas;GenerateSchemaFiles=OnStart;Format=XML;

You can find more information on each of the components of a schema in Column Definitions, SELECT Execution, INSERT Execution, UPDATE Execution, and DELETE Execution. You can also create stored procedures to implement capabilities of your API that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE statements. See Defining Stored Procedures for more information.

  <api:script>
     <api:info title="Persons" desc="Parse the OData Persons feed.">
    <!-- You can modify the name, type, and column size here. -->
    <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from XML. -->
      <attr name="ID"           xs:type="int" key="true" readonly="false"   other:xPath="/feed/entry/content/properties/ID" />
      <attr name="EmployeeID"   xs:type="int"            readonly="true"    other:xPath="/feed/entry/content/properties/EmployeeID" />
      <attr name="Name"         xs:type="string"         readonly="false"   other:xPath="/feed/entry/content/properties/Name" />
      <attr name="TotalExpense" xs:type="double"         readonly="true"    other:xPath="/feed/entry/content/properties/TotalExpense" />
      <attr name="HireDate"     xs:type="datetime"       readonly="true"    other:xPath="/feed/entry/content/properties/HireDate" />
      <attr name="Salary"       xs:type="int"            readonly="true"    other:xPath="/feed/entry/content/properties/Salary" />
    </api:info>
    
    <api:set attr="uri" value="http://services.odata.org/V4/OData/(S(5cunewekdibfhpvoh21u2all))/OData.svc/Persons" /> 
  
    <!-- The XPath attribute of a schema is the path to a repeating element that defines the separation of rows -->
    <api:set attr="XPath" value="/feed/entry/" />
  
    <!-- See the xmlproviderGet page in the Operations subchapter to set any needed HTTP parameters. -->
    <api:set attr="ContentType"   value="application/atom+xml" />
  
  <!-- The GET method corresponds to SELECT. Here you can change the parameters of the request for data. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:call op="xmlproviderGet">
      <api:push/>
    </api:call>
  </api:script>
  
  <!-- To add support for INSERTS please see the INSERT Execution page within the help for further information and examples. -->
  <api:script method="POST">
    <api:set attr="method" value="POST"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Inserts are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for UPDATES please see the UPDATE Execution page within the help for further information and examples. -->
  <api:script method="MERGE">
    <api:set attr="method" value="PUT"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Updates are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for DELETES please see the DELETE Execution page within the help for further information and examples. -->
  <api:script method="DELETE">
    <api:set attr="method" value="DELETE"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Deletes are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  </api:script> 

To gain more control over the schemas discovered dynamically through Automatic Schema Discovery, you can save the schema to a configuration file. The GenerateSchemaFiles property enables you to automatically generate schemas based on the data modeling settings configured in the connection string. The CreateSchema stored procedure enables you to create schemas for other XPaths after you connect.

Generating Schema Files Automatically

You can generate schemas when you connect or when you execute a query. The Sync App will save the schemas to the folder specified by Location.

• Set GenerateSchemaFiles to OnStart to generate schemas for all tables detected when you connect.
• Set GenerateSchemaFiles to OnUse to generate a schema for the referenced table when you query the table.

Using the CreateSchema Stored Procedure

You can call the CreateSchema stored procedure to generate a schema for the XPaths you specify. Below are the stored procedure's inputs and outputs.

Input

Output

Example Stored Procedure Call

With DataModel set to FLATTENDOCUMENTS in the connection string, the example stored procedure call below results in a schema that flattens all the XPath arrays into a single table.

See Flattened Documents Model for more information on this data model.

EXECUTE CreateSchema TableName='GenPeople', 
FileLocation='C:\\tests\\scripts', 
URI='C:\\tests\\people.xml', 
XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance'

Next Steps

• Column Definitions: Change column names and data types or the XPath to the column value.
• SELECT Execution: Access the HTTP request -- for example, to implement server-side searches.
• INSERT Execution: Enable inserts by mapping inputs from the INSERT statement to an HTTP request.
• UPDATE Execution: Enable updates by mapping the updated column values to an HTTP request.
• DELETE Execution: Enable deletes by mapping the primary key to an HTTP request.

The basic attributes of a column are the name of the column, the data type, whether the column is a primary key, and the XPath. The Sync App uses the XPath to extract nodes from hierarchical data.

Mark up column attributes in the api:info block of the schema file. Set the XPath in the other:xPath property, as shown in the example below. You can also provide a description of each attribute using the desc property.

<api:info title="Persons" desc="Parse the OData Persons feed.">
    <attr name="ID"           xs:type="int" key="true" readonly="false"   other:xPath="content/properties/ID" desc="The ID associated with an individual person." />
    <attr name="EmployeeID"   xs:type="int"            readonly="true"    other:xPath="content/properties/EmployeeID" desc="The employee ID associated with the person." />
    <attr name="Name"         xs:type="string"         readonly="false"   other:xPath="content/properties/Name" desc="The name of the person." />
    <attr name="TotalExpense" xs:type="double"         readonly="true"    other:xPath="content/properties/TotalExpense" desc="The total experience associated with the person." />
    <attr name="HireDate"     xs:type="datetime"       readonly="true"    other:xPath="content/properties/HireDate" desc="The hire date of the person." />
    <attr name="Salary"       xs:type="int"            readonly="true"    other:xPath="content/properties/Salary" desc="The salary of the person." />
  </api:info>

Defining Column XPaths

The other:xPath property is used to specify the XPath that selects the column's value.

Absolute paths start with a '/' and contain the full XPath to the nested data.

<attr name="ID"           xs:type="int" key="true" other:xPath="/feed/entry/content/properties/ID" desc="The ID associated with the person." /> 

Indexed XPath values can also be used to specify an element within the document in the case that multiple elements with the same name are nested at the same level. The indexes are zero based.

<api:info>
  <attr name=PhoneNumber1 xs:type="string" other:xPath="Person/PhoneNumber[0]" desc="The person's phone number." />
  <attr name=PhoneNumber2 xs:type="string" other:xPath="Person/PhoneNumber[1]" desc="The person's phone number." />
</api:info>

Notes:

• XPaths and column names (when used to generate the XPath) are case sensitive.
• Any paths specified outside an XPath will only be retrieved if they are found prior to a row (XPath) being found, with the exception of the last row pushed (which will push all found paths). This is due to each row being pushed in a streaming fashion.

Defining Row XPaths

A row XPath specifies the path to an element that repeats at the same height -- an object array that the Sync App splits into rows. With DataModel set to FlattenedDocuments or Relational, you can specify more than one XPath in a semicolon-separated list. See Parsing Hierarchical Data for guides to these data modeling strategies.

The connection string can define the XPath property or you can define the row XPath as an attribute of an individual schema. Use the api:set keyword to define the row XPaths for a schema. With DataModel set to FlattenedDocuments or Relational, you can specify more than one XPath in a semicolon-separated list.

<api:set attr="XPath" value="/root/people;/root/people/vehicles;/root/people/vehicles/maintenance" /> 

A wildcard XPath can also be used and is helpful in the case that the XPaths are all at the same height but contain different names:

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

Defining Column Value Formats

You can set the other:valueFormat property to "aggregate" to identify a column as an aggregate.

• The aggregate option will return the aggregate found at the XPath specified. For example, consider the following:
  

      <repeat>
      <name>TestAggregate</name>
      <myobjcol1>
        <object1>myData</object1>
        <object1>myData1</object1>
        <object2>myData2</object2>
      </myobjcol1>
      </repeat>
      

  The following example extracts the myobjcol1 element to a column named MyObjCol1:
  

      <api:info>
        <attr name="MyObjCol1" xs:type="string" other:xPath="myobjcol1" other:valueFormat="aggregate" desc="An object column." />
      </api:info>
      <api:set attr="XPath" value="/repeat"/>
      

  This column value will be the following:
  

      <myobjcol1>
        <object1>myData</object1>
        <object1>myData1</object1>
        <object2>myData2</object2>
      </myobjcol1>
      

Mapping SELECT criteria to query parameters

Some APIs will support filtering the results of a request by specifying a query parameter. If this parameter maps to a WHERE clause, you can use the other:filter property to program this mapping.

• other:filter is a semicolon separated list of <parameter name>:<operator list>. <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.

The below example is for two query parameters, 'modifiedSince' and 'modifiedBefore', which filter the results based on the 'modifiedAt' column.

    <attr name="ModifiedAt"           xs:type="datetime" readonly="false"   other:xPath="content/properties/modifiedAt" other:filter="modifiedBefore:<;modifiedSince:>,>=,=" desc="Datetime when last modified." />
    

This example would take the query statement

SELECT * 
FROM <table> 
WHERE modifedAt < '<datetime>'

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

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

When an INSERT statement is executed, the Sync App executes the POST method of the schema, where you can build the HTTP insert request.

Execute Inserts to REST

In the POST method, the _input item, one of the Items in API Script, contains the columns to insert. For example, consider the following statement:

INSERT INTO Persons
(Name, Id)
VALUES
('Maria Anders','7') 

You can use the _input item's attributes to set these column values in the HTTP insert request. In the OData API, this is an HTTP POST. The POST data to make the preceding insert in the OData API is below:

<entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
  <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
  <content type="application/xml">
    <m:properties>
      <d:Name m:type="Edm.String">Maria Anders</d:Name>
      <d:ID m:type="Edm.Int32">7</d:ID>
    </m:properties>
  </content>
</entry>

After validating that the needed attributes exist, you can set the column values in the POST data. Set the "data" attribute to the POST data -- the body of the api:set keyword is convenient for setting long or multiline values like this.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, use api:set to set the method attribute to the HTTP method you want within the scope of the api:script keyword.

<api:script method="POST">
    <api:set attr="method" value="POST"/>
    <api:validate attr="_input.Name" desc="Name and Id are required to insert." />
    <api:validate attr="_input.Id" desc="Name and Id are required to insert." />
    <api:set attr="data">
      <entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
        <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
        <content type="application/xml">
          <m:properties>
            <d:Name m:type="Edm.String">[_input.Name]</d:Name>
            <d:ID m:type="Edm.Int32">[_input.Id]</d:ID>
          </m:properties>
        </content>
      </entry>
    </api:set>
    <api:call op="xmlproviderGet"/>
  </api:script>
  

Access Components of INSERT Statements

In the POST method, the _query item contains the following attributes:

INSERT INTO Account (account_name, account_type) VALUES ('Contoso','Company')

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

• api:script
• api:set
• api:validate
• api:call

When an UPDATE statement is executed, the Sync App executes the MERGE method of the schema, where you can build the HTTP update request.

To see this method in a complete example, refer to Customizing Schemas.

Execute Updates to REST

In the MERGE method, the _input item, one of the Items in API Script, contains the columns to update. For example, consider the following statement:

UPDATE Persons 
SET Name='Ana Trujilo'
WHERE Id = '7' 

You can use the _input item's attributes to set these column values in the HTTP update request. In the OData API, this can be an HTTP PUT or PATCH. The PUT data to make the preceding update in the OData API is below:

<entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
  <id>http://services.odata.org/V4/OData/%28S%28tjlj1hwyun3kt2iv0ef21c2d%29%29/OData.svc//Persons(ID=4)</id>
  <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
  <content type="application/xml">
    <m:properties>
      <d:ID m:type="Edm.Int32">4</d:ID>
        <d:Name m:type="Edm.String">Ana Trujilo</d:Name>
    </m:properties>
  </content>
</entry>

After validating that the needed attributes exist, you can set the column values in the PUT data. Set the "data" attribute to the PUT data -- the body of the api:set keyword is convenient for setting long or multiline values like this.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, use api:set to set the method attribute to the HTTP method you want within the scope of the api:script keyword.

<api:script method="MERGE">
  <api:set attr="method" value="PUT"/>
  <api:validate attr="_input.Id" desc="An Id is required to update." />
  <api:set attr="data">
    <entry xmlns:d="http://docs.oasis-open.org/odata/ns/data" xmlns:m="http://docs.oasis-open.org/odata/ns/metadata" xmlns="http://www.w3.org/2005/Atom">
      <api:set attr="uri" value="[uri]([_input.Id])" />
      <id>[uri]</id>
      <category scheme="http://docs.oasis-open.org/odata/ns/scheme" term="ODataDemo.Person" />
      <content type="application/xml">
        <m:properties>
          <d:ID m:type="Edm.Int32">[_input.Id]</d:ID>
          <api:check attr="_input.Name">
            <d:Name m:type="Edm.String">[_input.Name]</d:Name>
          </api:check>
        </m:properties>
      </content>
    </entry>
  </api:set>
  <api:call op="xmlproviderGet"/>
</api:script>

Access the Components of UPDATE Statements

In the MERGE method, the _query item contains the following attributes:

UPDATE Account SET Name='John' WHERE Id = @myId

Id = @myId

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

• api:script
• api:set
• api:validate
• api:check
• api:call

When a DELETE statement is executed, the Sync App executes the DELETE method of the schema, where you can build the HTTP delete request.

To see this method in a complete example, refer to Customizing Schemas.

Execute Deletes to REST

In the DELETE method, the _input item, one of the Items in API Script, will contain the primary key of the record to delete. For example, consider the following statement:

DELETE FROM Persons WHERE Id = '7'

Below is the corresponding delete request in the OData API:

DELETE myapi.com/Persons(7)

In the corresponding DELETE method below, the required column, the primary key, is first validated and then used to modify the URI in an HTTP DELETE request:

<api:script method="DELETE">
  <api:set attr="method" value="DELETE"/>
  <api:validate attr="_input.Id" desc="An Id is required to delete." />
  <api:set attr="uri" value="[uri]([_input.Id])" />
  <api:call op="xmlproviderGet"/>
</api:script>
  

You can check that an input was provided and alert the user otherwise with the api:validate keyword. After validating that the primary key attribute exists, you can specify the primary key in the request URI. Use the api:set keyword to set the URI value.

The api:call keyword invokes the operation that will make the HTTP request. Before invoking the operation, set the method attribute to the HTTP method you want within the scope of the api:script keyword.

Access Components of DELETE Statements

In the DELETE script, the _query item contains the following attributes:

DELETE FROM Account WHERE Id = @myId

Id = @myId

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

• api:script
• api:set
• api:validate
• api:call

Below is the structure of the Persons XML document used in the custom schema examples. This data is a simplified version of the XML response from the odata.org Northwind test service. This service is used to demonstrate data manipulation operations to XML-based REST APIs.

 
<values odata.context="http://services.odata.org/V4/OData/OData.svc/$metadata#Persons">
  <value>
    <ID>0</ID>
    <Name>Paula Wilson</Name>
  </value>
  <value>
    <ID>1</ID>
    <Name>Jose Pavarotti</Name>
  </value>
  <value>
    <ID>2</ID>
    <Name>Art Braunschweiger</Name>
  </value>
  <value odata.type="#ODataDemo.Customer">
    <ID>3</ID>
    <Name>Liz Nixon</Name>
    <TotalExpense>99.99</TotalExpense>
  </value>
  <value odata.type="#ODataDemo.Customer">
    <ID>4</ID>
    <Name>Liu Wong</Name>
    <TotalExpense>199.99</TotalExpense>
  </value>
  <value odata.type="#ODataDemo.Employee">
    <ID>5</ID>
    <Name>Jaime Yorres</Name>
    <EmployeeID>10001</EmployeeID>
    <HireDate>2000-05-30T00:00:00Z</HireDate>
    <Salary>15000</Salary>
  </value>
  <value odata.type="#ODataDemo.Employee">
    <ID>6</ID>
    <Name>Fran Wilson</Name>
    <EmployeeID>10002</EmployeeID>
    <HireDate>2001-01-02T00:00:00Z</HireDate>
    <Salary>12000</Salary>
  </value>
</values>

Query Slicer logic will enable the Sync App to push down separate requests for each filter value using the IN clause.

This enables the Sync App to avoid client side filtering.

Setting up Query Slicer Logic

Open the RSD where Query Slicer logic will be implemented.

See the example apiscript below. This will need to be used with a restadoGet operation instead of an XML/JSON operation.

<api:info title="queryslicer" desc="Generated schema file." other:queryslicercolumn="flight_number2222" xmlns:other="http://apiscript.com/ns?v1">

<attr name="flight_number2222"                   xs:type="integer"  readonly="false"              other:xPath="/json/flight_number"   other:filter="{flight_number2222}"  />

<api:check attr="_input.flight_number2222">
  <api:set attr="uri" value="[uri]?flight_number={flight_number2222}"/>
</api:check>

With this configuration, the following query will now dynamically pass down separate requests for each filter value:

SELECT * FROM launches WHERE flight_number2222 IN ('1', '2', '3')

SELECT * FROM launches WHERE flight_number2222 IN (SELECT flightId FROM flights)

In order for this function to work, you need to set other:queryslicercolumn="flight_number2222" in the child table's RSD only.

Limits and Considerations

In a script, most of the places that access a sliced input will not return single elements. For example, if you do something like this within your GET block trying to access the sliced value of the ID field:

<api:set attr="URI" value="http://example.com/[_input.id]" />

This won't work and will give you back a URL containing the full SQL list (e.g. http://example.com/(1, 2, 3)) instead of just a single ID.

The only place in the script that you can use sliced IDs is in URIs, because the brace bits are expanded at a later stage where the sliced input is actually available:

<api:set attr="URI" value="http://example.com/{id}" />

Stored procedures are function-like interfaces to the data source that can be used to search, modify, or delete data. Stored procedures model actions that typically cannot be represented as SELECT, INSERT, UPDATE, or DELETE statements. Modeling stored procedures is a similar process to modeling tables in that you can use the same built-in data processing operations to implement stored procedures.

Stored Procedure Schemas vs. Table Schemas

With minor modifications, you can follow the same process to create a stored procedure that you would follow to add support for INSERT, UDPATE, and DELETE statements: define stored procedures in .rsb files, which, like .rsd files, consist of an info block and scripts that call data processing operations.

Instead of columns, the info block defines the input and output parameters of the stored procedure. Instead of the attr element, define inputs with the input element in the info block.

As with other SQL statements, when the stored procedure is executed, the _input item contains the input parameters. You can use the _input item to map the stored procedure inputs to the operation inputs.

As with table schemas, you can use the info block to process the response. Describe the outputs of a stored procedure with the output attribute in the info block. In the output attributes, you can specify the XPaths to extract nodes of hierarchical data.

Example Stored Procedure

The following stored procedure retrieves a Person record given their Id. This fully functional schema shows how to build the request and use XPaths to parse the response.

A stored procedure executes the GET method of the schema. Build the API request in this method: check that required inputs were provided and alert the user otherwise with the api:validate keyword. Use Value Formatters to simplify working with strings, dates, and math expressions.

Invoke the operation with the api:call keyword. To return data, insert the api:push keyword in the scope of the operation call.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="GetODataPersonById" description="Retrieves the OData Person specified by Id.">
    <output name="ID"            other:xPath="/feed/entry/content/properties/ID" />
    <output name="EmployeeID"    other:xPath="/feed/entry/content/properties/EmployeeID" />
    <output name="Name"          other:xPath="/feed/entry/content/properties/Name" />
    <output name="TotalExpense"  other:xPath="/feed/entry/content/properties/TotalExpense" />
    <output name="HireDate"      other:xPath="/feed/entry/content/properties/HireDate" />
    <output name="Salary"        other:xPath="/feed/entry/content/properties/Salary" />  
  </api:info>
  
  <api:set attr="uri" value="http://services.odata.org/V4/OData/(S(5cunewekdibfhpvoh21u2all))/OData.svc/Persons" /> 
  
  <!-- The XPath attribute of the schema splits the XML into rows based on elements that repeat at the same level. -->
  <api:set attr="XPath" value="/entry/" />

  <!-- See the xmlproviderGet page in the Operations subchapter to set any needed HTTP parameters. -->
  <api:set attr="ContentType"   value="application/atom+xml" />

  <!-- The GET method corresponds to EXECUTE. Within the script block, you can see the URI modified to append a query string parameter. The results of processing are pushed to the schema's output.  -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:set attr="uri" value="[uri]([_input.Id])?$format=atom"/>
    <api:call op="xmlproviderGet">
      <api:push />
    </api:call>
  </api:script>
	
</api:script>

Keyword Reference

See the API Script Reference for more information on the keywords used in this section:

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

There may be occasions where an API may have a login endpoint that provides an access token that expires after a certain period of time. However, what the token endpoint expects may be an input standard that deviates from OAuth 2.0, such as requiring JSON input instead of URL-encoded form-data. If you want to leverage the driver's automated refresh timing even for these integrations, then you can achieve this by overriding the GetOAuthAccessToken.rsb file and including it among the schema files in the folder specified in your Location connection setting.

Example Login

For the following example, consider an API that requires the following:

• The login request must be in the form of a POST request.
• An API Key must be supplied in the x-api-key header of the request.
• The Email and Password of a user must be provided in the body of the request in JSON format.
• The access token is returned in the token field of the response, and has an implicit lifetime of 30 minutes.

In this example, the details of the request are populated from specific connection properties via the _connection item. The URI for the request comes from the OAuthAccessTokenURL property. The value of OAuthClientId is used to populate the value of the x-api-key header here. Both User and Password are used to fill the JSON body of the request.

Any override of the GetOAuthAccessToken stored procedure must at minimum push OAuthAccessToken and ExpiresIn as outputs in order for the automated refresh to work properly. In the example below, the OAuthAccessToken output is mapped to the token field of the response in the element map. Since the expiration time is not in the response but is rather implicitly assumed, the RSB file instead sets ExpiresIn to 1800 (in seconds) explicitly in the init output item prior to pushing. Finally, the api:call keyword is configured to call jsonproviderGet instead of oauthGetAccessToken, since there is a custom tailored request that needs to be sent.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="GetOAuthAccessToken" description="Obtains the OAuth access token to be used for authentication.">
    <output name="OAuthAccessToken"    desc="The authentication token returned. This can be used in subsequent calls to other operations for this particular service."/>
    <output name="ExpiresIn"           desc="The remaining lifetime on the access token."/>
  </api:info>

  <api:set attr="login.elementmapname#" value="OAuthAccessToken" />
  <api:set attr="login.elementmappath#" value="/json/token" />

  <api:set attr="login.authscheme" value="NONE" />
  <api:set attr="login.uri" value="[_connection.OAuthAccessTokenURL]" />
  <api:set attr="login.method" value="POST" />
  <api:set attr="login.Header:Name#1" value="x-api-key" />
  <api:set attr="login.Header:Value#1" value="[_connection.OAuthClientId]" />
  
  <api:set attr="login.ContentType" value="application/json" />
  <api:set attr="login.Data">{"Email":"[_connection.User]","Password":"[_connection.Password]"}</api:set>

  <api:call op="jsonproviderGet" input="login" out="init">
    <api:set attr="init.ExpiresIn" value="1800" />
    <api:push item="init"/>
  </api:call>
  
</api:script>

The Sync App has high-performance operations for processing XML data sources. These operations are platform neutral: Schema files that invoke these operations can be used in both .NET and Java. You can also extend the Sync App with your own operations written in .NET or Java.

The Sync App has the following operations:

The jsonproviderGet operation is an APIScript operation that is used to process local JSON files or remote JSON data stores. It allows you to split JSON content into rows.

Required Parameters

• URI: The URI parameter specifies the location of the JSON content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://). The URI connection property can also provide this input.

• JSONPath: The JSONPath parameter is used to split the document into multiple rows. The Sync App will detect the paths to the rows when the DataModel property is set to FlattenedDocuments or Relational. See Parsing Hierarchical Data for a guide to configuring how the data is modeled.

  The XPath connection property can also provide the row XPaths. Specify the XPaths as absolute paths; define multiple paths in a semicolon-separated list.

  A wildcard JSONPath can also be used and is helpful in the case that the JSONPaths are all at the same height but contain different names:
  

  <api:set  attr="JSONPath" value="/feed/*" />

HTTP

The jsonproviderGet operation can be used to make HTTP requests to JSON data sources and process the results. It abstracts the complexity of connecting to JSON-based REST APIs but also gives you control over the layers involved, providing the following inputs to configure authentication, the HTTP request and headers, and firewall traversal.

Column Mapping

The jsonproviderGet operation reads the Column Definitions from the api:info section of the table schema file. In the column definition, the other:xPath property maps the column value to a JSON element.

Input Parameters

You can use api:set to specify the operation's input parameters, listed below. The connection properties also pass inputs to the operation; setting an attribute in the schema will override the connection property.

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

Processing

• URI: The URI parameter specifies the location of the JSON content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://).

• JSONPath: The JSONPath parameter specifies the XPath to an array element that is used to split the document into rows. Specify multiple paths in a semicolon-separated list; see DataModel to configure the tables discovered based on these paths. By default the Sync App will detect the object arrays in the document as the rows -- see Automatic Schema Discovery to configure the row scan.

  A wildcard JSONPath can also be used and is helpful in the case that the JSONPaths are all at the same height but contain different names:
  

  <api:set  attr="JSONPath" value="/feed/*" />

HTTP

• Method: The HTTP method that corresponds to the SQL data manipulation statement. The allowed values are GET, POST, PUT, DELETE, and MERGE. The default value is GET.
• ContentType: The content type of the HTTP post. Relevant only if data is specified.
• Data: Data to include in the put or the post.
• Text: Input JSON text (an alternative to URI).
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• ParamName#: The name for each parameter to pass with the request.
• ParamValue#: The value for each parameter to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: The file where exchanged/transferred data is logged.
• PushResponseHeader#: The response headers which should be returned from this operation. Any headers added to this list are returned as header:* parameters.
• PushResponseCookie#: The response cookies which should be returned from this operation. Any cookies added to this list are returned as cookie:* parameters.

Authentication

• User: The username used for authentication.
• Password: The password used for authentication.
• AuthScheme: The authentication method to use. Only relevant if User and Password are provided. The allowed values are BASIC, DIGEST, NONE, NTLM, NEGOTIATE. The default value is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosToken: The Kerberos token used for authentication.

OAuth

• Version: The OAuth version. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Other_Options: Other options to control the behavior of OAuth.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, and SOCKS5. The default value is NONE.

Advanced Processing

• ElementMapPath#: The XPath to the subelement that you want to specify as columns within your chosen row. This can be relative to the item (for example, title) or absolute (for example, /json/@version).
• ElementMapName#: The name of the chosen ElementMapPath. The ElementMapName input can be used to give a name to the XPath chosen in ElementMapPath.
• ElementArrayMapPath#: The XPath to the element that you want to specify as an array.
• ElementArrayMapName#: The name of the chosen ElementArrayMapPath. The ElementArrayMapName input can be used to give a name to the XPath chosen in ElementArrayMapPath.
• AggregateFormat: The format to use with aggregate values. The allowed values are JSON, XML. The default value is JSON.
• AggregatePath#: The XPath to the element that you want to return as an aggregate.
• AggregateName#: The name of the chosen aggregate. The AggregateName input can be used to give a name to the XPath chosen in AggregatePath.
• ResultCodeXPath: The XPath of the result code element in the response.
• ResultSuccessCode: The success code of the value located at ResultCodeXPath that is used to verify and identify the request as being successful.
• ErrorMsgXPath: The XPath of the error message returned in the response.
• IndexParentNodes: Multiple occurrences of the same element will be indexed if this is set to true. If set to false, multiple occurrences will be combined into an array. The allowed values are TRUE, FALSE. The default value is TRUE.
• TrimStartingParentIndexes: Specifies whether to trim starting parent indexes. Only valid when IndexParentNodes is TRUE. Specifically useful when the JSONPath specifies multidimensional arrays. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushEmptyArrayObjects: Specifies whether to push empty objects within multidimensional arrays. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushPrefix: The prefix to be prepended to each item attribute that is pushed.
• EnablePaging: Specifies whether the Rows@Next input will be used for paging. The allowed values are TRUE, FALSE. The default value is FALSE.
• OutputPrefix: Whether the prefix should be output with each pushed attribute of an item. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushOuterValueRow: Whether to generate special rows for paths outside of the repeat element when no other rows would contain them. If one of these rows is generated it will contain the values of all mapped paths outside the repeat element, along with a special attribute called @isoutervalue which is set to true. However, if there is a row in the repeat element which contains these values then the @isoutervalue is not pushed.

  The allowed values are TRUE, FALSE. The default value is FALSE.

Output Parameters

• *: Objects found.
• header:*: Any headers requested from the PushResponseHeader# input (e.g. adding "Content-Type" to PushResponseHeader# will return "header:Content-Type")
• cookie:*: Any cookies requested from the PushResponseCookie# input (e.g. adding "session" to PushResponseCookie# will return "cookie:session")

<api:call op="jsonproviderGet" out="output">
  <api:map from="output" to="temp" map="* = *" />
  <api:push item="temp" />
</api:call >

The flexible layout has different restrictions to be aware of:

• It is slightly slower than the optimized layout.
• It does not support column names containing a period, so you will want to set the hidden PathDelimiter property to an underscore and access flattened columns using names like a_b_c instead of a.b.c.

The xmlproviderGet operation is an APIScript operation that is used to process local and remote XML content. It allows you to split XML content into rows.

Required Parameters

• URI: The URI parameter specifies the location of the XML content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://). The URI connection property can also provide this input.

• XPath: The XPath parameter is used to split the document into multiple rows. The Sync App will detect the row XPaths in the document when the DataModel property is set to FlattenedDocuments or Relational. See Parsing Hierarchical Data for a guide to configuring how the data is modeled.

  The XPath connection property can also provide the row XPaths. Specify the XPaths as absolute paths; define multiple paths in a semicolon-separated list.

  A wildcard XPath can also be used and is helpful in the case that the XPaths are all at the same height but contain different names:
  

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

HTTP Operation

The xmlproviderGet operation can be used to make HTTP requests to XML data sources and process the results. It abstracts the complexity of connecting to XML-based REST APIs but also gives you control over the layers involved, providing the following inputs to configure authentication, the HTTP request and headers, and firewall traversal.

Column Mapping

The xmlproviderGet operation reads the Column Definitions from the api:info section of the table schema file. In the column definition, the other:xPath property maps the column value to an XML element.

Input Parameters

You can use api:set to specify the operation's input parameters, as shown below.

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

The connection properties also pass inputs to the operation; setting one of the following input attributes in the schema will override the connection property.

Processing

• URI: The URI parameter specifies the location of the XML content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://).

• XPath: The XPath parameter is used to split the document into multiple rows. The Sync App will detect the row XPaths in the document when the DataModel property is set to FlattenedDocuments or Relational. See Parsing Hierarchical Data for a guide to configuring how the data is modeled.

  You can also set the XPath connection property to delineate the paths to the tables. Specify absolute paths and define multiple paths in a semicolon-separated list.

  A wildcard XPath can also be used and is helpful in the case that the XPaths are all at the same height but contain different names:
  

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

HTTP

• Method: The HTTP method that corresponds to the SQL data manipulation statement. The allowed values are GET, POST, PUT, DELETE, and MERGE. The default value is GET.
• ContentType: The content type of the HTTP post. Relevant only if data is specified.
• Data: Data to include in the put or the post. To post a file use a file:// URI.
• Text: Input XML text (an alternative to URI).
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• ParamName#: The name for each parameter to pass with the request.
• ParamValue#: The value for each parameter to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: The file where exchanged/transferred data is logged.
• PushResponseHeader#: The response headers which should be returned from this operation. Any headers added to this list are returned as header:* parameters.
• PushResponseCookie#: The response cookies which should be returned from this operation. Any cookies added to this list are returned as cookie:* parameters.

Authentication

• User: The username used for authentication.
• Password: The password used for authentication.
• AuthScheme: The authentication method to use. Only relevant if User and Password are provided. The allowed values are BASIC, DIGEST, NONE, NTLM, NEGOTIATE. The default value is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosToken: The Kerberos token used for authentication.

OAuth

• Version: The OAuth version. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Other_Options: Other options to control the behavior of OAuth.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, and SOCKS5. The default value is NONE.

Advanced Processing

• ElementMapPath#: The XPath to the subelement that you want to specify as columns within your chosen row. This can be relative to the item (for example, title) or absolute (for example, /rss/@version).
• ElementMapName#: The name of the chosen ElementMapPath. The ElementMapName input can be used to give a name to the XPath chosen in ElementMapPath.
• ElementArrayMapPath#: The XPath to the element that you want to specify as an array.
• ElementArrayMapName#: The name of the chosen ElementArrayMapPath. The ElementArrayMapName input can be used to give a name to the XPath chosen in ElementArrayMapPath.
• AggregateFormat: The format to use with aggregate values. The allowed values are JSON, XML. The default value is XML.
• AggregatePath#: The XPath to the element that you want to return as an aggregate.
• AggregateName#: The name of the chosen aggregate. The AggregateName input can be used to give a name to the XPath chosen in AggregatePath.
• ResultCodeXPath: The XPath of the result code element in the response.
• ResultSuccessCode: The success code of the value located at ResultCodeXPath that is used to verify and identify the request as being successful.
• ErrorMsgXPath: The XPath of the error message returned in the response.
• IndexParentNodes: Multiple occurrences of the same element will be indexed if this is set to true. If set to false, multiple occurrences will be combined into an array. The allowed values are TRUE, FALSE. The default value is TRUE.
• PushPrefix: The prefix to be prepended to each item attribute that is pushed.
• EnablePaging: Specifies whether the Rows@Next input will be used for paging. The allowed values are TRUE, FALSE. The default value is FALSE.
• OutputPrefix: Whether the prefix should be output with each pushed attribute of an item. The allowed values are TRUE, FALSE. The default value is FALSE.
• PushOuterValueRow: Whether to generate special rows for paths outside of the repeat element when no other rows would contain them. If one of these rows is generated it will contain the values of all mapped paths outside the repeat element, along with a special attribute called @isoutervalue which is set to true. However, if there is a row in the repeat element which contains these values then the @isoutervalue is not pushed.

  The allowed values are TRUE, FALSE. The default value is FALSE.

Output Parameters

• *: The elements and the attributes found within each item element.
• header:*: Any headers requested from the PushResponseHeader# input (e.g. adding "Content-Type" to PushResponseHeader# will return "header:Content-Type")
• cookie:*: Any cookies requested from the PushResponseCookie# input (e.g. adding "session" to PushResponseCookie# will return "cookie:session")

<api:call op="xmlproviderGet" out="output">
  <api:map from="output" to="temp" map="* = *" />
  <api:push item="temp" />
</api:call>

The csvproviderGet operation is an API Script operation that is used to process CSV content. It allows you to split CSV content into rows.

Required Parameters

• URI: The URI parameter specifies the location of the CSV content. This URI scheme can be file:// for local files or can specify a remote data source: http:// (or https://), s3://, gdrive://, box://, or ftp:// (ftps://).

Network Operation

The csvproviderGet operation can be used to execute remote data retrieval operations. It abstracts the request and also enables configuration of most aspects through the following inputs, including authentication and firewall traversal. See ProxyAuthScheme and FirewallType the properties needed to negotiate a firewall.

Column Mapping

The csvproviderGet operation reads the api:info section of the table schema file to map various elements in the CSV document into column values within a row. It does so using the other:internalname property of the column definition.

HTTP

• Method: The HTTP method that corresponds to the SQL data manipulation statement. The allowed values are GET, POST, PUT, DELETE, and MERGE. The default value is GET.
• ContentType: The content type of the HTTP post. Relevant only if data is specified.
• Data: Data to include in the put or the post.
• Text: Input CSV text (an alternative to URI).
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• ParamName#: The name for each parameter to pass with the request.
• ParamValue#: The value for each parameter to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: The file where exchanged/transferred data is logged.
• PushResponseHeader#: The response headers which should be returned from this operation. Any headers added to this list are returned as header:* parameters.

Authentication

• User: The username used for authentication.
• Password: The password used for authentication.
• AuthScheme: The authentication method to use. Only relevant if User and Password are provided. The allowed values are BASIC, DIGEST, NONE, NTLM, NEGOTIATE. The default value is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosToken: The Kerberos token used for authentication.

OAuth

• Version: The OAuth version. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Other_Options: Other options to control the behavior of OAuth.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, and SOCKS5. The default value is NONE.

Output Parameters

• *: CSV columns from CSV content.
• header:*: Any headers requested from the PushResponseHeader# input (e.g. adding "Content-Type" to PushResponseHeader# will return "header:Content-Type")

The oauthGetAccessToken operation is an APIScript operation that is used to facilitate the OAuth authentication and refresh flows.

The Sync App includes stored procedures that invoke this operation to complete the OAuth exchange. The following example schema briefly lists some of the typically required inputs before the following sections explain them in more detail.

See Defining Stored Procedures for more information on working with custom stored procedures. For a guide to using the Sync App to authenticate, see the "Getting Started" chapter.

Creating a GetOAuthAccessToken Stored Procedure

Invoke the oauthGetAccessToken with the GetOAuthAccessToken stored procedure. The following inputs are required for most data sources and will provide default values for the connection properties of the same name.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="GetOAuthAccessToken"   description="Obtains the OAuth access token to be used for authentication with various APIs."                                                         >
    <input  name="AuthMode"               desc="The OAuth flow. APP or WEB."                                                                                                                    />
    <input  name="CallbackURL"            desc="The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. " />
    <input  name="OAuthAccessToken"       desc="The request token. OAuth 1.0 only."                                                                                                             />
    <input  name="OAuthAccessTokenSecret" desc="The request token secret. OAuth 1.0 only."                                                                                                      />
    <input  name="Verifier"               desc="The verifier code obtained when the user grants permissions to your app."                                                                       />

    <output name="OAuthAccessToken"       desc="The access token."                                                                                                                              />
    <output name="OAuthTokenSecret"       desc="The access token secret."                                                                                                                       />
    <output name="OAuthRefreshToken"      desc="A token that may be used to obtain a new access token."                                                                                         />
 </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"                                                    value="MyOAuthVersion"                 />
  <!-- Set RequestTokenURL to the URL where the request for the request token is made. OAuth 1.0 only.-->
  <api:set attr="OAuthRequestTokenURL"                                            value="http://MyOAuthRequestTokenURL" />
  <!-- Set OAuthAuthorizationURL to the URL where the user logs into the service and grants permissions to the application. -->
  <api:set attr="OAuthAuthorizationURL"                                           value="http://MyOAuthAuthorizationURL" />
  <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
  <api:set attr="OAuthAccessTokenURL"                                             value="http://MyOAuthAccessTokenURL"   />
  <!-- Set GrantType to the authorization grant type. OAuth 2.0 only. -->
  <api:set attr="GrantType"                                                       value="CODE"                           />
  <!-- Set SignMethod to the signature method used to calculate the signature of the request. OAuth 1.0 only.-->
  <api:set attr="SignMethod"                                                      value="HMAC-SHA1"                      />
  <api:call op="oauthGetAccessToken">
    <api:push/>
  </api:call>
  
</api:script>

Writing the RefreshOAuthAccessToken Stored Procedure

You can also use oauthGetAccessToken to refresh the access token by providing the following inputs:

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="RefreshOAuthAccessToken" description="Refreshes the OAuth access token used for authentication." >
    <input  name="OAuthRefreshToken"        desc="A token that may be used to obtain a new access token."           />

    <output name="OAuthAccessToken"         desc="The authentication token returned."                               />
    <output name="OAuthTokenSecret"         desc="The authentication token secret returned. OAuth 1.0 only."        />
    <output name="OAuthRefreshToken"        desc="A token that may be used to obtain a new access token."           />
    <output name="ExpiresIn"                desc="The remaining lifetime on the access token."                      />

  </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"                                                    value="MyOAuthVersion"                 />
    <!-- Set GrantType to REFRESH. OAuth 2.0 only. -->
    <api:set attr="GrantType"            value="REFRESH" />
    <!-- Set SignMethod to the signature method used to calculate the signature of the request. OAuth 1.0 only.-->
    <api:set attr="SignMethod"           value="HMAC-SHA1" />
    <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
    <api:set attr="OAuthAccessTokenURL"  value="http://MyOAuthAccessTokenURL" />
    <!-- Set AuthMode to 'WEB' when calling RefreshOAuthAccessToken -->
    <api:set attr="AuthMode" value="WEB"/>
  <api:call op="oauthGetAccessToken">
    <api:push/>
  </api:call>
  
</api:script>

Input Parameters

• OAuthVersion: The OAuth version. The allowed values are 1.0, 2.0. The default value is 1.0.
• AuthMode: The OAuth flow. OAuth 2.0 only. If you choose the App mode, this operation will launch your browser and prompt you to authenticate with your account credentials. Set this parameter to WEB to authenticate a Web app or if the Sync App is not allowed to open a Web browser. The default value is APP.
• OAuthRequestTokenURL: The URL where the Sync App makes a request for the request token. OAuth 1.0 only. Required for OAuth 1.0.
• OAuthAuthorizationURL: The URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthAccessTokenURL: The URL where the request for the access token is made. In OAuth 1.0, the authorized request token is exchanged for the access token.
• CallbackURL: The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. This value must match the callback URL you specify when you register an app. Note that your data source may additionally require the port.
• OAuthClientId: The client Id obtained when you register an app. Also called a consumer key.
• OAuthClientSecret: The client secret obtained when you register an app. Also called a consumer secret.
• OAuthAccessToken: The request token. OAuth 1.0 only.
• OAuthAccessTokenSecret: The request token secret. OAuth 1.0 only.
• OAuthRefreshToken: A token that may be used to obtain a new access token.
• GrantType: Authorization grant type. OAuth 2.0 only. The allowed values are CODE, PASSWORD, CLIENT, REFRESH. The default value is CODE.
• Verifier: The verifier code obtained when the user grants permissions to the Sync App. In the OAuth 2.0 code grant type, the verifier code is located in the code query string parameter of the callback URL. In OAuth 1.0, the verifier is located in the oauth_verifier query string parameter of the callback URL.
• SignMethod: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Cert: Path for the PFX personal certificate file. OAuth 1.0 only.
• CertPassword: Personal certificate password. OAuth 1.0 only.
• OtherOptions: Other options to control the behavior of OAuth.
• OAuthParam:*: Other parameters.
• PostData: The HTTP POST data.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• LogFile: Specifies a file where the request and response are logged.
• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, SOCKS5. The default value is NONE.
• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.

Output Parameters

• OAuthAccessToken: The access token.
• OAuthTokenSecret: The access token secret.
• OAuthRefreshToken: A token that may be used to obtain a new access token.
• ExpiresIn: The remaining lifetime on the access token.
• OAuthParam:*: Other parameters sent from the server.

The oauthGetUserAuthorizationURL is an APIScript operation that is used to facilitate the OAuth authentication flow for Web apps, for offline apps, and in situations where the Sync App is not allowed to open a Web browser. To pass the needed inputs to this operation, define the GetOAuthAuthorizationURL stored procedure. The Sync App can call this internally.

Define stored procedures in .rsb files with the same file name as the schema's title. The example schema briefly lists some of the typically required inputs before the following sections explain them in more detail.

For a guide to authenticating in the OAuth flow, see the "Getting Started" chapter. You can find more information about stored procedures and how to write them in Defining Stored Procedures.

Writing the GetOAuthAuthorizationURL Stored Procedure

Call oauthGetUserAuthorizationURL in the GetOAuthAuthorizationURL stored procedure.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">

  <api:info title="Get OAuth Authorization URL" description="Obtains the OAuth authorization URL used for authentication with various APIs."                                                          >
    <input  name="CallbackURL"                  desc="The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. " />

    <output name="URL"                          desc="The URL where the user logs in and is prompted to grant permissions to the app. "                                                               />
    <output name="OAuthAccessToken"             desc="The request token. OAuth 1.0 only."                                                                                                             />
    <output name="OAuthTokenSecret"             desc="The request token secret. OAuth 1.0 only."                                                                                                      />
  </api:info>

  <!-- Set OAuthVersion to 1.0 or 2.0. -->
  <api:set attr="OAuthVersion"          value="MyOAuthVersion"                 />
  <!-- Set ResponseType to the desired authorization grant type. OAuth 2.0 only.-->
  <api:set attr="ResponseType"           value="code"                           />
  <!-- Set SignMethod to the signature method used to calculate the signature. OAuth 1.0 only.-->
  <api:set attr="SignMethod"            value="HMAC-SHA1"                      />
  <!-- Set OAuthAuthorizationURL to the URL where the user logs into the service and grants permissions to the application. -->
  <api:set attr="OAuthAuthorizationURL"  value="http://MyOAuthAuthorizationURL" />
  <!-- Set OAuthAccessTokenURL to the URL where the request for the access token is made. -->
  <api:set attr="OAuthAccessTokenURL"   value="http://MyOAuthAccessTokenURL"/>
  <!-- Set RequestTokenURL to the URL where the request for the request token is made. OAuth 1.0 only.-->
  <api:set attr="OAuthRequestTokenURL"   value="http://MyOAuthRequestTokenURL"       />
  <api:call op="oauthGetUserAuthorizationUrl">
    <api:push/>
  </api:call>
  
</api:script>

<p>

Input Parameters

• OAuthVersion: The OAuth version. The allowed values are 1.0, 2.0. The default value is 1.0.
• OAuthAuthorizationURL: The URL where the user logs into the service and grants permissions to the application. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthRequestTokenURL: The URL where the Sync App makes a request for the request token. OAuth 1.0 only. Required for OAuth 1.0.
• CallbackURL: The URL to be used as a trusted redirect URL, where the user will return with the token that verifies that they have granted your app access. This value must match the callback URL you specify when you register an app. Note that your data source may additionally require the port. The default value is http://127.0.0.1/.
• OAuthClientId: The client Id. Also called a consumer key.
• OAuthClientSecret: The client secret. Also called a consumer secret.
• ResponseType: The desired authorization grant type. OAuth 2.0 only. The allowed values are CODE, IMPLICIT. The default value is CODE.
• SignMethod: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, RSA-SHA1, PLAINTEXT. The default value is HMAC-SHA1.
• Cert: Path for the personal certificate PFX file. OAuth 1.0 only.
• CertPassword: Personal certificate password. OAuth 1.0 only.
• OtherOptions: Other options to control the behavior of OAuth.
• OAuthParam:*: Other parameters. OAuth 1.0 only.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request.
• Proxy_Port: The port number of the proxy server.
• Proxy_User: The user Id used to authenticate with the proxy server.
• Proxy_Password: The password used to authenticate with the proxy server.
• Proxy_AuthScheme: The authentication scheme of the proxy server. The allowed values are BASIC, DIGEST, NONE, NTLM. The default value is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.
• Firewall_Type: The type of the firewall. The allowed values are NONE, TUNNEL, SOCKS4, SOCKS5. The default value is NONE.
• Firewall_Server: The IP address or host name of the firewall.
• Firewall_Port: The port number of the firewall.
• Firewall_User: The user Id used to authenticate with the firewall.
• Firewall_Password: The password used to authenticate with the firewall.

Output Parameters

• URL: The URL where the user logs in and is prompted to grant permissions to the app. In OAuth 1.0, if permissions are granted the request token is authorized.
• OAuthAccessToken: The request token. OAuth 1.0 only.
• OAuthTokenSecret: The request token secret. OAuth 1.0 only.
• OAuthParam:*: Other parameters sent from the server. OAuth 1.0 only.

The utiladoRefreshOAuth operation causes the Sync App to refresh any OAuth tokens that are close to expiring. This has no effect when AuthScheme is set to a non-OAuth authentication mode .

The operation takes no parameters and may be called like this:

<api:call op="utiladoRefreshOAuth" />

The utiladoSleep operation causes the process that calls it to suspend execution for a specifed duration.

The required parameter is timeout. The units are in seconds. For example,

<api:call op="utiladoSleep?timeout=2" />

The utiladoPushPageToken operation causes the process to push a page token for rows@next without pushing a row in the result set. This is useful in some complex scenarios, such as paging the outer call while only having the nested inner call push rows.

Example Usage

• Push a next page URL to rows@next:
  

  <api:set item="page" attr="pagetoken" value="[out1._next]" />
  <api:call op="utiladoPushPageToken" input="page" />

• Push a paging token or cursor to rows@next:
  

  <api:set item="page" attr="pagetoken" value="[out1.cursor]" />
  <api:call op="utiladoPushPageToken" input="page" />

• Push an incremented page number to rows@next:
  

  <api:set item="page" attr="pagetoken" value="[temp.currentpage | add(1)]" />
  <api:call op="utiladoPushPageToken" input="page" />

• Push an incremented offset to rows@next:
  

  <api:set item="page" attr="pagetoken" value="[temp.offset | add([temp.pagesize])]" />
  <api:call op="utiladoPushPageToken" input="page" />

The httpopsGet operation is an APIScript operation that is used to make HTTP GET requests.

Input Parameters

You can use api:set to specify the operation's input parameters, as shown below.

    <api:set  attr="url"                      value="http://www.example.com/target" /> 
  

Required Parameters

• URL: The URL to retrieve.

HTTP

• IfModifiedSince: The if-modified-since date and time to use as a filter for the results retrieved (for example: Sat, 25 Feb 2006 04:11:47 GMT).
• LocalFile: If specified, the results of the get will be written to this file.
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• Logfile: The file where exchanged/transferred data is logged.
• Verbosity: The detail level to use when logging the exchanged/transferred data to the file specified by the logfile input.

Authentication

• User: The username to authenticate with if the authscheme parameter is set to something other than None.
• Password: The password to authenticate with if the authscheme parameter is set to something other than None.
• AuthScheme: The authorization mechanism to use. Only relevant if user and password are provided. Valid values are BASIC, DIGEST, and NTLM. Default is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.

OAuth

• Version: The OAuth version. Use OAuth to authorize the request. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. Source is proxy_auto. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request. Source is proxy_server.
• Proxy_Port: The port number of the proxy server. Source is proxy_port.
• Proxy_User: The user Id used to authenticate with the proxy server. Source is proxy_user.
• Proxy_Password: The password used to authenticate with the proxy server. Source is proxy_password.
• Proxy_AuthScheme: The authentication scheme of the proxy server. Valid values are BASIC, DIGEST, NONE, NTLM, and NEGOTIATE. Default is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall. Source is firewall_host.
• Firewall_Port: The port number of the firewall. Source is firewall_port.
• Firewall_User: The user Id used to authenticate with the firewall. Source is firewall_user.
• Firewall_Password: The password used to authenticate with the firewall. Source is firewall_password.
• Firewall_Type: The type of the firewall. Source is firewall_type. Valid values are NONE, TUNNEL, SOCKS4,and SOCKS5. Default is NONE.

Other

• Internalconfig#: Sets an internal configuration setting.'/>
• Charset: The charset of the exchanged/transferred data. Default is UTF-8' />
• HTTPVersion: Version of the HTTP protocol to use. Valid values are 1.0 and 1.1. Default is 1.1
• FollowRedirects: Whether or not to follow HTTP redirects. Valid values are TRUE and FALSE. Default is TRUE.
• SSLCert: The SSL certificate to be accepted from the server. Any other certificate will be rejected. This can take the form of a full PEM certificate, the path to a file containing the certificate, the public key, the MD5 thumbprint, or the SHA-1 thumbprint. If not specified any valid certificate will be accepted. Default is TRUSTED
• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The store type of the client certificate. Valid values are USER,MACHINE,PFXFILE,PFXBLOB,JKSFILE,JKSBLOB,PEMKEY_FILE,PEMKEY_BLOB,PUBLIC_KEY_FILE,PUBLIC_KEY_BLOB,SSHPUBLIC_KEY_BLOB,P7BFILE,P7BBLOB,SSHPUBLIC_KEY_FILE,PPKFILE,PPKBLOB,XMLFILE,and XMLBLOB
• SSLClientCertPassword: The password of the client certificate.
• SSLClientCertSubject: The subject of the client certificate. Using an asterisk will find the first certificate in the store.

Output Parameters

• ssl:issuer: The issuer of the SSL certificate.
• ssl:subject: The subject of the SSL certificate.
• statuscode: The HTTP status code returned from the request.
• content: The content of the HTTP response.
• cookie:*: The cookies returned with the response.
• allcookies: All of the cookies from the response, returned as a single string.
• header:*: The headers returned with the response.

Example:

    <api:set attr="URL" value="http://www.example.com/target">
    <api:set attr="Header:Name#" value="x-custom">
    <api:set attr="Header:Value#" value="abcd">

    <api:call op="httpops.post" out="output">
      <api:set attr="temp.data" value="[output.content]">
    </api:call >
  

The httpopsPost operation is an APIScript operation that is used to make HTTP POST requests.

Input Parameters

You can use api:set to specify the operation's input parameters, as shown below.

    <api:set  attr="url"                      value="http://www.example.com/target" /> 
  

Required Parameters

• URL: The URL to retrieve.

HTTP

• Method: The HTTP Method. Valid values are POST,PUT, and PATCH. Default is POST.
• ContentType: The content type for the post. Default is application/x-www-form-urlencoded
• PostData: Data to include in the post. Use file:// followed by a file path to post the contents of a file. Default is empty
• FormEncoding: When encoding paramname# and paramvalue#, the format to use. Valid values are URLENCODED and FORMDATA. Default is URLENCODED
• Header:Name#: The name for each custom header to pass with the request.
• Header:Value#: The value for each custom header to pass with the request.
• ParamName#: The name for each parameter to pass with the request.
• ParamValue#: The value for each parameter to pass with the request.
• Cookie:*: Any cookies that should be added to the request.
• Timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. The default value is 60.
• Logfile: The file where exchanged/transferred data is logged.
• Verbosity: The detail level to use when logging the exchanged/transferred data to the file specified by the logfile input.

Authentication

• User: The username to authenticate with if the authscheme parameter is set to something other than None.
• Password: The password to authenticate with if the authscheme parameter is set to something other than None.
• AuthScheme: The authorization mechanism to use. Only relevant if user and password are provided. Valid values are BASIC, DIGEST, and NTLM. Default is BASIC.
• KerberosKDC: The KDC setting of Kerberos, available when AuthScheme is NEGOTIATE.
• KerberosRealm: The Realm setting of Kerberos, available when AuthScheme is NEGOTIATE.

OAuth

• Version: The OAuth version. Use OAuth to authorize the request. The allowed values are DISABLED, 1.0, 2.0. The default value is DISABLED.
• Token: The access token for OAuth.
• Token_Secret: The access token secret. OAuth 1.0 only.
• Client_Id: The OAuth client Id. OAuth 1.0 only.
• Client_Secret: The OAuth client secret. OAuth 1.0 only.
• Sign_Method: The signature method used to calculate the signature for OAuth 1.0. The allowed values are HMAC-SHA1, PLAINTEXT. The default value is HMAC-SHA1.

Proxy

• Proxy_Auto: Whether or not the proxy should be detected from Windows system settings. This takes precedence over other proxy settings and is not available in Java. Source is proxy_auto. The allowed values are TRUE, FALSE. The default value is FALSE.
• Proxy_Server: IP address or host name of the proxy server used for the request. Source is proxy_server.
• Proxy_Port: The port number of the proxy server. Source is proxy_port.
• Proxy_User: The user Id used to authenticate with the proxy server. Source is proxy_user.
• Proxy_Password: The password used to authenticate with the proxy server. Source is proxy_password.
• Proxy_AuthScheme: The authentication scheme of the proxy server. Valid values are BASIC, DIGEST, NONE, NTLM, and NEGOTIATE. Default is BASIC.
• Proxy_AuthToken: The proxy authentication token.
• Proxy_SSLType: The SSL type of the proxy server. The allowed values are AUTO, ALWAYS, NEVER, TUNNEL. The default value is AUTO.

Firewall

• Firewall_Server: The IP address or host name of the firewall. Source is firewall_host.
• Firewall_Port: The port number of the firewall. Source is firewall_port.
• Firewall_User: The user Id used to authenticate with the firewall. Source is firewall_user.
• Firewall_Password: The password used to authenticate with the firewall. Source is firewall_password.
• Firewall_Type: The type of the firewall. Source is firewall_type. Valid values are NONE, TUNNEL, SOCKS4,and SOCKS5. Default is NONE.

Other

• Internalconfig#: Sets an internal configuration setting.'/>
• Charset: The charset of the exchanged/transferred data. Default is UTF-8' />
• HTTPVersion: Version of the HTTP protocol to use. Valid values are 1.0 and 1.1. Default is 1.1
• FollowRedirects: Whether or not to follow HTTP redirects. Valid values are TRUE and FALSE. Default is TRUE.
• SSLCert: The SSL certificate to be accepted from the server. Any other certificate will be rejected. This can take the form of a full PEM certificate, the path to a file containing the certificate, the public key, the MD5 thumbprint, or the SHA-1 thumbprint. If not specified any valid certificate will be accepted. Default is TRUSTED
• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The store type of the client certificate. Valid values are USER,MACHINE,PFXFILE,PFXBLOB,JKSFILE,JKSBLOB,PEMKEY_FILE,PEMKEY_BLOB,PUBLIC_KEY_FILE,PUBLIC_KEY_BLOB,SSHPUBLIC_KEY_BLOB,P7BFILE,P7BBLOB,SSHPUBLIC_KEY_FILE,PPKFILE,PPKBLOB,XMLFILE,and XMLBLOB
• SSLClientCertPassword: The password of the client certificate.
• SSLClientCertSubject: The subject of the client certificate. Using an asterisk will find the first certificate in the store.

Output Parameters

• ssl:issuer: The issuer of the SSL certificate.
• ssl:subject: The subject of the SSL certificate.
• statuscode: The HTTP status code returned from the request.
• content: The content of the HTTP response.
• cookie:*: The cookies returned with the response.
• allcookies: All of the cookies from the response, returned as a single string.
• header:*: The headers returned with the response.

Example:

    <api:set attr="URL" value="http://www.example.com/target">
    <api:set attr="method" value="POST">
    <api:set attr="postdata" value="abcd">
    <api:set attr="contenttype" value="text/plain">

    <api:call op="httpops.post" out="output">
      <api:set attr="temp.data" value="[output.content]">
    </api:call >
  

The fileRead operation ingests the file located at the specified URI and returns a feed with one line of the target file's content per item.

Required Parameters

The input item for the fileRead operation must set the following parameter:

• file: a URI pointing to a file.

Optional Parameters

The input item for the fileRead operation can optionally set the following parameter:

• encoding: the encoding to use when reading the file. The allowed values are determined by the JVM/OS being used. Values for encodings that are generally supported by most operating systems and JVMs include UTF-8, ASCII, BASE64, Hex, windows-1522, and ISO-8859-2. The default is UTF-8.

Output Parameters

The output items returned by the fileRead operation have the following parameter:

• data: the data from the input file.

Example

In the following example, the path to the file "example.txt" is provided in the input item, the operation is executed, and, for each line in example.txt, the "currentlinevalue" variable is set to the value of the current line.

<api:set attr="input.file" value="C:/Users/Public/example.txt" />
<!-- Passing in the input item -->
<api:call op="fileRead" in="input" out="result" >
  <api:set attr="fileOut.currentlinevalue" value="[result.data]" />
</api:call>

The fileWrite operation writes the specified data to the file at the specified URI.

Required Parameters

The input item for the fileWrite operation must set the following parameters:

• file: the URI pointing to the file that you want to write data to. If this file does not exist, the Sync App creates it when executing this operation.
• data: the data that you want to write to the specified file.

Optional Parameters

The input item for the fileWrite operation can optionally set the following parameters:

• force: when this parameter is set to true, if one or more of the folders in the path specified in the "file" parameter do not exist, the Sync App creates those missing directories. The allowed values are true and false. If this parameter is set to false and folders in the specified directory are missing, the Sync App does not perform the write operation and returns an error. The default is true.
• mode: specifies whether to truncate (overwrite the existing data in the file with the new data) or append (add the new data onto the end of the existing data in the file) when executing this operation. The default behavior is to truncate.
• encoding: the encoding to use when creating a new file. The allowed values are determined by your JVM/OS. The default is UTF-8.

Output Parameters

The output items returned by the fileWrite operation have the following parameters:

• file: the full path of the file that was written to.
• cdate: the modified date of the file that was written to.

Example

The following example adds the message "This is a sample value." to the file "new.txt".

<rsb:set item=inputitem attr=file value="C:/Users/Publish/new.txt"/>
<rsb:set item=inputitem attr=data value="This is a sample value."/>
<rsb:call op="fileWrite" in=inputitem out=outitem/>

This section details a selection of advanced features of the REST Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

For further information, see Query Processing.

Logging

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Client SSL Certificates

The REST Sync App also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
• SSLClientCertPassword: The password for the TLS/SSL client certificate.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Sync App provides standards-based access to REST by modeling local and remote data as relational tables, views, and stored procedures. CData has its own configuration language called API Script that you can use to mark up schemas. API Script hides the complexity of crafting requests to REST and parsing the feeds returned.

However, API Script is also a high-level programming language that enables you to control almost every aspect of data access and processing. Your schema is a script interpreted by the API Script engine.

You use keywords, attributes, items, operations, and feeds to write scripts:

• Attribute: The name part of a name-value pair, as in attribute="address", value="123 Pleasant Lane".
• Item: A related group of attribute-value pairs that describe an input or output. For example:
  

  Attribute="name" value="Bob"
  Attribute="address" value="123 Pleasant Lane"
  Attribute="phone" value="123-4567"

• Feed: A list of items: for example, a list of customers with their addresses and phone numbers.
• Operation: A generic name for methods that accept items as input and produce feeds as output.
• Keyword: An API Script statement, like api:set.

• Project columns over a feed and split feed items into rows.
• Use high-level programming constructs such as if/else statements and case statements to control execution flow.
• Call operations and define your own.
• Create and modify items, the inputs to an operation, and feeds, the operation's result.
• Process the feed resulting from an operation call by iterating through its items.

Feeds are composed of items, but in API Script items themselves are used for much more than the individual parts of a feed. They are also used to represent inputs to operations.

Declare Items

In API Script items are created, named, and given attribute values through the api:set keyword:

<api:set item="input" attr="mask" value="*.txt" />

However, an item named "input" is never declared. Instead, the item is created the first time you try to set an attribute on it. In the example above, if the input item did not already exist, it would be created and the mask attribute would be set on it.

Select Attribute Values

To reference an attribute, use the syntax item.attribute (e.g., "input.mask"). To query an attribute value, surround the attribute name in square brackets ([]). This instructs the interpreter that you want to evaluate the string instead of interpreting it as a string literal.

For example, consider the following code snippet:

<api:set item="item1" attr="attr1" value="value1"/>
<api:set item="item1" attr="attr2" value="item1.attr1"/>
<api:set item="item1" attr="attr3" value="[item1.attr1]"/>

The results are the following:

• item1.attr1 gets assigned the literal string value "value1".
• item1.attr2 gets assigned the literal string value "item1.attr1".
• item1.attr3 gets assigned the value "value1", because the string "[item1.attr1]" gets evaluated at run time as the attr1 attribute of item1.

Default Item

In API Script there is always an implicit, unnamed item on an internal stack of items, the default item. In the following two cases, the default item is commonly used to make scripts shorter and easier to write:

• Calling operations: The default item is the item passed by default to the operation called by your script if no other item is specified. This means that you can use api:set to write attributes to the default unnamed item and those attributes will be passed as input to the next operation called in the script. This is one way to provide an operation's required parameters.
• Processing the current item in the output of an operation or script: If you do not specify a variable name for the result of an operation, then inside the api:call block that invokes the operation, the default item will refer to the current item produced by the operation.

<api:set attr="path" value="." />

Named Items

In addition to items declared within the script, several built-in items are available in the scope of a script. Built-in, or special, items are available in API Script that provide an interface for accessing the connection string and the SQL query. These special items are useful for mapping inputs to data processing operations.

The following sections detail the special items.

Script Inputs (_input)

The input for a script can be read from the _input item. The SQL statement provides the input for table and stored procedure scehmas: In a SELECT statement, the _input item contains the columns or pseudo columns specified in the WHERE clause.

When you read values from the default item in a script, you are reading values from _input; likewise, attributes that you write to the default item are passed as parameters to operations along with the input to the script. Only the variables defined in the info block or in the script will be available in the _input item.

Note that inside an api:call block _input is no longer the default item and you must reference it by name if you need access to it.

Script Outputs (_out[n])

The current item in the feed produced by the api:call keyword can be accessed through the default item or a named special item, "_outX", where X is the level of nesting of api:call keywords. For example, if you are inside a single api:call keyword, the item's name will be "_out1". If you are inside three levels of nested api:call keywords, then it will be _out3.

Connection String Properties (_connection)

The _connection item has the connection properties of the Sync App. The Sync App does not perform any validation of the connection string properties. It is left to the schema author to decide which properties are required, how they are used, etc.

In addition to providing access to the connection properties, the _connection item can be used to store pieces of data in a connection. For example, it may be necessary to store session tokens that can be reused while a connection lasts. You can use the api:set keyword to store any values, as shown in the code example below:

<api:set attr="_connection._token" value="[oauth.connection_token]"/>

Note: You can set only the attributes that start with the _ symbol. This is done so that the connection properties set by the user cannot be overriden.

SQL Clauses (_query)

The _query item has the following attributes that describe the query that was issued to the Sync App:

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'

INSERT INTO Account(account_name, account_type) SELECT customer_name, customer_type FROM Customer#TEMP

[account_name], [account_type]

DELETE FROM Account WHERE EXISTS SELECT customer_name, customer_type FROM Customer#TEMP

[customer_name], [customer_type]

Preserve Attr Values Across API Calls (global)

The global item is useful when there are multiple different values that need to be saved and preserved in a complex multi-request flow across multiple pages, outside of just the paging value in rows@next. Any attrs that are set in this item will continue to be accessible when processing the next page of data in the query process.

Value formatters enable you to generate new values with specific formatting. You can use value formatters to perform string, date, and math operations on values.

The general format for invoking formatters is

[ item.attribute | formatter(parameters) | formatter (parameters) | ...]

Examples

• In the following snippet any "*" character in the myid attribute's value is replaced by "-", and the resulting value is assigned to input1.id.
  

  <api:set attr="input1.id" value="[myid | replace('*', '-')]"/>

• Below, two value formatters are chained with the pipe ("|") character. In the example, only .log files are pushed from the operation.
  

  <api:call op="fileListDir">
    <api:check attr="name" value="[filename|tolower | endswith('.log')]">
      <api:push/>
    </api:check>
  </api:call>

[attr | allownull()]

Returns NULL if the attribute does not exist or the value if it does.

[attr | arrayfind(substring)]

Returns the index at which the string is found in the attribute array. The index is 1 based.

• searchstring: The string to search for in the original value.

[attr | base64decode()]

Converts the attribute value to a base 64 decoded string.

[attr | base64encode()]

Converts the attribute value to a base 64 encoded string.

[attr | capitalize()]

Returns the original attribute value with only its first character capitalized.

[attr | capitalizeall()]

Returns the original attribute value with the first character of all words capitalized.

[attr | center(integer_width[, character])]

Returns the attribute value centered in a string of width specified by the first parameter. Padding is done using the fillchar specified by the second parameter.

• width: The total width of the output string.
• character: The optional character used for padding. If not specified this defaults to a space.

[attr | contains(value[, ifcontains][, ifnotcontains])]

Returns true (or ifcontains) if the attribute value contains the parameter value, false (or ifnotcontains) otherwise.

• value: The string to find from the attribute value.
• ifcontains: The optional value returned if the attribute value contains the parameter value.
• ifnotcontains: The optional value returned if the attribute value does not contain the parameter value.

[attr | count(substring)]

Returns the number of occurrences in the attribute value of a substring specified by the first parameter.

• substring: The substring to search for in the attribute value.

[attr | currency([integer_count])]

Returns the numeric value formatted as currency.

• count: The optional number specifying how many places to the right of the decimal are displayed. The default is 2.

[attr | decimal([integer_count])]

Returns the numeric value formatted as a decimal number.

• count: The optional number that indicates how many places to the right of the decimal are displayed. Default is 2.

[attr | def([notexists][, exists])]

Checks for the existence of an attribute and returns the specified parameter value if it does not.

• notexists: The optional value to return if the attribute value does not exist.
• exists: The optional value to return if the original attribute exists. If not specified, the original value of the attribute is returned.

[attr | empty(value)]

Returns the specified value if the attribute value is empty, otherwise the original attribute value.

• value: The value that will be used if the attribute is empty.

[attr | endswith(substring[, iftrue][, iffalse])]

Determines whether the attribute value ends with the specified parameter. Returns true (or iftrue) if the attribute ends with the value and false (or iffalse) if not.

• substring: The string expected at the end.
• iftrue: The optional value returned if the attribute value ends with the parameter value.
• iffalse: The optional value returned if the attribute value does not end with the parameter value.

[attr | equals(value[, ifequals][, ifnotequals])]

Compares the attribute value with the first parameter value and returns true (or ifequals) if they are equal and false (or ifnotequals) if they are not.

• value: The string to compare with the attribute value.
• ifequals: The optional value returned if the attribute value equals the value represented by the first parameter.
• ifnotequals: The optional value returned if the attribute value does not equal the value represented by the first parameter.

[attr | extendtabs([integer_width])]

Replaces all tab characters found in the attribute value with spaces. If the tab size specified by the parameter is not given, a default tab size of 8 characters is used.

• width: The optional tab width, defaults to 8 if not specified.

[attr | expr(expression)]

Evaluates the mathematical expression.

• expression: The expression.

[attr | find(substring[, integer_startindex])]

Returns the lowest zero-based index at which the substring is found in the attribute value.

• substring: The string to search for in the attribute value.
• startindex: The optional index at which to start the search.

[attr | getlength()]

Returns the number of characters in the attribute value.

[attr | hmacshamd5([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHAMD5 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | hmacsha1([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHA1 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | hmacsha256([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHA256 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | hmacsha512([key], [toBase64, isBase64Encoded])]

Encrypt a string with the passed-in key and HmacSHA512 algorithm.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.
• isBase64Encoded: The optional boolean value that specifies whether the input is base 64 encoded. Default is false.

[attr | ifequal(value[, ifequals][, ifnotequals])]

Compares the attribute value with the first parameter value and returns true (or ifequals) if they are equal and false (or ifnotequals) if they are not.

• value: The string to compare with the attribute value.
• ifequals: The optional value returned if the attribute value equals the value represented by the first parameter.
• ifnotequals: The optional value returned if the attribute value does not equal the value represented by the first parameter.

[attr | ifmatches(value[, ifmatch][, ifnotmatch])]

Returns true (or ifmatch) if the attribute value matches the first parameter, otherwise false (or ifnotmatch).

• value: The value that will be compared with the attribute value.
• ifmatch: The optional value returned if the attribute value matches the parameter value.
• ifnotmatch: The optional value returned if the attribute value does not match the parameter value.

[attr | iftrue([iftrue][, iffalse])]

Checks the attribute value and returns true (or iftrue) if true and false (or iffalse) if false.

• iftrue: The optional value returned if the attribute value is true.
• iffalse: The optional value returned if the attribute value is false.

[attr | implode([separator])]

Implodes multiple values to a string separated by a separator.

• separator: The optional separator.

[attr | insert(integer_index, string)]

Inserts the specified string at the specified index.

• index: The zero-based index of the position in the original value where the new string should be inserted.
• string: The string to insert into the original value.

[attr | isalpha([ifalpha][, ifnotalpha])]

Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.

• ifalpha: The optional value returned if the attribute value is alphabetic.
• ifnotalpha: The optional value returned if the attribute value is not alphabetic.

[attr | isalphabetic([ifalpha][, ifnotalpha])]

Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.

• ifalpha: The optional value returned if the attribute value is alphabetic.
• ifnotalpha: The optional value returned if the attribute value is not alphabetic.

[attr | isalphanum([ifalphanum][, ifnotalphanum])]

Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.

• ifalphanum: The optional value returned if the attribute value contains only alphabetic or numeric characters.
• ifnotalphanum: The optional value returned if the attribute value contains nonalphabetic or nonnumeric characters.

[attr | isalphanumeric([ifalphanum][, ifnotalphanum])]

Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.

• ifalphanum: The optional value returned if the attribute value contains only alphabetic or numeric characters.
• ifnotalphanum: The optional value returned if the attribute value contains nonalphabetic or nonnumeric characters.

[attr | isdigit([ifnum][, ifnotnum])]

Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.

• ifnum: The optional value returned if the attribute value is numeric.
• ifnotnum: The optional value returned if the attribute value is not numeric.

[attr | islower([iflower][, ifnotlower])]

Returns true (or iflower) if all letters in the attribute value are lowercase and there is at least one character that is a letter, false (or ifnotlower) otherwise.

• iflower: The optional value returned if the attribute value is lowercase.
• ifnotlower: The optional value returned if the attribute value is not lowercase.

[attr | isnumeric([ifnum][, ifnotnum])]

Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.

• ifnum: The optional value returned if the attribute value is numeric.
• ifnotnum: The optional value returned if the attribute value is not numeric.

[attr | isspace([ifspace][, ifnotspace])]

Return true (or ifspace) if there are only white-space characters in the attribute value and there is at least one character, false (or ifnotspace) otherwise.

• ifspace: The optional value returned if the attribute value is a space.
• ifnotspace: The optional value returned if the attribute value is not a space.

[attr | isupper([ifupper][, ifnotupper])]

Returns true (or ifupper) if all letters in the attribute value are uppercase and there is at least one character that is a letter, false (or ifnotupper) otherwise.

• ifupper: The optional value returned if the attribute value is uppercase.
• ifnotupper: The optional value returned if the attribute value is not uppercase.

[attr | join([separator])]

Implodes multiple values to a string separated by a separator.

• separator: The optional separator.

[attr | jsonescape()]

Converts the attribute value to a JSON-escaped, single-line string.

[attr | just(integer_width[, character])]

Returns the attribute value left-justified in a string of length specified by the first parameter. Padding is done using the fillchar specified by the second parameter.

• width: The total width of the output string.
• character: The optional character used for padding. The default is a space.

[attr | lfind(substring[, integer_startindex])]

Returns the lowest zero-based index at which the substring is found in the attribute value.

• substring: The string to search for in the attribute value.
• startindex: The optional index at which to start the search.

[attr | ljust(integer_width[, character])]

Returns the attribute value left-justified in a string of length specified by the first parameter. Padding is done using the fillchar specified by the second parameter.

• width: The total width of the output string.
• character: The optional character used for padding. The default is a space.

[attr | lsplit(delimiter, integer_index)]

Splits the string represented by the attribute value into tokens delimited by the first parameter and returns the token at the index specified by the second parameter; counts from the left.

• delimiter: The string used as a separator for splitting the string into tokens.
• index: The index of the token requested where the first token is at index 1.

[attr | match(pattern[, index][, option])]

Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.

• pattern: The regular expression pattern to match.
• index: The optional numbered index of the match to return. The default is 0.
• option: The optional comma-separated list of regular expression options. Some of the commonly used options are IgnoreCase, Multiline, Singleline, and IgnorePatternWhitespace.

[attr | md5hash([converttobase64])]

Computes the MD5 hash of the attribute value.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.

[attr | notequals(value[, notequals][, equals])]

Compares the attribute value with the first parameter value. Returns true (or notequals) if they are not equal and false (or equals) if they are.

• value: The string to compare with the attribute value.
• notequals: The optional value returned if the attribute value does not equal the value represented by the first parameter.
• equals: The optional value returned if the attribute value equals the value represented by the first parameter.

[attr | nowhitespace()]

Removes the white space from the string represented by the atttribute value.

[attr | percentage([integer_count])]

Returns the numeric value formatted as a percentage.

• count: The optional number that indicates how many places to the right of the decimal are displayed.

[attr | regex(pattern[, index][, option])]

Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.

• pattern: The regular expression pattern to match.
• index: The optional numbered index of the match to return. The default is 0.
• option: The optional comma-separated list of regular expression options. Some of the commonly used options are IgnoreCase, Multiline, Singleline, and IgnorePatternWhitespace.

[attr | regexmatch(pattern[, index][, option])]

Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.

• pattern: The regular expression pattern to match.
• index: The optional numbered index of the match to return. The default is 0.
• option: The optional comma-separated list of regular expression options. Some of the commonly used options are IgnoreCase, Multiline, Singleline, and IgnorePatternWhitespace.

[attr | regexreplace(search, replacewith[, integer_startat])]

Replaces all occurrences of the regular expression pattern found in the attribute value with replacewith.

• search: The regular expression to search with.
• replacewith: The text to replace the match with.
• startat: The optional character index at which to start replacement. Default is 0.

[attr | remove(integer_index[, integer_count])]

Deletes characters from the attribute value; begins at the zero-based index specified by the first parameter.

• index: The position to begin deleting the characters.
• count: The optional number of characters to delete. If not provided all characters starting from the specified index will be deleted.

[attr | replace(oldvalue, newvalue[, ishex])]

Replaces all occurrences of the first parameter in the string represented by the attribute value with the value of the second parameter.

• oldvalue: The string to be replaced.
• newvalue: The string to replace all occurrences of the first parameter.
• ishex: The optional boolean value specifying whether the first parameter is a hex representation of a character to replace. Default is false.

[attr | rfind(substring[, integer_startindex])]

Returns the highest zero-based index at which the substring is found in the attribute value.

• substring: The string to search for in the original value.
• startindex: The optional index at which to start the search.

[attr | rjust(integer_width[, character])]

Returns the right-justified attribute value in a string of length specified by the second parameter. Padding is done using the fillchar specified by the first parameter.

• width: The total width of the output string.
• character: The optional character used for padding, if not specified this defaults to a space.

[attr | rsplit(delimiter, integer_index)]

Splits the string represented by the attribute value into tokens delimited with the first parameter and returns the token at the index specified by the second parameter; counts from the right.

• delimiter: The string used as a separator for splitting the string into tokens.
• index: The index of the token requested where the first token is at index 1.

[attr | sha1hash([converttobase64])]

Computes the SHA-1 hash of the attribute value.

• encodetobase64: The optional boolean value that specifies whether to convert the result into a base 64 encoded string. Default is true.

[attr | substring(integer_index[, integer_length])]

Returns a substring of the attribute value; starts at the index specified by the parameter and counts to the right.

• index: The zero-based index at which the substring starts from the right.
• length: The optional length of characters from the start index. The substring to the end is returned if length is not specified.

[attr | split(delimiter, integer_index)]

Splits the string represented by the attribute value into tokens delimited by the first parameter and returns the token at the index specified by the second parameter; counts from the left.

• delimiter: The string used as a separator for splitting the string into tokens.
• index: The index of the token requested where the first token is at index 1.

[attr | sqlescape()]

Converts the attribute value to an SQL-escaped, single-line string.

• dbtype: The database type to encode. The allowed values are SQL or SQLite. Default is SQL.

[attr | startswith(substring[, iftrue][, iffalse])]

Returns true (or iftrue) if the attribute value starts with the specified parameter, false (or iffalse) otherwise.

• substring: The string expected at the begining.
• iftrue: The optional value returned if the attribute value starts with the parameter value.
• iffalse: The optional value returned if the attribute value does not start with the parameter value.

[attr | striphtml()]

Returns the string with any HTML markup removed.

[attr | substring(integer_index[, integer_length])]

Returns a substring of the attribute value; starts at the index specified by the parameter.

• index: The zero-based index at which the substring starts.
• length: The optional length of characters from the start index. A substring to the end of the string is returned if length is not specified.

[attr | toalpha()]

Returns only the letters in a string.

[attr | toalphanum()]

Returns only the alphanumeric characters in a string.

[attr | tolower()]

Returns the string represented by the attribute value with all characters converted to lowercase.

[attr | toupper()]

Returns the string represented by the attribute value with all characters converted to uppercase.

[attr | trim()]

Trims leading and trailing white space from an attribute.

[attr | trimend()]

Trims trailing white space from an attribute.

[attr | trimstart()]

Trims leading white space from an attribute.

[attr | truncate(integer_count)]

Truncates the attribute value to the number of characters specified by the parameter.

• count: The number of characters in the resulting string.

[attr | urlencode([isurlcomponent])]

Converts the attribute value to a URL encoded string.

• isurlcomponent: The optional boolean value that specifies whether the attribute value is URL component or full URL. If set to false, it will only encode content after the first '?' character, but ignore all '=' and '&' characters. Default is true, an URL component.

[attr | urldecode()]

Converts the attribute value to a URL decoded string.

[attr | wordwrap([integer_width][, break][, cut][, wrapexp])]

Wraps a string to a given number of characters.

• width: The optional number of characters at which the string will be wrapped.
• break: The optional break parameter to be used to break the line.
• cut: The optional boolean value that specifies whether to wrap the string at or before the specified width. Default is false.
• wrapexp: The optional regex expression to be used as a breakable characters. Default is space.

[attr | print([delim])]

Returns a string with all the values of the attribute concatenated using the specified delimiter.

• delim: The optional delimiter to separate values with. Default is a comma.

[attr | xmldecode()]

Converts the attribute value to a XML decoded string.

[attr | xmlencode()]

Converts the attribute value to a XML encoded string.

[attr | compare([value][, inputformat])]

Returns a signed number indicating the relative values of dates represented by the attribute value and parameter value.

• value: The optional string representation of the date that will be compared with the attribute value. Default is now.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | date([outputformat])]

Returns the current system date and time in the format specified by the parameter if one was provided.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | dateadd([interval][, integer_value][, outputformat][, inputformat])]

Returns a string value of the datetime that results from adding the specified number interval (a signed integer) to the specified date part of the date.

• interval: The optional interval you want to add. Specify year, month, day, hour, minute, second, or millisecond.
• value: The optional number of intervals you want to add. Can either be positive for dates in the future or negative for dates in the past.
• outputformat: The optional output format specifier. Valid specifiers include d(short date pattern), D(long date pattern), f(long date/short time pattern), F(long date/time pattern), g(general short date/time pattern), G(general short date/long time pattern), r or R(RFC1123 pattern), s(sortable date/time pattern), t(short time pattern), T(long time pattern), file(Windows file time), MM/dd/yy, etc.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | datediff([interval][, value][, inputformat])]

Returns the difference (in units specified by the first parameter) between now and the date specified by the second parameter.

• interval: The optional interval you want the result in. Specify day, hour, minute, second, or millisecond.
• value: The optional string representation of the date to compare with attribute value. Default is now.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | day([inputformat])]

Returns the day component, expressed as a value between 1 and 31, of the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | dayofweek([inputformat])]

Returns the day of week for the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | dayofyear([inputformat])]

Returns the day of year expressed as a value between 1 and 366 for the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | filetimenow()]

Returns the date and time for the current system file time.

[attr | fromfiletime([outputformat])]

Converts a valid file time to a valid datetime value formatted as specified by the parameter if one was provided.

• outputformat: The optional output format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | isleap([ifleap][, ifnotleap])]

Returns true (or ifleap) if the 4-digit year represented by the attribute value is a leap year, false (or ifnotleap) otherwise.

• ifleap: The optional value returned if the attribute value is a leap year.
• ifnotleap: The optional value returned if the attribute value is not a leap year.

[attr | month([inputformat])]

Returns the month component expressed as a value between 1 and 12 of the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | now([outputformat])]

Returns the current system date and time in the format specified by the parameter if one was provided.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | todate([outputformat][,inputformat])]

Returns the date specified by the attribute value formatted as specified by the parameter if one was provided.

• outputformat: The optional output format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), file (Windows file time), MM/dd/yy, etc.
• inputformat: The optional input format specifier. Default is autodetected.

[attr | tofiletime([inputformat])]

Converts a valid datetime to a valid file time value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | tomonth()]

Returns the name of the month for the numeric value specified by the attribute value.

[attr | toutc([outputformat][, inputformat])]

Returns the date specified by the attribute value converted to UTC and formatted as specified by the outputformat parameter if one was provided.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g (general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), and file (Windows file time), MM/dd/yy, etc.

[attr | utcnow([outputformat])]

Returns the current system UTC date and time.

• outputformat: The optional format specifier. Valid specifiers include d (short date pattern), D (long date pattern), f (long date/short time pattern), F (long date/time pattern), g(general short date/time pattern), G (general short date/long time pattern), r or R (RFC1123 pattern), s (sortable date/time pattern), t (short time pattern), T (long time pattern), file (Windows file time), MM/dd/yy, etc.

[attr | weekday([inputformat])]

Returns the day of the week as an integer where Monday is 0 and Sunday is 6.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | year([inputformat])]

Returns the year component of the date represented by the attribute value.

• inputformat: The optional input format specifier. Default is autodetected.

[attr | abs()]

Returns the absolute value of the numeric attribute value.

[attr | add([value])]

Returns the sum of the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to add to the specified attribute value. Default is 1.

[attr | and(value)]

Returns the AND of two values. The values provided on each side must be 1/0, yes/no or true/false.

• value: The boolean value to compare by.

[attr | ceiling()]

Returns the smallest integer greater than or equal to a numeric attribute value.

[attr | div([value])]

Returns the result of dividing the numeric attribute value by the specified value of the parameter.

• value: The optional numeric value to divide the numeric attribute value by. Default is 2.

[attr | divide([value])]

Returns the result of dividing the numeric attribute value by the specified value of the parameter.

• value: The optional numeric value to divide the numeric attribute value by. Default is 2.

[attr | floor()]

Returns the largest integer less than or equal to the numeric attribute value.

[attr | greaterthan(value[, ifgreater][, ifnotgreater])]

Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.

• value: The numeric value to compare with the attribute value.
• ifgreater: The optional value returned if the attribute value is greater than the parameter value.
• ifnotgreater: The optional value returned if the attribute value is not greater than the parameter value.

[attr | isbetween(integer_lowvalue, integer_highvalue[, ifbetween][, ifnotbetween])]

Returns true (or ifbetween) if the attribute value is greater than or equal to the first parameter value and less than or equal to the second parameter value, false (or ifnotbetween) otherwise.

• lowvalue: The lower bound of the range to check.
• highvalue: The higher bound of the range to check.
• ifbetween: The optional value returned if the attribute value is greater than or equal to the first parameter value and less than or equal to the second parameter value.
• ifnotbetween: The optional value returned if the attribute value is less than the first parameter value or greater than the second parameter value.

[attr | isequal(value[, ifequal][, ifnotequal])]

Returns true (or ifequal) if the attribute value is equal to the parameter value, false (or ifnotequal) otherwise.

• value: The numeric value to compare with the attribute value.
• ifequal: The optional value returned if the attribute value is equal to the parameter value.
• ifnotequal: The optional value returned if the attribute value is not equal to the parameter value.

[attr | isgreater(value[, ifgreater][, ifnotgreater])]

Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.

• value: The numeric value to compare with the attribute value.
• ifgreater: The optional value returned if the attribute value is greater than the parameter value.
• ifnotgreater: The optional value returned if the attribute value is not greater than the parameter value.

[attr | isless(value[, ifless][, ifnotless])]

Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.

• value: The numeric value to compare with the attribute value.
• ifless: The optional value returned if the attribute value is less than the parameter value.
• ifnotless: The optional value returned if the attribute value is not less than the parameter value.

[attr | lessthan(value[, ifless][, ifnotless])]

Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.

• value: The numeric value to compare with the attribute value.
• ifless: The optional value returned if the attribute value is less than the parameter value.
• ifnotless: The optional value returned if the attribute value is not less than the parameter value.

[attr | mathadd([value])]

Returns the sum of the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to add to the specified attribute value. Default is 1.

[attr | mathmod(value)]

Returns the modulus of the numeric attribute value divided by the specified parameter value.

• value: The number to divide the attribute value by.

[attr | mathpow([value])]

Returns the numeric attribute value raised to the power specified by the parameter value.

• value: The optional power to raise the attribute value to. Default is 2.

[attr | mathround([integer_value])]

Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.

• value: The optional number of decimal places. Default is 2.

[attr | mathsub([value])]

Returns the difference between the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to subtract the attribute value by.

[attr | modulus(value)]

Returns the modulus of the numeric attribute value divided by the specified parameter value.

• value: The number to divide the attribute value by.

[attr | multiply([value])]

Returns the result of multiplying the numeric attribute value with the specified value of the parameter.

• value: The optional numeric value to multiply the numeric attribute value by. Default is 2.

[attr | or(value)]

Returns the OR of two values. The values provided on each side must be 1/0, yes/no or true/false.

• value: The boolean value to compare by.

[attr | pow([value])]

Returns the numeric attribute value raised to the power specified by the parameter value.

• value: The optional power to raise the attribute value to. Default is 2.

[attr | rand([integer_value])]

Returns a random integer between 0 and the parameter value.

• value: The optional value that limits the highest possible random integer. Default is 100.

[attr | random([integer_value])]

Returns a random integer between 0 and the parameter value.

• value: The optional value that limits the highest possible random integer. Default is 100.

[attr | round([integer_value])]

Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.

• value: The optional number of decimal places. Default is 2.

[attr | sqrt()]

Returns the square root of the numeric attribute value.

[attr | subtract([value])]

Returns the difference between the numeric attribute value and the value specified by the parameter.

• value: The optional numeric value to subtract the attribute value by.

While Value Formatters handle simple data manipulation and formatting, Operations are used for more complex work within API Script.

Post data to a URL using the HTTP POST method.

Required Parameters

• url: The destination URL

Optional Parameters

• timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. Default is 60
• proxy_server: The IP address or host name of the proxy server
• proxy_auto: Whether the proxy should be detected from Windows system settings. Allowed values: TRUE, FALSE. Default is FALSE
• proxy_port: The port number of the proxy server
• proxy_user: The user ID for the proxy server
• proxy_password: The password for the proxy server
• proxy_authscheme: Authentication scheme of the proxy server. Allowed values: BASIC, DIGEST, PROPRIETARY, NONE, NTLM. Default is BASIC
• proxy_authtoken: Proxy authentication token
• proxy_ssltype: SSL/TLS type of the proxy. Allowed values: AUTO, ALWAYS, NEVER, TUNNEL. Default is AUTO
• firewall_server: IP or host name of the firewall
• firewall_port: Port of the firewall
• firewall_user: Username for the firewall
• firewall_password: Password for the firewall
• firewall_type: Type of firewall. Allowed values: NONE, TUNNEL, SOCKS4, SOCKS5. Default is NONE
• kerberoskdc: Kerberos KDC setting, used when authscheme is Negotiate
• kerberosrealm: Kerberos realm setting, used when authscheme is Negotiate
• internalconfig#: Sets an internal configuration setting
• charset: Charset of exchanged data. Default is UTF-8
• httpversion: HTTP version. Allowed values: 1.0, 1.1. Default is 1.1
• cookie:*: Any cookies to add to the request
• followredirects: Whether to follow HTTP redirects. Allowed values: TRUE, FALSE. Default is TRUE
• sslcert: SSL/TLS certificate to accept. Can be PEM, file path, public key, thumbprint, or TRUSTED
• sslclientcert: Name of the client certificate store
• sslclientcerttype: Store type for the client certificate. Allowed values: USER, MACHINE, PFXFILE, etc
• sslclientcertpassword: Password for the client certificate
• sslclientcertsubject: Subject of the client certificate. An asterisk finds the first in the store
• user: Username for authentication
• password: Password for authentication
• authscheme: Authorization mechanism. Allowed values: BASIC, DIGEST, NTLM. Default is BASIC
• version: OAuth version. Allowed values: Disabled, 1.0, 2.0. Default is Disabled
• token: OAuth request token
• token_secret: OAuth request token secret (1.0 only)
• sign_method: Signature method for OAuth 1.0. Allowed values: HMAC-SHA1, PLAINTEXT. Default is HMAC-SHA1
• client_id: Client ID (OAuth 1.0 only)
• client_secret: Client secret (OAuth 1.0 only)
• header:name#: Name of a custom header
• header:value#: Value of a custom header
• paramname#: Name of a parameter to include
• paramvalue#: Value of a parameter to include
• formencoding: Encoding for parameters. Allowed values: URLENCODED, FORMDATA. Default is URLENCODED
• postdata: Raw data to include in the POST. Use file://path to include a file
• contenttype: Content type of the POST. Default is application/x-www-form-urlencoded
• logfile: Full path to log file for request/response data (requires verbosity)
• verbosity: Log verbosity, 1-5

Output Attributes

• ssl:issuer: Issuer of the SSL/TLS certificate
• ssl:subject: Subject of the SSL/TLS certificate
• http:statuscode: HTTP status code returned
• http:content: Content of the HTTP response
• cookie:*: Cookies returned with the response
• http:allcookies: All response cookies as a single string
• header:*: Headers returned with the response

Example

<api:set item="http" attr="URL" value="http://example.com/people"/>
<api:set item="http" attr="paramname#1" value="name"/>
<api:set item="http" attr="paramvalue#1" value="Charlie"/>
<api:set item="http" attr="paramname#2" value="age"/>
<api:set item="http" attr="paramvalue#2" value="28"/>

<api:call op="httpPost" in="http">
  <api:set attr="output.data" value="[http:content]"/>
</api:call>

<api:set attr="output.filename" value="httpContent.txt"/>
<api:push item="output"/>

Get a document from the Web using the HTTP GET method

Required Parameters

• url: The URL to retrieve

Optional Parameters

• timeout: The timeout, in seconds, for the operation to complete. Zero (0) means no timeout. Default is 60
• proxy_server: The IP address or host name of the proxy server
• proxy_auto: Whether the proxy should be detected from Windows system settings. Allowed values: TRUE, FALSE. Default is FALSE
• proxy_port: The port number of the proxy server
• proxy_user: The user ID for the proxy server
• proxy_password: The password for the proxy server
• proxy_authscheme: Authentication scheme of the proxy server. Allowed values: BASIC, DIGEST, PROPRIETARY, NONE, NTLM. Default is BASIC
• proxy_authtoken: Proxy authentication token
• proxy_ssltype: SSL/TLS type of the proxy. Allowed values: AUTO, ALWAYS, NEVER, TUNNEL. Default is AUTO
• firewall_server: IP or host name of the firewall
• firewall_port: Port of the firewall
• firewall_user: Username for the firewall
• firewall_password: Password for the firewall
• firewall_type: Type of firewall. Allowed values: NONE, TUNNEL, SOCKS4, SOCKS5. Default is NONE
• kerberoskdc: Kerberos KDC setting, used when authscheme is Negotiate
• kerberosrealm: Kerberos realm setting, used when authscheme is Negotiate
• internalconfig#: Sets an internal configuration setting
• charset: Charset of exchanged data. Default is UTF-8
• httpversion: HTTP version. Allowed values: 1.0, 1.1. Default is 1.1
• cookie:*: Any cookies to add to the request
• followredirects: Whether to follow HTTP redirects. Allowed values: TRUE, FALSE. Default is TRUE
• sslcert: SSL/TLS certificate to accept. Can be PEM, file path, public key, thumbprint, or TRUSTED
• sslclientcert: Name of the client certificate store
• sslclientcerttype: Store type for the client certificate. Allowed values: USER, MACHINE, PFXFILE, JKSFILE, JKSBLOB, PEMKEY_FILE, PEMKEY_BLOB, PUBLIC_KEY_FILE, PUBLIC_KEY_BLOB, SSHPUBLIC_KEY_BLOB, P7BFILE, P7BBLOB, SSHPUBLIC_KEY_FILE, PPKFILE, PPKBLOB, XMLFILE, XMLBLOB
• sslclientcertpassword: Password for the client certificate
• sslclientcertsubject: Subject of the client certificate. An asterisk finds the first in the store
• user: Username for authentication
• password: Password for authentication
• authscheme: Authorization mechanism. Allowed values: BASIC, DIGEST, NTLM. Default is BASIC
• version: OAuth version. Allowed values: Disabled, 1.0, 2.0. Default is Disabled
• token: OAuth request token
• token_secret: OAuth request token secret (1.0 only)
• sign_method: Signature method for OAuth 1.0. Allowed values: HMAC-SHA1, PLAINTEXT. Default is HMAC-SHA1
• client_id: Client ID (OAuth 1.0 only)
• client_secret: Client secret (OAuth 1.0 only)
• header:name#: Name of a custom header
• header:value#: Value of a custom header
• ifmodifiedsince: If-Modified-Since value (for example: Sat, 25 Feb 2006 04:11:47 GMT)
• localfile: If specified, write the results of the GET to this file
• logfile: Full path to log file for request/response data (requires verbosity)
• verbosity: Log verbosity, 1-5

Output Attributes

• ssl:issuer: Issuer of the SSL/TLS certificate
• ssl:subject: Subject of the SSL/TLS certificate
• http:statuscode: HTTP status code returned
• http:content: Content of the HTTP response
• cookie:*: Cookies returned with the response
• http:allcookies: All response cookies as a single string
• header:*: Headers returned with the response

Statements in API Script are defined using keywords, XML elements prefixed with "api:". Keywords include the usual features of a programming or scripting language, such as conditionals, loops, and flow control. However, API Script also includes special keywords tailored specifically for feed manipulation and generation; for example, api:call, api:enum, and api:set.

Most keywords take parameters that define or affect their behavior. Parameters are specified as XML attributes of the keyword element. The required and optional parameters, along with code examples, for each keyword are explained in this section.

Keyword Scope

In API Script, some keywords introduce scope; that is, some keywords can be nested inside the bodies of other keywords, and how they are nested is meaningful to the language:

• You can define instructions for an iteration within a keyword's scope: For example, the body of an api:call keyword is executed for each item in the feed produced by the operation invoked by api:call. Because of this, keywords nested inside api:call are executed for each iteration to produce each item in the feed.
• Scope associates matching pairs of keywords; for example, the api:else keyword defines an alternate execution path for a conditional keyword like api:equals or api:check. In API Script, a certain api:else keyword is associated with a conditional statement when it is nested as a direct child of the conditional statement. Thus, the api:else keyword must not be defined outside of the scope introduced by a conditional keyword.
• Keywords can introduce new items (variables) within their scope. For example, the api:call keyword will introduce a default output item representing the item being iterated over in the feed being generated.
• Keywords can inject additional attributes into the default item that provide more information within their scope. These attributes are called Control Attributes when described in this document. They are easily recognized because they start with the _ character; for example, _attr, _index, _value, etc.

The api:break keyword can be used to break out of iterations of api:call or api:enum. It can also be used to break out of the entire script if it is used outside the scope of any other API Script keywords.

Parameters

None

Control Attributes

None

Examples

Break out of api:enum. The example below lists the first two attributes of the item foo:

<api:set attr="foo.attr1" value="value1"/>
<api:set attr="foo.attr2" value="value2"/>
<api:set attr="foo.attr3" value="value3"/>

<api:enum item="foo">
[_index]: [_attr] has the value [_value]. 
<api:equals attr="_index" value="2"> 
<api:break/>
</api:equals>
</api:enum>

See Also

• api:continue: Continue to the next item.
• api:enum: Loop over the values of an item, list, range, or multivalued attribute.
• api:call: Loop over items returned by an operation.

The api:call keyword is used to call operations. Valid operations are the following:

• Built-in operations installed along with Sync App assemblies located in the bin subfolder of the application
• Operations you write yourself in .NET or Java and place in the bin subfolder of the application

Operations take items as input and return feeds as output. The scope of api:call is executed for every item in the feed returned from the call. Within the scope of api:call, you can inspect and modify the attributes in the item returned. You can then provide these attributes as inputs to another operation, thus forming an operation pipeline. Or, you can push out the item to the output.

The Output Item Stack and the Default Item

Each time an api:call is encountered, a new item is pushed onto an internal stack of items. The item on the top of this stack is the default item. This is the item that provides input to api:call when an item name is not explicitly specified in the input to the call.

The called operation writes attributes to the default item, and api:push pushes the default item to the output. When you push an item, only the attributes from the default item, at the top of the stack, are pushed.

The default item is swept clean after an item has been iterated over, and the api:call then works on the next item. This means that you will not be able to read a value set in a previous iteration of an api:call in the next iteration. To retain values across an iteration, copy them to a named item with api:set.

You can refer to the default item as _ or explicitly as _out1, _out2, _out[n], where N is the depth of the stack. This enables you to read all the values available at this point in the execution process; the lookup process starts from the default item and continues through the stack until a value is found.

In the example below, you can see how items are pushed to the top of the stack with each call and how the default item changes within the scope of each call.

<api:call op="operation1"> 
The default item here is _out1 
A push here would push the attributes from _out1 
The input item used to call operation2 is also _out1 
<api:call op="operation2"> 
The default item here is _out2 
A push here will push the attributes from _out2 
If there was another api:call here the input item used would be _out2 
_out2 is swept clean here for the next iteration 
</api:call> 
The default item here is again _out1 
A push here would again push the attributes from _out1 
The input item at this level is again _out1 
_out1 is swept clean here for the next iteration 
</api:call> 

Specify Input to an Operation

There are 3 ways to provide input to a call:

• The default item: If an input item is not specified, the called operation reads input values from the default item. You can use the api:set keyword to set values in the default item so that they are processed by the called operation.
  

  <api:set attr="mask" value="*.txt"/>
  <api:set attr="path" value="C:\\"/>
  <api:call op="fileListDir">
  ... 
  </api:call>

• An explicit input item: Instead of using the default item, you can use the in parameter to explicitly specify the items you want to use as input to the operation. If you choose to do so the default item is no longer used and the input values are read from the input items specified and, as shown below, the query string passed to the operation.
  

  <api:set attr="mask" value="*.* -- Will be ignored --"/>
  <api:set attr="myinput.mask" value="*.txt"/>
  <api:set attr="myinput.path" value="C:\\"/>
  <api:call op="fileListDir" in="myinput">
  ... 
  </api:call>

• Query string parameters: You can also use query string notation to specify input to an operation. The attributes specified as part of the query string are given precedence. Thus, if there is a name clash between attributes specified in the query string and those in the input item, the ones in the query string are preferred. However, the other attributes of the input item are still accessible. The example below shows the syntax to specify input in the query string:
  

  <api:set attr="myinput.mask" value="*.txt -- will be overridden --"/> 
  <api:set attr="myinput.path" value="C:\\"/> 
  <api:call op="fileListDir?mask=*.rsb" in="myinput">
  ...
  </api:call>

Parameters

• op: The name of the operation to be called.
• in[put]: A list of items used as input when invoking the operation. Attributes are looked up from left to right in the input items specified.

• out[put]: The item where the output attributes are placed. Within the api:call scope, the current output item can be retrieved using the item name specified here. You can also use _out[n] or _pipe to refer to the call's results.

  Note: Attributes set within a call are not available outside the scope of the call. This is because each iteration of the call deletes the attributes from the previous iteration; at the end of the call nothing is left. To access an attribute outside the scope of a call, use api:set to explicitly copy the attribute to the item you want to use outside the call.

• item: The name of the item to be used for both input and output.
• sep[arator]: The separator used to separate multiple input items. The default is a comma.
• ignoreprefix: A comma-separated list of prefixes that are ignored when found. Some operations return attributes with names of the form "prefix:name" (e.g., api:operation and sql:company). In some situations, you may want to treat both prefix:name and name as the same attribute.
• page and pagesize: The subset of items that will be iterated through. Used to enable paging of the operation or feed results. For example, if you specify page="2" and pagesize="5", the api:call keyword iterates only through items 6 to 10 of the resulting feed.

Control Attributes

• _index: The index of the item currently being iterated over by api:call.
• _op: The name of the operation being called. This is helpful when the operation name is not known until execution.
• _separator: The separator used to separate multiple input items.

Examples

Call an operation using the default item as input:

<api:set attr="path" value="C:\myfiles"/>
<api:call op="fileListDir">
  <api:push/>
</api:call>

See Also

• api:first: Write elements to be executed only for the first iteration of the call.
• api:catch: Catch errors within a call.
• api:continue: Continue to the next iteration.

The api:case keyword is used with the api:select keyword. The api:case keyword consists of a block of API Script that is executed if the value in api:select matches the value in api:case.

Parameters

• value: The pattern or value to compare against the value specified in api:select.
• match: The type of matches to find to determine whether the case statement should be executed. The default value is "exact", which requires an exact match of the value. Other supported types are "regex", for regular expression matching, and "glob", which supports a simple expression model similar to the one used in file-name patterns (e.g., *.txt). The .NET edition of the Sync App uses the .NET Framework version of regular expression matching. The Java edition uses Java regular expression constructs.

Control Attributes

None

Examples

Display an icon based on the condition. The api:case elements match "CompanyA" and "CompanyB" in the company_name attribute and if any occurrences are found take the action associated with that case.

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

See Also

• api:select: Write a multiselect API Script block.
• api:default: Write a default case for api:select.

The api:catch keyword is used to create an exception-handling block in a script. In addition to api:try, you can contain an api:catch block within any of the following keywords, the scope of which serves as an implicit api:try section:

• api:call
• api:script

Parameters

• code: The code parameter allows you to selectively catch exceptions. To catch all exceptions, use the character *.

Control Attributes

• _code: The code of the caught exception.
• _desc: A short description of the caught exception.
• _details: More information about the exception, if available.

Examples

Throw and catch an exception. Inside an api:call, an APIException is thrown and caught. Inside the scope of the keyword, the api:ecode and api:emessage attributes are added to the current item and pushed out.

<api:call op="...">
  <api:throw code="myerror" description="thedescription" details="Other Details."/>
  <api:catch code="myerror">
    <api:set attr="api:ecode" value="[_code]"/>
    <api:set attr="api:emessage" value="[_description]: [_details]"/>
    <api:push/>
  </api:catch>
</api:call>

<api:catch code="*">
  An exception occurred. Code: [_code], Message: [_desc]
</api:catch>

See Also

• api:try: Define a scope to catch exceptions.
• api:throw: Force an exception.

The api:check keyword can be used with or without a value parameter. Without a value parameter, it is used to ensure that an attribute is present in an item and that it is not a null string before the body of the api:check is executed.

If a value parameter is specified, the api:check body executes only if the expression evaluates to true. Other values are considered false. The evaluation is case insensitive.

Like other simple conditionals in API Script, it can be paired with an api:else keyword. Note that, unlike api:equals, api:check does not throw an exception if the attribute does not exist in the item.

Parameters

• item: The item in which to check the attribute. Specifying an item is not required. If no item is specified, the default output item is used instead.
• attr: The name of the attribute to check. This parameter is required.
• value: An expression that evaluates to true or false. For example, the result of a formatter that returns true or false.
• action: The action to execute if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Examples

<api:check attr="_input.In_Stock">
...
</api:check>

See Also

• api:exists: Check if an attribute exists.
• api:equals: Check for equality.
• api:notequals: Check for inequality.

The api:continue keyword can be used to move to the next iterations of api:call or api:enum.

Parameters

Control Attributes

Examples

Produce a feed that lists only the files in a directory. The api:continue keyword is used to skip over items representing a directory.

<api:call op="fileListDir">
  <api:equals attr="file:isdir" value="true">
    <api:continue/>
  </api:equals>
  <api:push/>
</api:call>

List the values of single-valued attributes. All multivalued attributes are skipped by using api:continue.

<api:set attr="foo.multiattr#" value="value1"/>
<api:set attr="foo.multiattr#" value="value2"/>
<api:set attr="foo.attr2"      value="value3"/>
<api:enum item="foo">
  <api:notequals attr="_count" value="1">
    <api:continue/>
  </api:notequals>
  [_index]: [_attr] has the value [_value] <!-- only evaluated for attr2 -->
</api:enum>

See Also

• api:break: Break out of api:call or api:enum iterations.
• api:enum: Iterate over the values of an item, attribute, list, or range.
• api:call: Call scripts, operations, or feeds.

The api:default keyword is used with the api:select keyword. The api:default keyword consists of a block of API Script that is executed if the value in api:select does not match any of the values in api:case.

Parameters

Control Attributes

Examples

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

<p>

See Also

• api:select: Write a multiselect API Script block.

The api:else keyword is used to execute an alternate block of code when a test like api:exists or api:match fails. It can also be used to execute an alternate block of code within an api:call when the call fails to produce any output items.

Unlike other languages API Script requires the api:else statement to be within the scope of the test it belongs to.

Parameters

Control Attributes

Examples

<api:call op="fileListDir" out="out">
  <api:null attr="filename">
    <api:set attr="title" value="Unnamed File"/>
    <api:else>
      <api:set attr="title" value="[filename]"/>
    </api:else>
  </api:null>
  <api:push title="[title]">
  [out.*]
  </api:push>
</api:call> 

See Also

• api:exists: Check if an attribute exists.
• api:equals: Check for equality.

The api:enum keyword can be used to enumerate over the attributes within an item, a delimited list, a supplied range of values, and the values of a multivalued attribute. The body of api:enum is executed for each element of the set that is being iterated upon.

Parameters

• item: The name of the item whose attributes you want to iterate over.
• attr: The expression to match that specifies which attributes are iterated over. For example, "api:*". You can also provide this parameter to iterate over the values of a multivalued attribute.
• prefix: The prefix based on which the item is enumerated.
• expand: The boolean value that specifies how api:enum behaves when multivalued attributes are encountered. If expand is set to true, the body of api:enum will be executed once for each value of the attribute. If false, all values will be concatenated in a single string value, and only a single iteration will be performed. By default, api:enum will not expand all values.
• sep[arator]: The separator to be used to concatenate values of a multivalued attribute if the expand parameter is false. Additionally, separator is used to tokenize the values in a list if listseparator is not defined. The default value is the new-line character "\n".
• list: The list of separated values to enumerate over. For example, if the list of values is "violet, indigo, blue, green, yellow, orange, red" and the separator is a ',' the scope of api:enum is executed for each color in the rainbow.
• range: The range of numbers or characters to enumerate over in ascending or descending order; for example, "a..z", "Z..A".
• recurse: Whether to enumerate over nested attributes. The default is true.

Control Attributes

• _attr: The name of the attribute being iterated over. When you are iterating over the values of a multivalued attribute, the attribute name will be the same except that it will also have the index as part of the name. The index is separated from the name by the hash symbol (#). For example, name#1, name#2, etc.
• _index: The index at which the attribute appears within an item or the position of the list element currently being enumerated over.
• _value: The value of the attribute being iterated over. For a multivalued attribute, the expand and separator parameter settings determine whether _value is a reference to one attribute value or a reference to all attribute values.
• _count: The number of values in an attribute or in a list.
• _separator: The separator used to separate the values in a list.

Examples

Display the attribute names and attribute values of the item named "input":

<api:set item="input" attr="Greeting" value="Hello" />
<api:set item="input" attr="Goodbye" value="See ya" />
<api:enum item="input">
  [_attr] is [_value]
  <br/>
</api:enum> 

goodbye is See ya greeting is Hello

<api:set attr="colors" value="violet, indigo, blue, green, yellow, orange, red"/>
<api:enum list="[colors]" separator=",">
  [_value] 
</api:enum>

<api:enum range="a..z">
  [_value] 
</api:enum>
<api:enum range="Z..A">
  [_value] 
</api:enum>
<api:enum range="1..25">
  [_value] 
</api:enum>

<api:set attr="foo.email#1" value="[email protected]"/>
<api:set attr="foo.email#2" value="[email protected]"/>
<api:enum attr="foo.email" expand="true">
    ([_index]) [_attr] -> [_value]
</api:enum>

(1) email#1 -> [email protected] (2) email#2 -> [email protected]

See Also

• api:break: Break out of api:call or api:enum iterations.
• api:continue: Skip an api:call or api:enum iteration.
• api:first: Provide special processing in the first iteration.
• api:last: Provide special processing in the last iteration.

The api:equals keyword compares the value of an attribute to a reference value. Unlike api:check, the api:equals keyword will throw an exception if the specified item does not contain the specified attribute. If the specified attribute exists and its value matches, then the comparison will succeed.

Note: Both api:equals and api:check expect the name of the attribute whose value will be compared with a given value. If you need to compare two values, you can use api:select instead. For example:

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

Parameters

• item: The item in which to compare the attribute. Specifying an item is not required. If no item is specified, the default output item is used instead.
• attr: The name of the attribute to compare.
• case: Whether to use case-sensitive or case-insensitive comparison. This defaults to case-sensitive comparison; to use case-insensitive comparison, set the case parameter to "ignore".
• value: The value to compare the attribute with.
• action: The action to perform if equality is met. Allowed values: break, continue.

Control Attributes

Example

Like other conditional keywords, the body of api:equals may also contain an api:else keyword, which will be executed if the values do not match. The following lists all files except .err files:

<api:call op="fileListDir">
  <api:equals attr="file:extension" value=".err">
  <api:else>
    <api:push/>
  </api:else>
  </api:equals>
</api:call>

See Also

• api:select: Choose between more than one alternative.
• api:notequals: Create a block that is executed when equality is not met.

The api:exists keyword checks that an attribute has a value in the specified item. The api:notnull keyword is a synonym for api:exists.

Parameters

• item: The item to check. If not specified, the default output item is used.
• attr: The name of the attribute to check.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir" output="out">
  <api:exists attr="filename">
    <api:set attr="title" value="[filename]"/>
    <api:else>
      <api:set attr="title" value="Unnamed File"/>
    </api:else>
  </api:exists>
  <api:push title="[title]">
  [out.file:*] 
  </api:push>
</api:call> 

See Also

• api:else: Create a block which is executed if the condition is not satisfied.

The api:first keyword is used to execute a section of a script for only the first iteration of an api:call or api:enum keyword. It is a convenient way to generate headings or to inspect the first item of a feed before going through the rest of the feed.

During the first iteration, the api:first body executes before any other code within the api:call or api:enum, regardless of where the api:first is located within the api:call or api:enum body. As a reminder of this, it is recommended to locate the api:first at the top of the api:call or api:enum body.

If the scope has no items, then neither api:first nor api:last are executed.

Parameters

Control Attributes

Examples

Create an HTML table from a feed where each item is represented as a row:

<api:call op="listCustomers">
  <api:first>
    <table>
    <thead>
      <api:enum item="customer">
        <td>[_attr]</td>
      </api:enum>
    </thead>
  </api:first>
    <tr>
      <api:enum item="customer">
        <td>[_value]</td>
      </api:enum>
    </tr>
  <api:last>
    </table>
  </api:last>
</api:call>

See Also

• api:last: Execute a code block only for the last iteration.

The api:finally keyword is used to execute a section of a script after control leaves an api:call, api:try, or api:script statement. It is a convenient way to clean up the formatting of generated documents.

Parameters

Control Attributes

Examples

The api:finally block is executed when an unhandled exception occurs. This can be used to ensure that tags are closed:

<table>
<api:call op="listCustomers" out="customer">
  <api:first>
    <thead>
        <api:enum item="customer">
          <td>[_attr]</td>
        </api:enum>
    </thead>
  </api:first>
  <tr>
      <api:enum item="customer">
        <td>[_value]</td>
      </api:enum>
  </tr>
  <api:finally>
  </table> <!-- ensure tags are still closed -->
  </api:finally>
</api:call>

See Also

• api:try: Define a scope to catch exceptions.
• api:call: Loop over items returned by an operation.
• api:script: Create a script block.

You can use the api:if keyword to evaluate expressions that can contain items, attributes, and values. The scope of the keyword is executed if the specified expression evaluates to true.

Parameters

• exp: The expression to evaluate. You can make string, date, and numeric comparisons.
• attr: The name of the attribute to compare the value of. The value of an attribute can be checked for a matching value or for the values null or notnull.
• value: The value to compare with the value of the attribute specified by attr.
• item: The item that contains the attribute being compared.
• operator: The name of the operator to compare the operands specified by attr and value. Allowed values: null, notnull, hasvalue, equals, equalsignorecase, notequals, lessthan, and greaterthan. Default: notnull.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

None

Examples

Evaluate a simple comparison of two values:

<api:if exp="[attr] == 10">

<api:set attr="attr1" value="value1"/>
<api:set attr="attr2" value="value2"/>
<api:if attr="attr1" value="[attr2]" operator="notequals"> <!-- Evaluates to true -->
<api:else>
False
</api:else>
True
</api:if>

<api:set attr="exists" value="true"/>
<api:if attr="exists"> <!-- Evaluates to true -->
[exists]
</api:if>

See Also

• api:exists: Check that an attribute has a value in the specified item.
• api:equals: Create a block that is executed when equality is met.

The api:include keyword is used to include API Script from other files. Like traditional includes in other programming languages, api:include is replaced by the contents of the file specified in the file parameter.

Parameters

• file | document: The name of the file to be included.

Control Attributes

Examples

Import certain attributes (companyname, copyright) in each script without duplication:

<api:include file="globals.rsb"/> 
[companyname] 
[copyright] 
<api:call ...>

The api:info keyword is used to describe the metadata for scripts. This information is used by the Sync App to implement basic error checking on user input and to set default values. The api:info keyword can contain the following:

• Column definitions
• Input parameters the script expects
• Output the script produces

Table Parameters

The following parameters can be defined for the table itself:

• desc[ription]: The description for the table. If a description is not provided, the name of the script is used.
• title: The title of the script. If a title is not provided, the name of the script is used.
• methods: The HTTP methods that can be executed against this script. SELECT, INSERT, UPDATE, and DELETE correspond to HTTP GET, POST, PUT/PATCH/MERGE, and DELETE, respectively.
• url: The URL of the script author.
• keywords: The keywords that describe the script.

Input, Output, and Column Parameters

The api:info keyword has additional parameters contained within its scope that define columns as well as script inputs and outputs. Note these parameters are not API Script keywords, but additional information for api:info.

attr

Other optional attributes provide more information about the column and enable the Sync App to represent these columns correctly in various tools.

<attr 
  name="Name"
  xs:type="string"
  other:xPath="name/full"
  readonly="true"    
  columnsize="100"
  desc="The name of the person." 
/> 

• name: The alphanumeric string that defines the name of the column.
• xs:type: The data type of the column. The string, int, double, datetime, and boolean types are supported.
• other: Attributes prefixed with 'other:' that provide extra information. These other properties can be operation specific. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.
• desc[ription]: A short description of the column.
• key: Whether the column is part of the primary key in the table.
• readonly: Whether the column can be updated. Allowed values are true and false.
• req[uired]: Whether the column must be specified in an insert. Allowed values are true and false.
• def[ault]: The default value for the column if none is specified.
• values: A comma-separated list of the valid values for the column. If specified, the engine will throw an error if the specified column value does not match one of the allowed values.
• reference[s]: The foreign key to the primary key of another table. The foreign key is specified with the following syntax: table.key. For example: "Employees.EmployeeId".
• columnsize: The maximum character length of a string or the precision of a numeric column. The precision of a numeric column is the number of digits.
• scale | decimaldigits: The scale of a decimal column. The scale is the number of digits to the right of the decimal point.
• isnullable: Indicates whether the column accepts null values. Note that this does not prevent sending or receiving a null value.

input

Input elements can be used in schemas for both tables and stored procedures.

The attributes of this parameter provide users with more information about the input and allow the engine to perform rudimentary checks.

In stored procedure schemas, one or more input elements are used to describe the inputs of the stored procedure. In table schemas an input element defines a pseudo column, a column that can only be used in the WHERE clause.

For XML data sources, pseudo columns cannot contain an XPath.

<input
  name="name"
  desc="Example of an input parameter"
  default="defValue"
  req="true"
  values="value1, value2, value3"
  xs:type="string" 
/>

• name: The name of the input. An alphanumeric string that may additionally contain the following: "#" denotes that the input can have multiple values, "myprefix:*" denotes a set of inputs with the same prefix, and a value of "*" denotes arbitrary input parameters.
• desc[ription]: A short description of the input.
• xs:type: The data type of the input. The string, int, double, datetime, and boolean types are supported.
• def[ault]: The default value to be used when no input value is supplied in the script call.
• key: Whether the input is a primary key.
• req[uired]: Whether the input is required. The engine will throw an error if the required input is not supplied and there is no default value defined. Allowed values are true and false.
• values: A comma-separated list of the allowed values for the input. If specified, the engine will throw an error if the specified input does not match one of the allowed values.
• alias: The alias of the input.
• other: Attributes prefixed with "other:" that provide extra information. These other properties can be operation specific.

output

Output parameters describe the output of the operation. However, they are ignored by the Sync App. Thus, the output of the operation can be entirely independent of what is defined in the info block.

<output  name="Id" 
         desc="The unique identifier of the record."
         xs:type="string"
         other:xPath="content/properties/Id" 
/>

• name: The name of the output. "myprefix:*" denotes a set of outputs with the same prefix, and a value of "*" denotes arbitrary output parameters.
• xs:type: The data type of the output. The string, int, double, datetime, and boolean types are supported.
• desc[ription]: A short description of the output.
• columnsize: The maximum character length of a string or the precision of a numeric output. The precision is the number of digits.
• other: Attributes prefixed with 'other:' that provide extra information about the output. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.

Examples

<api:info title="NorthwindOData" desc="Parse an XML document (NorthwindOdata.xml) into rows.">
  <attr name="ID"           xs:type="int" key="true" other:xPath="content/properties/ID" />
  <attr name="EmployeeID"   xs:type="int"            other:xPath="content/properties/EmployeeID" />
  <attr name="Name"         xs:type="string"         other:xPath="content/properties/Name" />
  <attr name="TotalExpense" xs:type="double"         other:xPath="content/properties/TotalExpense" />
  <attr name="HireDate"     xs:type="datetime"       other:xPath="content/properties/HireDate" />
  <attr name="Salary"       xs:type="int"            other:xPath="content/properties/Salary" />
</api:info> 

The following describes the metadata for a stored procedure that searches the Web using the Bing Search API:

<api:info title="SearchWeb"    desc="Use Bing to search the Web."> 
  <output name="Id"          xs:type="string"    other:xPath="content/properties/ID"          />
  <output name="URL"         xs:type="string"    other:xPath="content/properties/Url"         />
  <output name="Title"       xs:type="string"    other:xPath="content/properties/Title"       />
  <output name="Description" xs:type="string"    other:xPath="content/properties/Description" />
  <output name="DisplayURL"  xs:type="datetime"  other:xPath="content/properties/DisplayUrl"  />

  <input name="SearchTerms"    />
</api:info>

See Also

• api:script: Create a script block.
• api:call: Call scripts or operations.
• api:set: Set attributes in an item.

API Script keywords within the scope of the api:last keyword will only be executed after the last item is encountered and only if there were items returned by api:call or api:enum.

An api:last statement is executed only after the last item is processed; it maintains access to the last item in the feed. If the scope has no items, then neither api:first nor api:last are executed.

Parameters

Control Attributes

Examples

Create an HTML table from a feed where each item is represented as a row:

<api:call op="listCustomers">
  <api:first>
    <table>
    <thead>
      <api:enum item="customer">
        <td>[_attr]</td>
      </api:enum>
    </thead>
  </api:first>
  <tr>
    <api:enum item="customer">
      <td>[_value]</td>
    </api:enum>
  </tr>
  <api:last>
    </table>
  </api:last>
</api:call>

See Also

• api:first: Execute a code block only for the first iteration.

The api:map keyword is used to map attributes in one item to attributes in another item. Attributes are read from one item and written to another with the new names specified in the map string. The api:map keyword does not clear the destination item: It simply adds new attributes to it. Or, if an attribute exists in the destination item, it is overwritten, and other attributes remain as they were.

Parameters

• to: The name of the item into which attributes are to be written.
• from: The name of the item from which attributes are to be read.
• map: A list of attribute names that specify the attribute names in the destination item, followed by the attribute names in the source item. For example, you can map attributes with one prefix to attributes with another prefix by using the syntax below:
  

  customer:* = soap:*

  Any characters that are not valid attribute names are ignored and are used to demarcate the end of a name.

Control Attributes

• This keyword does not have any control attributes.

Examples

Map three attributes: The map is a succession of the "to" attribute name followed by the "from" attribute name.

<api:set item="item1" attr="var1" value="x"/>
<api:set item="item1" attr="var2" value="y"/>
<api:set item="item2" attr="attr1" value="z"/>
<api:map to="item2" from="item1" map="attr1=var1, attr2=var2"/>

You can map multiple attributes from items with one prefix to items with a different prefix. This is useful in changing the prefix from Sync App prefixes (for example, soap:*) to business domain prefixes (for example, customer:*). The following example creates a mapping from all soap:* attributes in the item soapout to attributes prefixed with customer:* in the item customer:

<api:map from="soapout" to="customer" map="customer:* = soap:*"/>

<api:map from="copyfrom" to="copyto" map="* = *"/>

See Also

• api:set: Set the attributes of an item.

The api:match keyword is similar to the api:equals keyword; however, it permits complex matching rules.

Parameters

• pattern: The pattern to match.

• type: The type of matches to find. The default value is "exact", which requires an exact match of the value. In this case api:match is identical to api:equals. Other supported types are "regex", for regular expression matching, and "glob", which supports a simple expression model similar to the one used in file-name patterns, for example, *.txt.

  The .NET edition of the Sync App uses the .NET Framework version of regular expression matching. The Java edition uses Java regular expression constructs.

• value: The value to match.

Control Attributes

None

Examples

Check for floating point numbers using a regex pattern. If the pattern is matched then the item will be pushed out.

<api:match pattern="\[-+\]?\[0-9\]*\.?\[0-9\]*" type="regex" value="-3.14">
  <api:push/>
</api:match>

See Also

• api:select: Compare multiple values.

The api:notequals keyword verifies that the attribute does not match the specified value. It has a similar behavior to the api:equals keyword.

Parameters

• item: The item in which to compare the attribute. Specifying an item is not required; if no item is specified, the default output item is used instead.
• attr: The name of the attribute to compare.
• value: The reference value to compare the attribute with.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir">
  <api:notequals attr="file:extension" value=".err">
    <api:push/>
  </api:notequals>
</api:call>

See Also

• api:equals: Check for equality.
• api:else: Create a block which is executed if the condition is not satisfied.

The api:null keyword checks that an attribute does not exist in the specified item.

Parameters

• item: The item to check. If not specified, the default output item is used.
• attr: The name of the attribute to check.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir" output="out">
  <api:null attr="filename">
    <api:set attr="title" value="Unnamed File"/>
    <api:else>
      <api:set attr="title" value="[filename]"/>
    </api:else>
  </api:null>
  <api:push title="[title]">
  [out.file:*] 
  </api:push>
</api:call>

See Also

• api:else: Create a block which is executed if the condition is not satisfied.

The api:notnull keyword checks that an attribute has a value in the specified item. The api:exists keyword is a synonym for api:notnull.

Parameters

• item: The item to check. If not specified, the default output item is used.
• attr: The name of the attribute to check.
• action: The action to perform if the expression evaluates to true. Allowed values: break, continue.

Control Attributes

Example

<api:call op="fileListDir" output="out">
  <api:notnull attr="filename">
      <api:set attr="[title]" value="[filename]"/>
    <api:else>
      <api:set attr="[title]" value="Unnamed file"/>
    </api:else>
  </api:notnull> 
  <api:push title="[title]">
  [out.file:*]
  </api:push>
</api:call>

See Also

• api:else: Create a block which is executed if the condition is not satisfied.

The api:push keyword pushes an item into the output feed of the script. If there are no api:push elements in your script, no output items will result from it.

Parameters

• item: The item to push into the feed. If not present, the item at the top of the stack will be pushed.
• title: The title for the feed item.
• desc[ription]: The description for the feed item. If none is provided, the scope of the api:push keyword will be used as the value of this attribute. The scope of the api:push keyword may contain other keywords, HTML tags, etc. Everything is evaluated as a template and is used to set the description.
• op: The name of the operation to call and then push the resulting items. For example:
  

    <api:push op="myOp"/>    
    

  This is a shorthand for the following:
  

  <api:call op="myOp">
    <api:push/>
  </api:call>

• enc[oding]: The encoding of the description. By default, the Sync App pushes the description as escaped XML. You can choose to encode the description as CDATA to include unescaped XML by setting this parameter to "cdata".

Control Attributes

Example

<api:call op="fileListDir?mask=*.log">
  <api:push>
    The .log file contains details about the transmission, including any error messages.
  </api:push>
</api:call>

The api:script keyword can be used to create script blocks that respond to SQL statements.

Parameters

• method: The HTTP method (GET, POST, PUT/PATCH/MERGE, or and DELETE) used to invoke the script. Specify multiple methods in a comma-separated list. These methods correspond to SELECT, INSERT, UPDATE, and DELETE queries, respectively.

Control Attributes

None

Examples

Call two operations that manipulate remote REST. This example uses an OData API, the odata.org Northwind reference service. In the example below, the first block will be executed only when the script is accessed using the POST method (an INSERT statement is executed), while the second block will be executed only when the script is accessed using the GET method (a SELECT statement is executed).

<api:script method="POST">
  <api:set attr="sql.rec:name" value="[_input.name]"/>
  <api:set attr="sql.rec:address" value="[_input.address]"/>
  <api:call op="sqliteInsert" in="sql">
    <api:push/>
  </api:call>
</api:script>
<api:script method="GET">
  <api:set attr="sql.query" value="SELECT * FROM [sql.table];"/>
  <api:call op="sqliteQuery" in="sql">
    <api:push/>
  </api:call>
</api:script>

The api:select keyword is similar to a switch-case block in other programming languages and can be used to create complex conditional statements. The body of api:select can contain one or more api:case keywords and one api:default keyword.

The value in api:select is matched with those specified in api:case. The body of the api:case statement contains the keywords and statements to be executed if the value specified matches the value in the api:select keyword.

The body of the api:default statement will be executed only if none of the api:case statements result in a match. The api:default keyword has no parameters and can appear only once in an api:select.

Parameters

• value: The value to compare with those specified in api:case statements.
• attr: The attribute whose value is compared with values specified in api:case statements.

Control Attributes

None

Examples

Set the icon depending on the company name. The api:case elements match "CompanyA" and "CompanyB" in the company_name attribute, and, if any occurrences are found, take the action associated with that case.

<api:select value="[company_name]">
  <api:case value="CompanyA">
    <img src="http://www.companya.com/favicon.ico" />
  </api:case>
  <api:case value="CompanyB">
    <img src="http://www.companyb.com/favicon.ico" />
  </api:case>
  <api:default>
    <img src="http://www.myhosting.com/generic.ico"/>
  </api:default>
</api:select>

<p>

See Also

• api:case: Write cases for api:select.
• api:default: Write a default case for api:select.

The api:set keyword sets a value in an attribute. If an attribute is set in an item that does not exist, the item is automatically created.

There are two ways to set a value using api:set. You can set the value parameter or, for large values that are multiline and complex, you can set the value using the scope of the keyword itself.

Parameters

• item: The item parameter is used to specify the item in which the attribute is set. Specifying an item is not required. If an item is not specified, the default item is used.
• attr: The name of the attribute. You can also specify the item using the dot notation (e.g., item.prefix:attr). Note that a complete attribute name has both a prefix and an attribute name, but the prefix is not required.
• value: The value to be assigned to the attribute. If this parameter is not provided, the entire body of the api:set keyword is used as the value. This is convenient for setting long or multiline values.
• copyfrom: The attributes from the item specified in this parameter are copied to the item specified by the item parameter.

Control Attributes

Examples

Use the scope of the keyword to set a value to the message attribute of the item named "input":

<api:set item="input" attr="message">
  Dear [name],
  You have won a cruise trip to Hawaii.
  Please confirm your acceptance by [date].
  Thanks, [sales]
</api:set>

See Also

• api:unset: Remove an attribute from an item.
• api:setm: Set multiple attributes with only one keyword.

The api:setc keyword enables you to add static text without escaping the special characters in API Script, such as square brackets. While special characters can be escaped with a backslash, this keyword provides a shortcut. For example, this keyword can be used to easily set an XPath.

Parameters

• item: The item in which to set the attribute.
• attr: The name of the attribute.
• value: The value to be assigned to the attribute. If this parameter is not provided, the entire body of the api:setc keyword is used as the value. This is convenient for setting long or multiline values.
• copyfrom: The attributes from the item specified in this parameter are copied to the item specified by the item parameter.

Control Attributes

None

Examples

Set an unescaped XPath:

<api:setc attr="xpath" value="/root/book[1]/@name" />

The api:setm keyword is a shorthand for api:set that can be used to perform multiple sets with just one keyword.

Each line, separated by \r\n, is a separate set operation. Multiline values can be specified with three single quotes ('''), as in Python.

The first equals sign ("=") separates the attribute name from the value. This means that attribute values can contain spaces. However, leading and trailing spaces are ignored. Quotes can be used to include leading or trailing spaces, as shown in the examples.

Parameters

• item: The item in which the attributes are set. Specifying an item is not required. If an item is not specified, the default item is used.

Control Attributes

None

Examples

Use the scope of the keyword to set contact attributes:

<api:setm>
name = ContactName 
company = ContactCompany
address = 600 Market Street
includespace = " This string has a leading space"
</api:setm>

See Also

• api:set: Set attributes in an item.

The api:throw keyword is used to raise an error (exception) from within a script.

Parameters

• code: A string value used to identify the source or meaning of the exception. This parameter is required.
• desc[ription]: An optional parameter that specifies a short message describing the error condition.
• details: An optional parameter that can specify additional data useful to diagnose the error condition.

Control Attributes

None

Example

The example below explicitly defines both the error code and description:

<api:throw code="myerror" desc="thedescription" />

See Also

• api:try: Define a scope for catching exceptions.
• api:catch: Catch thrown exceptions and define exception processing.

The api:try and api:catch keywords are used to create an exception-handling block in a script. If any keyword inside the api:try body throws an APIException, the Sync App will look for a matching api:catch keyword inside the same scope and will execute the catch body.

Parameters

None

Control Attributes

None

Example

Throw and catch an exception. Inside the scope of the keyword, api:ecode and api:emessage attributes are added to the current item and pushed out.

<api:call op="...">
  <api:try>
    <api:throw code="myerror" description="thedescription" details="Other Details."/>
    <api:catch code="myerror">
      <api:set attr="api:ecode" value="[_code]"/>
      <api:set attr="api:emessage" value="[_description]: [_details]"/>
      <api:push/>
    </api:catch>
  </api:try>
</api:call>

See Also

• api:catch: Process thrown exceptions.
• api:throw: Force an exception.

The api:unset keyword is used to delete attributes from an item or delete the item itself.

Parameters

• item: The item from which the attribute is to be removed. If you do not specify an item, the default output item is used. This parameter can also be used to remove an item.
• attr: The attributes to delete from the item. You can use a glob mask to delete more than one parameter, for example, "*.foo".

Control Attributes

Examples

Remove an attribute from an item before it is pushed out:

<api:call op="fileListDir">
  <api:unset attr="file:size"/>
  <api:push/>
</api:call>

See Also

• api:set: Set attributes in an item.

You can use api:validate to throw an error if a required value is not provided.

Parameters

• item: The item that contains the attribute to be validated.
• attr: The attribute to be validated. If the attribute does not exist, an error is thrown.
• code: The error code to be thrown if validation fails. Default: "api:validate".
• desc: The error description to be thrown if validation fails. By default a message is thrown that the specified attribute is required.

Examples

Throw a more specific message if the _query item does not contain the Id attribute:

<api:validate attr="_query.Id"   desc="The Id is required to delete."/>

See Also

• api:throw: Raise an error (exception) from within a script.

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

Connection

AWS Authentication

Azure Authentication

Keycloak Authentication

SSO

JSON and XML

CSV

OAuth

JWT OAuth

Kerberos

SSL

SSH

Firewall

Proxy

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

The type of authentication to use when connecting to remote services.

Remarks

The following options are available:

• AccessKey: Set this to authenticate with the storage key associated with your Azure account.
• AwsCredentialsFile: Set to use a credential file for authentication.
• AwsTempCredentials: Set this to leverage temporary security credentials alongside a session token to connect.
• AzureAD: Set this to perform Azure Active Directory OAuth authentication.
• AzureMSI: Set this to automatically obtain Managed Service Identity credentials when running on an Azure VM.
• AzureServicePrincipal: Set this to authenticate as an Azure Service Principal.
• AzureServicePrincipalCert: Set this to authenticate as an Azure Service Principal using a Certificate.
• AzureStorageSAS: Set this to authenticate with Shared Access Signature (SAS).
• Basic: Basic user/password authentication.
• Digest: Uses HTTP Digest authentication with User and Password.
• GCPInstanceAccount: When running on a GCP virtual machine, the provider can authenticate using a service account tied to the virtual machine.
• IAMSecretKey: Uses AccessKey and SecretKey to authenticate to the Oracle Cloud Storage.
• Negotiate: Kerberos authentication.
• None: No authentication is used.
• OAuth: Uses OAuth with a standard user account. The specific flow is determined by the InitiateOAuth, and OAuthVersion must be set to determine what version of OAuth is used.
• OAuthClient: Uses OAuth2 with the client credentials grant type. OAuthClientId and OAuthClientSecret are the credentials. OAuthVersion must be set to 2.0.
• OAuthJWT: Uses OAuth2 with the JWT bearer grant type. OAuthJWTCertType and OAuthJWTCert determine what certificate the JWT is signed with. OAuthVersion must be set to 2.0.
• OAuthPassword: Uses OAuth2 with the password grant type. User and Password are the credentials. OAuthVersion must be set to 2.0.
• OAuthPKCE: Uses OAuth2 with the authorization code grant type and PKCE extension. OAuthClientId is the credential. OAuthVersion must be set to 2.0.
• SFTP: The precise authentication method is controlled using the SSHAuthMode property. See this property's documentation for further information.

The access key used to authenticate to REST. This value is accessible from your security credentials page.

Remarks

User is used with AccessKey to authenticate the user against the REST server.

Your account secret key. This value is accessible from your security credentials page.

Remarks

Your account secret key. This value is accessible from your security credentials page depending on the service you are using.

The API Key used to identify the user to IBM Cloud.

Remarks

Access to resources in the REST REST API is governed by an API key in order to retrieve token. An API Key can be created by navigating to Manage --> Access (IAM) --> Users and clicking 'Create'.

The authenticating user, required with the Password for authentication to the server..

Remarks

This property varies, depending on the value of the AuthScheme and the type of connection.

Connection Type: FTP(S)

• AuthScheme=Basic: The FTP(S) server password.

Connection Type: HDFS/HDFS Secure

• AuthScheme=Negotiate: The HDFS instance password.

Connection Type: HTTP(S)

• AuthScheme=Basic: The password associated with the HTTP stream.
• AuthScheme=Digest: The password associated with the HTTP stream.
• AuthScheme=OAuthPassword: The password associated with the HTTP stream.

Connection Type: SharePoint SOAP

• AuthScheme=Basic: The SharePoint account password.

The authenticating user's password, required with the User name for authentication to the server.

Remarks

This property varies, depending on the value of the AuthScheme and the type of connection.

Connection Type: FTP(S)

• AuthScheme=Basic: The FTP(S) server password.

Connection Type: HDFS/HDFS Secure

• AuthScheme=Negotiate: The HDFS instance password.

Connection Type: HTTP(S)

• AuthScheme=Basic: The password associated with the HTTP stream.
• AuthScheme=Digest: The password associated with the HTTP stream.
• AuthScheme=OAuthPassword: The password associated with the HTTP stream.

Connection Type: SharePoint SOAP

• AuthScheme=Basic: The SharePoint account password.

The edition of SharePoint being used. Set either SharePointOnline or SharePointOnPremise.

Remarks

The edition of SharePoint being used. Set either SharePointOnline or SharePointOnPremise.

Specifies a value to be prepended to the client secret, to form the Authorization header. The prepended value is usually a client ID.

Remarks

Authorization headers are used in authentication schemes that require at least medium security, to send credentials to a server to authenticate a request. For example, when the AuthScheme is OAuth they are used to specify a custom access token type.

Specify the type of the user impersonation. It should be whether the User mode or the Admin mode.

Remarks

Specify the type of the user impersonation. It should be whether the User mode or the Admin mode. The Admin mode is available only for Enterprise with Governance accounts and will be upon request. It will not work for any other accounts.

This section provides a complete list of the Connection properties you can configure in the connection string for this provider.

Specifies the file storage service, server, or file access protocol through which your REST files are stored and retreived.

Remarks

Set the ConnectionType to one of the following:

• Local: REST files stored on your local machine.
• Amazon S3
• Azure Blob Storage
• Azure Data Lake Storage Gen2
• Azure Data Lake Storage Gen2 SSL
• Azure Files
• Box
• Dropbox
• FTP
• FTPS
• Google Cloud Storage
• Google Drive
• HDFS
• HDFS Secure
• HTTP: Connects to REST files hosted on HTTP streams.
• HTTPS: Connects to REST files hosted on HTTPS streams.
• IBM Object Storage Source
• OneDrive
• OneLake
• Oracle Cloud Storage
• SFTP
• SharePoint REST
• SharePoint SOAP

Specifies the format reported by the data source. Required when using either the CreateSchema stored procedure or the Generate Schema File feature.

Remarks

When using the Generate Schema File feature, you must set GenerateSchemFiles to either OnStart or OnUse.)

The Uniform Resource Identifier (URI) for the XML/JSON/CSV resource location.

Remarks

Set the URI property to specify a path to a file or stream.

NOTE:

• This connection property requires that you set ConnectionType.
• If specifying a directory path, it is generally recommended to end the URI with a trailing path separator character, as an example 'folder1/' instead of 'folder1'.

See Fine-Tuning Data Access for more advanced features available for parsing and merging multiple files.

Below are examples of the URI formats for the available data sources:

Example Connection Strings and Queries

Below are example connection strings to XML/JSON/CSV files or streams.

The hosting region for your S3-like Web Services.

Remarks

The hosting region for your S3-like Web Services.

Oracle Cloud Object Storage Regions