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 |
---|---|---|
| Audits model fields |
|
| Prevents reading deleted rows from tables containing the |
|
| Enables usage of Salesforce Bulk API for the |
|
| If TRUE, string values that exceed the length defined in Salesforce metadata for the containing field will be truncated to the defined length |
|
| Specifies the maximal number of records within the ID boundaries for each chunk. The value should be a positive number less than 250,000 |
|
| 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 |
|
| If set to 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 |
|
Import Properties
To view the full table, click the expand button in its top right corner
Property Name | Description | Required | Default |
---|---|---|---|
| Specifies if the importer should attempt to modify the object/field names so that they can be used unquoted |
|
|
| 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' |
| empty |
| 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.
When both includeTables and excludeTables patterns are present during the import, and the includeTables pattern matched first, excludePatterns will be applied.
|
| empty |
| Retrieves cardinalities during import using the REST API explain plan feature |
|
|
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
commandINSERT
CommandUPDATE
CommandDELETE
CommandCompareCriteriaEquals
InCriteria
LikeCriteria
-supported for string fields only.RowLimit
AggregatesCountStar
NotCriteria
OrCriteria
CompareCriteriaOrdered
OuterJoins
with join criteriaKEY
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