Last Update: 20.11.2024

Introduction

This documentation describes how to access a Salesforce CRM system via Lobster Integration (version > 4.6.11) using a JDBC/SQL interface.

Note: It does not describe the inner functionality of the HTTP/SOAP access provided by Salesforce.

SQL access requires the purchase of an additional license. A particular JDBC driver by company CDATA - which is provided via the additional license - must be installed.

Important note: This JDBC driver converts SQL statements sent by Lobster Integration into webservice requests to the Salesforce HTTP/SOAP interface. It is important to ensure that the SQL statements are kept simple and not too complex (e.g. complicated join conditions etc.). There is a high probability that conversion of complex statements into webservice requests will fail.

Salesforce setup

Two steps are neccessary to access Salesforce via SQL statements:

1) JBDC driver

Once the license has been activated, the JDBC driver is automatically displayed in the Update Center.
After downloading the driver, the Integration Server must be restarted, then you can continue with the next step.

Linux:
Restart the Integration Server.

Windows:
The following sequence must be followed:

Stop the Integration Server
Start script ./bin/hub.bat
End script ./bin/hub.bat with Ctrl+c
Start the Integration Server

Note: It is advisable to take a look at the Update Center from time to time, as updates can be displayed and carried out there.

2) Configuration of two additional database aliases

Two aliases are necessary because the jdbc driver cannot execute bulk and normal operations via one alias.

The bulk alias is used for access to Salesforce in phase 5 (SQLBulkUnit), the non-bulk alias for all other accesses (phases 1, 3 and 4). Reason: this separation prevents large bulk operations from being blocked by long running select statements.

The aliases are created in Administration → Databases/Connectors. Below you will find two templates that you can import. You can then adjust their values.
Alternatively, an alias can also be created via Templates → Presets → Salesforce.

a) Bulk alias

Import Template Bulk alias

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Configure PUBLIC "-//Lobster//DTD Configure 1.0//EN" "http://www.lobster.de/dtd/configure_1_1.dtd">
	<Configure class="com.ebd.hub.services.database.ConnectionService">
	<Call name="initPool">
		<Arg>
			<New class="com.ebd.hub.services.database.DatabaseSettings">
				<Set name="alias">salesforce</Set>
				<Set name="allowGrowing">True</Set>
				<Set name="database">jdbc:lobster:sforce2:</Set>
				<Set name="user">(your salesforce login username)</Set>
				<Set name="password">(your salesforce login password)</Set>
				<Call name="addNamedProperty">
					<Arg>Security Token</Arg>
					<Arg>(your security token)</Arg>
				</Call>
				<Call name="addNamedProperty">
					<Arg>Use Bulk API</Arg>
					<Arg>true</Arg>
				</Call>
				<Call name="addNamedProperty">
					<Arg>Bulk API Version</Arg>
					<Arg>v2</Arg>
				</Call>
 				<Call name="addNamedProperty">
					<Arg>Logfile</Arg>
					<Arg>"C:\Lobster\IS\logs\salesforce_jdbc\sf.log"</Arg>
				</Call> 
 				<Call name="addNamedProperty">
					<Arg>Verbosity</Arg>
					<Arg>0</Arg>
				</Call> 
  				<Call name="addNamedProperty">
					<Arg>Max Log File Size</Arg>
					<Arg>10MB</Arg>
				</Call>  
   	 	        <Set name="catalogName"></Set>
				<Set name="driver">de.lobster.jdbc.jdbc.sforce.SForceDriver</Set>
				<Set name="minSize">0</Set>
				<Set name="maxSize">0</Set>
				<Set name="idleTime">300000</Set>
				<Set name="sqlCommand"></Set>
				<Set name="rollback">True</Set>
				<Set name="caching">True</Set>
			</New>
		</Arg>
	</Call>
</Configure>

Parameter	Description	Example
alias	Alias name	<Set name="alias">salesforce</Set>
database	The connection string for the jdbc driver is specified here. For an overview, all parameters are specified in the database.xml as "addNamedProperty" so that they are displayed in the "JDBC Properties" tab, during import. If further properties are added, they can be added in the interface in the "JDBC Properties" tab. This is why the entry here is very short and is only there to identify the correct driver.	<Set name="database">jdbc:lobster:sforce2:</Set> ("2" und colon at the end required!)
user	Username Salesforce login	<Set name="user">(your salesforce login username)</Set>
password	Password Salesforce login	<Set name="password">(your salesforce login password)</Set>
Security Token	Fixed security token generated within Salesforce. Token creation, password change, etc causes the token to be sent to the user per mail. A new token can be created within Salesforce via View Profile → Settings → Reset my Security token. The token does not expire automatically.	<Call name="addNamedProperty"> <Arg>Security Token</Arg> <Arg>(your security token)</Arg> </Call>
Use Bulk API	Enables usage of Salesforce's Bulk API via the jdbcdriver. Must be true in the bulk alias configuration.	<Call name="addNamedProperty"> <Arg>Use Bulk API</Arg> <Arg>true</Arg> </Call>
Bulk API Version	Two versions exist (v1 and v2) - default is v1. Using v1, select queries are counted as well against the limit of max 15000 batches. Many selects can thus cause that limit to be quickly exceeded. To prevent this from happening, "v2" should be set here. Those jobs are processed with priority, causing long running select statements to possibly block other running jobs. For this reason the regular alias is used for select statements - no Salesforce jobs are created this way, the objects are directly accessed instead.	<Call name="addNamedProperty"> <Arg>Bulk API Version</Arg> <Arg>v2</Arg> </Call>
minSize, maxSize	must be 0, otherwise the Salesforce bulk jobs are not closed.	<Set name="minSize">0</Set> <Set name="maxSize">0</Set>
driver	Used Java driver class	<Set name="driver">de.lobster.jdbc.jdbc.sforce.SForceDriver</Set>
Use Connection Pooling	Activates ConnectionPooling of the jdbc driver. This parameter must be set to false for Bulk, which is the default value of the parameter and can therefore be omitted. If this is set to true, then the jdbc driver attempts to hang the generated bulks from two independent Lobster Integration jobs in a Salesforce job for as long as the connection is maintained. However, since each profile runs until the Salesforce job is finished, there is an error during the second profile run that no new bulk can be added to a closed Salesforce job.
allowGrowing, catalogName, idleTime, sqlCommand, rollback, caching	Those parameters are without meaning for Salesforce alias configuration. These are in every database alias by default.

b) Regular alias

Import Template reguler alias

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Configure PUBLIC "-//Lobster//DTD Configure 1.0//EN" "http://www.lobster.de/dtd/configure_1_1.dtd">
    <Configure class="com.ebd.hub.services.database.ConnectionService">
		<Call name="initPool">
		    <Arg>
		        <New class="com.ebd.hub.services.database.DatabaseSettings">
		            <Set name="alias">salesforce_nobulk</Set>
		            <Set name="allowGrowing">True</Set>
		            <Set name="database">jdbc:lobster:sforce2:</Set>
		            <Set name="user">(your salesforce login username)</Set>
		            <Set name="password">(your salesforce login password)</Set>
		            <Call name="addNamedProperty">
		                <Arg>Security Token</Arg>
		                <Arg>(your security token)</Arg>
		            </Call>
		            <Call name="addNamedProperty">
		                <Arg>Use Bulk API</Arg>
		                <Arg>false</Arg>
		            </Call>
		            <Call name="addNamedProperty">
		                <Arg>Use Connection Pooling</Arg>
		                <Arg>true</Arg>
		            </Call>
                	<Call name="addNamedProperty">
                    	<Arg>Logfile</Arg>
                    	<Arg>"C:\Lobster\IS\logs\salesforce_jdbc\sf.log"</Arg>
                	</Call>
                	<Call name="addNamedProperty">
                    	<Arg>Verbosity</Arg>
                    	<Arg>0</Arg>
                	</Call>
                	<Call name="addNamedProperty">
                   		<Arg>Max Log File Size</Arg>
                    	<Arg>10MB</Arg>
                	</Call> 
                    <Set name="catalogName"></Set>
		            <Set name="driver">de.lobster.jdbc.jdbc.sforce.SForceDriver</Set>
		            <Set name="minSize">0</Set>
		            <Set name="maxSize">0</Set>
		            <Set name="idleTime">300000</Set>
		            <Set name="sqlCommand"></Set>
		            <Set name="rollback">True</Set>
		            <Set name="caching">True</Set>
		        </New>
		    </Arg>
		</Call>
</Configure>

Parameter	Description	Example
alias, database, user, passwort, Security Token, minSize, maxSize, driver	see above
Use Bulk API	Enables usage of Salesforce's Bulk API via the jdbcdriver. Must be false in the regular alias configuration.	<Call name="addNamedProperty"> <Arg>Use Bulk API</Arg> <Arg>false</Arg> </Call>
Use Connection Pooling	Enables Connection Pooling within the JDBC driver. Should always be true, otherwise the daily limit of Salesforce connections (3000/day) can be exceeded very quickly.	<Call name="addNamedProperty"> <Arg>Use Connection Pooling</Arg> <Arg>true</Arg> </Call>
allowGrowing, catalogName, idleTime, sqlCommand, rollback, caching	see above

c) Access onto other Salesforce instances

Production instances can be accessed via login.salesforce.com, sandbox instances (test system aka production clone) via test.salesforce.com. If a sandbox instance shall be accessed, the following extension must set in the according alias (default is "false"):

database.xml

<Call name="addNamedProperty">
    <Arg>Use Sandbox</Arg>
    <Arg>true</Arg>
</Call>

It is also possible to purchase custom Subdomains at Salesforce,

e.g.: companyname.my.salesforce.com.

To be able to access the Salesforce instance via this URL, the "LoginURL" parameter must be added. This applies to the sandbox and the production system. For the sandbox, the Use Sandbox parameter must also be set to "true".

database.xml

<Call name="addNamedProperty">
    <Arg>LoginURL</Arg>
    <Arg>https://<mysubdomain>.my.salesforce.com/services/Soap/c/60.0</Arg>
</Call>

Note: 60.0 is currently the Salesforce API version supported by the jdbc driver (as of 04/2024)

d) Proxy configuration

Please enter all parameters in the alias using "addNamedProperty".

Parameter	Description	Default
Proxy Server	Hostname or IP address of the proxy	./.
Proxy Port	TCP port the proxy is running on	80
Proxy User	Username for authentication	./.
Proxy Password	Password for authentication	./.
Proxy Auth Scheme	Authentication type (BASIC, DIGEST, NEGOTIATE, PROPRIETARY)	BASIC
Proxy Auto Detect	Use system proxy settings. Set to false if the settings specified here should be used	false
Proxy Exceptions	semicolon-separated list of hostnames and IPs which should not use the proxy	./.
Proxy SSL Type	AUTO, ALWAYS, NEVER, TUNNEL (see driver documentation)	AUTO

For further details see the driver documentation: https://cdn.cdata.com/help/RFK/jdbc/Connection.htm

Configure OAuth2 (optional, if required/desired)

Requirements for using OAuth2 in conjunction with the Salesforce JDBC driver:

a) a corresponding app must be created on the Salesforce side

b) the values for Client Id(Consumer Key) and Cliend Secret(Consumer Secret) must be known.

If you have any questions, please consult the official Salesforce documentation or contact your Salesforce consultant.

Various steps are necessary to enable the process for automatically renewing the token. In any case, a Lobster Integration is required that already has access to Salesforce - initially with user/password authentication, as already described in this document.

Various procedures must be executed via the Admin Console or SQL console in the plugins.

The following is a step-by-step description of how to retrieve the access token and refresh token.

Note: None of the steps may be omitted, as they build on each other! The retrieval of the first access and refresh token can be done via an alias. However, all aliases must be configured for Oauth2 at the end.

Step 1 - Prepare Salesforce alias

The following properties must be added to the Salesforce alias in JDBC Properties. It is important that the callback URL in Salesforce and in Lobster Integration is http://localhost:33333. This is the standard callback URL for headless machines.

All placeholders in square brackets must still be replaced with the real values.

<Call name="addNamedProperty">
	<Arg>AuthScheme</Arg>
	<Arg>OAuth</Arg>
</Call>
<Call name="addNamedProperty">
    <Arg>OAuthClientId</Arg>
    <Arg>[Client Id(Consumer Key)]</Arg>
</Call>                
<Call name="addNamedProperty">
    <Arg>OAuthClientSecret</Arg>
    <Arg>[Client Secret(Consumer Secret)]</Arg>
</Call>
<Call name="addNamedProperty">
    <Arg>CallbackURL</Arg>
    <Arg>http://localhost:33333</Arg>
</Call>
<Call name="addNamedProperty">
    <Arg>InitiateOAuth</Arg>
    <Arg>OFF</Arg>
</Call>
<Call name="addNamedProperty">
    <Arg>OAuthSettingsLocation</Arg>
    <Arg>[<your_Lobster_home_directory>]/conf/Salesforce/OAuthSettings.txt</Arg>
</Call>
<Call name="addNamedProperty">
    <Arg>LoginURL</Arg>
    <Arg>[LoginURL]</Arg>
</Call>
<Call name="addNamedProperty">
    <Arg>OAuthAccessTokenURL</Arg>
    <Arg>[OAuthAccessTokenURL]</Arg>
</Call>

Parameter	Description	Example
AuthScheme	The schema with which the jdbc driver should authorize itself against Salesfoce. For OAuth, the value must be "OAuth".
OAuthClientId	The consumer key (see Salesforce description) is entered here	<Call name="addNamedProperty"> <Arg>OAuthClientId</Arg> <Arg> 3MVG9Ve.2wqUVx_bR4rnWKR66zawe4q34aygrhQ1zCQ2LCVCj1JxSp.X_VyaCU4l1HjBLzvKzSAre.xK </Arg> </Call>
OAuthClientSecret	The Consumer Secret (see Salesforce description) is entered here	<Call name="addNamedProperty"> <Arg>OAuthClientSecret</Arg> <Arg>32913EEE7324357WERZT849D54FEE3C85E43F6C32751AD79818BDE4D</Arg> </Call>
CallbackURL	The CallbackURL is required for the OAuth2 Granttype Authorization Code. The authorization for the user is returned to this URL when the token is renewed. As the Lobster Integration is a server application and no user actions can be executed in the background, the standard is retained here as a dummy.
InitiateOAuth	The parameter specifies which procedure must be carried out. "OFF" is required to set up OAuth2. This retrieves the access token and the first refresh token. After setup, this parameter is renamed to "REFRESH", because then only the refresh token needs to be renewed. The time at which the change must be made is explained later in this documentation.
OAuthSettingsLocation	The storage location in which the jdbc driver stores the latest token is specified here. It is recommended to specify a storage location in the ./conf directory.	<Call name="addNamedProperty"> <Arg>OAuthSettingsLocation</Arg> <Arg>/opt/Lobster/IS/conf/Salesforce/OAuthSettings.txt</Arg> </Call>
LoginURL	The URL with which the driver login to Salesforce. This URL differs depending on the Salesforce instance: For the test system: https://test.salesforce.com/ For the production system: https://login.salesforce.com/ If a domain has been purchased: https://<myCompany>.sandbox.my.salesforce.com	<Call name="addNamedProperty"> <Arg>LoginURL</Arg> <Arg> https://lobster--test.sandbox.my.salesforce.com </Arg> </Call>
OAuthAccessTokenURL	The URL with which the driver retrieves the AccessToken from Salesforce. This URL differs depending on the Salesforce instance: For the test system: https://test.salesforce.com/services/oauth2/token For the production system: https://login.salesforce.com/services/oauth2/token If a domain has been purchased: https://<myCompany>.sandbox.my.salesforce.com/services/oauth2/token	<Call name="addNamedProperty"> <Arg>OAuthAccessTokenURL</Arg> <Arg> https://lobster--test.sandbox.my.salesforce.com /services/oauth2/token </Arg> </Call>

Step 2 - Get verifier from Salesforce

The first step is to retrieve the authorisation URL from Salesforce. To do this, open the Admin Console and navigate to Tools → SQL Monitor. The SQL Monitor is always mentioned in the process; alternatively, alternatively, you can always use the SQL console in the Plugins.
The Salesforce alias that was customised in step 1 is used as the database alias.
The following command must then be executed:

execute GetOAuthAuthorizationUrl

A URL is returned as the result. This must be called up in a browser. The browser does not have to be located on the Integration Server. For example:

https://login.salesforce.com/services/oauth2/authorize?state=aHR0cDovL2xvY2FsaG9zdDozMzMzMw%3D%3D&client_id=3MVG99Oxasdfasdfasdf0_eLiYU4FC2.NRn9htUmh2uIfNM.13BM33aZClit&response_type=code&redirect_uri=https%3A%2F%2Foauth.cdata.com%2Foauth%2F

The response from the server to the URL is a redirect. It is possible that an error is displayed in the browser because the URL with "http://localhost:33333" does not exist, but this is correct.

The redirect URL that appears in the address bar of the browser can look like this, for example:

http://localhost:33333/?code=YVByeDR5X0t6c2NXenl5easdfasdfkRmRyakxtNlNScEJNR2hFbG8wNTlJb2t2c1IzTVJ1UHU0Skg3aXdfYjMzYkRiM2NrZ1FZbWc9PQ==&state=YUhSMGNEb3ZMMnh2WTJGc2FHOXpkRG96TXpNek13PT0=&rssbus=true

The value of the "code" parameter is the verifier. This is required for the next step (in the example, it is the value YVByeDR5X0t6c2NXenl5easdfasdfkRmRyakxtNlNScEJNR2hFbG8wNTlJb2t2c1IzTVJ1UHU0Skg3aXdfYjMzYkRiM2NrZ1FZbWc9PQ== )

Step 3 - Get access and refresh token

The following procedure must now be called in the SQL Monitor with the value from step 2.

execute GetOAuthAccessToken Verifier='<your Verifier>'

This procedure returns a table with a data set.
The values for the access token and refresh token are required from this data set.

Step 4 - Store token in the alias

Now the Salesforce alias must be adjusted in Administration → Databases/Connectors. The Access Token and Refresh Token from step 3 must be added once. The value for "InitialteOAuth" parameter must also be renamed to "REFESH".

<Call name="addNamedProperty">
    <Arg>InitiateOAuth</Arg>
    <Arg>REFRESH</Arg>
</Call>    
<Call name="addNamedProperty">
    <Arg>OAuthAccessToken</Arg>
    <Arg>[Access Token]</Arg>
</Call>
<Call name="addNamedProperty">
    <Arg>OAuthRefreshToken</Arg>
    <Arg>[Refresh Token]</Arg>
</Call> 

Logging

Activation

To activate the logging of the jdbc driver, additional parameters are specified at the alias of the JDBC driver:

<Call name="addNamedProperty">
    <Arg>Logfile</Arg>
    <Arg>"C:\Lobster\IS\logs\salesforce_jdbc\sf.log"</Arg>
</Call> 
<Call name="addNamedProperty">
    <Arg>Max Log File Size</Arg>
    <Arg>"10MB"</Arg>
</Call> 
<Call name="addNamedProperty">
    <Arg>Verbosity</Arg>
    <Arg>0</Arg>
</Call> 

Parameter	Description
Logfile	The log file's absolute path
Verbosity	Log level; von 1 (sparse) bis 5 (very high); should be set to 0 (= off) by default
Max Log File Size	maximum file size - a new file is opened if exceeded

The log file contains the communication between Salesforce and the jdbcdriver (details depending on verbosity level) in plain text. Created SQL as well as sent HTTP requests/responses are logged.

As a rule, "Verbosity" = 3 on a test system is sufficient for troubleshooting. If logging is no longer required, it should be deactivated again.

The log file is not created immediately on startup of the Integration Server, but at the first access on the alias.

Important note: On production systems logging shall only be activated with great care as log file size can spiral out of control very quickly!

Log file rollover

If "Max Log File Size" is reached, a rollover takes place: the current log file contains a timestamp in its file name (yyyyMMddHH-mmss) and a new log file (with the name specified in parameter "Logfile") is created. Logging always continues in this file.

Technical features in Salesforce

Following a few technical features are described relevant for the usage of the Lobster jdbcinterface to Salesforce.

Metadata cache

On first access to a Salesforce object after JDBC startup its metadata is requested and stored in an internal cache ("MetaCache").This speeds up future calls to this object and requires rebuilding the cache after every change made to this object (e.g. adding or removing a property).

To rebuild the cache, however, the Integration Server must be restarted.

Salesforce Bulk API

Bulk jobs provide the advantage that big amounts of data are processed way more quickly via insert or update. Within Salesfcore there exist limitations (depending on the licensed Salesforce Edition) regarding connections per day and batches per day. Thus it is also useful to send big data amounts in bulk.

Note: Bulk jobs in Salesforce are only created if the bulk alias is used (Use Bulk API = true) AND the SQLBulkUnit is used in Phase 5. So no bulk job is created if the bulk alias is used but the statement has a different origin like Phase 3 (filter/function) or Phase 4 (database node). If the regular alias is used, the following error message appears in Phase 5: "Got no salesforce job-ID".

A list of currently operated and finished bulk jobs can be viewed in the Salesforce GUI via Setup → Environment → Jobs → Bulk Data Load Jobs. A bulk job consists of 1 - n batches, each batch can contain 1 - 9999 (max) records.

	Attribute	Description
Head data	Job ID Object External ID Field Content Type ...	Head data of bulk job
Batch 1	Record 1	First record in batch 1
	Record 2	Second record in batch 1
	...	...
	Record 9999	Last record in batch 1
Batch 2	Record 1	First record in batch 2
	Record 2	Second record in batch 2
	...	...

Per database node one Salesforce bulk job is created (even with several data sheets/records within Lobster Integration only one bulk job per node is created - all data from each sheet is merged). The bulk jobs are processed serially by Lobster Integration. Each batch in a bulk job then gets processed in parallel within Salesforce.

Important note: "delete before insert" cannot be used by the SQLBulkUnit in connection with Salesforce!

Objects/Fields

Within Salesforce there are standard objects (Type = "Standard Object", provided by Salesforce like e.g. "Account") and custom defined objects (Type = "Custom Object"). Eeach object has a label for GUI access and an API name for API access. The API distinguishes between standard and custom objects - for custom objects it ends with "__c". An object possesses 1 - n fields. Fields possess a field label for GUI access and a field name for API access. Customized field names end with "__c" as well. Via the Salesforce GUI's Object Manager (Setup → Object Manager) details for each object's structure and its fields can be inspected. Standard objects cannot be deleted, but new fields or objects can be added to it. Standard fields cannot be deleted nor modified.

Object relations

Within Salesforce relations can be defined between objects. There are two main types of relations: lookup-relations and master-detail-relations. Their usage is described in section Salesforce via SQL - Tutorial.

Lookup relation

Lookup-relations are generally used if objects are only related in some cases. Examples from the Salesforce standard objects are "Account" and "Contact". This is a "loose" coupling - a contact can belong to an account but does not have to; it can exist only by itself. To connect a contact object to an account object, the field "AccountId" in contact (which is of type "Lookup(Account)") has to be filled with the internal ID of an account object.

Master detail relation

Within a master-detail-relation, the above is not possible. If a record is deleted from a master object, all related detail records are deleted as well. On creation of master-detail-relations the relation field is always created for the detail object. An example from the Salesforce standard objects cannot be provided for this scenario as a standard object can never be a detail object.