Getting Started With OData, "SQL for the Web"

Finding Your Tools

When someone starts working with databases, one of the first things they learn to do is to access data in the database. There are tons of tools, commercial and free, closed or open-source, desktop or server, that make it easier to interact with databases.

This is a screenshot of DBVisualizer, a tool that is available via a "freemium" model—a free version with a somewhat limited feature set, or a paid version that has a richer feature set. It is showing data that is in a Microsoft Azure database. SQL is used "behind the scenes" to query the database, where SQL stands for "Structured Query Language."

SQL for the Web

This is a small, simple table that we’re going to use for this post. It has three columns and 18 rows. Not very large, but the concepts apply, regardless of the size of the database.

While SQL is a very powerful and mature query language, it isn’t the best tool for querying data on the internet. There is a new query standard called OData that is considered to be "SQL for the Web"—a query "standard" that is more appropriate for querying across the internet. See OData.org for more information.

While there are many SQL tools that help developers learn about "SELECT * from…" and other SQL constructs, how can they learn about OData?

You need an OData-accessible data source and some tool that can process OData commands or queries. Let’s use the same Azure database as our OData-accessible database, and the Postman Chrome-browser extension as our query tool to learn the basics of OData.

Enabling Your Database

First, while a large number of databases can be accessed via SQL, OData isn’t as widely supported. So to access our Azure database, it needs to be "OData-enabled." While there are some Microsoft facilities that can be installed and configured, we’re going to look at a simpler alternative. We’re going to use DataDirect Cloud as a gateway to put an OData-frontend on our Azure database.  

DataDirect Cloud is a service which enables unified access to data in the cloud and on premise.

This is the console that shows the state of your subscription to the DataDirect Cloud service.

For this demo, we’re going to define a data source that corresponds to the database shown in DBVisualizer, a database that resides on Microsoft Azure.

Our Azure-resident database is fundamentally a SQL Server database—that’s the template we’ll use in DataDirect Cloud.

Here, we’ve provided the relevant credentials and address information. To make this database accessible via OData, however, we must specify a few more things. We have to specify which tables are going to be "exposed" via OData.

After clicking on the OData tab, you’ll need to specify which schema contains your tables, then choose which table(s) within that schema to include. Note that if your table doesn’t have a Primary Key defined, you must manually specify one.

When you’re done, the system will have created a service URI that corresponds to this particular table or set of tables that are accessible via OData. This URI can be seen as analogous to the address of a traditional network-resident database being queried by an SQL tool.

Now That You Have an OData Data Source, What Can You Do With It?

Just as you can query traditional relational databases via SQL, you can query OData data sources via OData! Using the free Postman Chrome-based application, you can interact with an OData source by specifying the appropriate OData/REST queries and providing the appropriate credentials.

Here’s an example of simply getting the basic info about our datasource—you simply direct your query to our OData-enabled Azure database using the service URI noted above and provide the necessary credentials within Postman.

To get the metadata about the exposed table or tables, we simply append $metadata to the end of our service URI.

To get the contents of the Customers table, append /Customers to the Service URI. By default, the data is returned in XML.

To get the data in JSON format instead, simply append the "format=json" directive to the URI.

To get a specific record, append the appropriate Primary Key value.

You can even get a count of the number of records in the Customers table. Here you specify the appropriate directive, "$count", on the Service URI.

This is just a sample of the kinds of interactive querying you can do via Postman against an OData data source. For more technical information about OData syntax, check out OData.org's OData 2.0 URI Conventions page.

Get Started With OData Today

Now that you have an idea of some of the benefits OData has to offer, you may be ready to try it yourself! We offer a simple way to OData-enable the leading enterprise and cloud data sources, allowing them to be accessed by the OData tool or application of your choice. This allows you bi-directional data access to leverage read and write support for OData entry points for any application or data source. Try DataDirect Cloud today and start harnessing the power of OData.