The Salesforce Connector, known by the type name salesforce, supports the SELECT, DELETE, INSERT and UPDATE operations against a Salesforce.com account. It is designed for use with the CData Virtuality Server Salesforce resource adapter.

Creating a Data Source

The CData Virtuality Server supports connecting to Salesforce via either Salesforce API v34 or Salesforce API v40-45 (check the Salesforce documentation for differences in versions). 

Salesforce API v34

 To deploy Salesforce API v34, use the salesforce conneсtor and translator:

CALL SYSADMIN.createConnection(name => 'salesforce34', jbossCLITemplateName => 'salesforce', connectionOrResourceAdapterProperties => 'user-name=user1, password=pass1, requestTimeout=240000, connectTimeout=120000') ;;
CALL SYSADMIN.createDataSource(name => 'salesforce34', translator => 'salesforce', modelProperties => 'importer.useFullSchemaName=false', translatorProperties => 'ModelAuditFields=true') ;;

Salesforce API v40-45

To deploy Salesforce API v40-45, use the salesforce41 conneсtor and translator:

CALL SYSADMIN.createConnection(name => 'salesforce41', jbossCLITemplateName => 'salesforce41', connectionOrResourceAdapterProperties => 'user-name=user1, password=pass1, requestTimeout=240000, connectTimeout=120000') ;;
CALL SYSADMIN.createDataSource(name => 'salesforce41', translator => 'salesforce41', modelProperties => 'importer.useFullSchemaName=false', translatorProperties => 'ModelAuditFields=true') ;;

Metadata

Before issuing queries to Salesforce, configure a connection using the SYSADMIN.createConnection() procedure:

CALL SYSADMIN.createConnection( 'salesforcealias', 'salesforce', 'user-name=user1,password=pass1' );
CALL SYSADMIN.createDatasource( 'salesforcealias', 'salesforce', '<model properties>', '<translator properties>');
 
-- Additional parameters, like connect or request timeouts, can be provided in the connection properties:
 
CALL SYSADMIN.createConnection( 'salesforcealias', 'salesforce', 'user-name=user1,password=pass1,connectTimeout=2000,requestTimeout=3000' );
 
-- Additional properties, like forceExcludeDeleted (for adding IsDeleted=FALSE clause on all the tables containing IsDeleted column) can be provided in the translator properties
CALL SYSADMIN.createDataSource('salesforcealias', 'salesforce', 'importer.useFullSchemaName=false', 'forceExcludeDeleted=true');;
 
--Create a Salesforce connection via Proxy:
CALL SYSADMIN.createConnection('salesforcealias', 'salesforce', 'user-name=user1,password=pass1,proxyURL=http://localhost:3128,proxyUsername=proxyname,proxyPassword=proxypass') ;;
 
--Create a Salesforce data source via OAuth tokens:
CALL "SYSADMIN.createConnection" (
"name" => 'salesforce1' /* Optional */
,"jbossCLITemplateName" => 'salesforce' /* Optional */
,"connectionOrResourceAdapterProperties" => 'ClientId=<CLIENT_ID>,ClientSecret=CLIENT_SECRET>,RefreshToken=REFRESH_TOKEN,AccessTokenEndpoint="https://na98.force.com/services/oauth2/token"' /* Optional */
,"encryptedProperties" => '' /* Optional */
);;
 
CALL "SYSADMIN.createDataSource" (
"name" => 'salesforce1' /* Optional */
,"translator" => 'salesforce' /* Optional */
,"modelProperties" => '' /* Optional */
,"translatorProperties" => '' /* Optional */
,"encryptedModelProperties" => '' /* Optional */
,"encryptedTranslatorProperties" => '' /* Optional */
);;

The connectTimeout, requestTimeout, and forceExcludeDeleted parameters are optional, and their default values are 120000, 240000, and FALSE, respectively.

proxyUsername and proxyPassword properties are available since v4.10

Translator Properties

To view the full table, click the expand button in its top right corner


Name

Description

Default

ModelAuditFields

Audits model fields

FALSE

forceExcludeDeleted

Prevents reading deleted rows from tables containing the IsDeleted column, if not specified otherwise by adding the IsDeleted=TRUE condition to the WHERE clause

FALSE

useBulkAPIForSelect

Enables usage of Salesforce Bulk API for the SELECT queries

FALSE

truncateStrings

If TRUE, string values that exceed the length defined in Salesforce metadata for the containing field will be truncated to the defined length

TRUE

maxChunkSize

Specifies the maximal number of records within the ID boundaries for each chunk. The value should be a positive number less than 250,000

100,000

pollingInterval

When using Salesforce BulkAPI for selecting, this parameter specifies how often to poll the API to check if the results are available (in milliseconds). Increasing this parameter leads to less API call usage, but may also increase waiting time on quick queries

1000

useTableAliasForColumns

If set to FALSE, the internal query rewriting into Salesforce syntax omits object references in the column names and results in more compact SOQL queries. For example, the following CData Virtuality query:

SELECT Account.Name, Account.Type FROM salesforce.Account

will be rewritten into the following query:

SELECT Name, Type FROM Account

instead of this:

SELECT Account.Name, Account.Type FROM Account

In many cases, this allows for overcoming the SOQL statement length limit (currently, 20000 characters). Please note that the property affects only simple SELECT queries and queries which cannot be pushed down

TRUE

Import Properties

To view the full table, click the expand button in its top right corner


Property Name

Description

Required

Default

NormalizeNames

Specifies if the importer should attempt to modify the object/field names so that they can be used unquoted

FALSE

TRUE

excludeTables

A case-insensitive regular expression that, when matched against a table name, will exclude it from import. Applied after table names are retrieved.

Not importing tables with a name starting with 'Account':importer.excludeTables=^Account.*$

FALSE

empty

includeTables

A case-insensitive regular expression that, when matched against a table name, will be included during import. Applied after table names are retrieved from the source.

  • Importing all tables with a name starting with 'Account': importer.includeTables=^Account.*$
When both includeTables and excludeTables patterns are present during the import, and the includeTables pattern matched first, excludePatterns will be applied.
  • Importing all tables with a name starting with 'Account'', but not the table with the name 'AccountPartner'/'accountpartner:
    importer.includeTables=^Account.*$,importer.excludeTables=^AccountPartner$

FALSE

empty

importStatstics

Retrieves cardinalities during import using the REST API explain plan feature

FALSE

FALSE


SQL Processing

Salesforce does not provide the same set of functionality as a relational database. For example, Salesforce does not support arbitrary joins between tables. However, working in combination with the CData Virtuality Server Query Planner, the Salesforce connector supports nearly all of the SQL syntax supported by the CData Virtuality Server.

The Salesforce Connector executes SQL commands by “pushing down” the command to Salesforce whenever possible, based on the supported capabilities. The CData Virtuality Server will automatically provide additional database functionality when the Salesforce Connector does not explicitly provide support for a given SQL construct. In these cases, the SQL construct cannot be “pushed down” to the data source, so it will be evaluated in the CData Virtuality Server, in order to ensure that the operation is performed.

In cases where certain SQL capabilities cannot be pushed down to Salesforce, the CData Virtuality Server will push down the capabilities that are supported and fetch a set of data from Salesforce. The CData Virtuality Server will then evaluate the additional capabilities, creating a subset of the original data set. Finally, the CData Virtuality Server will pass the result to the client.

SELECT sum(Reports) FROM salesforce.Supervisor WHERE Division = 'customer support';

Neither Salesforce nor the Salesforce Connector supports the sum() scalar function, but they do support CompareCriteriaEquals, so the query that is passed to Salesforce by the connector will be transformed into this query:

SELECT Reports FROM salesforce.Supervisor WHERE Division = 'customer support';

The sum() scalar function will be applied by the CData Virtuality Server Query Engine to the result set returned by the connector.

In some cases, multiple calls to the Salesforce application will be made to support the SQL passed to the connector.

DELETE From salesforce.Case WHERE Status = 'Closed';

The Salesforce API only supports deleting objects by ID. To do this, the Salesforce connector will first execute a query to get the IDs of the correct objects and then delete them, so the DELETE command will result in the following two commands:

SELECT ID From salesforce.Case WHERE Status = 'Closed';
 
DELETE From salesforce.Case where ID IN (<result of query>);

Please note that the Salesforce API DELETE call is not expressed in SQL, but the above is an SQL equivalent expression.

It's useful to be aware of unsupported capabilities, in order to avoid fetching large data sets from Salesforce and making your queries as efficient as possible.

Selecting from Multi-Select Picklists

A multi-select picklist is a field type in Salesforce that can contain multiple values in a single field. Query criteria operators for fields of this type in SOQL are limited to EQ, NE, includes and excludes. For more information on selecting from multi-select picklists in Salesforce, please refer to Salesforce documentation.

The CData Virtuality Server SQL does not support the includes or excludes operators, but the Salesforce connector provides user-defined function definitions for these operators that provide equivalent functionality for fields of type multi-select. The definition for the functions is as follows:

boolean includes(Column column, String param)
 
boolean excludes(Column column, String param)

For example, take a single multi-select picklist column called Status that contains all of these values:

  • current
  • working
  • critical

For this column, all of the below are valid queries:

SELECT * FROM salesforce.Issue WHERE true = includes (Status, 'current, working' );
 
SELECT * FROM salesforce.Issue WHERE true = excludes (Status, 'current, working' );
 
SELECT * FROM salesforce.Issue WHERE true = includes (Status, 'current;working, critical' );


The EQ and NE criteria will pass to Salesforce as supplied. For example, these queries will not be modified by the connector:

SELECT * FROM salesforce.Issue WHERE Status = 'current';
 
SELECT * FROM salesforce.Issue WHERE Status = 'current;critical';
 
SELECT * FROM salesforce.Issue WHERE Status != 'current;working';

Selecting All Objects

The Salesforce connector supports calling the queryAll operation from the Salesforce API. The queryAll operation is equivalent to the query operation with the exception that it returns data about all current and deleted objects in the system.

The connector determines if it will call the query or queryAll operation via reference to the isDeleted property present on each Salesforce object, and modelled as a column on each table generated by the importer. By default, this value is set to FALSE when the model is generated and thus the connector calls the query. You can change the value in the model to TRUE, changing the default behaviour of the connector to queryAll.

The behaviour is different if isDeleted is used as a parameter in the query. If the isDeleted column is used as a parameter in the query, and the value is TRUE, the connector will call queryAll:

SELECT * FROM salesforce.Contact WHERE isDeleted = TRUE;

If the isDeleted column is used as a parameter in the query, and the value is FALSE, the connector performs the default behaviour:

SELECT * FROM salesforce.Contact WHERE isDeleted = FALSE;

Selecting Updated Objects

If the option is selected when importing metadata from Salesforce, a GetUpdated procedure is generated in the model with the following structure:

GetUpdated (ObjectName IN string,
 
StartDate IN datetime,
EndDate IN datetime,
 
LatestDateCovered OUT datetime)
returns
ID string

See the description of the GetUpdated operation in the Salesforce documentation for usage details.

Selecting Deleted Objects

If the option is selected when importing metadata from Salesforce, a GetDeleted procedure is generated in the model with the following structure:

GetDeleted (ObjectName IN string,
StartDate IN datetime,
EndDate IN datetime,
EarliestDateAvailable OUT datetime,
LatestDateCovered OUT datetime)
returns
ID string,
DeletedDate datetime

See the description of the GetDeleted operation in the Salesforce documentation for usage details.

Relationship Queries

Salesforce does not support joins like a relational database, but it does have support for queries that include parent-to-child or child-to-parent relationships between objects. These are termed Relationship Queries. The SalesForce connector supports Relationship Queries through the OUTER JOIN syntax:

SELECT Account.name, Contact.Name from salesforce.Contact LEFT OUTER JOIN salesforce.Account
on Contact.Accountid = Account.id

In addition to LEFT OUTER JOIN, the SalesForce connector also supports the RIGHT OUTER JOIN operation. You can, therefore, execute both types of queries:

  • LEFT OUTER JOIN:

    SELECT sum(o.Amount) FROM salesforce.Opportunity o LEFT OUTER JOIN salesforce.Account a ON o.AccountId=a.Id;
  • RIGHT OUTER JOIN:

    SELECT sum(o.Amount) FROM salesforce.Opportunity o RIGHT OUTER JOIN salesforce.Account a ON o.AccountId=a.Id

This query shows the correct syntax to query a SalesForce model to produce a relationship query from child to parent. It resolves the following query to SalesForce:

SELECT Contact.Account.Name, Contact.Name FROM salesforce.Contact
SELECT Contact.Name, Account.Name FROM salesforce.Account LEFT OUTER JOIN salesforce.Contact
ON Contact.Accountid = Account.id

This query shows the correct syntax to query a SalesForce model to produce a relationship query from parent to child. It resolves the following query to SalesForce:

SELECT Account.Name, (SELECT Contact.Name FROM
Account.Contacts) FROM salesforce.Account

See the description of the Relationship Queries operation in the SalesForce documentation for limitations.

Supported Capabilities

The following are the connector capabilities supported by the Salesforce Connector. These SQL constructs will be pushed down to Salesforce:

  • SELECT command
  • INSERT Command
  • UPDATE Command
  • DELETE Command
  • CompareCriteriaEquals
  • InCriteria
  • LikeCriteria -supported for string fields only.
  • RowLimit
  • AggregatesCountStar
  • NotCriteria
  • OrCriteria
  • CompareCriteriaOrdered
  • OuterJoins with join criteria KEY

See Also

SQL Snippet to Create Views with LEFT( "foo"."bar", 4000) AS "Name" for Long Strings e.g. Salesforce for a ready-to-use snippet