Salesforce via SQL – Preparations

Last Update: 2026-06-22

Overview

This article covers JDBC-based SQL access to a Salesforce CRM system via Lobster Integration (requires version > 4.6.11).

NOTE This article does not cover Salesforce's native HTTP/SOAP interface.

SQL access requires a paid add-on license. The CDATA JDBC driver, which is included with the license, must be installed.

 WARNING The CDATA JDBC driver translates SQL statements from Lobster Integration into Salesforce HTTP/SOAP web service calls. Keep SQL statements simple ( no complex JOIN conditions). The more complex the statements, the higher the risk of translation failures.

Salesforce Integration

Two steps are required to enable SQL access to Salesforce via Lobster Integration:

1) JDBC Driver

Once the license is activated, the JDBC driver appears automatically in the Update Center.

After downloading the driver, restart the Integration Server once before proceeding.

Linux:

Restart the Integration Server.

Windows:

Follow this sequence:

• Stop the Integration Server

• Run ./bin/hub.bat

• Terminate ./bin/hub.bat with Ctrl+C

• Start the Integration Server

See also: Lobster Application Wrapper for newer installations.

NOTE Check the Update Center regularly for driver updates.

2) Adding Database Aliases

Two aliases are required because the JDBC driver cannot handle both bulk and non-bulk operations through a single alias.

The bulk alias is used for Salesforce access in phase 5 (SQLBulkUnit). The non-bulk alias handles all other access (phases 1, 3, and 4). This separation prevents long-running SELECT statements from blocking bulk operations.

Create aliases under Configuration > Databases and Connectors. The import templates below provide ready-to-use starting points. Alternatively, use Templates > Templates (Presets) > Salesforce.

a) 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>

b) Non-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_nobulk</Set>
		            <Set name="allowGrowing">True</Set>
		            <Set name="database">jdbc:lobster:sforce2:Logfile="C:\Lobster\IS\logs\salesforce_jdbc\sf.log";Verbosity="3";Max Log File Size="10MB"</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>
		            <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>

c) Accessing Other Salesforce Instances

Production instances connect via login.salesforce.com, sandbox instances (clones of the production system) via test.salesforce.com.

To target a sandbox, add the following JDBC property to the alias. The default for Use Sandbox is false:

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

Salesforce also supports custom subdomains, e.g. yourcompany.my.salesforce.com

To use a custom URL, add the LoginURL parameter. This applies to both production and sandbox. For sandbox, also set Use Sandbox to true.

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

NOTE 60.0 is the Salesforce API version currently supported by the JDBC driver (as of 04/2024).

d) Proxy Configuration

Add all parameters to the alias via addNamedProperty.

Further details in the driver documentation: https://cdn.cdata.com/help/RFK/jdbc/Connection.htm

Configuring OAuth2+PKCE (Optional)

NOTE OAuth2+PKCE requires CData Salesforce driver version 25.0.9595.0 or later. This requirement applies to the driver only, independently of the Lobster Data Platform and the _data version.

Prerequisites for using OAuth2+PKCE with the Salesforce JDBC driver:

a) A connected app must be created in Salesforce.

b) The Client ID (Consumer Key) and Client Secret (Consumer Secret) must be known.

Refer to the official Salesforce documentation or consult your Salesforce administrator for details.

Multiple steps are required to enable automatic token renewal. A Lobster Integration instance with existing Salesforce access via username/password authentication (as described above) is a prerequisite.

Several procedures must be executed via the Admin Console or the SQL Console in the Plugins.

The following steps walk through retrieving the initial access token and refresh token.

NOTE Do not skip any step as they are sequential dependencies. The initial token retrieval can be done through one alias, but all aliases must be configured for OAuth2 at the end.

Step 1: Configure the Salesforce Alias

Add the following properties to the Salesforce alias under JDBC Properties. The CallbackURL must be http://localhost:33333 in both Salesforce and Lobster Integration. This is the standard callback URL for headless deployments.

Replace all placeholders in square brackets with actual values.

<Call name="addNamedProperty">
	<Arg>AuthScheme</Arg>
	<Arg>OAuth2PKCE</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>

Step 2: Retrieve the Authorization URL and PKCE Verifier

Start by retrieving the Salesforce authorization URL. Open the Admin Console and navigate to Tools > SQL Monitor. References to the SQL Monitor throughout this guide also apply to the SQL Console under Plugins.

Use the Salesforce alias configured in Step 1.

Execute the following command:

execute GetOAuthAuthorizationUrl

The response returns the Authorization URL and the PKCE verifier.

Add the PKCE verifier as property PKCEVerifier to the Salesforce alias. The driver requires this value in Step 3.

<Call name="addNamedProperty">
    <Arg>PKCEVerifier</Arg>
    <Arg>[PKCE-Verifier]</Arg>
</Call>

Example:

NOTE The authorization URL is partially URL-encoded. Decode %3D at the end of the state value to =. In the example, %3D%3D becomes ==. Do not decode the redirect_uri value.

Open the Authorization URL in a browser. The browser does not need to run on the Integration Server host.

The server responds with a redirect. The browser may show an error because localhost:33333 does not exist, but this is expected.

The redirect URL that appears in the browser address bar will look similar to this:

The value of the code parameter is the authorization code needed in the next step. In the example it is YVByeDR5X0t6c2NXenl5easdfasdfkRmRyakxtNlNScEJNR2hFbG8wNTlJb2t2c1IzTVJ1UHU0Skg3aXdfYjMzYkRiM2NrZ1FZbWc9PQ==

Step 3: Retrieve the Access Token and Refresh Token

Using the authorization code from Step 2, call the following stored procedure in the SQL Monitor:

execute GetOAuthAccessToken Verifier='<Authorization Code>'

This procedure returns a single-row result set. Extract the Access Token and Refresh Token values.

Step 4: Store Tokens in the Alias

Update the Salesforce alias under Configuration > Databases/Connectors. Add the Access Token and Refresh Token from Step 3, and change InitiateOAuth to REFRESH.

<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

Enabling Logging

To enable JDBC driver logging, add the following parameters to the alias:

<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> 

The log file contains a plain-text record of all exchanges between the JDBC driver and Salesforce (detail level depends on Verbosity).

Both the generated SQL and the underlying HTTP request/response traffic are captured.

For troubleshooting on a test system, Verbosity = 3 is typically sufficient. Disable logging once it is no longer needed.

 WARNING On production systems, enable logging only with caution as log files can grow rapidly.

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

Log File Rollover

When the Max Log File Size limit is reached, a rollover occurs. The current log file is renamed with a yyyyMMddHH-mmss timestamp, and a new log file (with the name specified in Logfile) is created. Subsequent log output goes to the new file.

Salesforce Technical Notes

The following sections describe Salesforce-specific technical characteristics relevant to JDBC-based integration.

Metadata Cache

On first access to a Salesforce object, the driver fetches the object's metadata and stores it in an internal cache (MetaCache).

This speeds up subsequent accesses to the same object but means the cache must be rebuilt whenever the object schema changes (e.g., fields added or removed).

Restart the Integration Server to force a full cache rebuild.

Salesforce Bulk API

Bulk jobs dramatically improve throughput for large-volume INSERT and UPDATE operations.

Salesforce enforces per-day limits on connections and batch counts (dependent on the licensed edition), so bulk transfer is preferable for large datasets.

 WARNING Salesforce bulk jobs are only created when the database alias has use Bulk API = true set and phase 5 uses the SQLBulkUnit.

No bulk job is created when the alias has use Bulk API= true set, but the INSERT/UPDATE is performed by phase 3 (function) or phase 4 (database node).

Using the SQLBulkUnit with an alias that has use Bulk API= false set produces a phase 5 error: Got no salesforce job-ID.

In the Salesforce UI, navigate to Setup > Environments > Jobs > Bulk Data Load Jobs to view running and completed bulk jobs.

A bulk job consists of 1–n batches, each containing 1–9,999 records (configurable via SqlBulkUnit – Max. SQL statements in batch).

Each database node in the Lobster Data Platform creates one Salesforce bulk job (even across multiple data sheets, the records from all sheets within a node are merged into a single job).

Salesforce bulk jobs are processed sequentially by Lobster Integration.

Individual batches within a bulk job are processed in parallel by Salesforce.

 WARNING delete before insert  cannot be used with the SQLBulkUnit in a Salesforce context.

Objects and Fields

Salesforce has standard objects (Type = Standard Object, provided out of the box, e.g. Account) and custom objects (Type = Custom Object). Each object has a label (displayed in the UI) and an API name (used for API access).

Custom objects are identifiable by their API name suffix __c.

Each object has one or more fields. Fields have a field label (for the UI) and a field name (for API access). Custom field names also end with __c.

The Salesforce Object Manager (Setup > Object Manager) provides detailed schema information for all objects and their fields.

Standard objects cannot be deleted, but additional fields and custom objects can be added. Standard fields cannot be deleted or modified.

Object Relationships

Salesforce supports two primary relationship types: Lookup and Master-Detail. Usage examples are covered in Salesforce via SQL – Tutorial.

Lookup Relationship

Lookup relationships are used when objects have a loose association.

Standard object example: Account and Contact. A Contact is a loosely coupled entity that can be associated with an Account but can also exist independently.

To associate a Contact with an Account, the AccountId field in Contact (of type Lookup(Account)) must be populated with the Account's internal ID. Once set, the Salesforce UI displays a navigation link from the Contact to the Account.

Master-Detail Relationship

In a Master-Detail relationship, the detail object has no independent existence as it is owned by the master. Deleting a master record cascades deletion to all associated detail records. The relationship field is always created on the detail object.

No standard object example is available here because standard objects cannot act as detail objects in a Master-Detail relationship.