Stelo Technical Documents

SQDR: Salesforce Tips

Last Update: 15 May 2023
Product: SQDR & SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL050

Abstract

This technical document contains tips and troubleshooting hints when using SQDR for replication to or from Salesforce.

Contents:

• SQDR Salesforce ODBC driver is part of the optional SQDR_ODBC_XE package
• Occasional Error: Destination URL not reset
• Java VM options do not match previous options
• Forcing use of SQL Engine Direct Mode
• Specifying the location of the SchemaMap files
• Enable Logging
• Using a Proxy server
  

Install the optional SQDR_ODBC_XE package

The SQDR Salesforce ODBC driver is not included in a typical SQDR installation on Windows; it is part of the optional package SQDR_ODBC_XE. Note that there are two versions of the installer - one that will install or update JRE8, the other will install or update JRE17. On a combined tier deployment, use the installer with the version of JRE that is appropriate for your version of SQDR Plus - JRE8 for SQDR Plus v5, JRE17 for SQDR Plus v6. On a split tier deployment, we recommend using JRE17.

SQDR on Linux includes the SQDR Salesforce ODBC driver as part of the standard SQDR_ODBC package. The driver requires that a JRE (supplied with the operating system) is installed. In addition, modify the LD_LIBRARY_PATH and CLASSPATH environment variables as documented in the Linux ODBC Drivers appendix of the SQDR Help file.

Occasional Error: Destination URL not reset

Symptom:

A customer using the Salesforce driver as a destination of SQDR encountered a problem on their QA system:

Replication failed with the error:

Error applying change data for subscription MYSUB. SQL attempted: 'INSERT INTO "SFORCE ……………….89"}'. ODBC message: SQLSTATE HY000, native error 10014, [StarQuest][ODBC Salesforce driver][Salesforce]UNKNOWN_EXCEPTION: Destination URL not reset. The URL returned from login must be set in the SforceService in statement [INSERT INTO "SFORCE…..

Replication would resume if the SQDR Service was stopped for several minutes and then restarted.

The problem does not occur on their production system, which is more active than the QA system.

Solution:

Use TCP KeepAlive settings to maintain the connection to Salesforce.

• Append KA=1 (KA=KeepAlive) to the driver connection string.
• Use Regedit to change the system KeepAlive setting to a lower value (e.g. 300000) and reboot the system.

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters
KeepAliveTime: REG_DWORD

This value indicates how often TCP attempts to verify the connection
Measured in milliseconds--the default is 7200000 (2 hours)

References:

IBM: Enabling TCP/IP KeepAlive on Windows

Progress: Is it possible to set the TCP_KEEPALIVE socket option through the Connect for ODBC driver?

Progress: Salesforce error "Destination URL not reset"

Java VM options do not match previous options

Symptom:

When adding a new connection or modifying an existing one, the following message appears:

ODBC message: SOLSTATE HY000 native error 0, [StarQuest][ODBC Salesforce driver]
Java VM options do not match previous options

This example has been simplified - the actual message will show all the JVM options.

Solution:

Restart the SQDR service.

Explanation: This message appears because the JRE is already loaded in the SQDR process - i.e. the Salesforce driver has already been loaded by SQDR.

References:

Error: Java VM options do not match previous options, when connecting from ODBC Administrator to MongoDB

This KB article refers to the MongoDB driver and ODBC Admin as the application in question, but the same symptom and explanation apply to the Salesforce driver, which also uses a JRE, and the SQDR application.

Forcing use of SQL Engine Direct Mode

If you see a message SQL Engine service could not be reached, falling back to Direct, add the following property to the connection string to force the use of Direct mode, where the JRE runs in the SQDR process (typical) rather than as an external service. This is typically not needed, but specifying this simplifies the error output when you have an unrelated error condition.

SQLEngineMode=2

Specifying the location of the SchemaMap files

The driver uses a local schema map to instantiate the mapping of the remote Salesforce data model as tables and the metadata associated with those tables.

For an ODBC data source, use ODBC Administrator to specify the location of the SchemaMap files by entering a file name with a .config extension e.g. C:\temp\sqdr.config in the Schema Map field of the general panel.

For a connection string, add SchemaMap=C:\temp\sqdr.config or SMP=C:\temp\sqdr.config.

Note that as March 2021, the documentation incorrectly shows the short name as SM rather than SMP.

Several files will be created (if they don't already exist) in the specified directory. The directory must be writable by the user that the SQDRSVC service is running as (e.g. SYSTEM or a domain user).

If this value is not specified, the SchemaMap files are created in C:\Users\ServiceUser\AppData\Local\Progress\DataDirect\Salesforce_Schema, where ServiceUser is the user that the SQDRSVC service is running as. If SQDRSVC is running as Local System Account, the files are created in %WinDir%\System32\config\systemprofile\AppData\Local\Progress\DataDirect\SForce_Schema.

You can refresh the internal files related to an existing view of your data by using the SQL extension Refresh Map, which runs a discovery against your native data and updates your internal files accordingly. Note that REFRESH MAP is an expensive query.

You can also specify CreateMap=1 (short name CM) in the connection string or the Advanced tab of an ODBC data source to force the creation of new schema map files on a connection.

Enabling Logging

To enable logging:

• Copy ddlogging.properties from C:\Program Files\StarQuest\SQDR_ODBC_XE\Samples to a working directory e.g. C:\temp.
• Edit ddlogging.properties. We recommend using Wordpad or Notepad++, since the file has UNIX line endings)
• Change the destination location e.g.
  java.util.logging.FileHandler.pattern = C:\\temp\\ddlog%g.log
• If desired, change change these properties to FINEST
  datadirect.cloud.adapter.level
  datadirect.cloud.adapter.level
• For an ODBC data source, use ODBC Administrator to specify C:\temp\ddlogging.properties in the "Log Config file" field on the Advanced panel.
• For a connection string, add LogConfigFile=C:\temp\ddlogging.properties or LCF=C:\temp\ddlogging.properties.

Using a Proxy Server

The Salesforce driver can be used with a proxy server. Here is a sample connection string:

HostName=login.salesforce.com;TransactionMode=1;PXHN=myproxyserver;PXPT=3128

If the proxy server requires authentication, you can either:

• Configure the proxy server to allow the SQDR server to connect without authentication.

For example, when using squid on Linux, add the following to /etc/squid.conf and restart the squid service with systemctl restart squid.

acl mysqdr src 172.20.50.232/32
http_access allow mysqdr

• Configure the Salesforce driver to supply authentication
  • You must be using v8.0.0.0158 or later (supplied in SQDR_ODBC_XE 8.0.1.36 or later)
  • Use the keywords ProxyUser and ProxyPassword (PXPP)
  • Set the following in the net.properties file of the JRE:
    jdk.http.auth.proxying.disabledSchemes=
    jdk.http.auth.tunneling.disabledSchemes=