How an OData service becomes a relational ODBC model

graph TD
    M["$metadata (CSDL)"] -->|"on first use per connection"| R["Driver model: sets, columns, keys, annotations"]
    R --> K["Catalog for the application: SQLTables, SQLColumns, SQLStatistics"]
    R --> P["Translation of a statement into an OData request"]
    P -->|"HTTP"| A["JSON response"]
    A --> E{"Classify the response"}
    E -->|"collection value"| Z["Rows: property mapped to a column by name"]
    E -->|"@odata.nextLink"| F["next page"]
    E -->|"error"| D["Diagnostic records"]
    E -->|"other annotations"| I["skipped"]

Figure: The driver derives the meaning of each element from the schema document and the kind of request, not from the shape of the JSON. The classification of responses is described in How the driver classifies and reports responses.

OData describes data as entities with types, keys, nested values, and relationships; ODBC knows only tables made of rows and columns with a SQL type. The driver therefore maps only the part of the OData model that can be represented as a table without loss of meaning, and visibly leaves out the rest. This page states, for every kind of element, what the driver makes of it. It's the basis for everything under Supported SQL: only when it's clear which column corresponds to which property can a result be trusted.

Key features

An entity set is a table, a property with a simple type is a column, and the key of the entity type is the primary key.
The type comes from $metadata, not from the JSON: a number that arrives as text stays a number; a value that doesn't match the announced type is an error, not a silent reinterpretation.
What can't be represented as a column is visibly missing: complex values, collections, and relationships don't appear, instead of being flattened or offered as text.
Control information isn't data: @odata.nextLink controls paging; the other annotations of a response never become columns.

Basic concepts

What becomes of the schema document

The driver reads $metadata once per connection, on first use. The basis is the CSDL XML format of OData 4.0/4.01 (see Standards and specifications).

Element in `$metadata`	In the ODBC model	Rationale
`EntitySet`	table, table type `TABLE`	an addressable, queryable set of records of the same kind
`EntitySet` with a type that the document doesn't describe	missing	no type, no columns
`Property` with an `Edm.…` type that the driver maps	column; type, length, and precision according to Data types; `Nullable="false"` as `SQL_NO_NULLS`	one simple value per record
`Property` with another `Edm.…` type (`Edm.Geography…`, `Edm.Geometry…`, `Edm.Duration`, `Edm.Stream`)	missing	no lossless equivalent as a SQL type
`Property` of a complex type (`ComplexType`)	missing	a nested value has no column; flattening (`Adresse_Ort`) would invent columns that the service can neither filter nor write
`Property` of type `Collection(…)`	missing	several values per record can't be represented in one cell without losing their meaning
`Property` of an enumeration type (`EnumType`) or a `TypeDefinition`	missing	the driver reads only properties whose type is directly `Edm.…`
`Key` with `PropertyRef`	primary key and unique index (`SQLStatistics`); Access adopts it as its primary index	without it, a set isn't editable
`Key` that names a missing property	no key; the table is read-only	an incomplete key would hit several records
`NavigationProperty`	missing; `SQLForeignKeys` returns an empty result	the read path follows no relationship; joins run only through pass-through
`BaseType` (inheritance between entity types)	not evaluated: properties and key of the base type are missing from the derived set	inheritance isn't supported
`OpenType`: properties that appear only in the record	missing	they aren't described in `$metadata`
`Singleton`	missing	not a record of a set, no rows
`FunctionImport`, `Function`, `ActionImport`	missing; `SQLProcedures` returns an empty result	calls go only through pass-through
bound `Action` `UpdateByOriginal`/`DeleteByOriginal`	changing and deleting a table without a key	extension, see Requirements
other `Action`	skipped	–
annotation `Core.Computed`	column read-only (`SQL_DESC_UPDATABLE`)	–
annotation `Core.OptimisticConcurrency`	column counts as the row version	write conflict through `If-Match`
annotations `Aggregation.ApplySupported`, `Aggregation.CustomAggregationMethods`	grouping through `$apply`	–
annotations of the `Wkl.Store.V1` vocabulary	product name, schema, type names, indexes, statistics, pass-through, transactions	extension of this product
any other annotation	skipped	–

Names. The driver reports sets and columns under the names from $metadata. A name that the service encoded with _xHHHH_ is decoded, as far as Access can process the result; see Field name encoded or too long.

Views. OData doesn't distinguish a view from a table; every set appears as TABLE.

What becomes of a response with data

A read request returns a collection: a JSON object with the field value. The driver classifies its parts as follows:

Part of the response	Meaning	In the ODBC model
`value` (array)	the records of the page	one row per element. If `value` is missing or isn't an array, the response is an error (see Classify responses)
property of a record that describes a column	value	value of the column, converted according to the type from `$metadata`
property that `$metadata` describes but that's missing from the record	no value supplied	`NULL`
`null`	no value	`NULL`
property of a record that describes no column (complex value, collection, dynamic property, navigation)	–	skipped
`@odata.nextLink`	continuation	the driver fetches the next page when the application reads on; never a column
`@odata.context`, `@odata.count`, `@odata.etag`, and any other annotation	control information	skipped, never a column

Conversion by the announced type. A value is read according to its type in $metadata, not according to its JSON form: for an Edm.Decimal column, "12.50" is the number 12.50; for Edm.Double, "NaN" is the special value. If a value doesn't match the type, reading stops with the message “The value of column … doesn't match the announced type …”; the driver doesn't guess another type.

Counting. COUNT(*) goes to …/$count; the response is a number as text, not a collection.

What the driver assumes when writing

When changing and deleting, the driver builds the value for If-Match itself from the column that Core.OptimisticConcurrency names and that Access sends along as a condition: a binary value becomes W/"<Base64>", a text that starts with W/ stays as it is, any other text becomes W/"<Text>". A service must therefore accept an ETag of this form; the driver doesn't read the @odata.etag of a response. The data service Wkl.Data.Gateway accepts this form; with another service, check this before you put it into use.

What isn't mapped

Not mapped means: the driver doesn't show the element and works on without it. It reports no error and no warning. An application recognizes missing parts only by the fact that the column or table isn't in the catalog. Where such a part is needed, for example a join over a relationship or calling a function, the route goes through pass-through, if the service offers it.