• User Manuals »
  • Denodo OData2 Custom Wrapper - User Manual

Denodo OData2 Custom Wrapper - User Manual

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 Denodo Support Site.

Create the OData data source

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 URL to make 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.

  1. Click on “Save” button.

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

  • 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=proxy.denodo.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 are not supported.

  • The wrapper does not allow access to databases that contains tables without keys.  In this case it throws the following exception: 'Root types must have keys'.

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 do 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

This section explains a brief example of a connection to Microsoft SharePoint Online.

Register an app in SharePoint

In order to register an app in SharePoint online, follow these steps:

  1. Navigate to https://<your_site>.sharepoint.com/_layouts/15/appregnew.aspx

  1. Press the button “Generate” for Client Id and Client Secret.

  1. Give a name for the app,

  1. Fill in the “App Domain” field

  1. Enter Redirect URL value. It should be http://localhost:9090/oauth/2.0/redirectURL.jsp

  1. Press the button “Create”

Get the Realm of the site

Realm is a constant GUID for a site. In order to retrieve it, a tool such as Postman can be used. To obtain the Realm, it is necessary to carry out the following steps:

  • Make a GET request like this:

  • https://your_site.sharepoint.com/_vti_bin/client.svc

  • Header:
  • Authorization: Bearer

  • Get the Bearer realm component from the WWW-Authenticate response header and save it.

  • Get the client id component from the WWW-Authenticate response header and save it. This value is what later we will call Audience Principal ID.

NOTE: If you’re using cookies you might not get back the WWW-Authenticate header with the Bearer realm. Delete the cookies in Chrome or use the Postman Interceptor to avoid this issue.

Get the authorization code

Construct an authorization url as follows:

https://<your_site>.sharepoint.com/_layouts/15/OAuthAuthorize.aspx?client_id=<client_GUID>&scope=Web.read&response_type=code&redirect_uri=http://localhost:9090/oauth/2.0/redirectURL.jsp

Change the parameters of the URL to fit your connection data.

Navigate to the URL from your browser. Login to the site if you have not logged in already. This opens a consent page prompts the user to grant (or deny) the app the permissions that the app requests. In this case, the user would be granting the app read access to the current site (Web).

Once you grant the permission (by clicking trust), SharePoint Online site asks Access Control Service (ACS) to create a short-lived (approximately 5 minutes) authorization code unique to this combination of user and app. ACS sends the authorization code to the SharePoint site.

SharePoint Online site redirects the browser back to the redirect URI that was specified when the app was registered. It also includes the authorization code as a query string. The redirect URL is structured like the following:

http://localhost:9090/oauth/2.0/redirectURL.jsp/?code=<authcode>

Get the access token and refresh token

Construct the below POST request:

  • URL:
  • https://accounts.accesscontrol.windows.net/<site_realm>/tokens/OAuth/2

  • Header:
  • Content-Type = "application/x-www-form-urlencoded"

  • Post parameters (in the body of the request):

  • grant_type=authorization_code

  • client_id=<client_id>@<site_realm>

  • client_secret=<client_secret>

  • code=<auth_code>

  • redirect_uri=http://localhost:9090/oauth/2.0/redirectURL.jsp

  • resource=<audience_principal_ID>/<site_host>@<site_realm>

  • Where:
  • <site_realm> is the Bearer realm obtained in the step “Get the Realm of the site”.

  • <client_id> is <client id when registering the app>@<site realm from the step “Get the Realm of the site>.

  • <client_secret> is the client_secret obtained when registering the app.

  • <auth_code> is the auth code obtained in step “Get the authorization code”.

  • <resource> is <audience principal ID>/<sharepoint domain>@<site realm>.

  • <audience_principal_ID> is a permanent security principal ID for SharePoint. <audience_principal_ID> is obtained in step “Get the Realm of the site” (the value “client_id” in the response header WWW-Authenticate).

        

NOTE: all values need to be URL encoded (including the client_secret)

Create a base view over the service

Once the necessary data to make a connection to Sharepoint Online has been obtained, the last step is to configure the custom wrapper.

The following image shows a template of how the fields of the custom wrapper should be filled

with the previously obtained data.