The OData version 4 protocol is how Invantive Bridge Online offers the data of a database to other software. Microsoft Power BI, Microsoft Power Query on Excel, Qlik and Tableau speak it without an add-on, so a table of any of the platforms behind [[Invantive UniversalSQL/Invantive UniversalSQL|Invantive UniversalSQL]] can be read straight into a report. The protocol is offered for reading only. A request retrieves rows. Nothing is created, changed or removed through it. Which parts of the standard are read and which are not is listed in [[Supported OData 4 Features]]. Each shape of request is worked out in [[Invantive Bridge Online/Examples/Examples|Examples]]. ## The Address of a Database Every database has an address of its own, which Invantive Cloud shows on the Bridge Online configuration of that database. The address ends in `odata4`: ```text https://bridge-online.invantive.com/powerbi/bridge/v10/106/odata4/ ``` The `106` is the number of the database. A table follows the address as one path segment, with the system query options behind a question mark: ```text https://bridge-online.invantive.com/powerbi/bridge/v10/106/odata4/ExactOnlineREST.Financial.GLAccountsBulk@eol?$top=10 ``` The examples of this manual shorten everything up to and including `odata4/` to `.../odata4/`. ## Authentication Every request carries HTTP basic authentication with the Bridge user name and the Bridge password of an Invantive Cloud account. That password is the same for every database the account may reach. The address the request comes from is checked as well, so a new location has to be allowed before it can retrieve anything. A database may also require a `Referer` header. Both are settled on the configuration of the database; see [[Bridge Online Configuration]]. ## Names of the Entity Sets An entity set is a table of the database and carries the name of that table: the catalogue, the schema and the table name, followed by the alias of the data container. `ExactOnlineREST.Financial.GLAccountsBulk@eol` is such a name. The OData standard allows only letters, digits and underscores in a name, so a service may be configured to leave the full stop and the at sign out. The names a database offers are therefore read from its data model rather than composed by hand; see [[Retrieve the Data Model]]. ## What a Request Answers The answer is a JSON document. The rows sit in `value`, and `@odata.context` in front of them names the shape they have: ```json { "@odata.context": "https://bridge-online.invantive.com/powerbi/bridge/v10/106/odata4/$metadata#ExactOnlineREST.Financial.GLAccountsBulk@eol", "value": [ { "ID": "ddcd50dc-1956-4307-8ef4-17933562f202", "Division": 920474, "DivisionName": "ACME BV", "Code": "0110" } ] } ``` The context is worth reading rather than skipping. It names the entity set the rows come from, and after a grouping it names the columns the grouping projected, so a client can tell a grouped answer from an ungrouped one without inspecting a single row. ## Streaming Rows are streamed. The service starts writing the answer while it is still retrieving, so a large table never has to fit in memory as a whole and the first rows reach the client early. One consequence deserves attention. An error which appears once the rows are being written can no longer change the HTTP status code, because that was sent with the first bytes of the answer. A client therefore checks that the JSON document it received is complete, and discards it when it is not. ## No Paging A table is retrieved in one request. The answer carries no `@odata.nextLink`, and the system query options `$skip` and `$skipToken` are refused, so there is no page to walk to. Retrieving less is done by asking for less: `$select` for fewer columns, `$filter` for fewer rows, `$top` for a sample and `$apply` for totals instead of rows. That is also what keeps a download inside the [[Invantive Bridge Online/Fair Use Limits|Fair Use Limits]]. ## Omitting Null Values A client with no use for null values asks for them to be left out with the request header `Prefer: omit-values=nulls`. Every property whose value is null is then absent from the answer, and the response header `Preference-Applied: omit-values=nulls` confirms that it happened. On a wide table with many empty columns this makes the answer considerably smaller. A database can be configured to require the header. A request without it is then refused, which protects against a client downloading far more than it reads. ## Caching An answer may be served from a cache, so that a repeated download does not repeat the load on the platform. The request header `X-Invantive-Analysis-Cache-Control` states how old an answer may be, in the form `max-age=<seconds>` or `s-max-age=<seconds>`. Each database has a minimum age of its own, which the header cannot lower. ## Errors An error is answered as an OData error document: ```json { "error": { "code": "itgengcr019", "message": "The query option '$filter' cannot be combined with the query option '$apply'. Move the condition into the query option '$apply' as 'filter(...)/groupby(...)'. (itgengcr019, 8f1c0e2a-4d7b-4a19-9d63-2c5f7a1e0b44)" } } ``` The `code` holds the Invantive message code of the condition, which is the term to search for on [forums.invantive.com](https://forums.invantive.com/tag/invantive-bridge-online). The `message` repeats that code and a unique identifier of the occurrence at its end, because Microsoft Power BI and Microsoft Power Query show the message and nothing else. Beside them the document carries a `details` list with diagnostic entries such as `RequestId`, `ExceptionUid` and `Url`, which name one request in a support ticket. A refused request is answered with HTTP status 500, also where the request itself was at fault. A download the client broke off is answered with HTTP status 499. ## Limits Every download counts against the daily limits of the subscription, and every answer carries the consumption so far in its headers. Both are described in [[Invantive Bridge Online/Fair Use Limits|Fair Use Limits]].