Salesforce Connector

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

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

SELECT Name, Type FROM Account

SELECT Account.Name, Account.Type FROM Account

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:

  SQL

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

• RIGHT OUTER JOIN:

  SQL

  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