Denodo OData2 Custom Wrapper - User Manual

Last modified on: 21 May 2024

Download original document


You can translate the document:

Introduction

OData is a protocol to access data created by Microsoft. It provides CRUD operations and is similar to JDBC or ODBC but not limited to databases.

OData uses protocols ATOM and JSON and the requests use a REST model. For this reason, OData is an implementation of the RESTful API that describes the data and their model.

OData services

There are several ways to access an OData service from Virtual DataPort:  

Denodo OData2 Custom Wrapper

The Denodo OData2 Custom Wrapper allows you to access OData services even if they require authentication (HTTP BASIC or NTLM supported) or behind a proxy server (which might also be authenticated).

Import the Custom Wrapper

To import the custom wrapper, follow these steps:

  1. In the VDP Administration Tool, go to:

 

  • Until Denodo 6.0: File → Jar management

  • From Denodo 7.0: File → Extension management

  1. Click on “Create” button and select the “denodo-odata2-wrapper-{denodo-version}-{version}-jar-with-dependencies.jar” file downloaded from the Denodo Support Site.

Create the OData data source in Denodo 8.0 and earlier

To create a new OData custom data source:

  1. In the VDP Administration Tool, go to: File → New… → Data source → Custom

  1. In the “Create a New Custom Data Source” window, do the following:

  • Set a name for the new OData data source in the “Name” field.

  • Click on “Select Jars” and select the file imported in the previous section.

  • The “Class name” field must be filled with:

com.denodo.connect.odata.wrapper.ODataWrapper

  • Click on the “Click to refresh the input parameters of the data source” option. The data source parameters are now shown
  • Set the parameters as follows:
  • Service Endpoint (mandatory): is the URL to the OData service. Must be something like: http://services.odata.org/OData/OData.svc/ 

  • Service Format (mandatory): is the format used by the OData custom wrapper to access the OData service. Must be one of these:
  • JSON
  • XML-Atom

  • Service Version: if specified, the custom wrapper try to force the compatibility of the OData service with one of these versions:
  • V1
  • V2

  • Pass-through session credentials: if checked, the value of the login and password fields are used for introspection. During execution, the credentials of the user authenticated in VDP are used.

  • User: OData service user for HTTP Basic Authentication or NTLM Authentication, this is an optional parameter.

  • Password: OData service password for HTTP Basic Authentication or NTLM Authentication, this is an optional parameter.

  • Proxy Host: host to connect  to the client through a proxy, this is an optional parameter.

  • Proxy Port: port to connect  to the client through a proxy, this is an optional parameter.

  • Proxy User: user for the authentication proxy, this is an optional parameter.

  • Proxy Password: password for the authentication proxy, this is an optional parameter.

  • Use NTLM Authentication: if checked, the fields “User” and “Password” will use NTLM Authentication instead of HTTP Basic Authentication. A NTLM domain could be used using the field “NTLM Domain”.

  • NTLM Domain: OData service domain for NTLM Authentication, this is an optional parameter.

  • Timeout: Maximum time (in milliseconds) the custom wrapper will wait for a query to finish. If it is empty, it will wait indefinitely until the sentence ends.

  • Use OAuth2: if checked, the protocol OAuth 2.0 will be used for authorization. With this option the fields: Access Token, Refresh Token, Client Id, Client Secret, and Token Endpoint Url are mandatory. You can use the OAuth credentials wizard to obtain the Access Token and the Refresh Token. You can read more in the VDP ADMINISTRATION GUIDE in the subsection of OAuth.

  • Access Token: You can use the OAuth credentials wizard to get it.

  • Refresh Token: You can use the OAuth credentials wizard to get it. It is used when the Access Token has expired to get a new access token.

  • Client Id: consumer key from the remote access application definition.

  • Client Secret: consumer secret from the remote access application definition.

  • Token Endpoint URL: it is the URL to make the OAuth refresh request when the Access Token has expired.

  • OAuth Extra Parameters: extra parameters to be used in the refresh token requests, this is an optional field.
  • Note: Multiple parameters are allowed to be added.
  • The format must be as follows: field_1="value_1";field_2="value_2";...;field_n="value_n" ;. Being “field“ the name of the parameter and “value“ its value.

  • Refr. Token Auth. Method: controls how the credentials are sent to the service when requesting a new OAuth access token. Must be one of these:
  • Include the client credentials in the body of the request
  • Send client credentials using the HTTP Basic authentication scheme

  • HTTP Headers: custom headers to be used in the underlying HTTP client, this is an optional parameter.
  • Note: Multiple HTTP headers are allowed to be added.
  • The format must be as follows: field_1="value_1";field_2="value_2";...;field_n="value_n";. Being “field“ the name of the header and “value“ its value.

  • Authentication Grant: grant type used by OAuth to retrieve the access token.
  • Authentication code: used by confidential and public clients to exchange an authorization code for an access token.
  • Client credentials: used by clients to obtain an access token outside of the context of a user.

  1. Click on the “Save” button.

Create the OData data source since Denodo 9.0

The following instructions are based on the Denodo 9.0 version (or later) of the Custom Wrapper. Note that the parameters for 6.0 are the same, but they are all configured right in the base view form.

To create a new OData custom data source:

  1. In the VDP Administration Tool, go to: File → New… → Data source → OData 4
  2. Service Endpoint (mandatory): is the URL to the OData service. Here you can configure all the Endpoint parameters such as described in the previous section, but the options are grouped in four tabs: Configuration, Proxy, Authentication and Filters.

  1. Service Format (mandatory): is the format used by the OData custom wrapper to access the OData service. Must be one of these:
  1. JSON
  2. XML-Atom
  1. Service Version: if specified, the custom wrapper try to force the compatibility of the OData service with one of these versions:
  1. V1
  2. V2

Create the base view

To create a new base view using the OData data source:

  1. Double-click on the OData data source and then click on “Create base view”.

  1. Set the parameters as follows:

  • Entity Collection (mandatory): must be one of the collections defined into the OData service. Note: If the OData service defines textual names (titles) for its entity collections (as seen at the Service Document), different from their “href” values, it’s the “href” value what should appear here (the fragment to be used in URLs).

  • Custom Query Options: Data service-specific information to be placed in the data service URI. A custom query option is any query option URI with the following form customOption = customValue.

In this parameter you can put several query options separated by &.

Custom query options MUST NOT begin with a $ or @ character, according the the standard of OData 4.

For example: http://host/service/Products?debug-mode=true

  • Expand Related Entities: if checked, the references to other entities appear directly in the main entity as arrays or registers.

  • Enable Pagination: if checked, two parameters are added to the view to permit the pagination of the results:

  • fetch_size
  • offset_size

Example

  1. Create a base view over this test service:

a)

  • Entity = Products

  • Service Format = XML-Atom

  • Expand Related Entities = false

  • Enable Pagination = false

b) This is the same option but accessing with a proxy

  • Entity = Products

  • Service Format = XML-Atom

  • Expand Related Entities = false

  • Use NTLM Authentication = false

  • Enable Pagination = false

  • Enable Pagination = false

  • Proxy Host=some.proxy.com

  • Proxy Port=3128

  • Proxy User=guest

  • Proxy Password=******

  • Timeout = 1000000


  1. The schema of the base view is shown and you can rename it.


  1. After clicking on “Save”, you can execute queries (SELECT, INSERT, UPDATE or DELETE), for example:

  • SELECT * FROM products WHERE id = 6;

  • INSERT INTO products

(id,name,description,releasedate,rating,price) VALUES

(9,'HDTV','32 inch 720p television',NOW(), 2, 600);

  • UPDATE products SET price = 800 WHERE id = 9;

  • DELETE FROM products WHERE ID = 9;

Known Limitations

  • This custom data source currently only works with OData versions 1.0 or 2.0.

  • OData version 3.0 is partially supported interpreting it as a lower version, but this method may not work. More information:

 

  • You can't filter elements specified obtained through "expand" related entities. VDP must post-filter these items using ROW syntax in the query.

  • The insertion and the update of complex fields are not supported.

  • The insertion of arrays is not supported.

  • The wrapper does not allow access to databases that contain tables without keys.  In this case it throws the following exception: 'Root types must have keys'.
  • Feed Customization annotations where using XML Atom is not supported. JSON format is recommended in this scenario.

Other ways to consume OData sources

Using ATOM/XML

Is possible to access to OData server through a URL. For example, the following URL returns all the entities of the OData server:

http://services.odata.org/OData/OData.svc/

Using this URL you can access to all entities of one type:

http://services.odata.org/OData/OData.svc/Products

And you can access one entity using the identifier:

http://services.odata.org/OData/OData.svc/Products(0)

Importing ATOM/XML into VDP

  1. In the VDP Administration Tool, go to: File → New… → Data source → XML

  1. Set the parameters as follows:

  1. Name: the name of the new XML data source.

  1. Data route: “HTTP Client” configured as:

  1. HTTP method: GET

  1. URL: URL to the entities. For example:

http://services.odata.org/OData/OData.svc/Products

  1. Create a new base view:

  1. We can only recover data selecting “entry” in the section “Stream output at specified level”.

  1. When clicking “Ok” we have a similar base view to the next one:


  1. If we execute the view, the results are located into the field “entry”:


Using JSON

To access OData using JSON only is necessary to add the following parameter to the URL:

        ?$format=JSON

It’s also possible to access OData using JSON adding the following parameters to the HTTP header when doing a GET:

        Accept: application/json

The following is an example of using the URL to access OData through JSON:

http://services.odata.org/OData/OData.svc/Products?$format=json

Importing JSON into VDP

The steps to access to OData using JSON are:

  1. In the VDP Administration Tool, go to: File → New… → Data source → JSON

  1. Set the parameters as follows:

  • Name: the name of the new data source.

  • Data route: “HTTP Client” configured as:

  • HTTP method: GET

  1. Create a base view from the new datasource:

  • We can get only the data setting the JSON root as: /JSONFile/value


  1. When clicking “Ok” we have a similar base view to the next one:

  1. Data are located in the field “array_value”:

  1. Data can be directly accessed if you specified the root “/JSONFile/value”:


Troubleshooting

Symptom

Error message: “Received exception with message 'com.ctc.wstx.exc.WstxEOFException: Unexpected EOF; was expecting a close tag for element <feed> at [row,col {unknown-source}]: [<row>,<col>]' ”

Resolution

There is a problem with the implementation of the OData Service that you are trying to access. The wrapper is attempting to project a property that does not validate the constraints of the model. You must check the values of the properties of the entry located in the row indicated in the error message taking into account the metadata.

References

OData official page

  • Documentation:

  • Libraries:

Wikipedia article:

OData references into the Microsoft webpage:

  • Create and Consume JSON-Formatted OData:
  • Building Rich Internet Apps with the Open Data Protocol:

  • OData Operations:

  • Examples:

Web pages with OData examples:

  • Example read-only service in the official web site:

  • Example read-write service in the official web site:

  • Example OData installing the module odata-server of JayData server

Appendix

Connection to Microsoft Sharepoint Online

In order to connect to Microsoft Sharepoint online using the Denodo OData2 Custom Wrapper you must follow the steps explained in the Connecting to Sharepoint using OData section of the How to integrate Denodo with Sharepoint Online document.