HTTP: OData v4 (reference)

The OData v4 interface under /odata/v4/—for Access via the ODBC driver, Excel and Power BI via Power Query, and any other OData client. It reads tables and views, writes individual records, aggregates via $apply and describes itself in the schema document.

A second interface over the same catalog, for clients that speak OData v4 instead of the native API: Power Query in Excel and Power BI, and an ODBC driver for OData through which Access links tables. Such a client learns everything from the schema document and follows the annotations of the protocol. It doesn't know the window convention or the key in the query part of the native API. Sign-in, permissions, rate limiting, audit and error format are the same as on the native API (see HTTP: error format and HTTP: sign-in).

The service answers PUT with 405, as on the native API.

Which databases and schemas a caller can use is stated by the two lists above the service roots—for clients that offer a selection, such as the connection dialog of the ODBC driver, like the database list in the SQL Server dialog. It's an ordinary service document of the protocol whose entries have the kind ServiceDocument—the kind that OData provides for referring to another service (OData JSON Format 4.01, section “Service Document”). A generic client can follow them without knowing this service:

{"@odata.context":"https://daten.example.com/odata/v4/$metadata",
 "value":[{"name":"fachdaten","kind":"ServiceDocument","url":"fachdaten/"}]}

Writes go to tables and views, and always exactly one record. The statement is built from the columns of the catalog, as with the native API; the same rules apply unchanged. The caller determines the record (via the key in the address) and the values (via the body), never a condition. If the key is missing from the address, the request is rejected instead of hitting the whole set. A view accepts a change if the caller's rules allow views with this verb and SQL Server considers the view updatable—as with the SQL Server ODBC driver, through which Access edits linked views; if SQL Server refuses, the service responds 422. Columns of a view that SQL Server can't update—a scalar subquery, an expression, an aggregate—are reported by the service as read-only (Core.Computed); it rejects a change that names one of them with 400 and the name of the column. Exceptions are views with an INSTEAD OF trigger: there the trigger decides what such a column means. Access doesn't adopt this information: a linked view still offers such a column for editing, with this driver as with SQL Server's ODBC Driver 18, and if you change it, the refusal also discards the other changes to the record. Where users edit in Access, link the base table for writing and the view only for reading. The native API (cf) writes only to tables.

Every write names one record by its complete key. The only exception is released tables without a key: there the caller names the record by its old values, and even then exactly one record is written.

The response is the stored record with its new ETag; on create, additionally the address of the new record in the Location header. This way the caller knows identity keys, default values and computed columns without having to read the record again.

A key that the database assigns itself—as an identity or by a default such as NEWSEQUENTIALID()—may be omitted on create; the response and Location name the assigned value.

A timestamp with a time zone is converted to Coordinated Universal Time for a column without a zone and stored as wall-clock time: …T17:00:00Z as 17:00, …T17:00:00+02:00 as 15:00. It isn't rejected here (unlike on the native route); see Timestamps, numbers, and names.

Fields with @ in their name—@odata.etag, @odata.type—are skipped instead of rejected. A client that sends back a record it has read includes them; they aren't columns.

One service root per alias and schema. In OData, an entity set is named like a simple identifier, and the objects of a database are only unique per schema. With the schema in the root, the set has exactly the same name as the object (APPARAT), without a separator that could also occur in a real name.

The schema document applies per caller. It names tables if the caller may read tables of the alias, and views if they may read views—nothing that would be refused to them. Functions and procedures don't appear. A schema without a readable object, including a mistyped one, responds with 404 and not with an empty service.

let
    Quelle = OData.Feed("https://gateway.example.com/odata/v4/fachdaten/dbo/", null,
        [Implementation = "2.0", Headers = [#"X-Api-Key" = "…"]]),
    Apparate = Quelle{[Name = "APPARAT"]}[Data],
    Keller = Table.SelectRows(Apparate, each Text.Contains([Benennung], "Keller"))
in
    Keller

In this example, the key is in plaintext in the query and therefore in every copy of the workbook. That's a limitation of sign-in by header, not of this interface.

With Windows sign-in, the header isn't needed: OData.Feed("https://gateway.example.com/odata/v4/fachdaten/dbo/", null, [Implementation = "2.0"]), plus the authentication kind “Windows” in Power Query's credentials dialog.

Key. The key of a set is the primary key, otherwise the first unique index—but only if none of its columns may be empty and all have a type that the protocol allows as a key. A view without its own index gets the key that SQL Server names for its result (sp_describe_first_result_set in browse mode, is_part_of_unique_key)—the same information from which the SQL Server ODBC driver derives the key of a view. It's adopted only if the view shows all these columns: if SQL Server has to add a key column of a base table that the view doesn't select (a join), the visible columns alone don't identify a record. A view without such a key is offered without a key: readable and filterable, but no single record is addressable. The protocol actually expects one; without this exception, precisely the views through which a client is supposed to read would be unreachable.

A view is described by its result, not by its stored column list. A view over SELECT * whose tables have changed since keeps the old list until someone runs sp_refreshview, and SQL Server delivers the new columns under the old names, so values arrive under the wrong column names and with the wrong type. The service therefore describes every view via sys.dm_exec_describe_first_result_set, as the ODBC driver describes a result, and writes a warning to the log once per view.

What the service says about the database. Whatever OData doesn't know but a client needs in order to behave toward an application like the product's driver is given as an annotation of a vocabulary of its own in $metadata. The vocabulary (namespace Wkl.Store.V1, alias Store) is available without sign-in under /odata/v4/$vocabulary/Wkl.Store.V1.xml; a client that doesn't know it skips the annotations and loses nothing.

Power Query in Excel for Microsoft 365 reads the extended schema document unchanged.

Rows and pages are planning aids, not a count. Access reads them via SQLStatistics when linking and plans its joins accordingly. Without a page count, Access may resolve a join by looking up the rows of one table individually by key, one request for each row of the other side; with a page count, it sends the join in one statement. The row count alone doesn't change the plan. Both numbers come from catalog views that SQL Server maintains continuously; reading them costs equally little regardless of table size and needs no additional permission.

The key specification Menge(4711) or Menge(Auftrag=7,Zeile='a') is read with the filter grammar and then checked for its form: exactly one equality comparison per key column, nothing else. APPARAT(1 or nummer ne 0) is valid as an expression and would hit every row; it's rejected with 400.

Types and encoded names: Timestamps, numbers, and names.

What Power Query shows (Excel, OData.Feed with Implementation="2.0"):

Row version. If a table has one, every record carries it as @odata.etag (W/"<Base64>"), a single record also in the ETag header. The schema document announces the column with Core.OptimisticConcurrency. The value is the same one that If-Match accepts when writing. For editable tables, the column isn't optional: a client that wants to change a record otherwise has nothing to attach a precondition to. That's why this product's OData ODBC driver refuses update and delete without a row version instead of silently overwriting a change made by someone else. For a table without a row version, the row stamp takes its place if it's turned on for it.

Next pages are offered by the service according to the same rules as nextLink in the native API, here as @odata.nextLink and as an absolute address, as the protocol intends. Behind an upstream proxy, the proxy therefore has to pass on scheme and host (X-Forwarded-* with the corresponding middleware); otherwise context and next addresses point to the host this process sees.

If the set is ordered by its key—no $orderby from the caller, a key of integers, text or uniqueidentifier, all key columns selected—the next page continues after the key of the last row ($skiptoken, such as ?$skiptoken=3691 or ?$skiptoken=Auftrag=7,Zeile='a') instead of after a row count ($skip). $skip becomes OFFSET … ROWS, and SQL Server counts all previous rows again for every page, so each page takes longer than the one before. With $skiptoken, the time per page stays the same. The token is opaque to the client; the service reads it like the key specification of an address and rejects it with 400 for a read request ordered differently. In all other cases, $skip is used. A decimal key is deliberately excluded: read through the filter grammar, its value could be rounded.

Page size as the client prefers. Without $top, the service cuts a read into pages of the configured size (Gateway:Limits:DefaultTop, 1,000 rows by default). A client that reads narrow rows, such as only the keys of a table, can ask for larger pages with the header Prefer: odata.maxpagesize=10000. The service applies the size within MaxTop and MaxRows of the object, confirms it in the Preference-Applied header, and still offers the next page: unlike $top, the preference doesn't end the read. An explicit $top takes precedence. The ODBC driver asks for a size itself, based on the width of the selected columns.

Next pages don't count against the request limit (Gateway:Security:RateLimit)—here as in the own contract. For this, every next address carries the option wkl-page, a check value over caller, path, all other options and an expiry time (15 minutes). The key for it is generated randomly when the process starts and never leaves it. Reason: a client reads a large set page by page, each page is a request, and an abort with 429 in the middle of reading would leave a table incomplete in Access with “ODBC–call failed.” So only the start of a read counts. The rules:

Nothing is ever rejected because of wkl-page: an invalid check value only removes the exemption; the service checks sign-in and rights on every page as usual. The option appears only in addresses that the service builds itself; a client passes it on without reading it (it doesn't start with $).

One deviation from the protocol and one addition are based on how Power Query behaves:

An extension of this service, not part of OData. For several changes in one step, OData provides the $batch with change sets; it has no transaction across several individual requests, as ODBC applications expect. A generic OData client doesn't use this extension; it's intended for the ODBC driver and programs that address it explicitly.

A client brackets several requests in a transaction, like an application via the SQL Server driver with BeginTrans/CommitTrans/Rollback. The service announces this in the schema document (Store.Transactions on the entity container, the address below); only then does the ODBC driver report transactions.

{ "Name": "fachdaten", "Provider": "sqlserver", "ConnectionString": "…",
  "KeylessWriteTables": [ "dbo.Protokoll" ] }

The forms of the entries are the same as for RowStampTables (schema.tabelle, schema.*, *), as is the check at startup. A listed table that does have a key after all continues to be written via that key and is named in the log.

Via the protocol, this happens with two bound actions that the schema document declares only for released tables. That's how a client recognizes whether the table is writable:

POST /odata/v4/fachdaten/dbo/Protokoll/UpdateByOriginal
{ "original": { "Nummer": 2, "Text": "zwei", "Betrag": null, "Aktiv": false },
  "changes":  { "Text": "neu" } }
 
POST /odata/v4/fachdaten/dbo/Protokoll/DeleteByOriginal
{ "original": { … } }

The action name may be qualified with the namespace of the schema (fachdaten.dbo.UpdateByOriginal), as the protocol writes it. The check is made according to what the action does: update needs the right for PATCH, delete the right for DELETE. A right only to create doesn't open this door.

Response on success: 200 with {"value": 1}.

A set can be read grouped and aggregated, according to OData Data Aggregation 4.0. The reason is Access: the database engine sends SUM, COUNT(column), MIN, MAX, GROUP BY, HAVING, DISTINCT and, for a standard deviation, SUM(x * x) over a linked table to the ODBC driver, regardless of what the driver reports about itself, and doesn't calculate on its own if the driver refuses—the query then fails with “ODBC–call failed.” Without pass-through, that is, for every key without the sql right, $apply and—where the alias accepts them—checked statements lead to sums and groups. It's a read: the read rights of the set apply, and a sum over a million rows travels as one number.

GET …/Auftrag?$apply=groupby((Werk),aggregate($count as Anzahl,Menge with sum as Summe))
GET …/Auftrag?$apply=filter(Werk eq 3)/aggregate(Menge with average as Mittel)
GET …/Rolle?$apply=groupby((Abteilung),aggregate($count as N))&$filter=N gt 1&$orderby=N desc&$top=10
GET …/Rolle?$apply=compute(Id mul Id as Q)/aggregate(Q with sum as Quadratsumme)
GET …/Rolle?$apply=groupby((Abteilung))                          distinct values (DISTINCT)

{"@odata.context":"…/$metadata#Auftrag(Werk,Anzahl,Summe)",
 "value":[{"Werk":1,"Anzahl":2140,"Summe":18350.5}, …]}

What $apply runs—in this order, each transformation at most once:

After $apply, the other options refer to the groups, as the protocol intends: $filter is SQL HAVING and may name an alias ($filter=N gt 1), $orderby orders the groups, $top, $skip and $count=true window and count them. They name only what the result has—a grouping property or an alias; the service rejects another column of the set with 400. Without $orderby, the groups are ordered by their grouping properties, so that consecutive windows don't overlap.

The schema document announces what is run: Aggregation.ApplySupported with the four transformations and without rollup, plus the custom counting method. The ODBC driver groups only via a service that announces this. On the native route (/api/v1/cf/…), $apply remains rejected.

The statement is a derived table from which the groups are read like rows:

SELECT TOP (100) * FROM (
    SELECT [Kunde], COUNT_BIG(*) AS [Anzahl] FROM [dbo].[Umsatz] WHERE [Nr] > @p0 GROUP BY [Kunde]
) AS [gw_g] WHERE [Anzahl] > @p1 ORDER BY [Anzahl] DESC

Only this way can the condition on the groups name an alias, and the window follows the same logic as in an ordinary read. Values go in as parameters, as everywhere; numbers from compute are written by the service from the value it has read, never from the caller's text.

• HTTP: native API (reference) · Sign-in
• Load data into Excel with Power Query
• Timestamps, numbers, and names