Supported SQL (reference)

This reference shows which SQL statements the driver translates into OData requests, how it translates them, and what it rejects. You need this page if a query from Access, ADO.NET, or VBA doesn't run as expected, or if you want to write a query so that it works without custom statements. The concept How the driver translates statements gives the overview. Which sets and columns exist and where their types come from is described first in How an OData service becomes a relational ODBC model.

SELECT <COLUMNS | aggregates> FROM <entity-set> [WHERE <condition>] [GROUP BY <columns> [HAVING <condition>]]
  [ORDER BY <columns> [OFFSET n ROWS [FETCH NEXT m ROWS ONLY]]]
INSERT INTO <entity-set> (…) VALUES (…)
UPDATE <entity-set> SET … WHERE <key> = ? [AND <row-version> = ?]
DELETE FROM <entity-set> WHERE <key> = ? [AND <row-version> = ?]

A translated statement reads exactly one entity set. Everything else goes to the address for custom statements or is rejected.

Access sends totals and groups over a linked table to the driver regardless of what SQLGetInfo says about it, and doesn't compute them itself if the driver refuses – the query then fails with “ODBC–call failed” (also with SQL_ODBC_SQL_CONFORMANCE = 0). With an address for custom statements, they go to the database unchanged. Without one, the driver translates them to $apply (OData Data Aggregation 4.0) – but only if the schema document announces Org.OData.Aggregation.V1.ApplySupported with the required transformations. A service without this announcement would at best answer $apply with an error and at worst, if it ignores unknown options, with the ungrouped rows, which the application would take for the groups.

Access doesn't send AVG, but SUM and COUNT(x), and divides itself; for StDev and Var, additionally SUM(x * x); it keeps TOP n; ORDER BY names the column fully qualified ("dbo"."Artikel"."Gruppe"), even inside an aggregate; it computes a grouping by Year(…) itself over the column it read.

The protocol doesn't know COUNT(x) – the values that aren't missing – as a standard method; for it, the driver uses Wkl.Store.V1.count if the service announces it with Aggregation.CustomAggregationMethods, and refuses otherwise. It chooses the aliases gw_aN and gw_cN so that no property of the set has that name.

Column types as with Microsoft's driver for SQL Server, so that Access creates the same fields: a count is int, the sum of integers up to int is int (a larger value fails, as there, on the overflow), the sum of bigint is bigint, and that of a decimal number decimal(38, s); MIN and MAX have the type of the column. One deliberate difference: the average of integers is float with decimal places, where SQL Server returns an integer – the service computes the true average, and describing it as an integer would truncate what it sends. A column takes the name of its alias in the statement; without an alias it has no name, as with ODBC Driver 18 (Access then names it Expr1000, Expr1001 … itself).

Rejected with 42000 instead of approximated: calculating between aggregates (SUM(a) - SUM(b)), division, functions other than SUM, MIN, MAX, AVG, COUNT, a column in the selection that isn't grouped, DISTINCT in SUM or AVG, a parameter inside an aggregate, * alongside GROUP BY. With an address for custom statements, these go to the database as well.

In Access, a translated totals query returns the same values and field types as through Microsoft ODBC Driver 18. A sum that overflows int fails as it does there.

An application that shows a large result screen by screen requests each page with the paging form of SQL Server and the SQL standard. The driver translates it without an address for custom statements: OFFSET n becomes $skip=n, FETCH NEXT m ROWS ONLY becomes $top=m; condition and sorting go along as $filter and $orderby as usual. An example of how a data grid sends it over its own connection with SQLExecDirect:

SELECT S."ID", S."Kunde", S."Umsatz" FROM "dbo"."Umsatz" S
ORDER BY S."Umsatz" DESC, S."ID" OFFSET 500000 ROWS FETCH NEXT 200 ROWS ONLY

becomes Umsatz?$select=ID,Kunde,Umsatz&$orderby=Umsatz desc,ID&$skip=500000&$top=200.

The driver recognizes the words OFFSET, FETCH, ROWS, NEXT, and so on only at the end of ORDER BY; a column named Next can still be written as is.

How expensive a deep page is depends on the service and its database, not on the driver. The data service Wkl.Data.Gateway turns $skip into an OFFSET … ROWS, and SQL Server reads all rows before it for that. Through the driver, a shallow page additionally costs the fixed overhead of an HTTP request.

Column names are the alias in the statement; without an alias the name is empty, as with ODBC Driver 18. Column types follow the rules under “Grouping and summarizing”.

• LIKE works only in the forms x%, %x, %x%, and without _ as a wildcard. A single character in square brackets ([_], [%]) or after an escape character (ESCAPE) counts as that character and is allowed. A character class such as [a-c] or [^a] has no equivalent in OData.
• The driver translates expressions in the field list only inside an aggregate.
• SQLGetInfo doesn't announce joins and subqueries; Access sends joins anyway, and they run only through the address for custom statements.
• The cursor reads forward, without bookmarks and without locks.
• GUID values (uniqueidentifier, Edm.Guid): as a literal in the Access notation ({guid {33333333-…}}), as text, or as a parameter of type Guid or as 16 bytes; the OData v4 form without a type name goes to the service. For ODBC 2 applications such as Access, the driver returns a GUID without a requested type as text, as ODBC Driver 18 does. This applies in Access to datasheets, forms, subforms, and lookup lists.

Access:  SELECT ... FROM Artikel WHERE Gruppe = 'S' ORDER BY Nummer
Service: GET .../Artikel?$filter=Gruppe eq 'S'&$orderby=Nummer&$select=...

Driver Wkl OData, both bitnesses; grouping only against a service that announces $apply (see Requirements for an OData service).

• Escape clauses and data types
• How the driver translates statements
• Set up pass-through queries