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:

OData:Atom
OData:JSON


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.


URLData_00.png

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


http://services.odata.org/northwind/northwind.svc

The http: is the HOST
http://Services.odata.org/northwind/northwind.svc: 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)




URLData_01.PNG

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:
http://services.odata.org/V4/Northwind/Northwind.svc/Products

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



URLData_02.png

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


URLData_02b.png

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:
http://services.odata.org/V4/Northwind/Northwind.svc/Products?$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 


URLData_03.png(FF)
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".
 http://services.odata.org/Northwind/Northwind.svc/Customers(%27ALFKI%27)/$links/Orders


URLData_04 FF

But in ME you would get the following:

URLData_04b.PNG

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



URLData_05.PNG

These are the columns in the Orders table.



URLData_06.PNG

No comments:

Post a Comment

Do you know what you installed while custom installing SQL Server 2022?

 When you install any software in Windows OS, the key information gets into the Registry and some of which you can look up in the control pa...