Retrieving Data From an OData service
Retrieving data from an OData interface can be pretty easy. In this article you will learn how to retrieve data from an OData service. Before we can retrieve data we need an OData profile. The interface supports OData version 2-4.
Retrieving Multiple Rows
Retrieving multiple rows from an OData service can be done by using the OData Interface Wizard.
- Create or open an existing method.
Make sure the template is set to an OData template, default OData.
Click on the Interface Script tab in the tab list.
Click on Start Wizard in the ribbon. A popup appears to select a profile of the OData service to connect to.
When a Service URL is specified in the profile the entity sets are loaded on the left side.
If no Service URL is specified in the profile this can be set using the Set OData Service button.
When clicking on an entity set on the left, the properties of this entity set are loaded on the right.
As you can see the properties are automaticly assigned a field type. Properties in structures are defined as [Structure].[Property], see Location.Address. A structure can also have another structure, City in Location in this case. These always need to be seperated by a dot.
By clicking the checkbox in front of a property, a dropdown is shown. Here you can select an object field or method output field to assign the property to.
You can add a new object or method output field by clicking on the plus-button. This will show a popup asking wether you want to create to field on object or method level. After this you can specify a field name, default this is set to the entity property name.
Select a couple of more properties and click on Save. The wizard closes as you can see the interface is now filled in.
For more information about the interface script see the article Interface Script.
Click on Save. The interface script and the field are added to the object / method.
Click on Test Method and select a profile.
Our data from the OData service was successfully retrieved in the Ometa Business Connector.
Retrieving a Single Row
It's also possible to retrieve a single row using the key field of the entity set. Specifying input fields using the OData interface wizard is currently not supported.
- Create or open an existing method.
- Click on Interface Script in the ribbon.
- Make sure Operation is set to SingleGet.
- Click on Save.
Specify the key field as input field.
Click on Test Method, select a profile and specify the key value.
The data with the key value 'KSFO' for key field IcaoCode is retrieved in this case.
Query Options
| Query Option | Description |
|---|---|
| Filter | Used to filter a collection of resources. |
| Expand | Specifies the related resources to be included in line with the retrieved resources. |
| NavigateTo | The list to navigate to in the given entity set. |
| OrderBy | Used to sort the requested resources in an ascending (asc) or descending (desc) way. |
| Top | The number of items in the queried collection to be included in the result. |
| Skip | The number of items in the queried collection that are to be skipped and not included in the result. |
| Count | Indicates whether only the number of resources in the result should be returned. You will only have a count value in the result when you set 'Count=True' in your interface script. |
| InlineCount | Indicates whether a count of the matching resources should be included with the requested resources in the result. The difference with 'Count' is that an inline count returns the number of rows in the result and add the requested resources as well. So you will receive data, not just a count result. |
| CountOutputFieldName | The external name of the field to store the result of the (inline)-count in. This field is required when using 'Count' or 'InlineCount' |
| RoutePrefix | The prefix of the entity set controller endpoint. This can be different when working with derived types for example. |
| SelectOnlyOutputFields | Whether to select only the output fields when executing a GET request to the OData interface. This option can be disabled when the OData service doesn't support the $select for example. When this option is specified in the interface script, the value from the profile is ignored. |
| UseAbsoluteAssociationsUri | Option to toggle between relative and absolute uris used in the OData requests. This only has an effect on write actions were the insert or update has a reference to another entity (known in the protocol as association). By default, a short relative url is used: "Categories@odata.bind": "Categories(1005)" to refer to the entity. If UseAbsoluteAssociationsUri=True is specified in the interface script, this url changes to specify the full url: "Categories@odata.bind": "https://services.odata.org/V4/OData/(S(iemb1b0csyws2o44cd4mmntn))/OData.svc/Categories(1005)" |
Note
The query options are not available in the wizard yet. You'll have to add them manually to the interface script in order to use them.
As you can see in the screenshot above, we request a multi get of the cases that are created by 'OMETA\SYSTEM'. The results should be ordered by the 'Name' ascending. We skip the first 3 results and only take the first 2. We've also added an inline count leading to the following result:
Note
Skip and Top have no effect on the count operation. As you can see in the screenshot above, the count returns 7, which means 7 records matched the given filter expression. Also note that we return the result of the count operation with every row.
Query Options on the View
When using an OData method to display data in a view with paging, the OData interface will try to integrate the query options into the requests to the OData service. This means that sorting, paging and filtering will be translated into $top, $skip, $filter and $orderby.
This is optimal as the querying will be done on the OData service side which doesn't require the Ometa framework to process the full record set again to enforce filtering/sorting. However, when using fields with a conversion script to sort or filter by, the framework can not translate it into a valid OData request, and thus will do the sorting and paging on the Ometa framework.
Examples
Below are a few more examples of OData method configurations to read data.
Retrieving a Single Row From a Singleton
The following request will retrieve data from a singleton. A singleton refers to a special entity that can be addressed directly, in this case the Person 'me'. To facilitate this, specify Singleton= in the interface script.
| Username | First Name | Last Name | Middle Name | Gender | Age | Emails | Favorite Feature | Features | Address Info | Address Info Address | Address Info City | Address Info City Name | Address Info Country Region | Address Info City Region | Home Address |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| aprilcline | April | Cline | Female | 0 | April@example.com;#April@contoso.com | Feature1 | [{"City":{"Name":"Lander" ... | P.O. Box 555 | [{"Name":"Lander", ... | Lander | United States | WY |
Columns with json were shortened to fit the table, value matches with the response body.
Retrieve Data From a Navigation Property Within a Singleton
The following request will retrieve data from a singleton. This shows an example of a combination of Singleton= and NavigateTo=.
Single Get on a Navigation Property Within a Singleton
This example uses the Microsoft Graph API to fetch a specific row found within a singleton.
The target is the following url: https://graph.microsoft.com/v1.0/identityGovernance/entitlementManagement/accessPackages(%277fe11ff2-9c82-42f0-90f6-c96be1cadd74%27)
/identityGovernance/ is the root singleton, with /entitlementManagement/ as a structured property. Finally, the entitlementManagement has a collection of accessPackages.
In this example the goal is to fetch a single access package using an ID.