Quick Start Guide
User Manual:
Open the PDF directly: View PDF .Page Count: 25
RELIERSOFT JDBC DRIVER FOR
SALESFORCE
The Salesforce JDBC driver is designed to work immediately with tools such as Squirrel, DBeaver and
DbVisualizer. If preferred, it may also be used in a Java IDE such as Eclipse, or added into a Maven build.
It provides SQL support for Salesforce.com (as opposed to SOQL), via a re-writing engine and via multi-pass
execution where necessary.
It also provides data manipulation language (DML) support with its support for SQL UPDATE, DELETE, INSERT,
UPSERT and MERGE statements.
CONTENTS
SQL Support
2
Select
2
Insert
2
Delete
2
Update
2
Upsert
3
Merge
3
Connection Parameters
4
System Requirements
4
Using the driver with Squirrel
5
Using the driver with DBeaver:
6
Using the driver with DbVisualizer:
13
Using the driver in an IDE
20
Connection String Parameters and Properties
21
Support
25
1
SQL SUPPORT
SELECT
Asterisk
SELECT * and SELECT [alias].* is supported.
Aggregation
Group By is supported, subject to Salesforce governor limits. SELECT DISTINCT is also supported, not subject to
Salesforce governor limits.
Joins
Select query is supported including joins (inner and left join).
Functions
The driver includes some functions to reduce the dependency on hardcoded Id references that may change
between Salesforce instances.
To_UserName(UserId) returns the User Name given the User Id (for example an OwnerId).
To_RecordType returns the Salesforce RecordTypeId from a given record type name.
INSERT
The driver supports both explicit INSERT and full-specification insert.
Full-specification insert involves listing out the columns being specified for the new record and is usually the
easiest form. For example
INSERT INTO account (name, LOCATION) values ('A test', 'Paris');
DELETE
The driver supports DELETE FROM with an optional WHERE clause to specify a condition around the
records deleted. For example, the following SQL will remove all accounts starting with the letter A.
delete from account where name like 'A%'
The connection parameter performHardDeletes causes records to be removed from the recycle bin after the
statement completes.
UPDATE
The driver supports UPDATE SET WHERE .
UPDATE ACCOUNT SET Site = 'Phone ' || Phone
WHERE Name between 'AAAAAAAAA' and 'CCCCCCCCC';
Expressions supported include concatenation (|| operator) and arithmetic (numeric types only).
2
UPSERT
The driver supports UPSERT INTO in a similar manner to an INSERT INTO query, however it also has
the optional clauses MATCHING ON to specify the field to use for lookup, and IGNORE NULL or INCLUDE NULL,
to specify whether to update fields to null (empty string) in the operation. If these optional clauses are not
specified, they assume the default behavior per connection parameters.
For example,
upsert into custom__c (Id, Name, NormalText__c)
values ('a002800000bsFe4AAE', 'Tescns', 'hello')
MATCHING ON Id, Name IGNORE NULL
MERGE
Merge is supported from a target-based approach. In a SQL MERGE statement there is a source and target and
matching on clause. The target is always a table, while the source can be either table of select query.
With matching on clause we make criteria for matching source columns with target columns.
There are two possible actions when matched and when not matched.
When matched we can do following SQL statements:
INSERT, inserts new record into target table with new data or with data found in source, or some
columns from source and some with constants.
UPDATE can update record in target table with new data or with data from source or mixed (some
columns from source some columns with constants).
DELETE deletes record from target table
When not matched we can do following SQL statements:
INSERT inserts a new record with constants.
For example,
merge into account
using (select name from custom__c) c on account.name = c.name
when matched then update set account.name = c.name
when not matched then insert (name) values ('not matched')
3
CONNECTION PARAMETERS
Connection parameters can be set by appending the connection URL string, beginning with "?", delimiting
property=value pairs with "&". An example URL is,
jdbc:sforce://login.salesforce.com?proxyHost=127.0.0.1&proxyPort=8888&proxyUser=me&proxyPassword=he
llo&acceptAllCertificates=true&failIfFieldToLong=false
See the end of the document for the full list of properties available.
Alternatively, configuration can be set in Java code using the Property object.
SYSTEM REQUIREMENTS
Minimum
o Java 7.
o At least 200 megabytes memory available to Java virtual machine.
Recommended
o 4-core CPU for XML to table transformation
o Java Virtual Machine running with at least 4096 megabytes of available memory (subject to
usage requirements). Refer to application-specific instructions on how to set this in
respective tools.
4
USING THE DRIVER WITH SQUIRREL
Connector Configuration
Setting up the driver in Squirrel is as easy as clicking on the Drivers tab on the left, and configuring the
Salesforce entry.
1) In the Drivers tab on the left of the screen, click the plus button to add a new driver. Enter any name you
wish. Click the "Extra Class Path" tab, and the "Add" button. Locate the driver on your computer. Click the "List
Drivers" button. You should see com.reliersoft.sforce.jdbc.Driver in the Class Name at the bottom.
2) In the "Example URL" field, enter "jdbc:sforce://login.salesforce.com" and click OK.
3) In the "Aliases" tab, click the plus button to add a new alias. Enter any name you wish. Select the driver, and
enter your Salesforce User Name and Password. Update the connection URL to configure any optional settings
desired per "Connection Parameters". Click OK.
4) Double click on the alias name and click "Connect" to connect to Salesforce. Once successfully connected,
Salesforce information will be shown.
Successful Salesforce connection using Squirrel
Recommended: Increase Memory Allocation
On a 64-bit operating system and JVM, it is recommended to edit \ squirrel-sql\squirrel-sql.bat file to increase the amount of memory that can be used to handle result sets. After
opening this file, look for the “–Xmx” part, probably on the second last line and defaulted to “-Xmx256m”, and
edit it to allow a larger memory allocation, with 4096m being the recommended value (if the machine allows).
For example, the line may become as follows (change is highlighted in yellow).
start "SQuirreL SQL Client" /B "%LOCAL_JAVA%" –Xmx4096m -Dsun.java2d.noddraw=true
-cp %SQUIRREL_CP% -splash:"%SQUIRREL_SQL_HOME%/icons/splash.jpg"
net.sourceforge.squirrel_sql.client.Main %TMP_PARMS%
The maximum rows to return field, shown below and found in the top right of the SQL Editor window, can then
be edited from the default 100 to a larger value to return larger grids (or disabled completely).
If an error is returned indicating the file cannot be saved, check file ownership (Windows 10) and user rights.
5
USING THE DRIVER WITH DBEAVER:
Connector Configuration
1) Firstly configure the driver using Database -> Driver Manager
2) If Salesforce driver does not already exist, use the URL template "jdbc:sforce://login.salesforce.com", and
class name "com.reliersoft.sforce.jdbc.Driver". Other attributes can be left blank. Click Find Class, and select
the entry that appears. Click Ok.
6
3) Set up the new connection using Database -> New Connection, choose "Salesforce".
Recommended: Increase Memory Allocation
On a 64-bit operating system and JVM, it is recommended to edit \dbeaver\dbeaver.ini file
and add a –vmargs sections like follows if it is not already defined. Use this to allocate at least 4096 megabytes
of memory and support larger sets of data (subject to machine memory). For example, dbeaver.ini may appear
as follows.
-startup
plugins/org.eclipse.equinox.launcher_1.3.100.v20150511-1540.jar
--launcher.library
plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.300.v20150602-1417
-showsplash
-vmargs -Xms4096m
The maximum rows to return field, highlighted below, can then be edited from the default 200 to a larger
value to return larger grids.
If an error is returned indicating the dbeaver.ini file cannot be saved, check file ownership (Windows 10) and
user rights.
7
Table Information
Table Description
Description of the object.
8
Column Information
Description
Description of the attribute.
9
Data Tab
Export
Right click -> Export.
Filter
Click the filter funnel.
Sort
Click on the column header icon.
10
Indices, Foreign Keys and Global References
11
References
The object relationships within Salesforce. This is similar to the view shown in the Schema browser.
12
USING THE DRIVER WITH DBVISUALIZER:
Connector Configuration
1) In DbVisualizer, go to the Driver Manager (Tools in the top menu, then Driver Manager...) and click the
"Create a new driver" button.
2) Enter any name you wish. The URL Format can remain empty. In the "Driver File Paths" section, open up the
driver.
3) Once the driver has been added, in the main screen of DbVisualizer, you may use the following settings to
connect to Salesforce (entering your Salesforce Userid and Password):
The settings should be as follows:
Database
Type:
Database URL: jdbc:sforce://login.salesforce.com?anyoptionalparameters
Generic
Recommended: Increase Memory Allocation
On a 64-bit DbVisualizer distribution, it is recommended to edit \DbVisualizer\dbvis.vmoptions
file and replace the first line with the following to allocate at least 4096 megabytes of memory and support
larger sets of data (subject to machine memory). For example,
-Xmx4096m
-XX:MaxPermSize=192m
-XX:StringTableSize=1000003
If an error is returned indicating the file cannot be saved, check file ownership (Windows 10) and user rights.
13
Once connected, the Salesforce schema will show and be visible through the DbVisualiser tabs.
14
Table Information
Remarks
Description of the object.
Is_External_Object
This indicates if the object is from at external data source. It will be NO for all default and custom objects.
15
Column Information
Remarks
Description of the attribute.
Column_Label
The Label value defined for the attribute. Usually, this is the value labelling the field in the front-end Layout.
Is_System_Populated
Whether the attribute is populated automatically.
Is_Auto_Unique
Whether the attribute is populated automatically in a unique manner.
Is_External_Id
Whether this attribute identifies the entity in an external system. This option is chosen when a field is defined
and is Yes or No value, applicable to any data type.
16
Data Tab
Export
Right click -> Export.
Filter
Click the filter funnel.
Row Count
The number of rows in the table, as reported by Salesforce metadata.
17
Indices
The indexed columns on Salesforce. Usually, this is Id, plus reference attributes, plus external Id attributes.
Sometimes, there will be other fields Salesforce has indexed.
18
References
The object relationships within Salesforce. This is similar to the view shown in the Schema browser.
19
USING THE DRIVER IN AN IDE
In any Java IDE such as Eclipse, you may add the driver to your build path and connect through your Java
application. Below is an example:
public class main {
public static void main(String[] args) {
try {
Class.forName("com.reliersoft.sforce.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
try {
Connection connection = null;
String salesforceUsername = "";
String salesforcePassword = "";
connection = DriverManager.getConnection(
"jdbc:sforce://login.salesforce.com",
salesforceUsername,salesforcePassword);
System.out.println("Connected to Salesforce.");
} catch (SQLException e) {
e.printStackTrace();
return;
}
}
}
20
CONNECTION STRING PARAMETERS AND PROPERTIES
locale
Example value: en_US
Default value: en_US
The language to use for the display of metadata (table, column and related information). This does not affect
the display of time-of-day (timezones) and currency (monetary) amount displays, which are determined by the
user settings.
proxyHost
Example value: myproxy.com
Default value: [system settings]
If a specific proxy needs to be specified, it can be entered using this parameter, providing either an IPv4
address, IPv6 address or a hostname.
proxyPort
Example value: 8080
Default value: [system settings]
If a specific proxy server is being specified, the TCP/IP port number it is reachable on.
proxyUser
Example value: fred
Default value: (none)
If a specific proxy server is being specified and requires an explicit authentication, the username to use to
authenticate with the proxy server.
proxyPassword
Example value: pASS
Default value: (none)
If a specific proxy server is being specified and requires an explicit authentication, the password to use to
authenticate with the proxy server.
fetchSize
Example value: 1000
Default value: 2000
The fetch size to use in SOQL calls to SFDC. Valid values are between 1 and 2000, according to Salesforce limits.
The maximum value is 2000 however Salesforce may use a lower value for very wide objects. The driver may
multi-thread requests for chunks to impove fetch performance.
21
batchSize
Example value: 400
Default value: 200
For DML operations, this is the maximum number of operations per submission to Salesforce back-end. For
example, DELETE FROM [object], where object has 10,000 records and the batchSize is 200, will result in 20
submissions to Salesforce. Additionally, if JDBC batch methods are used, then this is the limit prior to
submitting the batch.
For example if the batch size is 200 and the following code is performed for 10,000 inserts, there will be 50
request messages sent to Salesforce,
public static void main(String[] args) {
Connection conn;
Statement stmt;
props = new Properties();
props.setProperty("user","Salesforce.User@org.com");
props.setProperty("password","abcdef");
url = "jdbc:sforce://test.salesforce.com";
try {
conn = DriverManager.getConnection(url, props);
stmt = conn.createStatement();
sql="insert into Account (name) values ('hello world!')";
// insert this record ten thousand times
for ( int i = 0; i < 10000; i++) {
stmt.addBatch(sql);
}
}
catch (Exception e) {
e.printStackTrace();
}
}
Note that in some cases, where there is a large overhead in relation to an operation, this value may need to be
reduced to very low values in order not to exceed Salesforce API limits.
compression
Example value: false
Default value: true
Sets whether to enable compression for downloads from SFDC. Generally this option is recommended.
failIfFieldTooLong
Example value: false
Default value: true
The behaviour if an input field is too large for its target field. If this is set to false, then a value too long to fit a
field will be truncated to fit into the field. If this is set to true, it will error instead. The default is true (to fail the
record).
22
stopOnErrors
Example value: true
Default value: false
If an error is encountered during a DML operation, then if this is set to true, an exception will be thrown and
there will be no further requests sent to SFDC in relation to that statement. If set to false (the default),
execution will continue after the first error. For example, some records may fail an update and some may not.
setFieldsToNullInUpdates
Example value: false
Default value: true
If a value for update is null or an empty string, and this has been set to false, the value will not be changed.
The default value is true, meaning that by default all updates are performed including those that set a value to
nothing.
Note, Salesforce treats nulls and empty strings as the same. Consequently, Salesforce JDBC driver also treats
null and empty string as equivalent.
performHardDeletes
Example value: true
Default value: false
When set to true, the default behaviour of moving deleted records to the recycle bin is replaced by a hard
delete, whereby records are removed from the recycle bin immediately after a DELETE statement is
performed. This may be necessary if Data Usage limits have been reached.
setFieldsToNullInUpserts
Example value: false
Default value: true
When set to true, this attribute will set fields to null if the value specified or resolved in an UPSERT SQL
statement comes out to null (or empty string, which is equivalent in the context of Salesforce JDBC).
This behaviour can be overridden at the SQL statement level by the optional IGNORE NULL and INCLUDE NULL
clauses of UPSERT.
Note, Salesforce treats nulls and empty strings as the same. Consequently, Salesforce JDBC driver also treats
null and empty string as equivalent.
acceptAllCertificates
Example value: true
Default value: false
If set to true, SFDC certificate is not validated. Default false.
readTimeout
Example value: 20000
Default value: 30000
The time to wait for a response from SFDC before timing out, in milliseconds. If waiting more than this time for
a response from SFDC, a timeout exception is thrown. Default is 30 seconds.
23
connectTimeout
Example value: 60000
Default value: 30000
The timeout value applicable when connecting, in milliseconds. If waiting more than this time for a response
from SFDC, a timeout exception is thrown. Default is 30 seconds.
timeout
Example value: 15000
Default value: (none)
Timeout value for both readTimeout and connectTimeout.
token
Example value: d8OVlZ7hnPefP4mLT1x7F4Sy0
Default value: (none)
Salesforce security token in relation to the user account (User settings -> Reset my security token).
Alternatively, the security token can be appended to the password (which is equivalent).
api
Example value: 36.0
Default value: 35.0
Target back-end API version. Valid values are of the form [integer].[integer]
Note, API versions much younger or older than the driver release date, may not be compatible with the driver.
To resolve this, check the website for a new version of the driver. The current driver version can be found with
getDriverVersion().
The default API version may change with newer releases of the driver. To check the API endpoint version being
used, refer to getDatabaseProductVersion().
schema
Example value: MYSANDBOX
Default value: (none)
Sets a schema name for all database metadata to be presented under. Default is no schema.
fetchThreads
Example value: 1
Default value: (equal to the number of logical CPU cores times two)
The number of threads used to fetch data from the result of a back-end SOQL statement. More threads will
deliver higher throughput but use more CPU.
24
SUPPORT
Please contact support@reliersoft.com for any enquiries.
25
Source Exif Data:
File Type : PDF
File Type Extension : pdf
MIME Type : application/pdf
PDF Version : 1.5
Linearized : No
Page Count : 25
Language : en-US
Tagged PDF : Yes
Author : Simon
Creator : Microsoft® Office Word 2007
Create Date : 2016:11:16 04:02:59+11:00
Modify Date : 2016:11:16 04:02:59+11:00
Producer : Microsoft® Office Word 2007
EXIF Metadata provided by EXIF.tools
.