Requirements for an OData service (reference)

The driver talks to any OData v4 service that offers the required features below. If a service also offers one of the extensions, the driver uses it; without it, the driver behaves as if it didn't exist. You need this page if you check whether a service works with the driver, or if you build a service for Access applications yourself. The data service Wkl.Data.Gateway offers all features on this page.

GET  <service-root>/$metadata                       schema document (CSDL, XML)
GET  <service-root>/<entity-set>?$select=…&$filter=…     reading, JSON response
POST/PATCH/DELETE <service-root>/<entity-set>[(<key>)]   writing

The features are grouped by how binding they are for the driver: without the requirements for reading, the driver doesn't connect or reads incorrectly; without the requirements for writing, all sets remain read-only; the extensions are recommendations whose absence turns off a feature but falsifies nothing. The fundamentals of OData v4 are described in the OASIS standards: OData Version 4.01 Part 1: Protocol, Part 2: URL Conventions (query options, literals), OData Common Schema Definition Language (CSDL) XML Representation 4.01 ($metadata), and OData JSON Format 4.01. The driver requires only features that OData 4.0 already has; it uses $query (4.01) if available.

Required for reading

Required for writing

Extensions (recommended) that the driver uses if the service offers them

Terms of the Wkl.Store.V1 vocabulary. The vocabulary is an extension of this product, not part of OData. A service that offers it places it under its own address and includes it with edmx:Reference (example below); the driver recognizes the terms by their namespace, with or without an alias.

Which of the driver's commitments applies depends on what the service offers and honors. The driver recognizes announcements in $metadata, but not whether a service actually honors a header such as Idempotency-Key. What it can't recognize, you must clarify with the service's provider or verify yourself before use.

What a service must commit to for retries. The driver retries a change at most twice, after 1 and after 3 seconds, with the same Idempotency-Key. The service must therefore keep the first response for a key for at least a few seconds and return exactly that response to the retry, without executing the change again. The driver doesn't retry an exceeded time limit (HYT00): whether the change was executed is then unknown, and the application must read the record again. It doesn't retry the completion of a transaction (commit, rollback) either.

What a service must commit to for security reasons. It refuses a request without a valid sign-in or second factor before it executes anything; the driver then sends it again. And it doesn't redirect addresses under the service root to another origin (see Residual risks).

None. The driver checks the features when connecting and at the time of each use, not in advance.

• Without Core.OptimisticConcurrency, Access puts the old values of all columns into the condition when changing. That can't be expressed in OData; the driver rejects it instead of dropping the check. The remedy lies with the service, for example with a computed concurrency property.
• Without grouping and without an address for custom statements, totals queries and joins from Access fail with “ODBC–call failed”; see Troubleshooting.
• Without Idempotency-Key support, a retry after a dropped connection can create a record twice or report a write conflict even though the change went through. A service that doesn't know the header ignores it; the driver doesn't detect that. The header is described by an expired IETF draft, not a standard (see Standards and specifications).
• Custom statements on the wire. The driver sends {"sql": …, "parameters": {…}} and expects {"columns": […], "data": […], "affectedRowCount": …}. Bound values remain values: the ? placeholders, which ODBC counts by position, become named placeholders – a question mark in a string, a bracketed name, or a comment remains untouched. A response without columns is rejected. Inferring the types from the values of the first row would mean: an integer in a decimal column becomes an integer, a timestamp becomes text, and an empty result has no shape at all – while the application binds its buffers before it reads the first row.
• Output parameters, further result sets, describing. When an application binds output parameters, the driver also sends "outputs": {"p1": "Edm.Int32", "p2": {"type": "Edm.String", "maxLength": 20}} and reads the values from outputs in the response; it reads further result sets from results (each with columns, data, truncated). When an application asks for the columns before execution, the driver sends "describe": true and expects the columns of the first set without the service running anything. It asks for a description only from a service that names the address in Store.PassThrough itself: a service that ignores describe would otherwise run the statement. If a service returns no outputs, the application gets NULL as the output value.
• Description of the columns. Besides name, type, and the facets (maxLength, precision, scale), the driver reads three optional members per column: storeType (the database's type name, like Store.Type in $metadata), identity (true for a value the database assigns), and label (the name the database gives the column, where it differs from name). Rows are JSON objects, so for a column without a name or with a name already taken, the service has to assign a key of its own. With label, the driver shows the application the database's name – empty or repeated, as ODBC Driver 18 does – and reads the values under name. Without label, the application sees that key.
• Store.PassThrough applies only at the same origin (scheme, host, port) as the service root.
• The driver skips fields of a response that it doesn't know. On the pass-through route, it reads columns, data, affectedRowCount, truncated, results, and outputs (see Limits).
• No redirect to another origin. The driver follows a redirect only when reading and only to the same scheme, host, and port; if the service redirects elsewhere, the request fails with 08S01.

Excerpt of a schema document with the extensions that Access needs for editing and grouping:

<edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Aggregation.V1.xml">
  <edmx:Include Namespace="Org.OData.Aggregation.V1" Alias="Aggregation" />
</edmx:Reference>
<edmx:Reference Uri="https://daten.example.com/odata/v4/$vocabulary/Wkl.Store.V1.xml">
  <edmx:Include Namespace="Wkl.Store.V1" Alias="Store" />
</edmx:Reference>
…
<EntityContainer Name="Container">
  <Annotation Term="Store.Product" String="Microsoft SQL Server" />
  <Annotation Term="Store.ProductVersion" String="17.00" />
  <Annotation Term="Store.Schema" String="dbo" />
  <Annotation Term="Aggregation.ApplySupported">
    <Record>
      <PropertyValue Property="Transformations">
        <Collection><String>filter</String><String>compute</String><String>groupby</String><String>aggregate</String></Collection>
      </PropertyValue>
      <PropertyValue Property="Rollup" EnumMember="Aggregation.RollupType/None" />
    </Record>
  </Annotation>
  <Annotation Term="Aggregation.CustomAggregationMethods">
    <Collection><String>Wkl.Store.V1.count</String></Collection>
  </Annotation>
</EntityContainer>

This is what the data service returns (abridged). The terms appear with the alias that the edmx:Reference assigns; the driver reads both notations.

Driver Wkl OData, both bitnesses. OData v2 and v3, as well as services without $metadata or without JSON, aren't supported.

• How the driver translates statements
• Driver connection string