Query processing

Devart ODBC Driver for Dynamics 365 supports most core elements of the SQL-92 standard. Users can write queries using standard SQL as well as Dynamics 365’s native query language, FetchXML. Queries written in SQL are translated as follows:

• SELECT statements — Translated into FetchXML.
• UPDATE, INSERT, DELETE statements — Translated into corresponding Dynamics 365 API calls for data modification.

The driver supports two execution modes: remote and local.

Remote execution

When a user writes a SQL query that is fully supported by FetchXML, the driver translates it into FetchXML and sends it to Dynamics 365 for execution. This process is known as remote execution. Queries written directly in FetchXML are also executed in this mode.

Example

For instance, the driver translates the following SQL query into an equivalent FetchXML query:

SQL

SELECT Id, Amount FROM Opportunity;

FetchXML

<fetch>
  <entity name="opportunity">
    <attribute name="id"/>
    <attribute name="amount"/>
  </entity>
</fetch>

Local execution

If a SQL query contains expressions or operations that are not supported by FetchXML, the driver uses a two-step process to handle it.

Example

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 Dynamics 365. Instead, it performs the following steps:

1. The driver generates a simplified FetchXML query and sends it to Dynamics 365 for execution.

<fetch>
  <entity name="opportunity">
    <attribute name="id"/>
    <attribute name="amount"/>
  </entity>
</fetch>

The retrieved data is stored in the driver’s local cache.

2. The driver 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.