OData - Preview Feature

Preview feature in v3, released in v4

The Open Data Protocol (OData) is a data access protocol built on core protocols like HTTP and commonly accepted methodologies like REST for the web. For example, instead of creating a JDBC/ODBC connection and issuing an SQL command, you could access that table with an HTTP GET via the URL:

SQL statement	OData query
`SELECT * FROM SYSADMIN.Roles;;`	`GET http://localhost:8080/odata4/datavirtuality/SYSADMIN/Roles`

Use the correct case (upper or lower) in the resource path. Unlike SQL, the names used in the URI are case-sensitive.

The returned results from the OData query can be in Atom/AtomPub XML or JSON format. By default, JSON is used.

Security and Access

By default, OData access is secured using HTTP basic authentication. Only users with the special"odata-role" role have access.

Query Options

Like SQL queries, OData queries can be enhanced in several ways.

Filtering Results

OData queries support a special query option $filter for filtering query results, just like the WHERE clause does in SQL:

SQL statement	OData query
`SELECT * FROM SYSADMIN.Roles WHERE id = 1 ;;`	`GET http://localhost:8080/odata4/datavirtuality/SYSADMIN/Roles?$filter=id eq 1`

Spaces around 'eq' are for readability of the example only; in actual URLs, they must be percent-encoded as %20. OData mandates percent encoding for all spaces in URLs. Please see the URL Conventions section in the OData documentation for more information.

Formatting Results

To request the result to be formatted in a specific format, add the query option $format:

XML

GET http://localhost:8080/odata4/datavirtuality/SYSADMIN/Roles?$format=JSON

The system query option $format must be either 'json', 'xml', or 'atom'.

Getting More Results

OData utilizes batching, which means that you get 256 rows of data at a time. In one of the top lines of the results, you have a ready query with the special $skiptoken option which you can use to get the next 256 rows:

XML

GET  http://localhost:8080/odata4/datavirtuality/DV/customers?$skiptoken=xxx

Combining Query Options

Query options can be used together, like in this example:

XML

GET http://localhost:8080/odata4/datavirtuality/SYSADMIN/Roles?$filter=id eq 1&$format=XML

Querying Navigations

OData also supports querying navigations from one entity to another. Navigation is similar to the foreign key relationships in relational databases:

SQL statement	OData query
`SELECT CountryRegion.* FROM mysql_adventureworks.Address` `JOIN mysql_adventureworks.CountryRegion ON Address.CountryRegionCode = CountryRegion.CountryRegionCode WHERE Address.AddressID = 1 ;;`	`GET http://localhost:8080/odata4/datavirtuality/mysql_adventureworks/address(1)/FK_CountryRegion_Address`

Please note that at the moment, query navigation is not supported for virtual views - only for data source tables with foreign keys assigned directly in the source. Such keys should be imported by enabling the importer.importKeys model property on data source creation.

OData Metadata

OData defines its schema using the Conceptual Schema Definition Language (CSDL):

http://localhost:8080/odata4/datavirtuality/DV/$metadata

Since the OData schema model is not a relational schema model, the Data Virtuality Server uses the following semantics to map its relational schema model to the OData schema model:

Relational entity	Mapped OData entity
Model name	Schema namespace, EntityContainer name
Table/View	EntityType, EntitySet
Table columns	EntityType's properties
Primary key	EntityType's key properties
Foreign key	Navigation property on EntityType
Procedure	FunctionImport, ActionImport
Procedure's table return	ComplexType

By design, the Data Virtuality Server does not define any "embedded" ComplexType in the EntityType.

Datatype Mapping

Data type	OData type
`STRING`	`Edm.String`
`BOOLEAN`	`Edm.Boolean`
`BYTE`	`Edm.SByte`
`SHORT`	`Edm.Int16`
`INTEGER`	`Edm.Int32`
`LONG`	`Edm.Int64`
`FLOAT`	`Edm.Single`
`DOUBLE`	`Edm.Double`
`BIG_INTEGER`	`Edm.Decimal`
`BIG_DECIMAL`	`Edm.Decimal`
`DATE`	`Edm.Date`
`TIME`	`Edm.TimeOfDay`
`TIMESTAMP`	`Edm.DateTimeOffset`
`BLOB`	`Edm.Stream`
`CLOB`	`Edm.Stream`
`XML`	`Edm.Stream`
`VARBINARY`	`Edm.Binary`

"EntitySet Not Found" Error

When issuing OData queries, you may encounter the following error:

XML

{"error":{"code":null,"message":"Cannot find EntitySet, Singleton, ActionImport or FunctionImport with name 'xxx'."}}

It may be caused by one of the following:

You supplied the wrong model-name/table-name combination - please check the spelling and case;
The entity is not part of the metadata, such as when a table does not have any PRIMARY KEY or UNIQUE KEY(s).

Executing Stored Procedures

Functions and Actions

The mapping of entities and their properties is relatively straightforward. Mapping the Data Virtuality Server procedures to OData functions and actions is more complicated. OData does not have an out parameter concept; thus, OUT parameters are ignored, and INOUT parameters are treated only as IN. A result set is mapped to a complex type collection result.

An OData function will be used if:

The procedure/function has a return value - either scalar or a result set;
The procedure/function has no LOB input parameters - currently, Clob, Blob and XML are considered LOB types;
The procedure/function is side effect free - this is determined by an explicit value of 0 for the update count. For example: CREATE VIRTUAL PROCEDURE … OPTIONS (UPDATECOUNT 0) AS BEGIN …

If any of those conditions are not met, the procedure/function is represented instead by an OData action. However, if a result set has a LOB value, the procedure is not mapped as multiple streaming values cannot be returned.

Please note that OData functions and actions are called differently:

A function is called by a GET request where the parameter values are included in the URI;
An action is called by a POST request where the content provides the parameter values.

Currently, only unbounded functions and actions are supported.

We recommend always checking $metadata on functions and actions to verify how the procedures/functions are mapped.

Executing Functions

SQL statement	OData query
`SELECT * FROM (EXEC "SYSADMIN.getTypeOfResource"("name" => 'SYSADMIN.Roles')) AS alias ;;`	`GET http://localhost:8080/odata4/datavirtuality/SYSADMIN/getTypeOfResource(name='SYSADMIN.Roles')`

Executing Actions

SQL statement OData query

CALL "SYSADMIN.setCurrentDWH"(nameInDv => 'mysql_adventueworks', nameInSource => 'adventueworks') ;;

POST http://localhost:8080/odata4/datavirtuality/SYSADMIN/setCurrentDWH
Host: host
Content-Type: application/json
Accept: application/json
{
"nameInDv" : "mysql_adventueworks",
"nameInSource" : "adventueworks"

}

Updating Data

OData protocol supports the CREATE/UPDATE/DELETE operations along with READ operations shown above. These operations use different HTTP methods.

INSERT is accomplished through the HTTP method POST:

XML

POST /service.svc/Customers HTTP/1.1
Host: host
Content-Type: application/json
Accept: application/json
{
  "CustomerID": "AS123X",
  "CompanyName": "Contoso Widgets",
  "Address" : "Seattle, 58 Contoso St"
}

UPDATE is performed with the HTTP method PUT:

XML

PUT /service.svc/Customers('ALFKI') HTTP/1.1
Host: host
Content-Type: application/josn
Accept: application/json
{
  "CustomerID": "AS123X",
  "CompanyName": "Updated Company Name",
   "Address" : "Seattle, Updated street" 
}

The DELETE operation uses the HTTP method DELETE:

XML

DELETE /service.svc/Customers('ALFKI') HTTP/1.1
Host: host
Content-Type: application/json
Accept: application/json

Limitations

At the moment, our implementation of OData does not support the following:

Search
Delta processing
Data-aggregation extension to specification
Analogues of SQL CREATE (table, view, procedure), ALTER (view, procedure), DROP/DROP IF EXISTS (table, view, procedure), SELECT INTO queries
Multiple virtual databases (only the"datavirtuality" system VDB may be used)
$it usage is limited to only primitive collection properties