OData Features
Reformatted Results
By default in TDV, all resources that are published as database data services are available through OData in the XML-based Atom format. Client interfaces (the applications requesting data) can tell OData to reformat the Atom results to JSON using $format=json (instead of the default $format=atom) in the client interface definitions. For example:
http://services.odata.org/OData/OData.svc/ds_orders?$format=json
A client that wants only JSON responses might set the header to something similar to this:
GET /OData/OData.svc/ds_orders HTTP/1.1 host: services.odata.org accept: application/json
Client applications can choose to support only specific content types, or to support all content types. To accept all content types, the header might look like this:
GET /OData/OData.svc/ds_orders HTTP/1.1 host: services.odata.org
System Query Options
You can use options to filter out unqualified records on the OData server side, before the data is returned. This can significantly reduce the amount of data transferred, and improve performance, compared to applying filters using the SQL WHERE clause on the TDV side.
Expand Option
The expand option retrieves records and includes their associated objects. For example,
http://services.odata.org/OData/OData.svc/ds_orders/?$expand=orders
For each entity within the product’s entity set, the values of all associated orders are represented in-line.
The corresponding TDV SQL query would be:
SELECT * from /shared/OData/ODataDemo/DemoService/Products('?$expand=orders')
Filter Option
The filter option retrieves only records that meet certain criteria. For example,
http://services.odata.org/OData/OData.svc/ds_orders/?$filter=orderid gt 1000
This request is used for query and return only records whose orderid value is greater than 1000.
The corresponding TDV SQL query would be:
SELECT * from /shared/OData/ODataDemo/DemoService/Products('$filter=orderid gt 1000')
OrderBy Option
The orderby option arranges the returned records in the order of a designated entity’s values. For example,
http://services.odata.org/OData/OData.svc/ds_orders/?$orderby=customerid
This request displays records in the order of customerid values.
The corresponding TDV SQL query would be:
SELECT * from /shared/OData/ODataDemo/DemoService/Products('$orderby=customerid')
Skip Option
The skip option retrieves records by skipping a specific number of rows. For example,
http://services.odata.org/OData/OData.svc/ds_orders/?$skip=2
This request returns all records except the first two rows.
The corresponding TDV SQL query would be:
SELECT * from /shared/OData/ODataDemo/OData.svc/ds_orders('$ skip=2')
Top Option
The top option retrieves the first n records. For example,
http://services.odata.org/OData/OData.svc/ds_orders/?$top=4
This request returns the first four rows.
The corresponding TDV SQL query would be:
SELECT * from /shared/OData/ODataDemo/DemoService/Products('$ top=4')
Select Option
The select option retrieves values in the column with the specified name. For example,
http://services.odata.org/OData/OData.svc/ds_orders/?$select=shipname
All returned records display only the values in the column named shipname. The other columns display [NULL].
The corresponding TDV SQL query would be:
SELECT * from /shared/OData/ODataDemo/DemoService/Products(' ?$select=shipname')
Combinations of Options
You can concatenate system query options by putting ampersands between them. For example,
http://services.odata.org/OData/OData.svc/ds_orders/?$filter=orderid gt 1000&$top=2
This request return the first two rows whose orderid is greater than 1000.
The corresponding TDV SQL query would be:
SELECT * from /shared/OData/ODataDemo/DemoService/Products(' ?$filter=orderid gt 1000&$top=2 ')