SQL Search for Keys Syntax and Usage

Note

This feature is only available in NCache Enterprise Edition.

NCache provides you with the ability to query indexed cache data through its own SQL like indexing mechanism. It lets you search for keys fulfilling the given criteria and then returns the keys of the result set.

ExecuteReader is used for searching the cache as it processes the query on the server side and then sends the result in chunks to the client. As soon as the chunk is exhausted after being enumerated over, the next chunk is sent over to the client.

Retrieving the keys only is a really helpful approach since you will get all the keys as a result of the query. If you want to perform any further operations on the result set, it can be done on the client cache using the keys by retrieving the keys from the Client Cache using GetBulk. This saves the user the time since operations performed on Client Cache are faster and provides the user with a higher flexibility.

For example, a query when executed, fetches the keys from the cache. These keys can now be used to perform operations on your client cache, given that the data also resides in the client cache. This makes the operations faster and rapid.

Pre-Requisites

  • Indexing for searchable objects and their attributes need to be configured first as explained in Configuring Query Indexes in Administrator's Guide.
  • Include the following namespaces in your application:
    • Alachisoft.NCache.Client
    • Alachisoft.NCache.Runtime.Caching
    • Alachisoft.NCache.Runtime.Exceptions
    • Alachisoft.NCache.Client.Services
  • The application must be connected to cache before performing the operation.
  • Cache must be running.
  • Make sure that the data being added is serializable.
  • To ensure the operation is fail safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

Modern Syntax

From NCache 5.0 onward, the query syntax has been changed which provides you extended searching options that includes projecting * as well as projecting specific columns. Here is a query sample which returns * on the basis of ProductID.

try
{
    // Pre-condition: Cache is already connected
    // Items are already present in the cache

    // Create a query which will be executed on the data set
    // Use the Fully Qualified Name (FQN) of your own custom class
    string query = "SELECT * FROM FQN.Product WHERE ProductID > ?";

    // Use QueryCommand for query execution
    var queryCommand = new QueryCommand(query);

    // Providing parameters for query
    queryCommand.Parameters.Add("ProductID",50000);

    // 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 result = reader.GetValue<string>(1);
            // Perform operations using the retrieved keys
        }
    }
    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 performed during state transfer
        // Operation Timeout
    }
}
catch (Exception ex)
{
    // Any generic exception like ArgumentException, ArgumentNullException
}

Legacy Syntax

Note

Legacy API is only available in NCache Enterprise Edition.

The following example searches the cache for the items and returns all the keys which fulfill this criteria using ExecuteReader. For Java, the method used for searching the keys in the cache is search.

try
{
    // Pre-condition: Cache is already connected
    // Items are already present in the cache.

    // Create a query which will be executed on the data set
    // Use the Fully Qualified Name (FQN) of your own custom class
    string query = "SELECT FQN.Product WHERE this.ProductID > ?";

    // Use QueryCommand for query execution
    var queryCommand = new QueryCommand(query);

    // Providing parameters for query
    queryCommand.Parameters.Add("ProductID",50000);

    // Executing QueryCommand through ICacheReader
    ICacheReader reader = cache.ExecuteReader(queryCommand);

    // Check if the result set is not empty
    if (reader.FieldCount > 0)
    {
        while (reader.Read())
        {
            Product result = reader.GetValue<"ProductID">(1);

            // Perform operations using the retrieved keys
        }
    }
    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
}

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

Additional Resources

NCache provides sample application for SQL Searching at:

  • GitHub
  • Shipped with NCache: %NCHOME%\samples\dotnet\SearchUsingSQL

See Also

Locking Data For Concurrency Control
SQL Search for Keys Syntax and Usage
SQL IN Operator Syntax and Usage
SQL Like Operator Syntax and Usage
SQL GROUP BY Syntax and Usage
Query Operators
Search Cache with LINQ