SQL queries with date comparisons fail with ODBC 8.0 Salesforce driver

Printable View

Information

Title	SQL queries with date comparisons fail with ODBC 8.0 Salesforce driver

URL Name	sql-queries-with-date-comparisons-fail-with-odbc-8-0-salesforce-driver

Article Number	000114743

Environment	Product: Progress DataDirect for ODBC for Salesforce Driver Version: 8.0 OS: All supported platforms Database: Salesforce.com Application: All supported applications

Question/Problem Description

Queries that worked with the version 7.1 Salesforce driver do not work with the version 8.0 driver.

This query worked with version 7.1:
select col from account where rec_date > '$$lastmodifieddate'
The query has to be changed as follows to work with ODBC 8.0:
select col from account where rec_date > {d '$$lastmodifieddate' }

Steps to Reproduce

Clarifying Information

Error Message	[DataDirect][ODBC Salesforce driver][Salesforce]syntax error or access rule violation: incompatible data types in combination

Defect Number

Enhancement Number

Cause

The original query compares a date to a timestamp.
The version 8.0 Salesforce driver has an updated SQL Engine, which has restrictions on which data types can be compared.

Resolution

Modify the query to compare data types as follows:

SQLtype	Boolean	Date	Double	Integer	timestamp	varchar
Boolean	Works	Error	Error	Works	Error	Works (if char string represents true/false)
Date	Error	Works	Error	Error	Error	Works (if string is valid date in quotes)
Double	Works	Error	Works	Works	Error	Works (if string is valid double value)
Integer	Works	Error	Works	Works	Error	Works(if string is valid integer value)
timestamp	Error	Error	Error	Error	Works	works (if String is valid Timestamp value)
varchar	Works	Works	Works	Works	Error	works

Workaround

Notes

References to other documentation:
Progress DataDirect for ODBC for Salesforce 8.0
SQL Query Compatibility
https://docs.progress.com/bundle/dci-pdfs/resource/datadirect-salesforce-odbc-80-sql-engine-upgrade-guide.pdf

Progress Article(s):
Strict type checking with DATE LITERALS in Salesforce ODBC driver

Last Modified Date	2/6/2021 12:25 AM

Files

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Progress, Telerik, Ipswitch, Chef, Kemp, Flowmon and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.

Sitefinity

NativeChat

Kendo UI

Telerik

Test Studio

Fiddler Everywhere

Chef

MOVEitNew Release

WS_FTP

OpenEdge

Kinvey

WhatsUp Gold

DataDirect

Corticon

Kemp

Flowmon

SQL queries with date comparisons fail with ODBC 8.0 Salesforce driver

Information

MOVEit
New Release