Search Data Structures in Cache with SQL Queries
Note
This feature is only available in the NCache Enterprise Edition.
The data structures can be queried in the cache if they have searchable attributes associated with them during data structure creation. These searchable attributes include:
Prerequisites
- To learn about the standard prerequisites required to work with all NCache client side features please refer to the given page on Client Side API Prerequisites.
- For API details, refer to: ICache, QueryCommand, ICacheReader, SearchService, ExecuteReader, GetValue, IDistributedList, GetGroupData, FieldCount, IDistributedDictionary, GetByTag.
- To learn about the standard prerequisites required to work with all NCache client side features please refer to the given page on Client Side API Prerequisites.
- For API details, refer to: Cache, QueryCommand, CacheReader, getSearchService, executeReader, getFieldCount, getValue, DistributedList, getGroupData, DataStructureAttributes, DistributedMap, getByTag.
- To learn about the standard prerequisites required to work with all NCache client side features please refer to the given page on Client Side API Prerequisites.
- For API details, refer to: Cache, QueryCommand, SearchService, getSearchService, executeReader, getFieldCount, getValue, getGroupData, CacheReader, DataStructureAttributes, getByTag.
- To learn about the standard prerequisites required to work with all NCache client side features please refer to the given page on Client Side API Prerequisites.
- For API detais refer to: Cache, QueryCommand, CacheReader, execute_reader, SearchService, get_search_service, DataStructureAttributes, get_field_count, get_value, get_group_data, get_by_tag.
Search Data Structures by Groups
SQL Query
The following code sample fetches all items from the cache that have been associated with a specified group, Electronics. The group can be associated using DataTypeAttributes class during data structure creation.
Important
- If there are multiple lists specified against the group, all of them will be returned in one result, granted that their data structures are similar.
- For multiple cache items, for example, if a
CacheItem and a list belong to the same group, you need to ensure that the data is handled accordingly.
try
{
// Pre-conditions: Cache is already connected
// Items with groups exist in cache
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with group
// Provide Fully Qualified Name (FQN) of your custom class
string query = "SELECT ProductID FROM FQN.Customer WHERE $Group$ = ?";
/// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
// Providing parameters for query
queryCommand.Parameters.Add("$Group$", "Electronics");
// Executing QueryCommand through ICacheReader
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
// Check if the result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
string ProductID = reader.GetValue<string>(1);
// Perform operations
}
}
else
{
// Null query result set retrieved
}
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
}
catch (Exception ex)
{
// Any generic exception like ArgumentException, ArgumentNullException
}
try {
// Pre-conditions: Cache is already connected
// Items with groups exist in cache
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with group
// Provide Fully Qualified Name (FQN) of your custom class
String query = "SELECT productID FROM FQN.Customer WHERE $Group$ = ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
// Providing parameters for query
queryCommand.getParameters().put("$Group$", "Electronics");
// Executing QueryCommand through CacheReader
CacheReader reader = cache.getSearchService().executeReader(queryCommand);
// Check if the result set is not empty
if (reader.getFieldCount() > 0) {
while (reader.read()) {
String productID = reader.getValue(1, String.class);
// Perform operations
}
} else {
// Null query result set retrieved
}
} catch (OperationFailedException ex) {
if (ex.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT) {
// Make sure that the query format is correct
} else {
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
} catch (Exception ex) {
// Any generic exception like IllegalArgumentException or NullPointerException
}
try {
// Pre-conditions: Cache is already connected
// Items with groups exist in cache
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with group
// Provide Fully Qualified Name (FQN) of your custom class
val query = "SELECT productID FROM FQN.Customer WHERE $Group$ = ?"
// Use QueryCommand for query execution
val queryCommand = new QueryCommand(query)
// Providing parameters for query
queryCommand.setParameters(Map("$Group$" -> "Electronics"))
// Executing QueryCommand through CacheReader
val reader = cache.getSearchService.executeReader(queryCommand)
// Check if the result set is not empty
if (reader.getFieldCount > 0) while (reader.read) {
val productID = reader.getValue(1, classOf[String])
// Perform operations
}
else {
}
}
catch {
case exception: Exception => {
// Handle any errors
}
}
// This is an async method
try {
// Pre-conditions: Cache is already connected
// Items with groups exist in cache
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with group
// Provide Fully Qualified Name (FQN) of your custom class
var query = "SELECT productID FROM FQN.Customer WHERE $Group$ = ?";
/// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
// Providing parameters for query
let map = new Map();
var parameters = map.set("$Group$", "Electronics");
queryCommand.setParameters(parameters);
// Executing QueryCommand through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if the result set is not empty
if (reader.getFieldCount() > 0) {
while (reader.read()) {
var productID = reader.getValue(1, Number());
// Perform operations
}
} else {
// Null query result set retrieved
}
} catch (error) {
// Handle errors
}
try:
# Pre-conditions: Cache is already connected
# Items with groups exist in cache
# Custom class is query indexed through NCache Web Manager or config.ncconf
# Search for items with group
# Provide Fully Qualified Name (FQN) of your custom class
query = "SELECT product_id FROM FQN.Customer WHERE $Group$ = ?"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Providing parameters for query
parameters = {"$Group$": "Electronics"}
query_command.set_parameters(parameters)
# Executing QueryCommand through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if the result set is not empty
if reader.get_field_count() > 0:
while reader.read():
product_id = reader.get_value(str, 1)
# Perform operations
else:
# None query result set retrieved
print("Query result is None")
except Exception as exp:
# Handle errors
Group API
The following code sample fetches all the lists from the cache that have been associated with a specified group, Electronics. The group can be associated using DataTypeAttributes class during the data structure creation.
try
{
// Pre-condition: Cache is already connected
// List with this group already exists in cache
string groupName = "Electronics";
IDictionary<string, IDistributedList<Product>> result = cache.SearchService.GetGroupData<IDistributedList<Product>>(groupName);
if (result != null && result.Count > 0)
{
// Iterate over list
foreach (var item in result)
{
foreach (var i in item.Value)
{
// Perform operations
}
}
}
else
{
// No data against the group found
}
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// NCache specific exception
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
}
catch (Exception ex)
{
// Any generic exception like ArgumentNullException or ArgumentException
}
try {
// Precondition: Cache is already connected
// List with this group already exists in the cache
String groupName = "Electronics";
Map<String, DistributedList<Product>> result = cache.getSearchService().getGroupData(groupName);
if (result != null && result.size() > 0) {
// Iterate over list
for (var item : result.entrySet()) {
// Perform operations
}
} else {
// No data against the group found
}
} catch (OperationFailedException exception) {
if (exception.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT) {
// Make sure that the query format is correct
} else {
// NCache specific exception
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
} catch (Exception exception) {
// Any generic exception like IllegalArgumentException or NullPointerException
}
try {
// Precondition: Cache is already connected// Precondition: Cache is already connected
// List with this group already exists in the cache
val groupName = "Electronics"
val result = cache.getSearchService.getGroupData(groupName)
if (result != null && result.nonEmpty) {
// Iterate over list
for (item <- result) {
// Perform operations
}
}
else {
// No data against the group found
}
}
catch {
case exception: Exception => {
// Handle any errors
}
}
// This is an async method
try {
// Precondition: Cache is already connected
// List with this group already exists in the cache
var groupName = "Electronics";
var result = await (
await this.cache.getSearchService()
).getGroupData(groupName);
if (result != null && result.size() > 0) {
// Iterate over list
for (var item in result.entrySet()) {
// Perform operations
}
} else {
// No data against the group found
}
} catch (error) {
// Handle errors
}
try:
# Precondition: Cache is already connected
# List with this group already exists in the cache
group_name = "Electronics"
result = cache.get_search_service().get_group_data(group_name)
if result is not None and len(result) > 0:
# Iterate over list
for item in result:
# Perform operations
print(item)
else:
# No data against the group found
print("No data found")
except Exception as exp:
# Handle errors
Note
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
SQL Query
The following code sample fetches the items from the cache that have been associated with a specified tag, Stainless Steel. The tag can be associated using DataTypeAttributes class during the data structure creation.
Important
- If there are multiple dictionaries specified against the tag, all of them will be returned in one result dictionary, granted that their data structures are similar.
- For multiple cache items, for example if a
CacheItem and a dictionary belong to the same tag, you need to ensure that the data is handled accordingly.
try
{
// Pre-conditions: Cache is already connected
// Items are already present in the cache with tags
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with tags
// Use the Fully Qualified Name (FQN) of your own custom class
string query = "Select $Value$ FROM FQN.Product WHERE $Tag$ = ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("$Tag$", "Stainless Steel");
// Executing query
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
// Check if result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
// Get the value of the result set
Product result = reader.GetValue<Product>(1);
}
}
else
{
// Null query result set retrieved
}
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
}
catch (Exception ex)
{
// Any generic exception like ArgumentException, ArgumentNullException
}
try {
// Pre-conditions: Cache is already connected
// Items are already present in the cache with tags
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with tags
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "Select $Value$ FROM FQN.Product WHERE $Tag$ = ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.getParameters().put("$Tag$", "Stainless Steel");
// Executing query
CacheReader reader = cache.getSearchService().executeReader(queryCommand);
// Check if result set is not empty
if (reader.getFieldCount() > 0) {
while (reader.read()) {
// Get the value of the result set
Product result = reader.getValue(1, Product.class);
}
} else {
// Null query result set retrieved
}
} catch (OperationFailedException ex) {
if (ex.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT) {
// Make sure that the query format is correct
} else {
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
} catch (Exception ex) {
// Any generic exception like IllegalArgumentException or NullPointerException
}
try {
// Pre-conditions: Cache is already connected
// Items are already present in the cache with tags
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with tags
// Use the Fully Qualified Name (FQN) of your own custom class
val query = "Select $Value$ FROM FQN.Product WHERE $Tag$ = ?"
// Use QueryCommand for query execution
val queryCommand = new QueryCommand(query)
queryCommand.setParameters(Map("$Tag$" -> "Stainless Steel"))
// Executing query
val reader = cache.getSearchService.executeReader(queryCommand)
// Check if result set is not empty
if (reader.getFieldCount > 0) while (reader.read) {
// Get the value of the result set
val result = reader.getValue(1, classOf[Product])
}
else {
// Null query result set retrieved
}
}
catch {
case exception: Exception => {
// Handle any errors
}
}
// This is an async method
try {
// Pre-conditions: Cache is already connected
// Items are already present in the cache with tags
// Custom class is query indexed through NCache Web Manager or config.ncconf
// Search for items with tags
// Use the Fully Qualified Name (FQN) of your own custom class
var query = "Select $Value$ FROM FQN.Product WHERE $Tag$ = ?";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
var map = new Map();
var parameters = map.set("$Tag$", "Stainless Steel");
queryCommand.setParameters(parameters);
// Executing query
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if result set is not empty
if (reader.getFieldCount() > 0) {
while (reader.read()) {
// Get the value of the result set
var result = reader.getValue(1, ncache.JsonDataType.Object);
// Perform operations
}
} else {
// Null query result set retrieved
}
} catch (exception) {
// Handle errors
}
try:
# Pre-conditions: Cache is already connected
# Items are already present in the cache with tags
# Custom class is query indexed through NCache Web Manager or config.ncconf
# Search for items with tags
# Use the Fully Qualified Name (FQN) of your own custom class
query = "Select $Value$ FROM FQN.Product WHERE $Tag$ = ?"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
parameters = {"$Tag$": "Stainless Steel"}
query_command.set_parameters(parameters)
# Executing query
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
# Get the value of the result set
result = reader.get_value(Product, 1)
# Perform operations
else:
# Null query result set retrieved
print("Query result is None")
except Exception as exp:
# Handle errors
Tag API
The following code sample fetches all the dictionary instances from the cache that have been associated with a specified tag, Stainless Steel. The tag can be associated using the DataTypeAttributes class during the data structure creation.
try
{
// Pre-condition: Cache is connected
// Items against this tag exist in cache
Tag tag = new Tag("Stainless Steel");
// Get dictionary against tag
IDictionary<string, IDistributedDictionary<string, Product>> result = cache.SearchService.GetByTag<IDistributedDictionary<string, Product>>(tag);
if (result != null && result.Count > 0)
{
foreach (var item in result)
{
// Perform operations
}
}
else
{
// Dictionary does not exist against tag
}
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// NCache specific exception
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
}
catch (Exception ex)
{
// Any other generic exception like ArgumentNullException or ArgumentException
}
try {
// Precondition: Cache is already connected
// Items against this tag already exist in the cache
Tag tag = new Tag("Stainless steel");
// Get map against tag
Map<String, DistributedMap<String, Product>> result = cache.getSearchService().getByTag(tag);
if (result != null && result.size() > 0) {
for (var item : result.entrySet()) {
// Perform operations
}
} else {
// Dictionary does not exist against tag
}
} catch (OperationFailedException exception) {
if (exception.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT) {
// Make sure that the query format is correct
} else {
// NCache specific exception
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
} catch (Exception ex) {
// Any generic exception like IllegalArgumentException or NullPointerException
}
try {
// Precondition: Cache is already connected// Precondition: Cache is already connected
// Items against this tag already exist in the cache
val tag = Tag("Stainless steel")
// Get map against tag
val result = cache.getSearchService.getByTag(tag)
if (result != null && result.nonEmpty) {
for (item <- result) {
// Perform operations
}
}
else {
// Dictionary does not exist against tag
}
}
catch {
case exception: Exception => {
// Handle any errors
}
}
// This is an async method
try {
// Precondition: Cache is already connected
// Items against this tag already exist in the cache
var tag = [new ncache.Tag("Stainless steel")];
// Get map against tag
var result = await (await this.cache.getSearchService()).getByTag(tag);
if (result != null && result.size() > 0) {
for (var item in result.entrySet()) {
// Perform operations
}
} else {
// Dictionary does not exist against tag
}
} catch (error) {
// Handle errors
}
try:
# Precondition: Cache is already connected
# Items against this tag already exist in the cache
tag = ncache.Tag("Stainless steel")
# Get map against tag
result = cache.get_search_service().get_by_tag(tag)
if result is not None and len(result) > 0:
for item in result:
# Perform operations
print(item)
else:
# Dictionary does not exist against tag
print("Dictionary not found")
except Exception as exp:
# Handle errors
NCache uses the $DataType$ keyword that will fetch all the data structures with the specified Named Tag. The following code assumes a list has been added with Named Tag Discount with a value 0.4 and fetches it using queries.
try
{
// Pre-conditions: Cache is already connected
// Data Structures exist with NamedTag "Discount" and value 0.4
// Create query
string query = "SELECT * FROM $DataType$ WHERE Discount = 0.4";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
// Execute Query to search cache
ICacheReader queryResult = cache.SearchService.ExecuteReader(queryCommand);
// queryResult contains all the keys and metadata of result
if (queryResult.FieldCount > 0)
{
while (queryResult.Read())
{
// Key of list can be fetched through
queryResult.GetValue<DistributedList>(1);
}
}
else
{
// Null query result set retrieved
}
}
catch (OperationFailedException ex)
{
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// NCache specific exception
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
}
catch (Exception ex)
{
// Any other generic exception like ArgumentNullException or ArgumentException
}
try {
// Pre-conditions: Cache is already connected
// Data Structures exist with NamedTag "Discount" and value 0.4
// Create query
String query = "SELECT * FROM $DataType$ WHERE Discount = 0.4";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
// Execute Query to search cache
CacheReader queryResult = cache.getSearchService().executeReader(queryCommand);
// queryResult contains all the keys and metadata of result
if (queryResult.getFieldCount() > 0) {
while (queryResult.read()) {
// Key of list can be fetched through
queryResult.getValue(0, DistributedList.class);
// Perform operations
}
} else {
// Null query result set retrieved
}
} catch (OperationFailedException ex) {
if (ex.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT) {
// Make sure that the query format is correct
} else {
// NCache specific exception
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
} catch (Exception ex) {
// Any generic exception like IllegalArgumentException or NullPointerException
}
try {
// Pre-conditions: Cache is already connected
// Data Structures exist with NamedTag "Discount" and value 0.4
// Create query
val query = "SELECT * FROM $DataType$ WHERE Discount = 0.4"
val queryCommand = QueryCommand(query)
// Execute Query to search cache
val queryResult = cache.getSearchService.executeReader(queryCommand)
// queryResult contains all the keys and metadata of result
if (queryResult.getFieldCount > 0) while (queryResult.read) {
// Key of list can be fetched through
val key = queryResult.getValue(0, classOf[String])
// Perform operations
}
else {
// No data containing the named tag(s) exist
}
}
catch {
case exception: Exception => {
// Handle any errors
}
}
// This is an async method
try {
// Pre-conditions: Cache is already connected
// Data Structures exist with NamedTag "Discount" and value 0.4
// Create query
var query = "SELECT * FROM $DataType$ WHERE Discount = 0.4";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
// Execute Query to search cache
var searchService = await this.cache.getSearchService();
var queryResult = await searchService.executeReader(queryCommand);
// queryResult contains all the keys and metadata of result
if (queryResult.getFieldCount() > 0) {
while (queryResult.read()) {
// Key of list can be fetched through
queryResult.getValue(0, Number());
// Perform operations
}
} else {
// Null query result set retrieved
}
} catch (error) {
// Handle errors
}
try:
# Pre-conditions: Cache is already connected
# Data Structures exist with NamedTag "Discount" and value 0.4
# Create query
query = "SELECT * FROM $DataType$ WHERE discount = 0.4"
query_command = ncache.QueryCommand(query)
# Execute Query to search cache
search_service = cache.get_search_service()
query_result = search_service.execute_reader(query_command)
# queryResult contains all the keys and metadata of result
if query_result.get_field_count() > 0:
while query_result.read():
# Key of list can be fetched through
query_result.get_value(int, 1)
# Perform operations
else:
# No data containing the named tag(s) exist
print("Data not found")
except Exception as exp:
# Handle errors
Additional Resources
NCache provides a sample application for querying data structures on
GitHub.
See Also
Counter Behavior and Usage in Cache
Configure Searchable Attributes
Configure Invalidation Attributes
Remove Data Structure from Cache