Search Data Structures in Cache with SQL Queries

Note

This feature is only available in NCache Enterprise Edition.

Data types can be queried in cache if they have searchable attributes associated against them during data type creation. Searchable attributes include:

Groups
Tags
Named Tags

Pre-requisites

Install the following NuGet packages:
- Alachisoft.NCache.SDK.
Include the following namespaces in your application:
- Alachisoft.NCache.Client
- Alachisoft.NCache.Client.DataTypes
- Alachisoft.NCache.Client.DataTypes.Collections
- Alachisoft.NCache.Client.Services
- Alachisoft.NCache.Runtime.Caching
- Alachisoft.NCache.Runtime.Exceptions
The application must be connected to cache before performing the operation.
Cache must be running.
For API details, refer to: ICache, ICacheReader, DataTypeAttributes, ExecuteReader, SearchService, QueryCommand.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.

Add the following Maven dependencies in your pom.xml file:

<dependency>
    <groupId>com.alachisoft.ncache</groupId>
    <artifactId>ncache-client</artifactId>
    <version>5.2.0</version>
</dependency>

Import the following packages in your application:
- import com.alachisoft.ncache.client.*;
- import com.alachisoft.ncache.runtime.exceptions.*;
- import com.alachisoft.ncache.client.*;
- import com.alachisoft.ncache.client.datastructures.*;
The application must be connected to cache before performing the operation.
Cache must be running.
For API details, refer to: Cache, CacheReader, DataStructureAttributes executeReader, getSearchService(), QueryCommand.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.

Include the following modules in your application:
- const ncache = require('ncache-client')
The application must be connected to cache before performing the operation.
Cache must be running.
For API details, refer to: Cache, CacheReader, DataStructureAttributes executeReader, getSearchService(), QueryCommand.
To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.
To handle any unseen exceptions, refer to the Troubleshooting section.

Search Data Structures by Group

SQL Query

The following code sample fetches all items from the cache which have been associated with a specified group "Electronics". The group can be associated using DataTypeAttributes class during data type 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 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
    {

    }
}
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
}

// 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
}

Group API

The following code sample fetches all lists from the cache which have been associated with a specified group "Electronics". The group can be associated using DataTypeAttributes class during data type creation.

Important

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
}

// 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
}

Recommendation: To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Search Data Structures by Tags

SQL Query

The following code sample fetches all Dictionary instances from the cache which have been associated with a specified tag "Stainless Steel". The tag can be associated using DataTypeAttributes class during data type creation.

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
}

// 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
}

Tag API

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 data is handled accordingly.

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     
}

// 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
}

Search Data Structures by Named Tags

NCache supports querying on data structures if Named Tags have been specified. This uses the $DataType$ keyword which will fetch all data structures with the specified Named Tag.

The following code assumes a list has been added with NamedTag "Discount" 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";

    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<string>(0);
        }
    }
    else
    {
        // No data containing the named tag(s) exist
    }
}
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";

    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,String.class);
             // Perform operations
        }
    }
    else
    {
        // No data containing the named tag(s) exist
    }
}
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
}

// 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";

    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
    {
        // No data containing the named tag(s) exist
    }
}
catch (error)
{
    // Handle errors
}

Additional Resources

NCache provides sample application for querying on GitHub.

Search Data Structures in Cache with SQL Queries

Note

Pre-requisites

Search Data Structures by Group

SQL Query

Important

Group API

Important

Search Data Structures by Tags

SQL Query

Tag API

Important

Search Data Structures by Named Tags

Additional Resources

See Also