OData

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:

There are some important things to keep in mind when working with OData:

• Unlike SQL, the names used in the URI are case-sensitive, so use the correct case (upper or lower) in the resource path;
• The returned results from the OData query can be in Atom/AtomPub XML or JSON format. By default, JSON is used;
• The view or table should have a primary key to be visible;
• The user must have the odata-role to access OData.

Examples

1. Creating a view visible by the admin user:

-- Create a view with primary key
CREATE VIEW views.view1 (a integer, b integer, PRIMARY KEY(a)) AS SELECT 1 a, 2 b;;

The view views.view1 is visible for the admin user via OData by running http://localhost:8080/odata4/datavirtuality/views/view2 request with the admin user credentials, because the admin user has the odata-role assigned by default.

2. Making a view visible for other user(s):

-- 1. Create a view with primary key
CREATE VIEW views.view2 (a integer, b integer, PRIMARY KEY(a)) AS SELECT 1 a, 2 b;;

-- 2. Create a 'test' user
CALL SYSADMIN.addUser(name => 'test', pwd => 'test', role_name => 'connect-dv-role') ;;

-- 3. Assign the odata-role to the user
CALL SYSADMIN.addUserRole(user_name => 'test', role_name => 'odata-role');;

-- 4. Create an additional role
CALL SYSADMIN.addRole(name => 'test-role', allowCreateTempTables => true) ;;

-- 5. Assign this role to the user
CALL SYSADMIN.addUserRole(user_name => 'test', role_name => 'test-role');;

-- 6. Set permission for the view to the user
CALL SYSADMIN.setPermissions(role_name => 'test-role', resourceName => 'views.view2', permissions => 'CRUDEAL') ;;

Now views.view2 is visible via OData for the test user by running http://localhost:8080/odata4/datavirtuality/views/view2 request with the test user credentials.

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:

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:

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:

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

Combining Query Options

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

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:

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 CData Virtuality Server uses the following semantics to map its relational schema model to the OData schema model:

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

Datatype Mapping

Errors and Solutions

"Cannot find EntitySet"

{"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).

"Illegal character in path at index XXX"

{"error":{"code":null,"message": "java.net.URISyntaxException: Illegal character in path at index XXX: URL/odata_error_ ('error code')/Property Reference"}}

If you issued an OData query and encountered this error, double-check the property names: according to the OData specification, they cannot contain whitespaces.

Executing Stored Procedures

Functions and Actions

The mapping of entities and their properties is relatively straightforward. Mapping the CData 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

Executing Actions

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:

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:

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:

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

OData as a Data Source

You can connect OData as a data source - for more information, please refer to the dedicated page in the Reference Guide.

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