Exact Synergy Enterprise

How-to: Retrieving sets of data from entities

Introduction

Retrieve set is also known as Entities or R+. It is used to retrieve sets of data from entities based on a set of criteria called retrieve criteria. R+ can be used with web service calls and component level calls depending on the R+ provider.

Menu path

Not applicable.

Description

The R+ has the following features:

• Batch size
• Session ID
• Filter
• Sorting
• Default value

Note: The sorting and default value features are additional features introduced in Exact Synergy Enterprise product update 242 and Exact Globe Next product update 395.

Batch size

Batch size controls the number of records returned by R+. This is important for web service calls, which have a limited record size for sending data. The default value for the batch size is “0”, which represents all the records.

Session ID

Session ID is used with the batch size for paging. R+ only supports “forward only” paging whereas session ID retrieves the next batch of results based on the batch size. The default value for the session ID is “0”, which represents a new session. Session ID will be returned when the session ID is “0”. The session ID can then be used for subsequent pages.

Filter

Filter enables the client of R+ to return a set of record according to the criteria. The criteria is known as the filter criteria and it is the “where” clause in the SQL query. Filter criteria is a collection of criteria and they only support “AND” operation. The filter is based on the following operators:

Filter also includes sorting and setting default values for properties such as the following:

Column selection

By default, all the columns that are available through the web service are retrieved. To limit the columns that are retrieved, insert the property "#SELECT". The value should be a list of all the property names involved, separated by ",". If the operation "=" or "IN" is used, only the given columns will be retrieved. However, if the operation "<>" or "NOT IN" is used, all the columns except the ones given will be retrieved. It is possible to give more than one #SELECT criteria but it is not possible to combine the inclusion and exclusion in one retrieve action. Only the first action will be performed, and any conflicting ones will be ignored.   

Sorting

Sorting is based on the property name “#SORT”. It is based on the “Order by” clause of an SQL transaction query, which supports ASC and DESC, and separated by “,”. In the SQL query, ASC is a default and can be left blank.

The format for sorting is “#SORT=property1 ASC, property2 DESC, property3”.

Note: When sorting with a primary key property, the property should be the last or only the property is listed. As the primary key property is always unique, there is no purpose to follow it with subsequent properties.

For example, the following is supported:

     #SORT=propertykey ASC
     #SORT=property ASC, propertykey ASC

The following is not supported:

     #SORT=propertykey ASC, property ASC

Default value

Default value can be defined by using the “SET” operator.

Note: The “SET” value is useful only if the entity supports default values for the specified property. Default value is currently available only in the repository provider for R+.

Example

There is not much difference between a web service call and a component level call, except that calls to a web service is referenced to a web service whereas component calls are referenced to components. There are also slight differences in the behaviors between the web service and reference. This can be seen from the following sample codes.

The following examples illustrate the use of R+ with web services and component calls. Both the examples require connection properties and buttons to retrieve the data from the resource entity. The examples will retrieve two batches of resources that are inactive and the resource IDs of the resources with the resource ID greater than “1”. The results will be displayed in two separate list views; first and second batch. The returned results will then be sorted by the resource status and full name.

Web service

The following web service example is based on Exact Synergy Enterprise. Only Exact Synergy Enterprise supports web service calls.

1. Prerequisites

This example uses a form, which consists of the following controls:

• txtAddress — Text box to define the address of the web service.
• txtUsername — Text box to define the login user name.
• txtPassword — Text box to define the password of the user name.
• btnRetrieve — Button to start the retrieving process.
• lstResult1 — List view to return the first set of record.
• lstResut2 — List view to return the second set of record.
2. Setting reference

The R+ web service reference name is Exact.Entities.svc. This can be referenced by using the Add Service Reference menu. Exact.Entities.svc can be found in your web server. For example, http://localhost/Synergy/Exact.Entities.svc.

Note: Give the namespace a name. For example, Entities.

3. Setting variables

Define a client variable for the web service reference. The following example uses a global variable.



Create a private function to create an instance of the web service.



At btnRetrieve, click the event based on the following:

• Call the lnitEntities function that you have defined earlier.
• Initialize the components and objects.



4. Setting batch size

You have to ensure that there are more than two records in the database for this to work. In this example, a small batch size is selected to show the paging.

rc.BatchSize = 2

5. Setting filter criteria
• Standard filter criteria

Get a list of active and hired employees with IDs greater than “1”.



• Default value

The following criterion is to request the entity to return the message ID for the binary service.



• Column selection

Return only the full name, status, and employee number of the selected employees.



• Sorting

Return the list that was sorted by the status and resource name.



• Retrieve first batch

Create a private function that will add the results from R+ to the list view.



Get the first batch of the results.



• Setting session ID

Assign the returned session ID to the next batch of records. The paging will work only if there is a returned session ID. The session ID tells R+ to get to where it last stopped.

rc.SessionId = returnedEntities.SessionID

• Retrieve second batch

After assigning the session ID, retrieve the next batch.



After retrieving the second batch, the results will be displayed in both the list views.

Component level

The following component level example is based on Exact Globe Next. Exact Globe Next does not support web service calls.

1. Prerequisites

This example uses a form, which consists of the following controls:

• txtSQLServer — Text box to define the server name.
• txtDatabase — Text box to define the database name.
• btnRetrieve — Button to start the retrieving process.
• lstResult1 — List view to return the first set of record.
• lstResult2 — List view to return the second set of record.
2. Setting reference

The service that is required for component level R+ is:

• Exact.Services.Client.dll
3. Setting variables
• At btnRetrieve, click event and make a connection to the environment.



• Declare variables and initialization.



4. Setting batch size

To make this work, ensure there are more than two records in the database. A small batch size is chosen to show how the paging works.

rc.BatchSize = 2

5. Setting filter criteria
• Standard filter criteria

Get a list of active and hired employees with IDs greater than “1”.



• Default value

The following criterion is to request the entity to return the message ID for the binary service.



• Sorting

Return the list that was sorted by the status and resource name.



• Retrieve first batch

Create a private function that will add results to the list view.



Get the first batch of results.



• Setting session ID

Assign the returned session ID for the next batch of records. In order for the paging to work, the returned session ID is required. The session ID tells R+ to get to where it last stopped.

rc.SessionId = returnedEntities.SessionID

• Retrieve second batch

After assigning the session ID, retrieve the next batch of records.



After retrieving the second batch of records, the results will be displayed in both the list views.

Related documents

• Exact Globe Entities Services Startpage
• Exact Synergy Enterprise Web Services Startpage
• How-to: Consuming entity services using entity service client proxy