Sunday, February 12, 2017

URL Accessing data on a REST based OData service

This is often a easy way to find database data by sending queries to the service site.
There are two formats of resource representations:


The document OData:Operations describes the actions that can be performed on the URLs. Although most sites follow the conventions defined by specification not all of them do.

The following is a picture of the URL Components from the the OData site that is relevant to our discussion.


As an example let us take the Northwind OData service at this site here:

The http: is the HOST is the SERVICE ROOT

The SERVICE ROOT is required to find all the resources provided by the OData service.

Simply put, if you access the Service Root you would see the following in Microsoft
Edge (Microsoft Edge 39.14965.1001.0)


This is essentially the same as what you see in Mozilla Firefox

URLData_01b.PNG Mozilla (Firefox 51.0.1 (32-bit))

The collection in either of these you see are the tables in the Northwind database exposed as a service.

Accessing the table:

Now how do we access one of these tables. Let us say the Products table. All you have to do is to append Products to the Service Root as in the following:

The Firefox browser would display this:
Only part of the browser display is in this image.


However in Microsoft Edge you would get a response asking you whether or not you want to save.


I have found this is does not always happen and displays the same information as the Firefox.

You may query the browser to format it in JSON with the following instruction:$FORMAT=json

Filtering the table:
After getting to the Products table, let us say you want to get only the first Product which has ProductID=1, then you change the URL to 

In ME you get the same results above.

You can further filter the table.  For example you can find all the Orders placed by the Customer whose CustomerID="ALFKI".$links/Orders

URLData_04 FF

But in ME you would get the following:


Looking at the Northwind database, this result is the same as the query.


These are the columns in the Orders table.


PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...