• User Manuals /
  • Denodo SalesForce REST Custom Wrapper - User Manual

Denodo SalesForce REST Custom Wrapper - User Manual

Last modified on: 05 Feb 2019

Download original document


You can translate the document:

Introduction

salesforce-rest-customwrapper is a Virtual DataPort set of custom wrappers created to be able to perform both CRUD and complex-query operations against SalesForce from VDP in an integrated manner. This custom wrapper communicates with SalesForce using the REST API enabled by SalesForce and using JSON to exchange data with it.  

Features

Wrapper types

The salesforce-rest-customwrapper library includes several different wrappers, all in the com.denodo.salesforce.rest.wrapper package:

 

  • Abstract Wrapper (class AbstractSalesforceRestWrapper): this wrapper cannot be used as it is an abstract class

  • Entities Wrapper (class SalesforceRestWrapper): which allows you to create one base view for each entity available in Salesforce (even custom entities)

  • Entities Wrapper (class SalesforceRestSelectWrapper): same behavior as previous one but contains a dropdown to select entity so only the entities in the dropdown can be used (not valid neither for custom entities nor for those not available in the dropdown)

  • SOQL Wrapper (class SalesforceSOQLWrapper): which allows you to perform SOQL queries from VDP so that you can perform more specific queries against SalesForce (not supported by the entities in the library). This is a read only wrapper.

  • Predefined SOQL Wrapper (class SalesforcePredefinedSOQLWrapper): which allows you to perform a predefined SOQL query from VDP. This wrapper always executes the same query so no delegation is being performed. This is a read only wrapper.

 

 

Using the entities wrappers you can perform CRUD operations against SalesForce entities and custom entities in an SQL-way. Thanks to SalesforceSOQLWrapper, you can execute arbitrary SOQL (Salesforce Object Query Language) queries and fetch their results both as a piece of JSON and also as a VDP complex object (a set of fieldName/fieldValue pairs in the shape of a JDBC array of structs). SalesforcePredefinedSOQLWrapper allows you the configuration of a SOQL query to be executed always and see its results as simple properties and, for those that are objects, in a similar way to the SalesforceSOQLWrapper: as a piece of JSON and as a VDP complex object.

Architecture

All the wrappers use the new SalesForce.com REST API, based on JSON information interchange and OAuth 2.0 authentication tokens (it also supports XML but we use JSON). Using this REST API instead of the older contract-first SOAP API gives us much greater flexibility and doesn’t require us to maintain a heavy AXIS-based Web Service skeleton generated from the service’s WSDL. OAuth-based web authentication flow allows VDP admins to configure these wrappers without the need to store SalesForce user/password information in VDP (OAuth is the authentication system developed by twitter for their client apps).

OAuth functionality includes auto-refresh of tokens, so that once the authentication token has expired (SalesForce does not allow these tokens to be valid forever), the custom wrappers will execute automatic refresh logic that will obtain a new one.

Sometimes Salesforce may change the instance you have to connect to. In those cases, as the refresh flow returns the instance URL, the custom wrapper will adapt by performing a refresh in order to get the new instance url. This way, requests will be executed against the new instance. No manual changes will be necessary at all.

All the wrappers include, also, configuration parameters that allow you to (optionally) use an HTTP/HTTPS proxy to connect to Salesforce.

All the wrappers allow you to set the Salesforce REST API version that will be used. By default v30.0 will be used although there is a parameter you can set to use a different one: v29.0, v33.0 and so on.

The wrappers access Salesforce resources by creating connections to the Salesforce instance url (something like https://na2.salesforce.com) using httpclient library. SSL connections are configured to use TLSv1.2.

When calling SalesForce, you can let it know you accept compressed responses. This is also a configuration option for these custom wrappers. Anyway, you should take into account custom wrappers only use compression if configured so and you are fetching all the data or performing a select with filtering. No compression will be used by these custom wrappers if deleting an entity or adding a new one. Apart from that, REST API is not required to compress responses  even if you tell it you accept them. Based on this, it’s recommended that, in case you are fetching a big amount of data, you try if compression makes a difference for you in performance.

Capabilities

The SalesforceRestWrapper and SalesforceRestSelectWrapper  can delegate to SalesForce the following query artifacts and operators:

 

  • AND conditions

  • OR conditions (see Limitations section)

  • Operators: =, <>, >, >=, <, <=, LIKE, IN, IS NULL, IS NOT NULL, BETWEEN.

  • ORDER BY clauses (see “limitations”)

 

Thanks to the delegation of so many operations, the execution of this custom wrapper is faster as most part of the filtering operations will be performed by Salesforce itself instead of having to rely on VDP in-memory post-filtering.

The other custom wrappers SalesforcePredefinedSOQLWrapper and SalesforceSOQLWrapper do not execute VQL sentences as they already contain a SOQL query that will be directly sent to Salesforce and executed there.

Limitations

Salesforce has a limitation in the length of the queries, if we send a request too large to the server, SalesForce would respond with a message as this "Request Header Fields Too Large". In this case we would have to reduce the size of the request.  

The following limitations described in this section are related to VQL sentences so they only affect SalesforceRestWrapper and SalesforceRestSelectWrapper as the other custom wrappers use SOQL sent to SalesForce directly. They should be taken into account when using those custom wrappers.

  • Delegation of ORDER BY.
  • VDP 5.5+: The current VDP Custom Wrapper API allows the delegation of the ORDER BY clauses although, as some fields are not searchable at SalesForce, the ORDER BY clauses will be delegated to the source only when all the fields included at the ORDER BY clause are searchable. Otherwise, ordering will be performed by VDP in-memory.

  • ORDER BY when joining views:
  •  VDP 5.5+: As ORDER BY is being delegated to SalesForce, there can be inconsistent results when trying to MERGE JOIN views from SalesForce and other sources. This is due to the lack of control over SalesForce ORDER BY collation. This may lead us to sources with different ORDER BY criteria and, as a consequence, to inconsistent results. In order to avoid this, if criteria are different or you don't really know, the safest approach is to avoid MERGE JOIN and use, for example, a HASH JOIN so that how sources perform ORDER BY does not matter.

 

  • Insertion/update of BINARY values.
  • VDP 5.5+: The current VDP JDBC Driver does not allow setting binary (either byte[] or VDBJDBCBlob) parameters to Prepared Statements, so there is no possibility for inserting / updating values to the content of the Attachment entity in SalesForce  via VDP.

 

Supported entities

The SalesforceRestSelectWrapper can only work with the entities available at the entity selector dropdown whereas the SalesforceRestWrapper can work with almost every entity available at your Salesforce account (some entities do not support queries or are restricted to be used under certain conditions). For example, some of the mostly used entities you may end up using are:

 

  • Account
  • Approval
  • Asset
  • Attachment
  • Campaign
  • Case (also related: CaseComment)
  • Solution
  • CaseSolution
  • CategoryNode
  • Contact
  • Contract
  • Event
  • Lead
  • Note
  • Opportunity (also related: OpportunityContactRole, OpportunityLineItem)
  • Pricebook (also related: PricebookEntry)
  • Product
  • Task
  • User
  • UserRole
  • ServiceContract
  • SlaProcess (entitlement Process)
  • EntitlementTemplate
  • Entitlement (also related: EntitlementContact)
  • BusinessHours
  • MilestoneType
  • CaseMilestone
  • ContractLineItem
  • ProductEntitlementTemplate
  • Order
  • OrderItem
  • Question
  • Reply
  • ReplyReportAbuse
  • Vote

But you can also use less known entities such as  OpportunityHistory, ContactHistory or a Custom Object defined by yourself at your Salesforce instance (e.g. MyCustomLicense__c).

Note that all the entities are supported in a specific manner, which means that the data type of each of their fields is respected, and any custom field added to them at Salesforce.com is automatically detected and added to the base view during creation. If any custom field is added once the base view has been created, you can perform a source refresh of the view at VDP so that the new field can be added to the view.

 

The SalesforceSOQLWrapper does not depend on any entities as it just receives an SOQL query and then simply sends it over to SalesForce. That’s also the case of the SalesforcePredifinedSOQLWrapper.

How to use the custom wrappers

Whenever you communicate with SalesForce, you need to be authenticated. That’s why the usage of these custom wrappers needs authentication details in order to work.  Authentication when using the SalesForce REST API is done by means of OAuth 2.0. Before configuring any SalesForce base view in VDP you will have to obtain the authentication tokens from your SalesForce instance.

Getting OAuth authentication tokens

This section will give you just a brief introduction to OAuth 2.0 authentication process as Salesforce already provides good documentation on how to get the authentication tokens required to use these Denodo custom wrappers.

The SalesForce REST Custom Wrappers can use two different authentication flows supported by Salesforce:

  • OAuth 2.0 Web Server Authentication Flow: this is the recommended flow to be used. It is based on the use of a token that can be refreshed once expired if configured so at SalesForce.

The token may not be refreshable if “Refresh Token Policy” is set to “Immediately expire Refresh Token”. In that case, we may have an invalid refresh token or not have refresh token at all (depending on how we configured our remote access).

  • OAuth 2.0 Username-Password Flow: the use of this flow is not recommended (even by Salesforce) due to its lower security. It should not be used in production environments. It’s based on the use of the Salesforce account username and password.

Further information on how OAuth authentication works can be found at SalesForce:

OAuth 2.0 Web Server Authentication Flow

In order to use the SalesForce REST Custom Wrappers with OAuth 2.0 Web server authentication flow, you need to provide the custom wrappers with the following parameters:

  • client id

                  

  • refresh token (optional as if “Refresh Token Policy” is set to “Immediately expire Refresh Token” we may not have a refresh token)        

  • access token (optional if you provide refresh token, as it can be regenerated with the use of the other tokens)

You can configure in your SalesForce instance the duration of the access token, but it has to expire at some point. The VDP automatically refreshes the access token (once expired) using the client id and refresh token of the application in case you provide them. Note that, if policy is “Immediately expire Refresh Token” and you have a refresh token, this will not be refreshable as it will have been invalidated by SalesForce when policy was set to expire.

In order to get those tokens, you have to:

  • Client identifier (Referenced in Salesforce as Consumer Key)

  • Client secret (Referenced in Salesforce as Consumer Secret)

  • Obtain your user tokens using one of the following processes

  • Use the OAuth 2.0 Credentials Wizard in VDP Admin Tool, available since Denodo 5.0 at Tools → OAuth Credentials Wizards → OAuth 2.0 Wizard.

With the VDP OAuth 2.0 Credentials Wizard (recommended if you are using Denodo 5.0 or above) you can input your SalesForce authentication details and follow the steps to obtain your tokens:

Note the above screenshot configures access to a SalesForce SandBox instance. For configuring access to a production instance, you should use login.salesforce.com instead of test.salesforce.com (check the SalesForce documentation for more details). Also note that in order to use this wizard for the SalesForce wrapper, we recommend that the VDP Admin Tool is executed in the same machine as the VDP Server.

Once you have obtained all the tokens, we can start configuring VDP.

OAuth 2.0 Username-Password Flow

In order to use the custom wrappers with the username-password flow, you need to provide them with these authentication parameters:

  • clientId: consumer key from the remote access application definition

  • clientSecret: consumer secret from the remote access application definition

  • username: your Salesforce account username

  • password: your Salesforce account password

  • securityToken: a security token to be concatenated to the password

  • access token (optional, as it can be regenerated with the use of the other tokens)

All those tokens (except the access token) are necessary to get an access token either for the first time or once the current one expires. You can configure, in your SalesForce instance, the duration of this access token (but it has to expire anyway).

Although, explaining this is not the aim of this manual, in order to get those tokens, you have to:

  • Register your application (you don’t have to develop anything) as a remote access point at your SalesForce instance. With this step you will get the clientId and clientSecret

  • Get the securityToken from your Salesforce instance

Once you have done so, we can start configuring VDP. Remember the fetched tokens and your SalesForce instance URL as these are the parameters VDP will request from you.

 

Further information on how to get these authentication tokens can be found at SalesForce: http://help.salesforce.com/help/doc/en/remoteaccess_oauth_username_password_flow.htm

Configuring VDP 

In order to install it, you have to import the corresponding jar file (denodo-salesforce-rest-wrapper-[DENODO_PLATFORM_VERSION]-[WRAPPER_VERSION]-jar-with-dependencies.jar) in VDP (through File/Extensions/Jar management) and add all the wrappers (or the one/s you need) as Data Sources. Once done, you can create the base views.

SalesforceRestWrapper and SalesforceRestSelectWrapper

The custom wrapper SalesforceRestSelectWrapper allows you to create one base view for each SalesForce entity available in the entity dropdown whereas the custom wrapper SalesforceRestWrapper allows you to create one base view for each entity available in Salesforce (even custom entities). Base views created from any of them need the following mandatory parameters:

 

  • ENTITY: name of the entity you wish to create the base view for (e.g. Account, mycustomlicense__c ,...). The entity name will be selected from a dropdown for SalesforceRestSelectWrapper  or written for SalesforceRestWrapper (case insensitive).

  • BASE_URL: your SalesForce instance base url (e.g. https://na2.salesforce.com). This url will be used to perform data requests to.

  • SANDBOX: whether the instance you are connecting to is a Sandbox or not. Default value is false (Production instances). This will be used for the refresh flow: executed against login.salesforce.com for production instances and test.salesforce.com for Sandbox ones.

  • CLIENT_ID: the Customer Key you got from SalesForce when enabling OAuth. This value will be used to get a new accessToken in case the current one has expired.

 

There are also some optional parameters (some of them will be required depending on the used authentication flow):

 

  • API_VERSION: the REST API Version that will be used. If not set, v30.0 will be used. In case you provide a value for this field, it has to be something in the form vXY.Z. That is, something like v29.0, v33.0 and so on

  • ACCESS_TOKEN: The access token to communicate with SalesForce. If you provide it, it will not be necessary for the wrappers to get one at first… just once it expires.

  • REFRESH_TOKEN: not required but used for the web flow if present. This value will be used to get a new accessToken in case the current has expired.

  • CLIENT_SECRET: required for the username-password flow. It is the consumer secret from the remote access application definition

  • USERNAME: required for the username-password flow. It is your Salesforce account username.

  • PASSWORD: required for the username-password flow. It is your Salesforce account password.

  • SECURITY_TOKEN: required for the username-password flow.

  • RESPONSE_COMPRESSION_TYPE: it must be one of GZIP, DEFLATE or NONE. If not provided, NONE is the default value. If using GZIP or DEFLATE, custom wrapper will let SalesForce know we accept compressed responses

  • PROXY_HOST: the proxy host

  • PROXY_PORT: the proxy port

  • PROXY_USERNAME: if not null, the username to connect to the proxy

  • PROXY_PASSWORD: if not null, the password to connect to the proxy

 

Once you provide these parameters, the wrapper will connect to SalesForce in order to ask for a description of all the fields existing for the given entity in your SalesForce installation, including custom ones. Each field will be given its own column with the correct data type.

 

The image below displays the form to fill in with the entity details explained above. Here we are creating a base view for the account entity using the web flow. Note how, in this case, we provide data for the proxy host and port but not the username and password as our proxy does not require authentication.

The image below displays the fields for the Opportunity entity. Once we create a base view for the Opportunity, we can see its fields. Those fields are being fetched at that moment from Salesforce (and when you refresh the base view later). Note how as our entity contains custom fields they are displayed at the bottom of the entity (the ones ending with __c).

SalesforceSOQLWrapper

The SalesforceSOQLWrapper is a read only wrapper that allows you to perform SOQL queries from VDP so that you can execute more specific queries against SalesForce. The base view created from the SalesforceSOQLWrapper needs the same parameters as the entity-based ones except for the ENTITY field, which has no use in this case. This generated base view will have the following columns:

 

  • QUERY: the SOQL query executed (mandatory input parameter).

  • RESULT_JSON: a JSON document representing the query output (an object containing the field names and their values -note that some internal parameters may have been removed due to their lack of usefulness (only the data will be returned).

  • RESULT_COMPLEX: the same result in the form of a VDP JDBC array of fieldName/fieldValue structs.

 

The image below shows the VQL Shell with an SOQL query (using the sf_soql base view we have created) so that you can see the response. It includes the result as a JSON String and the result as an array.

The previous image does not let you see the array itself so here you can see it for the first record:

SalesforcePredefinedSOQLWrapper

The SalesforcePredefinedSOQLWrapper is a read only wrapper that allows you to perform a predefined SOQL query from VDP. The wrapper always executes the same query with which it was defined. The base view created from this wrapper needs the same parameters as the SalesforceSOQLWrapper and also this one:

 

  • SOQL_QUERY: the SOQL query to be executed.

The generated base view columns depend on the SOQL_QUERY. Every simple field returned by the query will be a column and, every field that is returned from Salesforce as an object, will be represented in two different columns: one with the JSON itself and another with a VDP complex object containing the pairs key - value of that JSON object.

 

Below we can see the fields for the query:             

SELECT Contact.Id, Contact.Owner.Id, Contact.Name,

Contact.Owner.email, Contact.Account.Id, Contact.Account.Name,

Contact.Account.Phone, Contact.Account.Type,

Contact.Account.External_Customer_Code__c from Contact

In the above image you can see two simple properties: id and name and two complex ones: owner and account as explained before.

The execution of the previous base view will return what you can see below:

Note that, in order to fetch the structure of the query, the query needs to be performed when we define the base view and, the output, analyzed to know which fields are simple and which ones complex. If we have no information on a field (=all the data returned by the query has a null value for that property) it will be displayed as a simple property.

Examples

Once you have created the base views for your entities, you can start using them. Let’s see some sentences we can execute:

  • Select Account by id:
  • SELECT Id, Name, Phone, Type,fax from sf_account where Id = '001V000000FE83kIAD'

  • Select Accounts based on account name and user email:
  • SELECT a.Id, a.Name, a.Phone, a.Type, a.fax from sf_account a, sf_user u WHERE a.OwnerId = u.Id and a.Name like 'Test%'

  • Update Opportunity state:
  • Update sf_Opportunity SET StageName = 'Closed - Won' Where Id = '006V00000045z6HIAQ'

  • Add Note to Opportunity:
  • Insert into sf_Note (ParentId, Body, Title) values (‘345456476757RR’, ‘Send email to communicate with the client’, ‘Communication’)

  • Execute SOQL to fetch Accounts (this could be done by directly executing a Select against our base view sf_account):
  • Select RESULT_JSON, RESULT_COMPLEX from sf_soql where QUERY = "Select Id, Name from Account"

 

The result of this sentence will be a String as the value of the RESULT_JSON column (e.g. {"Id":"0014000000VLyoiAAD","Name":"GenePoint España"}) and an array as the value of the RESULT_COMPLEX column with the pairs key - value in the JSON, for example:

Id                0014000000VLyoiAAD

Name                GenePoint España

  • Execute predefined SOQL query to fetch information on the contacts and their accounts.

The result of this base view will be a list of tuples where each one contains a complex object that represents the account for the contact in that tuple.