Chapter 8. OData Support 8.1. OData Support 8.1.1. OData OData (Open Data Protocol) is a web protocol that utilizes URIs to identify and interact with resources allowing for a great level of data integration and interoperability across clients, servers and services. 8.1.2. Use Case REST is commonly used by companies that have implemented service-oriented architectures. However, it does not provide unified calling semantics nor does it provide a data model, meaning that each company has needed to define its own. OData is a solution to this problem because it provides a specification that defines standard ways to define data source operations and also standardizes the way in which you define your data schema. By standardising your service and providing metadata that applications can use, you can avoid vendor lock-in. 8.1.3. Implementation in Red Hat JBoss Data Virtualization When you deploy a virtual database, the OData protocol is supported by default. OData support is implemented and deployed through two WAR files that you can find at EAP_HOME/dataVirtualization/vdb/teiid-odata.war and EAP_HOME/dataVirtualization/vdb/teiid-olingo-odata4.war. 8.1.4. Accessing Information via OData If you have a virtual database called "northwind" deployed and you wish to access a customers table in an NW model, use HTTP GET via this URL: The equivalent SQL command via a JDBC/ODBC connection is this: Note Make sure that you fully qualify the table name along with the model name. Also, use the correct case (upper or lower) to match what you have in the virtual database. You can output your query results in AtomPub XML (the default), Atom or JSON format. Note Sometimes you may see this message: This means you either supplied an incorrect model-name.table-name combination so please check the spelling and case. Alternatively, it may mean that your table did not have any PRIMARY KEY or UNIQUE KEY(s) on them. Since OData access is key oriented, every table must have a primary key or at least one unique key. (Note that Red Hat JBoss Data Virtualization does support composite primary keys.) If you do not see all the rows, issue another call with the same URL, but with the USDskiptoken query option specified from the result: You can submit criteria with your query to filter the results: This is the equivalent of making a JDBC/ODBC connection and issuing the SQL To request the result in JSON format, add this option: USDformat=json. Here is an example: You can combine query options as needed. Here is an example: You can navigate from one entity to another like this: That is the equivalent to this SQL query: You can also use OData to perform CREATE/UPDATE/DELETE operations along with READ operations. Here are the HTTP methods involved: INSERT/CREATE is accomplished through the "POST" HTTP method: An UPDATE is performed with an HTTP "PUT" command. Here is an example: Here is an example delete command: 8.1.5. Security OData access is restricted by HTTPBasic authentication which is authenticated against the "teiid-security" domain. You must assign users the odata role. In order to change the default security domain, use a JBoss AS deployment-overlay to override the web.xml file. 8.1.6. Proxy and Cloud Environments If the Red Hat JBoss Data Virtualization server is configured behind a proxy server or deployed in cloud environment, or using a load-balancer then the URI of the server which is handling the OData request is different from URI of the proxy. To generate valid links in the OData responses, you must configure the "proxy-base-uri" property in the web.xml file. If it is a system-wide property, configure it like this: Note When the volume of result rows exceed the configured batch size, cursoring logic is implemented. On every request, only batch-size number of rows are returned. Each such request is considered an active cursor, with a specified amount of idle time specified by the skip-token-cache-time parameter. After the cursor has timed out, it closes and the remaining results and will not be available for any further queries. Since there is no session-based tracking of these cursors, if the request for skiptoken comes after the expiry, the original query will be executed again and tries to reposition the cursor to relative absolute position, however the results are not guaranteed to be same as the underlying sources may have been updated with new information meanwhile. 8.1.7. How Red Hat JBoss Data Virtualization exposes schema for OData OData defines its schema using Conceptual Schema Definition Language (CSDL). Every VDB, that is deployed in an ACTIVE state in the Red Hat JBoss Data Virtualization server exposes its metadata in CSDL format. For example if you want retrieve metadata for the Northwind database, you need to issue a query like this: Since the OData schema model is not a relational schema model, Red Hat JBoss Data Vritualization maps its relational schema model to OData's schema model like this: Table 8.1. Table Mappings 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 FunctionImport Procedure Navigation Property on EntityType, Association, AssosiationSet Procedure's Table Return ComplexType. (Note that Red Hat JBoss Data Virtualization does not define any "embedded" ComplexType in the EntityType. It also does not define any one EntityContainer that resulted from different VDB models as a default container. Therefore, you can only access entities by supplying their full paths.) 8.1.8. Native Queries Native and direct queries are not supported. However, you can use the web service translator's invokehttp method directly to issue a REST-based call and parse the results of this via SQLXML. 8.1.9. Server Use Red Hat JBoss Data Virtualization can expose any data source as an OData-based web service. 8.1.10. Configuration Configure the OData WAR archive by setting the following properties in the web.xml file: Table 8.2. web.xml Settings Property Name Description Default Value batch-size Number of rows to send back each time, -1 returns all rows 256 skiptoken-cache-time Time interval between the results being recycled/expired between USDskiptoken requests 300000 local-transport-name Data Virtualization local transport name for connection odata invalid-xml10-character-replacement Replacement string if an invalid XML 1.0 character appears in the data - note that this replacement will occur even if JSON is requested. No value (the default) means that an exception will be thrown with XML results if such a character is encountered. proxy-base-uri Defines the proxy server's URI to be used in OData responses. n/a connection.XXX Sets XXX as an execution property on the local connection. Can be used for example to enable result set cache mode. n/a 8.1.11. Limitations The following feature limitations currently apply. JBoss Data Virtualization now supports OData Version 4. Blob support for media types are not supported. USDvalue construct to retrieve individual column value is supported. create/update/delete USDlinks is not supported. 8.1.12. Client Tools for Access There are various ways you write your OData access layer. Your choice depends upon your needs and your programming model. Here are some suggestions: Your Browser: The OData Explorer is an online tool for browsing an OData data service. Microsoft .NET Framework 3.51: the WCF Data Services framework is available as a separate download for .NET 3.x. Microsoft .NET Framework 4.0: the WCF Data Services framework built into .NET 4.0. Silverlight 3: the Data Services client library for Silverlight is available for download. Java: the Restlet 2.0 library for Java (including Java on your Android phone) supports the OData protocol. Java: Use a library like OData4J for Java based access, or any Rest based framework JavaScript: the XMLHttpRequest object is standard in modern browsers or you can use jQuery, which comes out of the box with .NET 4.0 or is available for download. PHP: the Toolkit for PHPprovides OData support for PHP clients. AJAX: if you're using AJAX for ASP.NET, Microsoft provides the ASP.NET Ajax Library for getting to OData. Excel 2010 PowerPivot: PowerPivot comes with OData support built right in. Windows Desktop: LINQPad is a good tool for building OData queries interactively but it has some limitations: firstly, it is not able to handle FunctionImports (procedures). If the model contains only procedures, no tables then also it acts error. Secondly, it does not work with multiple schema, as it does not show all the schemas, only the default one. Since no default schema is set in the VDB's USDmetadata, it finds the first one and uses it. OData V2 does allow multiple Schemas and multiple EntityContainers in a single USDmetadata so it appears that LINQPad is not yet fully compliant with this specification. Shell Scripts: use CURL tool

Commands:
http://localhost:8080/odata/northwind.1/NW.customers
SELECT * FROM NW.customers
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"> <code>NotFoundException</code> <message lang="en-US">EdmEntitySet NW.customer is not found</message> </error>
http://localhost:8080/odata/northwind.1/NW.customers?USDskiptoken=xxx
http://localhost:8080/odata/northwind.1/NW.customers?USDfilter=name eq 'bob'
SELECT * FROM NW.customers where name = 'bob'
http://localhost:8080/odata/northwind.1/NW.customers?USDformat=JSON
http://localhost:8080/odata/northwind.1/NW.customers?USDfilter=name eq 'bob'&USDformat=JSON
http://localhost:8080/odata/northwind.1/NW.customers(1234)/NW.orders?USDfilter=orderdate gt datetime'2012-12-31T21:23:38Z'
SELECT o.* FROM NW.orders o join NW.customers c on o.customer_id = c.id where c.id=1234 and o.orderdate > {ts '2012-12-31 21:23:38'}
POST /service.svc/Customers HTTP/1.1 Host: host Content-Type: application/atom+xml Accept: application/atom+xml Content-Length: nnn <?xml version="1.0" encoding="utf-8"?> <entry xml:base="http://host/service.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom"> <id>http://host/service.svc/Customers('ASDFG')</id> <title type="text" /> <updated>2008-12-07T8:00:00Z</updated> <author> <name /> </author> <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" href="Orders(1)" /> <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" href="Orders(2)" /> <content type="application/xml"> <m:properties> <d:CustomerID>ASDFG</d:CustomerID> <d:CompanyName>Contoso Widgets</d:CompanyName> <d:Address> <d:Street>58 Contoso St</d:Street> <d:City>Seattle</d:City> </d:Address> </m:properties> </content> </entry>
PUT /service.svc/Customers('ALFKI') HTTP/1.1 Host: host Content-Type: application/atom+xml Accept: application/atom+xml Content-Length: nnn DataServiceVersion: 1.0 MaxDataServiceVersion: 3.0 Prefer: return-content <?xml version="1.0" encoding="utf-8"?> <entry xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom"> <content type="application/xml"> <m:properties> <d:CustomerID>ALFKI</d:CustomerID> <d:CompanyName>Updated Company Name</d:CompanyName> <d:Address> <d:Street>Updated Street</d:Street> </d:Address> </m:properties> </content> </entry>
DELETE /service.svc/Customers('ALFKI') HTTP/1.1 Host: host Content-Type: application/atom+xml Accept: application/atom+xml Content-Length: nnn DataServiceVersion: 1.0
<context-param> <param-name>proxy-base-uri</param-name> <param-value>USD{system-property-name}</param-value> </context-param>
http://localhost:8080/odata/northwind/USDmetadata