OData4 query options of Invantive Bridge Online

Wiki (en)
, ,
1

Invantive Bridge Online as an OData4 producer provides a proxy between the Invantive SQL engine of the Invantive Cloud and the OData4 protocol.

The OData4 protocol defines a number of query options. Not all OData4 options are currently supported. This topic provides an overview.

At least all OData4 query options and filter clause generated by Power BI and Power Query are supported.

OData4 Query Options

Invantive Bridge Online provides the following query options:

  • $select: specify list of columns to retrieve or wildcard *.
  • $filter: specify criteria whether to include rows or not.
  • $count: count rows.
  • $top: limit number of rows.

The $select operator has a behavior deviating from the OData4 standard when specified with a value different from *. Due to the restrictions described in How to support $select on non-CLR types? · Issue #2338 · OData/WebApi · GitHub, the columns not included in the list will still be returned but with their default value. This reduces the data volume, but is not according to specification.

OData4 Operators

The following operators are supported:

  • eq: check on equality.
  • ne: check on inequality.
  • mod: modulo
  • concat: concatenate texts.
  • and: combine two boolean values.
  • or: combine two boolean values.
  • startswith: text starts with text.
  • endswith: text ends with text.
  • contains: text contains text.
  • ge: greater than or equal.
  • le: less than or equal.
  • gt: greater than.
  • lt: less than.
  • add: add two values.
  • mul: multiply two values
  • div: divide two values.
  • sub: subtract two values

Explicitly ignored query options without any effect are at least the following:

  • $orderby: data ordering. Ignored for performance reasons.

Explicitly not supported query options are at least the following:

  • $apply
  • $search
  • $skip: number of rows to skip, by design unsupported.

Currently explicitly not supported operators include at least:

  • substring
  • has

Support for more operators may be added upon demand. Please register a topic in the Ideas category when support for another standardized OData4 operator is needed.

Other Protocols and Content Types

Invantive Bridge Online only supports the JSON content type of the OData4 specification. The XML content type has been obsoleted and the only product allegedly able to use it is Tableau before Q4 2022. Since 2022 Q4 Tableau supports OData4 as provided by Invantive Bridge Online:

In a future release, Invantive Bridge Online might add protocols such as MS-TDS from Microsoft simulating Microsoft SQL Server.