12.28. Salesforce Translator 12.28.1. Properties The Salesforce translators support SELECT , DELETE , INSERT and UPDATE operations against a Salesforce.com account. Important Salesforce API version 22.0 support has been deprecated. The Salesforce translator, known by the type name salesforce , provides Salesforce API 22.0 support. The translator must be used with the corresponding Salesforce resource adapter of the same API version. Note The resource adapter for this translator is provided by configuring the salesforce data source in the Red Hat JBoss EAP instance. The Salesforce 34 translator, known by the type name of salesforce-34 , provides Salesforce API 34.0 support. The translator must be used with the corresponding Salesforce resource adapter of the same API version. You can access Salesforce using a JSON Web Token (JWT) login module for security: Create a self-signed certificate in Salesforce by clicking user -> setup -> security-controls -> Certificate and Key Management . Download the certificate and the keystore. Create a connected application and select OAuth and click all the scopes . Save the custom domain. Create a profile and a permission set assigned to the connected application. Add the certificate by clicking Digital Certificate . Add the security domain to the settings file: 12.28.2. Salesforce Translator: Execution Properties Table 12.28. Execution Properties Name Description Default ModelAuditFields Audit Model Fields false MaxBulkInsertBatchSize Batch size to use when inserting in bulk 2048 The Salesforce translator can import metadata. Table 12.29. Import Properties Name Description Required Default NormalizeNames 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. Use a negative look-ahead inclusion pattern to act as an inclusion filter. false n/a 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 source. false n/a importStatstics Retrieves cardinalities during import using the REST API explain plan feature. false false When both includeTables and excludeTables patterns are present during the import, the includeTables pattern matched first, then the excludePatterns will be applied. 12.28.3. Salesforce Translator: 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 JBoss Data Virtualization Query Planner, the Salesforce connector supports nearly all of the SQL syntax supported by JBoss Data Virtualization. The Salesforce Connector executes SQL commands by pushing down\u009d the command to Salesforce whenever possible, based on the supported capabilities. JBoss Data Virtualization 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\u0080to the data source, so it will be evaluated in JBoss Data Virtualization, in order to ensure that the operation is performed. In cases where certain SQL capabilities cannot be pushed down to Salesforce, JBoss Data Virtualization will push down the capabilities that are supported, and fetch a set of data from Salesforce. Then, JBoss Data Virtualization will evaluate the additional capabilities, creating a subset of the original data set. Finally, JBoss Data Virtualization will pass the result to the client. SELECT sum(Reports) FROM Supervisor where Division = 'customer support'; Neither Salesforce nor the Salesforce Connector support the sum() scalar function, but they do support CompareCriteriaEquals, so the query that is passed to Salesforce by the connector will be transformed to this query. SELECT Reports FROM Supervisor where Division = 'customer support'; The sum() scalar function will be applied by the JBoss Data Virtualization 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 Case WHERE Status = 'Closed'; The API in Salesforce to delete objects only supports deleting by ID. In order to accomplish this, the Salesforce connector will first execute a query to get the IDs of the correct objects, and then delete those objects. So the above DELETE command will result in the following two commands. SELECT ID From Case WHERE Status = 'Closed'; DELETE From Case where ID IN (<result of query>); The Salesforce API DELETE call is not expressed in SQL, but the above is an SQL equivalent expression. It is useful to be aware of unsupported capabilities, in order to avoid fetching large data sets from Salesforce and making your queries perform as well as possible. 12.28.4. Salesforce Translator: Multi-Select Picklists A multi-select pick list is a field type in Salesforce that can contain multiple values in a single field. Query criteria operators for fields of this type in Salesforce Object Query Language (SOQL) are limited to EQ, NE, includes and excludes. The full Salesforce documentation for selecting from mullti-select pick lists can be found at Querying Mulit-select Picklists . JBoss Data Virtualization 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 are: For example, take a single multi-select picklist column called Status that contains all of these values. current working critical For that column, all of the below are valid queries: SELECT * FROM Issue WHERE true = includes (Status, 'current, working' ); SELECT * FROM Issue WHERE true = excludes (Status, 'current, working' ); SELECT * FROM Issue WHERE true = includes (Status, 'current;working, critical' ); EQ and NE criteria will pass to Salesforce as supplied. For example, these queries will not be modified by the connector. SELECT * FROM Issue WHERE Status = 'current'; SELECT * FROM Issue WHERE Status = 'current;critical'; SELECT * FROM Issue WHERE Status != 'current;working'; 12.28.5. Salesforce Translator: 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 modeled 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 query. Users are free to change the value in the model to True, changing the default behavior of the connector to be queryAll. The behavior 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 Contact where isDeleted = true; If the isDeleted column is used as a parameter in the query, and the value is 'false' the connector performing the default behavior will call the query. select * from Contact where isDeleted = false; 12.28.6. Salesforce Translator: 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. 12.28.7. Salesforce Translator: 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. 12.28.8. Salesforce Translator: 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 Outer Join syntax. SELECT Account.name, Contact.Name from Contact LEFT OUTER JOIN Account on Contact.Accountid = Account.id This query shows the correct syntax to query a SalesForce model with to produce a relationship query from child to parent. It resolves to the following query to SalesForce. SELECT Contact.Account.Name, Contact.Name FROM Contact select Contact.Name, Account.Name from Account Left outer Join Contact on Contact.Accountid = Account.id This query shows the correct syntax to query a SalesForce model with to produce a relationship query from parent to child. It resolves to the following query to SalesForce. SELECT Account.Name, (SELECT Contact.Name FROM Account.Contacts) FROM Account See the description of the Relationship Queries operation in the SalesForce documentation for limitations. 12.28.9. Salesforce Translator: Bulk Insert Queries SalesForce translator also supports bulk insert statements using JDBC batch semantics or SELECT INTO semantics. The batch size is determined by the execution property MaxBulkInsertBatchSize , which can be overridden in the vdb.xml file. The default value of the batch is 2048. The bulk insert feature uses the async REST based API exposed by Salesforce for execution for better performance. 12.28.10. Salesforce Translator: Supported Capabilities The following are the connector capabilities supported by the Salesforce Translator. These SQL constructs are 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 12.28.11. Salesforce Translator: Native Queries Salesforce procedures may optionally have native queries associated with them. See Section 12.7, "Parameterizable Native Queries" . The operation prefix (for example, select;, insert;, update;, delete; - see the native procedure logic below) must be present in the native query, but it will not be issued as part of the query to the source. Example 12.11. Example DDL for a SF native procedure 12.28.12. Salesforce Translator: Native Procedure Warning This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the translator property called "SupportsNativeQueries" to true. See Section 12.6, "Override Execution Properties" . SalesForce translator provides a procedure with name native that gives ability to execute any ad hoc native Salesforce queries directly against the source without any JBoss Data Virtualization parsing or resolving. The metadata of this procedure's execution results are not known to JBoss Data Virtualization, and they are returned as object array. User can use an ARRAYTABLE construct ( Section 2.6.10, "Nested Tables: ARRAYTABLE" ) to build a tabular output for consumption by client applications. JBoss Data Virtualization exposes this procedure with a simple query structure as below. 12.28.13. Salesforce Translator Example: Select Example 12.12. Select Example In the above code, the "search" keyword is followed by a query statement. Note The Salesforce Object Query Language (SOQL) is treated as a parameterized native query so that parameter values may be inserted in the query string properly. See Section 12.7, "Parameterizable Native Queries" . The results returned by search may contain the object Id as the first column value regardless of whether it was selected. Also queries that select columns from multiple object types will not be correct. 12.28.14. Salesforce Translator Example: Delete Example 12.13. Delete Example In the above code, the "delete;" keyword is followed by the ids to delete as varargs. 12.28.15. Salesforce Translator Example: Create and Update Example 12.14. Create Example In the above code, the "create" or "update" keyword must be followed by the following properties. Attributes must be matched positionally by the procedure variables - thus in the example attribute two will be set to 2. Property Name Description Required type Table Name Yes attributes comma separated list of names of the columns The values for each attribute is specified as separate argument to the "native" procedure. Update is similar to create, with one more extra property called "id", which defines identifier for the record. Example 12.15. Update Example Important By default the name of the procedure that executes the queries directly is called native, however user can set override execution property vdb.xml file to change it.

Commands:
<security-domain name="oauth2-jwt-security"> <authentication> <login-module code="org.teiid.jboss.oauth.JWTBearerTokenLoginModule" flag="required" module="org.jboss.teiid.security"> <module-option name="client-id" value="xxxxx"/> <module-option name="client-secret" value="xxxx"/> <module-option name="access-token-uri" value="https://login.salesforce.com/services/oauth2/token"/> <module-option name="jwt-audience" value="https://login.salesforce.com"/> <module-option name="jwt-subject" value="your@sf-login.com"> <module-option name="keystore-type" value="JKS"/> <module-option name="keystore-password" value="changeme"/> <module-option name="keystore-url" value="USD{jboss.server.config.dir}/salesforce.jks"/> <module-option name="certificate-alias" value="teiidtest"> <module-option name="signature-algorithm-name" value="SHA256withRSA"/> </login-module> </authentication> </security-domain>
SELECT sum(Reports) FROM Supervisor where Division = 'customer support';
SELECT Reports FROM Supervisor where Division = 'customer support';
DELETE From Case WHERE Status = 'Closed';
SELECT ID From Case WHERE Status = 'Closed'; DELETE From Case where ID IN (<result of query>);
boolean includes(Column column, String param) boolean excludes(Column column, String param)
SELECT * FROM Issue WHERE true = includes (Status, 'current, working' ); SELECT * FROM Issue WHERE true = excludes (Status, 'current, working' ); SELECT * FROM Issue WHERE true = includes (Status, 'current;working, critical' );
SELECT * FROM Issue WHERE Status = 'current'; SELECT * FROM Issue WHERE Status = 'current;critical'; SELECT * FROM Issue WHERE Status != 'current;working';
select * from Contact where isDeleted = true;
select * from Contact where isDeleted = false;
GetUpdated (ObjectName IN string, StartDate IN datetime, EndDate IN datetime, LatestDateCovered OUT datetime) returns ID string
GetDeleted (ObjectName IN string, StartDate IN datetime, EndDate IN datetime, EarliestDateAvailable OUT datetime, LatestDateCovered OUT datetime) returns ID string, DeletedDate datetime
SELECT Account.name, Contact.Name from Contact LEFT OUTER JOIN Account on Contact.Accountid = Account.id
SELECT Contact.Account.Name, Contact.Name FROM Contact
select Contact.Name, Account.Name from Account Left outer Join Contact on Contact.Accountid = Account.id
SELECT Account.Name, (SELECT Contact.Name FROM Account.Contacts) FROM Account
CREATE FOREIGN PROCEDURE proc (arg1 integer, arg2 string) OPTIONS ("teiid_rel:native-query" 'search;SELECT ... complex SOQL ... WHERE col1 = USD1 and col2 = USD2') returns (col1 string, col2 string, col3 timestamp);
SELECT x.* FROM (call pm1.native('search;SELECT Account.Id, Account.Type, Account.Name FROM Account')) w, ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x
SELECT x.* FROM (call pm1.native('delete;', 'id1', 'id2')) w, ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x
SELECT x.* FROM (call pm1.native('create;type=table;attributes=one,two,three', 'one', 2, 3.0)) w, ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM (call pm1.native('update;id=pk;type=table;attributes=one,two,three', 'one', 2, 3.0)) w, ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x