Skip to content

Querying The Offline Store

End users can run various OData system queries like $search and $filter on the local offline store after the initial download.

The Search System Query

If the property type of an entity is Edm.String, then the property is searchable. By default, all searchable properties of the entity are scanned for a given search term using a LIKE SQL query. If there are many searchable properties, the search may be time-consuming. To improve performance, selective search is supported, limiting searches to selected properties.

Offline OData supports the $search system query with the following capabilities and limitations:

  • Capabilities:

    • Searching simple properties of the type Edm.String.
    • Searching collection-typed properties.

  • Limitations:

    • Searching dynamic properties is only possible if the underlying entity type is open. However, selective search is not supported for dynamic properties, meaning all dynamic properties will be searched even if only one dynamic property is selected.
    • If no searchable property is selected or if the targeting entity type has no searchable properties, the $search query option will be ignored when generating the query result.

The Search OData API

The following OData Core APIs are designed for the $search query:

It's worth noting that Offline OData supports prefix search introduced in SAPOData. A search term ending with a star (*) indicates prefix matching.

The examples below illustrate how to apply the $search query on the local store.

// search from the Email property of the Customer entity type
EntitySet customerSet = dataService.getEntitySet("Customers");
EntityType customerType = customerSet.getEntityType();
Property emailProp = customerType.getProperty("Email");

// basic search term
SearchTerm searchTerm = SearchTerm.word("Jenni");
DataQuery searchQuery = new DataQuery().from(customerSet).select(emailProp).search(searchTerm);
EntityValueList entities = dataService.executeQuery(searchQuery).getEntityList();

// prefix search
searchTerm = SearchTerm.prefix("Jenni");
searchQuery = new DataQuery().from(customerSet).select(emailProp).search(searchTerm);
entities = dataService.executeQuery(searchQuery).getEntityList();

// search phrase
StringList stringList = new StringList();
stringList.add("Jennifer@sap.com");
searchQuery = new DataQuery().from(customerSet).select(emailProp).search(SearchTerm.phrase(stringList));
entities = dataService.executeQuery(searchQuery).getEntityList();

// logical search operator
searchQuery = new DataQuery().from(customerSet).search(SearchTerm.word("Jenni").or(SearchTerm.word("Victor")));
entities = dataService.executeQuery(searchQuery).getEntityList();

// search from the Email property of the Customer entity type
val customerSet = dataService.getEntitySet("Customers")
val customerType = customerSet.entityType
val emailProp = customerType.getProperty("Email")

// basic search term
var searchTerm = SearchTerm.word("Jenni")
var searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
var entities = dataService.executeQuery(searchQuery).getEntityList()

// prefix search
searchTerm = SearchTerm.prefix("Jenni")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
entities = dataService.executeQuery(searchQuery).getEntityList()

// search phrase
val stringList = StringList()
stringList.add("Jennifer@sap.com")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(SearchTerm.phrase(stringList))
entities = dataService.executeQuery(searchQuery).getEntityList()

// logical search operator
searchQuery = DataQuery().from(customerSet).search(SearchTerm.word("Jenni").or(SearchTerm.word("Victor")))
entities = dataService.executeQuery(searchQuery).getEntityList()

// search from the Email property of the Customer entity type
let customerSet: EntitySet = dataService.entitySet(withName:"Customers")
let customerType: EntityType = customerSet.entityType
let emailProp = customerType.property(withName:"Email")

// basic search term
var searchTerm : SearchTerm = SearchTerm.word("Jenni")
var searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
var entities = try dataService.executeQuery(searchQuery).entityList()

// prefix search
searchTerm = SearchTerm.prefix("Jenni")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(searchTerm)
entities = try dataService.executeQuery(searchQuery).entityList()

// search phrase
let stringList : StringList = StringList()
stringList.append("Jennifer@sap.com")
searchQuery = DataQuery().from(customerSet).select(emailProp).search(SearchTerm.phrase(stringList))
entities = try dataService.executeQuery(searchQuery).entityList()

// logical search operator
searchQuery =  DataQuery().from(customerSet).search(SearchTerm.word("Jenni").or(SearchTerm.word("Victor")));
entities = try dataService.executeQuery(searchQuery).entityList()

Note

$search system query with an empty search term (E.g. $search="" ) is ignored and will be removed while querying the local store. It is mandatory to provide a valid search term or search phrase to the $search system query option.

The Search Case Sensitivity

By default, the UltraLite database uses a case-sensitive collation, which makes the search functionality case-sensitive as well. You can modify the case sensitivity in the mobile services cockpit; however, this option only affects new databases. The collation of existing UltraLite databases cannot be changed. If the collation is set to case-insensitive in the mobile services cockpit, search conditions will also be case-insensitive.

For an existing case-sensitive database, you can perform a case-insensitive search for a single character. For example, the query $search=[Aa]bc will search for both Abc and abc from the local store.

The Underlying UltraLite Search SQL

The UltraLite LIKE SQL operator is used to search within searchable columns. For more information on the restrictions of the LIKE search, refer to [UltraLite LIKE Search Condition] (https://help.sap.com/docs/SAP_SQL_Anywhere/7eca48cee41b418bbd54feff83d57803/82719aea6ce21014ad0dfc15ee220514.html).

Terms can be searched using a LIKE query with the string %term% in the selected searchable columns.

The following characters have special meaning in UltraLite LIKE query:

_ : The underscore (_) matches any one character. For example, "a_" matches "ab" and "a5", but not "a".

% : The percent (%) will match any string with zero or more characters. For example, "go%" matches "goes" and "goods".

[] : The [] （square brackets）matches any single character in the specified range or set. For example, "[BT]oy" matches "Boy" or "Toy". [%] matches only the percent character (the percent character does not act as a wildcard in this context), and [_] matches only the underscore character.

Search Performance Considerations

Performance of a search query is linear to the number of rows in the table multiplied by the number of columns being searched. Therefore, performance may degrade with larger datasets. To boost the performance, it is necessary to index the columns being searched and utilize prefix search to leverage the index efficiently. For example, if there are 1 million rows in the Customers entity set, and the property email of type Edm.String is indexed, using a prefix search query like $select=Email&$search=Emily* will benefit significantly from the index. If the index is not present, and the prefix search is not applicable, it is recommended to use the search query together with other filters to limit the rows to search.

Debugging Search With ILOData

You can use the $search system query within the ILOData command line utility. Here are a few ILOData query examples:

  • Basic search query: get Customers?$search=abc
  • Search query with logical operator AND, OR, NOT: get Customers?$search=abc AND (NOT def) OR xyz
  • Search query with parenthesis: get Customers?$search=abc AND (def OR xyz)
  • Search query with NOT operator: get Customers?$search=NOT def

Note

AND, NOT, OR are uppercase logical operators for the $search system query.

Offline OData Query Functions

The Offline OData supports the below functions for the standard OData $filter system query options.

  • cast
  • ceiling
  • concat
  • day
  • date
  • endswith
  • floor
  • fractionalseconds
  • hour
  • indexof
  • isof
  • length
  • minute
  • month
  • now
  • round
  • second
  • startswith
  • substring
  • substringof
  • contains
  • time
  • tolower
  • totaloffsetminutes
  • totalseconds
  • toupper
  • trim
  • year

Besides the above standard query functions, Offline OData supports the following query functions specially designed for the Offline OData use case.

  • sap.entityexists
  • sap.islocal
  • sap.hasPendingChanges
  • sap.inerrorstate
  • sap.upsertedlastdownload

The sap.entityexists Function

The sap.entityexists is the Offline OData specific function to check if a related entity exists.

The following example illustrates how to use the sap.entityexists query function on the local store.

EntitySet customerSet = dataService.getEntitySet("Customers");
EntityType customerType = customerSet.getEntityType();
Property ordersNavigationProp = customerType.getProperty("Orders");
DataPath dataPathToOrders = DataPath.of(ordersNavigationProp)

// GET Customers?$filter=sap.entityexists(Orders)
DataQuery searchQuery = new DataQuery().from(customerSet).filter(OfflineODataQueryFunction.entityExists(dataPathToOrders));
EntityValueList entities = dataService.executeQuery(searchQuery).getEntityList();

val customerSet = dataService.getEntitySet("Customers")
val customerType = customerSet.entityType
val ordersNavigationProp = customerType.getProperty("Orders")
var dataPathToOrders = DataPath.of(ordersNavigationProp)

// GET Customers?$filter=sap.entityexists(Orders)
var searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.entityExists(dataPathToOrders))
var entities = dataService.executeQuery(searchQuery).getEntityList()

let customerSet: EntitySet = dataService.entitySet(withName:"Customers")
let customerType: EntityType = customerSet.entityType
let ordersNavigationProp = customerType.property(withName:"Orders")
let dataPathToOrders = DataPath.of(ordersNavigationProp)

// GET Customers?$filter=sap.entityexists(Orders)
let searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.entityExists(dataPathToOrders))
let entities = try dataService.executeQuery(searchQuery).entityList()

Note

ILOData command line utility supports the function sap.entityexists. Below is an example ILOData query: GET Customers?$filter=sap.entityexists(Orders)

The sap.islocal Function

The sap.islocal is a Offline OData specific function to check if an entity is local. An entity is local if there are changes that have been made to the entity that have not yet been downloaded from the back end, regardless of whether or not those operations have been sent to the back end.

For more information, refer to Local Information

Note

The sap.islocal function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=not sap.islocal()

The sap.hasPendingChanges Function

The sap.hasPendingChanges is the Offline OData specific function to check if an entity has any pending change requests in the request queue. The sap.hasPendingChanges is different from sap.islocal based on whether or not the change requests have been sent to the back end. An entity that has changes in the request queue that have not been sent will be both local and pending. When the changes in the request queue have been sent to the back end but the results have not been received by a download operation, the entity will be local but not pending.

For more information, refer to Pending Information.

Note

The sap.hasPendingChanges function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=sap.hasPendingChanges()

The sap.inerrorstate Function

The sap.inerrorstate is the Offline OData specific function to check if an entity is in the error state.

For more information, refer to Finding Entities in Error State.

Note

The sap.inerrorstate function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=sap.inerrorstate()

The sap.upsertedlastdownload Function

The sap.upsertedlastdownload is the Offline OData specific function to check if an entity was inserted or updated in the last download.

The following example illustrates how to use the sap.upsertedlastdownload query function on the local store.

EntitySet customerSet = dataService.getEntitySet("Customers");

// GET Customers?$filter=sap.upsertedlastdownload()
DataQuery searchQuery = new DataQuery().from(customerSet).filter(OfflineODataQueryFunction.upsertedLastDownload());
EntityValueList entities = dataService.executeQuery(searchQuery).getEntityList();

val customerSet = dataService.getEntitySet("Customers")

// GET Customers?$filter=sap.upsertedlastdownload()
var searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.upsertedLastDownload())
var entities = dataService.executeQuery(searchQuery).getEntityList()

let customerSet: EntitySet = dataService.entitySet(withName:"Customers")

// GET Customers?$filter=sap.upsertedlastdownload()
let searchQuery = DataQuery().from(customerSet).filter(OfflineODataQueryFunction.upsertedLastDownload())
let entities = try dataService.executeQuery(searchQuery).entityList()

Note

The sap.upsertedlastdownload function is supported by the ILOData command line utility. Below is an example ILOData query: GET Customers?$filter=sap.upsertedlastdownload()

Last update: August 1, 2024