The CData Sync App provides a straightforward way to continuously pipeline your API data to any database, data lake, or data warehouse, making it easily available for Analytics, Reporting, AI, and Machine Learning.
The API connector can be used from the CData Sync application to pull data from API and move it to any of the supported destinations.
Create a connection to API by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the API icon is not available, click the Add More icon to download and install the API connector from the CData site.
Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.
The API Connector for CData Sync can be used to connect to a variety of data sources from within your application.
The Sync App can be used to connect to a variety of data sources, called Profiles, from within your application. An API profile is a collection of schemas modeling data from an application or online service as tables, views or stored procedures. API Profile files have the file exension ".apip" and can be download from the CData website.
To establish a connection using a Profile, set the Profile property to the path of the API profile file, and ProfileSettings to a connection string containing the credentials to the data-source. The most common forms of authentication are supported, including HTTP basic, HTTP digest, NTLM, and OAuth. For more information on the required connection properties, please refer to the documentation of each profile.
This section details a selection of advanced features of the API Sync App.
The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.
The Sync App offloads as much of the SELECT statement processing as possible to API and then processes the rest of the query in memory (client-side).
See Query Processing for more information.
See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.
By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.
To specify another certificate, see the SSLServerCert property for the available formats to do so.
To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.
In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.
Set the following properties:
The CData Sync App provides standards-based access to API 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 API 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="name" value="Bob" Attribute="address" value="123 Pleasant Lane" Attribute="phone" value="123-4567"
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.
In API Script items are created, named, and given attribute values through the api:set keyword:
<api:set item="input" attr="mask" value="*.txt" />
The line above sets the "mask" attribute to the value "*.txt" on the item named "input". In this case, the input item is like a variable in API Script.
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.
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:
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:
<api:set attr="path" value="." />
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.
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.
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.
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.
The _query item has the following attributes that describe the query that was issued to the Sync App:
| query | The SQL statement. For example:
SELECT Id, Name FROM Accounts WHERE City LIKE '%New%' AND COUNTRY = 'US' GROUP BY CreatedDate ORDER BY Name LIMIT 10,50; |
| selectcolumns | A comma-separated list of the columns in the SELECT clause. For example, the Id and Name columns in the example. If "*" is specified in the SELECT clause, the value of [_query.selectcolumns] is "*". |
| table | The table name. For example, Accounts in the example. |
| isjoin | Whether the query is a join. |
| jointable | The table in the JOIN clause. |
| criteria | The WHERE clause. For example, the following WHERE clause in the example:
City LIKE '%New%' AND COUNTRY = 'US' |
| orderby | The ORDER BY clause. For example, Name in the example. |
| groupby | The GROUP BY clause. For example, CreatedDate in the example. |
| limit | The limit specified in the LIMIT or TOP clauses of the SELECT statement. For example, 50 in the example. |
| offset | The offset specified in the LIMIT or TOP clauses of the SELECT statement. For example, 10 in the example. |
| insertselect | The SELECT statement nested in an INSERT statement. |
| updateselect | The SELECT statement nested in an UPDATE statement. |
| upsertselect | The SELECT statement nested in an UPSERT statement. |
| deleteselect | The SELECT statement nested in a DELETE statement. |
| bulkoperationcolumns | The columns of the table the bulk operation modifies, separated by commas. For example, consider the following query:
INSERT INTO Account(account_name, account_type) SELECT customer_name, customer_type FROM Customer#TEMP[_query.bulkoperationcolumns] returns the following: [account_name], [account_type] |
| temptablecolumns | The columns selected from the temp table in a bulk operation, separated by commas. For example, consider the following query:
DELETE FROM Account WHERE EXISTS SELECT customer_name, customer_type FROM Customer#TEMP[_query.temptablecolumns] returns the following: [customer_name], [customer_type] |
| nullupdates | The columns of an UPDATE statement, separated by commas, that contain a null value or a null parameter value. |
| isschemaonly | Whether the query retrieves only schema information. |
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) | ...]where formatter is the name of the formatter and parameters is an optional set of parameters to control formatter output. Formatter output can be provided as input to another formatter with the pipe character ("|").
<api:set attr="input1.id" value="[myid | replace('*', '-')]"/><api:call op="fileListDir">
<api:check attr="name" value="[filename|tolower | endswith('.log')]">
<api:push/>
</api:check>
</api:call>
Returns NULL if the attribute does not exist or the value if it does.
Returns the index at which the string is found in the attribute array. The index is 1 based.
Converts the attribute value to a base 64 decoded string.
Converts the attribute value to a base 64 encoded string.
Returns the original attribute value with only its first character capitalized.
Returns the original attribute value with the first character of all words capitalized.
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.
Returns true (or ifcontains) if the attribute value contains the parameter value, false (or ifnotcontains) otherwise.
Returns the number of occurrences in the attribute value of a substring specified by the first parameter.
Returns the numeric value formatted as currency.
Returns the numeric value formatted as a decimal number.
Checks for the existence of an attribute and returns the specified parameter value if it does not.
Returns the specified value if the attribute value is empty, otherwise the original attribute value.
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.
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.
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.
Evaluates the mathematical expression.
Returns the lowest zero-based index at which the substring is found in the attribute value.
Returns the number of characters in the attribute value.
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.
Returns true (or ifmatch) if the attribute value matches the first parameter, otherwise false (or ifnotmatch).
Checks the attribute value and returns true (or iftrue) if true and false (or iffalse) if false.
Implodes multiple values to a string separated by a separator.
Inserts the specified string at the specified index.
Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.
Returns true (or ifalpha) if all characters in the attribute value are alphabetic and there is at least one character, false (or ifnotalpha) otherwise.
Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.
Returns true (or ifalphanum) if all characters in the attribute value are alphanumeric and there is at least one character, false (or ifnotalphanum) otherwise.
Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.
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.
Returns true (or ifnum) if all characters in the attribute value are digits and there is at least one character, false (or ifnotnum) otherwise.
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.
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.
Implodes multiple values to a string separated by a separator.
Converts the attribute value to a JSON-escaped, single-line string.
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.
Returns the lowest zero-based index at which the substring is found in the attribute value.
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.
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.
Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.
Computes the MD5 hash of the attribute value.
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.
Removes the white space from the string represented by the atttribute value.
Returns the numeric value formatted as a percentage.
Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.
Searches the string represented by the attribute value for an occurrence of the regular expression supplied in the pattern parameter.
Replaces all occurrences of the regular expression pattern found in the attribute value with replacewith.
Deletes characters from the attribute value; begins at the zero-based index specified by the first parameter.
Replaces all occurrences of the first parameter in the string represented by the attribute value with the value of the second parameter.
Returns the highest zero-based index at which the substring is found in the attribute value.
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.
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.
Computes the SHA-1 hash of the attribute value.
Returns a substring of the attribute value; starts at the index specified by the parameter and counts to the right.
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.
Converts the attribute value to an SQL-escaped, single-line string.
Returns true (or iftrue) if the attribute value starts with the specified parameter, false (or iffalse) otherwise.
Returns the string with any HTML markup removed.
Returns a substring of the attribute value; starts at the index specified by the parameter.
Returns only the letters in a string.
Returns only the alphanumeric characters in a string.
Returns the string represented by the attribute value with all characters converted to lowercase.
Returns the string represented by the attribute value with all characters converted to uppercase.
Trims leading and trailing white space from an attribute.
Trims trailing white space from an attribute.
Trims leading white space from an attribute.
Truncates the attribute value to the number of characters specified by the parameter.
Wraps a string to a given number of characters.
Returns a string with all the values of the attribute concatenated using the specified delimiter.
Converts the attribute value to a XML decoded string.
Converts the attribute value to a XML encoded string.
Returns a signed number indicating the relative values of dates represented by the attribute value and parameter value.
Returns the current system date and time in the format specified by the parameter if one was provided.
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.
Returns the difference (in units specified by the first parameter) between now and the date specified by the second parameter.
Returns the day component, expressed as a value between 1 and 31, of the date represented by the attribute value.
Returns the day of week for the date represented by the attribute value.
Returns the day of year expressed as a value between 1 and 366 for the date represented by the attribute value.
Returns the date and time for the current system file time.
Converts a valid file time to a valid datetime value formatted as specified by the parameter if one was provided.
Returns true (or ifleap) if the 4-digit year represented by the attribute value is a leap year, false (or ifnotleap) otherwise.
Returns the month component expressed as a value between 1 and 12 of the date represented by the attribute value.
Returns the current system date and time in the format specified by the parameter if one was provided.
Returns the date specified by the attribute value formatted as specified by the parameter if one was provided.
Converts a valid datetime to a valid file time value.
Returns the name of the month for the numeric value specified by the attribute value.
Returns the date specified by the attribute value converted to UTC and formatted as specified by the outputformat parameter if one was provided.
Returns the current system UTC date and time.
Returns the day of the week as an integer where Monday is 0 and Sunday is 6.
Returns the year component of the date represented by the attribute value.
Returns the absolute value of the numeric attribute value.
Returns the sum of the numeric attribute value and the value specified by the parameter.
Returns the AND of two values. The values provided on each side must be 1/0, yes/no or true/false.
Returns the smallest integer greater than or equal to a numeric attribute value.
Returns the result of dividing the numeric attribute value by the specified value of the parameter.
Returns the result of dividing the numeric attribute value by the specified value of the parameter.
Returns the largest integer less than or equal to the numeric attribute value.
Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.
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.
Returns true (or ifequal) if the attribute value is equal to the parameter value, false (or ifnotequal) otherwise.
Returns true (or ifgreater) if the attribute value is greater than the parameter value, false (or ifnotgreater) otherwise.
Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.
Returns true (or ifless) if the attribute value is less than the parameter value, false (or ifnotless) otherwise.
Returns the sum of the numeric attribute value and the value specified by the parameter.
Returns the modulus of the numeric attribute value divided by the specified parameter value.
Returns the numeric attribute value raised to the power specified by the parameter value.
Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.
Returns the difference between the numeric attribute value and the value specified by the parameter.
Returns the modulus of the numeric attribute value divided by the specified parameter value.
Returns the result of multiplying the numeric attribute value with the specified value of the parameter.
Returns the OR of two values. The values provided on each side must be 1/0, yes/no or true/false.
Returns the numeric attribute value raised to the power specified by the parameter value.
Returns a random integer between 0 and the parameter value.
Returns a random integer between 0 and the parameter value.
Returns the numeric attribute value rounded to the number of decimal places specified by the parameter.
Returns the square root of the numeric attribute value.
Returns the difference between the numeric attribute value and the value specified by the parameter.
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.
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:
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.
None
None
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>
The api:call keyword is used to call operations. Valid operations are the following:
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.
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>
There are 3 ways to provide input to a call:
<api:set attr="mask" value="*.txt"/>
<api:set attr="path" value="C:\\"/>
<api:call op="fileListDir">
...
</api:call>
<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>
<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>
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.
Call an operation using the default item as input:
<api:set attr="path" value="C:\myfiles"/>
<api:call op="fileListDir">
<api:push/>
</api:call>
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.
None
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>
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:
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>
Catch all exceptions:
<api:catch code="*">
An exception occurred. Code: [_code], Message: [_desc]
</api:catch>
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.
<api:check attr="_input.In_Stock">
...
</api:check>
The api:continue keyword can be used to move to the next iterations of api:call or api:enum.
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>
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.
<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>
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.
<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>
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.
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
To enumerate over a list of values use the list and separator parameters of api:enum. For example, the code below lists the colors specified in the colors attribute:
<api:set attr="colors" value="violet, indigo, blue, green, yellow, orange, red"/>
<api:enum list="[colors]" separator=",">
[_value]
</api:enum>
To enumerate over a range of values, use the range attribute. For example, the code below lists the character set from a to z, from Z to A, and the numbers from 1 to 25:
<api:enum range="a..z">
[_value]
</api:enum>
<api:enum range="Z..A">
[_value]
</api:enum>
<api:enum range="1..25">
[_value]
</api:enum>
To enumerate over all the values of a multivalued attribute, use the attr argument to specify a multivalued attribute and set expand to true:
<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>
The example above results in the following output:
(1) email#1 -> [email protected] (2) email#2 -> [email protected]
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>
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>
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.
<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>
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.
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>
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.
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>
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.
None
Evaluate a simple comparison of two values:
<api:if exp="[attr] == 10">
Evaluate the equality of a given value and the value of a given attribute:
<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>
Evaluate whether an attribute exists:
<api:set attr="exists" value="true"/>
<api:if attr="exists"> <!-- Evaluates to true -->
[exists]
</api:if>
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.
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:
The following parameters can be defined for the table itself:
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.
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."
/>
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"
/>
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"
/>
<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>
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.
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>
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.
customer:* = soap:*Any characters that are not valid attribute names are ignored and are used to demarcate the end of a name.
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"/>
In this example, the var1 and var2 attributes of item1 are mapped respectively to the attr1 and attr2 attributes of item2. The attr1 attribute, which is set in item2 with the value z, is overwritten with x, the value of var1 from item1. The attr2 attribute, which does not exist in item2, is created and set with y, the value of var2 in item1.
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:*"/>
Copy all attributes from one item to another item:
<api:map from="copyfrom" to="copyto" map="* = *"/>
The api:match keyword is similar to the api:equals keyword; however, it permits complex matching rules.
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.
None
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>
The api:notequals keyword verifies that the attribute does not match the specified value. It has a similar behavior to the api:equals keyword.
<api:call op="fileListDir">
<api:notequals attr="file:extension" value=".err">
<api:push/>
</api:notequals>
</api:call>
The api:null keyword checks that an attribute does not exist in the specified item.
<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>
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.
<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>
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.
<api:push op="myOp"/>
This is a shorthand for the following:
<api:call op="myOp">
<api:push/>
</api:call>
<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.
None
Call two operations that manipulate remote API. 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.
None
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>
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.
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>
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.
None
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.
None
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>
The api:throw keyword is used to raise an error (exception) from within a script.
None
The example below explicitly defines both the error code and description:
<api:throw code="myerror" desc="thedescription" />
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.
None
None
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>
The api:unset keyword is used to delete attributes from an item or delete the item itself.
Remove an attribute from an item before it is pushed out:
<api:call op="fileListDir">
<api:unset attr="file:size"/>
<api:push/>
</api:call>
You can use api:validate to throw an error if a required value is not provided.
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."/>
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.
For more information on establishing a connection, see Establishing a Connection.
| Property | Description |
| User | The API user account used to authenticate. |
| Property | Description |
| OAuthVersion | The version of OAuth being used. |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
| Property | Description |
| LogModules | Core modules to be included in the log file. |
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
| Property | Description |
| Profile | The profile property specifies the location on disk of the profile you would like to connect to. |
| ProfileSettings | A semicolon-separated list of name-value pairs required by your chosen data source, e.g.: username=myname; password=mypassword;appid=myapp. |
| Property | Description |
| AuthScheme | The scheme used for authentication. Accepted entries are None, NTLM, Basic or OAuth. |
| Password | The authtoken used to authenticate to the API Server. |
| URL | The API Server base resource url. For example, http://localhost:8153/api.rsc. |
| Property | Description |
| MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| Other | These hidden properties are used only in specific use cases. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.
| Property | Description |
| User | The API user account used to authenticate. |
The API user account used to authenticate.
Together with Password, this field is used to authenticate against the API server.
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthVersion | The version of OAuth being used. |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
The version of OAuth being used.
The version of OAuth being used. The following options are available: Disabled,1.0,2.0
The client Id assigned when you register your application with an OAuth authorization server.
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The certificate to be accepted from the server when connecting using TLS/SSL.
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
| Description | Example |
| A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
| A path to a local file containing the certificate | C:\cert.cer |
| The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
| The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
| The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
The protocol used by a proxy-based firewall.
This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.
| Type | Default Port | Description |
| TUNNEL | 80 | When this is set, the Sync App opens a connection to API and traffic flows back and forth through the proxy. |
| SOCKS4 | 1080 | When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
| SOCKS5 | 1080 | When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.
The name or IP address of a proxy-based firewall.
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.
Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.
The TCP port for a proxy-based firewall.
This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.
The user name to use to authenticate with a proxy-based firewall.
The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.
A password used to authenticate to a proxy-based firewall.
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.
The hostname or IP address of a proxy to route HTTP traffic through.
The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.
By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.
The TCP port the ProxyServer proxy is running on.
The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.
The authentication type to use to authenticate to the ProxyServer proxy.
This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.
Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
The authentication type can be one of the following:
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.
A user name to be used to authenticate to the ProxyServer proxy.
The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.
You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:
user@domain domain\user
A password to be used to authenticate to the ProxyServer proxy.
This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.
If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.
If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.
For SOCKS 5 authentication or tunneling, see FirewallType.
By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.
The SSL type to use when connecting to the ProxyServer proxy.
This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:
| AUTO | Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option. |
| ALWAYS | The connection is always SSL enabled. |
| NEVER | The connection is not SSL enabled. |
| TUNNEL | The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy. |
A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .
The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.
Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| LogModules | Core modules to be included in the log file. |
Core modules to be included in the log file.
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
See the Logging page for an overview.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\\CData\\API Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
This section provides a complete list of the APIProfile properties you can configure in the connection string for this provider.
| Property | Description |
| Profile | The profile property specifies the location on disk of the profile you would like to connect to. |
| ProfileSettings | A semicolon-separated list of name-value pairs required by your chosen data source, e.g.: username=myname; password=mypassword;appid=myapp. |
The profile property specifies the location on disk of the profile you would like to connect to.
The Sync App enables you to connect to a variety of data sources by selecting a profile using the Profile property. The selected profile should be available in the configured Location.
A semicolon-separated list of name-value pairs required by your chosen data source, e.g.: username=myname; password=mypassword;appid=myapp.
The Sync App allows you to connect to a variety of data sources by selecting a profile using the Profile property. A profile may require other connection properties to function; these connection properties are determined by the profile author and can be specified via the ProfileSettings property.
This section provides a complete list of the APIServer properties you can configure in the connection string for this provider.
| Property | Description |
| AuthScheme | The scheme used for authentication. Accepted entries are None, NTLM, Basic or OAuth. |
| Password | The authtoken used to authenticate to the API Server. |
| URL | The API Server base resource url. For example, http://localhost:8153/api.rsc. |
The scheme used for authentication. Accepted entries are None, NTLM, Basic or OAuth.
Together with Password and User, this field is used to authenticate against the service. Basic is the default option.
The authtoken used to authenticate to the API Server.
Authtokens are created in the API Server. To create an API Server user, click Settings -> Users in the API Server administration console.
The API Server base resource url. For example, http://localhost:8153/api.rsc.
The API Server base resource url. For example, http://localhost:8153/api.rsc.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| MaxRows | Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time. |
| Other | These hidden properties are used only in specific use cases. |
| PseudoColumns | This property indicates whether or not to include pseudo columns as columns to the table. |
| Timeout | The value in seconds until the timeout error is thrown, canceling the operation. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
These hidden properties are used only in specific use cases.
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
| DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
| ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
| RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
This property indicates whether or not to include pseudo columns as columns to the table.
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The value in seconds until the timeout error is thrown, canceling the operation.
If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.
If Timeout expires and the operation is not yet complete, the Sync App throws an exception.
A filepath pointing to the JSON configuration file containing your custom views.
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.
This User Defined View configuration file is formatted as follows:
For example:
{
"MyView": {
"query": "SELECT * FROM NorthwindOData WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"