Easysoft ODBC-Salesforce Driver User's Guide - Configuration

Configuring the Easysoft ODBC-Salesforce Driver

The Easysoft ODBC-Salesforce Driver is installed on the computer where your applications are running. ODBC applications access ODBC drivers through the ODBC Driver Manager and a data source. The data source tells the Driver Manager which ODBC driver to load, which Salesforce server to connect to and how to connect to it. This chapter describes how to create data sources, use DSN-less connections and configure the Easysoft ODBC-Salesforce Driver.

Before setting up a data source, you must have successfully installed the Easysoft ODBC-Salesforce Driver.

For Easysoft ODBC-Salesforce Driver installation instructions, see Installation.

Chapter Guide

Configuring the Easysoft ODBC-Salesforce Driver

This section describes how to configure the Easysoft ODBC-Salesforce Driver to connect to Salesforce by using a data source or a DSN-less connection string. The section assumes you are, or are able to consult with, a database administrator.

Setting Up Data Sources on Unix

There are two ways to set up a data source to your Salesforce data:

¯ OR ¯

By default, the Easysoft ODBC-Salesforce Driver installation creates SYSTEM data sources named [SALESFORCE_SAMPLE] and [SFSL_SAMPLE]. If you are using the unixODBC included in the Easysoft ODBC-Salesforce Driver distribution, the SYSTEM odbc.ini file is in /etc.

If you built unixODBC yourself, or installed it from some other source, SYSTEM data sources are stored in the path specified with the configure option --sysconfdir=directory. If sysconfdir was not specified when unixODBC was configured and built, it defaults to /usr/local/etc.

If you accepted the default choices when installing the Easysoft ODBC-Salesforce Driver, USER data sources must be created and edited in $HOME/.odbc.ini.


Note

To display the directory where unixODBC stores SYSTEM and USER data sources, type odbcinst -j.

By default, you must be logged in as root to edit a SYSTEM data source defined in /etc/odbc.ini.


You can either edit the sample data source or create new data sources.

Each section of the odbc.ini file starts with a data source name in square brackets [ ] followed by a number of attribute=value pairs.


Note

Attribute names in odbc.ini are not case sensitive.


The Driver attribute identifies the ODBC driver in the odbcinst.ini file to use for a data source.

When the Easysoft ODBC-Salesforce Driver is installed into unixODBC, it places an Easysoft ODBC-Salesforce entry in odbcinst.ini. For Easysoft ODBC-Salesforce Driver data sources therefore, you need to include a Driver = Easysoft ODBC-Salesforce entry.

To configure a Salesforce data source, in your odbc.ini file, you need to specify:

For example:

 [Salesforce]

 Driver = Easysoft ODBC-Salesforce

 User = myuser@mydomain

 Password = mypassword

¯ OR ¯

 [Easysoft SalesforceSlim]

 Driver = Easysoft ODBC-Salesforce

 User = myuser@mydomain

 Password = mypassword

If you are not logging in from a trusted network, you also need to specify the security token for your Salesforce user. For example:

 Token = A1B2cDefG3HijKlMNOPQRS45

For more information about security tokens, see Token.

Environment

The Easysoft ODBC-Salesforce Driver must be able to find the following shared objects, which are installed during the Easysoft ODBC-Salesforce Driver installation:

By default, this is located in /usr/local/easysoft/unixODBC/lib.

By default, this is located in /usr/local/easysoft/lib.

By default, this is located in /usr/local/easysoft/lib.

You may need to set and export LD_LIBRARY_PATH, SHLIB_PATH or LIBPATH (depending on your operating system and run-time linker) to include the directories where libodbcinst.so, libeslicshr.so and libessupp.so are located.


Note

The shared object file extension (.so) may vary depending on the operating system (.so, .a or .sl).


Establishing a Test Connection

The isql query tool lets you test your Easysoft ODBC-Salesforce Driver data sources.

To test the Easysoft ODBC-Salesforce Driver connection

1.  Change directory into /usr/local/easysoft/unixODBC/bin.

2.  Type ./isql.sh -v data_source, where data_source is the name of the target data source.

3.  At the prompt, type an SQL query. For example:

 SQL> select * from firsttable;

¯ OR ¯

 Type help to return a list of tables:

 SQL> help

 

Setting Up Data Sources on Windows

To connect an ODBC application on a Windows machine to a Salesforce database:

1.  Open ODBC Data Source Administrator:

 The ODBC Data Source Administrator dialog box is displayed:

2.  Select the User DSN tab to set up a data source that only you can access.

¯ OR ¯

 Select the System DSN tab to create a data source which is available to anyone who logs on to this Windows machine.

3.  Click Add... to add a new data source.

 The Create New Data Source dialog box displays a list of drivers:

4.   If you want to use SQL statements from your client application, select Easysoft ODBC-Salesforce Driver and click Finish.

¯ OR ¯

 If you want use Salesforce SOQL statements from your client applications, select Easysoft ODBC-Salesforce SOQL Driver and click Finish.

 The DSN Setup dialog box is displayed:

For details of the attributes that can be set on this dialog box, see Attribute Fields.


64-bit Windows

The Easysoft installer program installs both a 32-bit and a 64-bit version of the Easysoft ODBC-Salesforce Driver. If you want to use a 64-bit ODBC application, you need to use the 64-bit Easysoft ODBC-Salesforce Driver. If you want to use a 32-bit ODBC application, you need to use the 32-bit Easysoft ODBC-Salesforce Driver.

There is both a 32-bit and a 64-bit version of ODBC Administrator. The 64-bit ODBC Administrator is located in Control Panel under Administrative tools. To access the 32-bit ODBC Administrator in Windows 7 and earlier, in the Windows Run dialog box, type:

%windir%\syswow64\odbcad32.exe

On Windows 8 and later, both the 32-bit and 64-bit ODBC Administrator are located in Control Panel under Administrative tools: ODBC Data Sources (32-bit) and ODBC Data Sources (64-bit).

Easysoft ODBC-Salesforce Driver data sources created in the 64-bit ODBC Administrator will specify the 64-bit version of the Easysoft ODBC-Salesforce Driver. Easysoft ODBC-Salesforce Driver data sources created in the 32-bit ODBC Administrator will specify the 32-bit version of the Easysoft ODBC-Salesforce Driver.

If you want to create an Easysoft ODBC-Salesforce Driver System data source for use with a 64-bit application, use the 64-bit ODBC Administrator. If you want to create an Easysoft ODBC-Salesforce Driver System data source for use with a 32-bit application, use the 32-bit ODBC Administrator.

For Easysoft ODBC-Salesforce Driver User data sources, it does not matter which version of the ODBC Administrator that you use.


Attribute Fields

This section lists the attributes which can be set for the Easysoft ODBC-Salesforce Driver in a table showing:

Attributes which are text fields are displayed as value.

Attributes which are logical fields can contain either 0 (to set to off) or 1 (to set to on) and are displayed as "0|1".

If an attribute can contain one of several specific values then each possible entry is displayed and separated by a pipe symbol.

For example, in the statement:

DIALECT=1|2|3

the value entered may be "1", "2" or "3".

Fields Common to Both Drivers

These following fields are common to both the Easysoft ODBC-Salesforce Driver and the Easysoft Salesforce SOQL ODBC Driver.

DSN

The name of the User or System data source to be created, as used by the application when calling the SQLConnect or SQLDriverConnect functions.

Interface Value

DSN Dialog Box (Windows)

DSN

odbc.ini file (Unix)

[value]

Connect String

DSN=value

Description

Descriptive text that may be retrieved by certain applications to describe the data source.

Interface Value

DSN Dialog Box (Windows)

Description

odbc.ini file (Unix)

Description=value

Connect String

Not Used

Logon Uri

The URI of the Salesforce login server. You should not need to change the default value for this attribute, unless you are connecting to a sandbox server, in which case replace "login" with "test". For example: https://test.salesforce.com/services/Soap/u/34.

Interface Value

DSN Dialog Box (Windows)

Logon Uri

odbc.ini file (Unix)

Uri=value

Connect String

URI=value

User Name

The name of your Salesforce.com, Database.com or Force.com user. For example, myuser@mydomain.com.

To specify the user name in the connection string, use UID rather than User. For more information about specifying Easysoft ODBC-Salesforce Driver attributes in the connection string, see DSN-less Connections.

Interface Value

DSN Dialog Box (Windows)

User Name

odbc.ini file (Unix)

User = value

Connect String

UID=value

Password

The password for the user you specified with User Name.

To specify the password in the connection string, use PWD rather than Password.



Interface Value

DSN Dialog Box (Windows)

Password

odbc.ini file (Unix)

Password = value

Connect String

PWD=value

Token

The security token for the user you specified with User Name.

When you access Salesforce by using the Web Services API (which is what the Easysoft ODBC-Salesforce Driver uses) you need to supply a security token when logging in, unless you are logging in from a trusted network. (A trusted network is one whose IP addresses fall within the range specified in the Salesforce Network Access administration page.)

If you do need to specify a security token and do not supply one, your connection attempt will fail with an error message that contains:

LOGIN_MUST_USE_SECURITY_TOKEN

Salesforce emails the security token to the email address associated with your Salesforce user account. If you have not received a security token, you can regenerate it. Salesforce will then email the new security token to you. To regenerate your security token, log in to Salesforce and then choose Setup from the user menu. Search for "security token" in the Quick Find box. Click Reset Security Token in the Reset Security Token page. When you receive the token in your email client, copy it and then paste it into your data source / connection string. For example:

Token = vVocPXPuAfTT5frONl2Vygp4



Interface Value

DSN Dialog Box (Windows)

Token

odbc.ini file (Unix)

Token = value

Connect String

TOKEN = value

Show Archive

When ON (checked or set to "Yes"), the Easysoft ODBC-Salesforce Driver calls the Salesforce QueryAll method rather than the Query method when you execute a query. The QueryAll method allows you to query Task and Event records whether archived or not. If you need to query archived tasks and events therefore, enable the Show Archive attribute in your data source.

If you have a data source that was configured using an earlier version of the driver, you may also need to alter the Login Uri attibute value. You need to make sure that the Salesforce API specified in the URI supports the QueryAll method, which was introduced in version 29. The simplest way to do this on Windows is to create a new Easysoft ODBC-Salesforce Driver data source in ODBC Adminstrator. In the dialog box, copy the Login URI value, and then use the Cancel button to discard the data source. In your existing data source, replace the Login URI value with the new value. On Unix, look for the file dsn_template in the driver distribution. The uri value specifed here is the one to use.



Interface Value

DSN Dialog Box (Windows)

Show Archive

odbc.ini file (Unix)

Archive = Yes | No

Connect String

ARCHIVE = Yes | No

Domain

The domain portion of your Salesforce user name. If you are configuring a data source for use with an Oracle Heterogeneous Service agent (HSODBC or DG4ODBC), you need to use both the User Name and Domain attributes to supply your user name. For example, if your Salesforce user name is myuser@mydomain, you would specify it in your data source like this:

User = myuser

Domain = mydomain

Omit the @ symbol.



Interface Value

DSN Dialog Box (Windows)

Not Available for Easysoft ODBC-Salesforce Driver

odbc.ini file (Unix)

Domain = value

Connect String

DOMAIN = value

Easysoft Easysoft ODBC-Salesforce Driver Fields

These fields are only applicable to the Easysoft ODBC-Salesforce Driver.

Proxy

If you use a proxy server connect to Salesforce, use the Proxy attribute to specify this server's details. The Proxy attribute has the following format:

http://address:port

where addess is the host name or IP address of the proxy server and port is the proxy server port. For example:

http://squid.example.com:8080



Interface Value

DSN Dialog Box (Windows)

Logon Uri

odbc.ini file (Unix)

Proxy=value

Connect String

PROXY=value

Proxy User

If your proxy server has authentication enabled, use this attribute to supply a user name that can connect to the proxy server.

The Easysoft ODBC-Salesforce Driver supports the Basic and Digest proxy authentication schemes.



Interface Value

DSN Dialog Box (Windows)

Proxy User

odbc.ini file (Unix)

Proxy_User = value

Connect String

PROXY_USER=value

Proxy Password

The password for the user you specified with Proxy User.



Interface Value

DSN Dialog Box (Windows)

Proxy Password

odbc.ini file (Unix)

Proxy_Password = value

Connect String

PROXY_PASSWORD=value

Map Keyword

Use this option if you need to work with a Salesforce object whose name is a reserved word in SQL and your application does not allow you to quote identifiers such as table and column names. For example, Salesforce has a User object, and "USER" is a reserved word. To work around this, the Easysoft ODBC-Salesforce Driver will append the value you specify with Map Keyword to any reserved word. For example, if you specify a value of "tab" with Map Keyword, the Easysoft ODBC-Salesforce Driver will represent the User object as "Usertab". This is the name that your application will display when retrieving a list of tables and this is the name that you need to use in your SQL.



Interface Value

DSN Dialog Box (Windows)

Map Keyword

odbc.ini file (Unix)

Map_Keyword = value

Connect String

MAP_KEYWORD = value

Local Filtering

When ON (checked or set to "Yes"), the Easysoft ODBC-Salesforce Driver increases its preferred batch size for queries, which alters the number of Salesforce API calls it makes. Salesforce may create batches that are larger or smaller than the requested size to maximise performance.

There is no one correct setting for Local Filtering, it depends on how your application executes its queries. You may find that Local FIltering increases query performance for one application and decreases performance for another.

By default, Local Filtering is OFF.



Interface Value

DSN Dialog Box (Windows)

Local Filtering

odbc.ini file (Unix)

Filter_Local = Yes | No

Connect String

FILTER_LOCAL = Yes | No

Cache Timeout

The time in minutes that Salesforce records wil be stored in the Easysoft ODBC-Salesforce Driver's cache. The default timeout is 15 minutes. If you set the timeout to 0, the Easysoft ODBC-Salesforce Driver will not cache records.

To reduce Salesforce API usage and reduce the amount of data retrieved over the network, the Easysoft ODBC-Salesforce Driver caches Salesforce records. By default, a record will remain in the cache until the timeout has elapsed.

Note that any changes to the record that happen at the Salesforce end will not be reflected in the cached copy until the cache is refreshed (i.e. the timeout expires and the record is fetched again.) If a cached record is changed by the Easysoft ODBC-Salesforce Driver, the cache is refreshed to reflect this change.



Interface Value

DSN Dialog Box (Windows)

Cache Timeout

odbc.ini file (Unix)

Query_Timeout = num

Connect String

QUERY_TIMEOUT = num

Custom Null

By default, the Easysoft ODBC-Salesforce Driver reports SQL_NULLABLE for custom columns as they may be NULL even though the column definition in Salesforce does not allow NULLs. When ON (set to Yes), the Easysoft ODBC-Salesforce Driver reports SQL_NO_NULLS for custom columns. This setting was introduced as a workaround for customers who were getting the SQL Server linked server error "inconsistent metadata for a column" when working with custom columns.



Interface Value

DSN Dialog Box (Windows)

Custom Null

odbc.ini file (Unix)

Cust_Null = num

Connect String

CUST_NULL = Yes | No

DisguiseLong

When ON (set to Yes), the Easysoft ODBC-Salesforce Driver describes SQL_LONGVARCHAR fields as SQL_VARCHAR fields and SQL_LONGVARBINARY fields as SQL_VARBINARY fields. The maximum length the driver returns for such fields is 8000.

By default DisguiseLong is OFF.



Interface Value

DSN Dialog Box (Windows)

Not Available

odbc.ini file (Unix)

DisguiseLong = Yes | No

Connect String

DISGUISELONG = Yes | No

Easysoft ODBC-Salesforce Slim Driver Fields

These fields are only applicable to the Easysoft ODBC-Salesforce SOQL Driver.

Private Key File

The private key for use with the SSL session.



Interface Value

DSN Dialog Box (Windows)

Private Key File

odbc.ini file (Unix)

PrivateKeyFile = value

Connect String

PRIVATEKEYFILE = value

Certificate File

The file that contains the public key certificate of the CA that signed the Salesforce certificate. The CA certificate file must be in base-64 PEM format.

If the CA certificate is not installed on your client machine, you need to export the certificate and install it on the client.



Interface Value

DSN Dialog Box (Windows)

Certificate File

odbc.ini file (Unix)

CertificateFile = value

Connect String

CERTIFICATEFILE = value

Trust Cert

Whether the Easysoft ODBC-Salesforce SOQL Driver tries to validate the server certificate to verify the identity of the Salesforce machine. Normally, you should leave Trust Cert set to Yes.



Interface Value

DSN Dialog Box (Windows)

Trust Cert

odbc.ini file (Unix)

TrustServerCertificate = 0 | 1

Connect String

TRUSTSERVERCERTIFICATE = 0 | 1

DSN-less Connections

In addition to using a data source, you can also connect to a database by using a DSN-less connection string of the form:

SQLDriverConnect(..."DRIVER={Easysoft ODBC-SalesForce};

UID=user;PWD=password;"...)

where user and password are a valid Salesforce user name and password and database is the Salesforce database you want to connect to. You need to use the Easysoft ODBC-Salesforce DRIVER keyword to identify the Easysoft ODBC-Salesforce Driver.

Other Easysoft ODBC-Salesforce Driver attribute settings, as described in Setting Up Data Sources on Unix, can be added to the connection string using the same PARAMETER=value; format. For example, the following connection string connects to a Salesforce instance that requires the user to supply a security token.

Windows example:

"DRIVER={Easysoft Salesforce ODBC Driver};UID=myuser;PWD=mypassword;TOKEN=A1B2cDefG3HijKlMNOPQRS45"

¯ OR ¯

"DRIVER={Easysoft Salesforce ODBC SOQL Driver};UID=myuser;PWD=mypassword;TOKEN=A1B2cDefG3HijKlMNOPQRS45"

Linux example:

"DRIVER={Easysoft ODBC-SalesForce};UID=myuser;PWD=mypassword;TOKEN=A1B2cDefG3HijKlMNOPQRS45"

¯ OR ¯

"DRIVER={Easysoft SalesforceSlim};UID=myuser;PWD=mypassword;TOKEN=A1B2cDefG3HijKlMNOPQRS45"


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.