OData 2 Sources¶

OData is a protocol created by Microsoft, to access data. 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 JSON (and optionally Atom) for representing data, and requests follow a REST model. For this reason, OData is considered an implementation of a RESTful API which additionally offers a way to describe the published data and its model.

The two main versions of the OData protocol: 2 and 4. This data source is to query OData version 2 sources.

Create OData v2 Data Sources¶

To consume data from an OData v2 service, create an OData v4 data source. That is, click the menu File > New > Data source and select OData v2.0 (Custom).

Scroll down, expand Service Endpoint and fill in the form:

Service Endpoint (mandatory): this is the URL to the OData service. Select the HTTP Client option as shown in the previous image and then you can configure all the endpoint parameters, grouped in four sections: Configuration, Proxy, Authentication and Filters.
- Configuration
  - HTTP Method: For OData v2 sources should be GET.
  - Base URL: URL of the OData v2 endpoint.
Filters: usually, you do not need to set anything here.

The other settings of this panel are configured like any other HTTP Path of a JSON or XML data source (the page HTTP Path explains how to do it).
Service Format (mandatory): format used by the wrapper to access the OData service. Must be one of these: JSON or XML-Atom (JSON is default and recommended in OData v4).
Service Version (optional): if specified, the wrapper tries to force the compatibility of the OData service with one of these versions: V1 or V2.

Click Save.

Create OData v2 base views¶

Now, you have to create a base view to query the data of an endpoint of this OData service. To do this, in the wizard of the OData data source, click CREATE BASE VIEW and enter these values:

Entity Collection (mandatory): name of one of the collections defined at 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 (optional): parameters that will be added to the URLs sent to the OData service for obtaining data. A custom query option is any query option with the following form customOption = customValue. To specify several query options, separate each option with the character &. Custom query options cannot begin with the characters $ nor @, according to the OData v4 standard. Example: http://host/service/Products?debug-mode=true
Expand Related Entities (optional): if selected, the references to other entities appear directly in the main entity as arrays or registers.
Enable Pagination (optional): if selected, the base view will have two more fields that can be used to paginate through the results. Names of these fields: fetch_size and offset_size.

Examples¶

You can create a sample base view over a public OData v2 endpoint: https://services.odata.org/V2/(S(vpkxbq1whuprcdoeauyoletp))/OData/OData.svc/

After setting the base view parameters, click Create Base View. Now, you can execute query like these:

SELECT * FROM bv_odata2_products WHERE id = 6;

INSERT INTO bv_odata2_products
   (id, name, description, releasedate, rating, price)
VALUES
   (9, 'HDTV', '32 inch 720p television', NOW(), 2, 600);

UPDATE bv_odata2_products SET price = 800 WHERE id = 9;

DELETE FROM bv_odata2_products WHERE ID = 9;

Known Limitations¶

This custom data source works with OData versions 1.0 or 2.0.
You cannot filter elements specified obtained through “expand” related entities. The execution engine 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.

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¶

Official documentation of OData: http://www.odata.org/docs/
OData documentation provided by Microsoft:
- Create and Consume JSON-Formatted OData: http://msdn.microsoft.com/es-es/magazine/jj190799.aspx
- Building Rich Internet Apps with the Open Data Protocol: http://msdn.microsoft.com/es-es/magazine/ff714561.aspx
- OData Operations: https://www.odata.org/documentation/odata-version-2-0/operations/
- Examples: http://msdn.microsoft.com/en-us/library/ff478141.aspx
Web sites with OData examples:
- Example read-only service in the official web site:
- Example read-write service in the official web site:
  - http://services.odata.org/V2/(S(gsrk2wcskjydxw2iixw3kvdr))/OData/OData.svc
  - http://services.odata.org/V2/(S(gsrk2wcskjydxw2iixw3kvdr))/OData/OData.svc/Categories
  - http://services.odata.org/V2/(S(gsrk2wcskjydxw2iixw3kvdr))/OData/OData.svc/Products
  - http://services.odata.org/V2/(S(gsrk2wcskjydxw2iixw3kvdr))/OData/OData.svc/Suppliers

Appendix¶

Connection to Microsoft SharePoint Online¶

To connect to Microsoft SharePoint online using the Denodo OData2 Custom Wrapper, follow the steps of article How to integrate Denodo with SharePoint Online (section Connecting to SharePoint using OData) of the Knowledge Base.

USER MANUALS