Devart ODBC Driver for Salesforce supports most core elements of the SQL-92 standard. Users can write queries in both SQL and Salesforce’s native query language—SOQL. Queries written in SQL are translated in the following way:
The driver supports two execution modes: remote and local.
When a user writes a SQL query that is fully supported by SOQL, the driver translates it into SOQL and sends it to Salesforce for execution. This process is referred to as remote execution. Queries written directly in SOQL are also executed this way.
For more information about requirements that a SELECT must meet to be translatable, see SQL translation.
Example:
For example, the driver translates the following SQL query into an equivalent SOQL query.
SQL
SELECT Id, Amount FROM Opportunity;
SOQL
SELECT Id, Amount FROM Opportunity;
For more information about the requirements a SELECT must meet to be translatable, see SQL translation.
If a SQL query contains expressions or operations not supported by SOQL, the driver uses a two-step process to handle it.
For example, consider the following query, which contains the unsupported expression Amount + 1.
SELECT Id, Amount + 1 FROM Opportunity;
The driver cannot send this query directly to Salesforce. Instead, it performs the following steps:
1. Generates a simplified SOQL query and sends it to Salesforce for execution.
SELECT Id, Amount FROM Opportunity;
The retrieved data is stored in the driver’s local cache.
2. Executes the original SQL query SELECT Id, Amount + 1 FROM Opportunity against the local cache.
The approach is known as local execution since the final query runs on locally cached data.