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

Install the following NuGet packages:
- Alachisoft.NCache.SDK.
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.
For API details, refer to: ICache, ICacheReader, ExecuteReader, GetBulk, SearchService, QueryCommand.
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.
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>

Indexing for searchable objects and their attributes need to be configured first as explained in Configuring Query Indexes in Administrator's Guide.
Import the following packages in your application:
- import com.alachisoft.ncache.client.*;
- import com.alachisoft.ncache.runtime.exceptions.*;
The application must be connected to cache before performing the operation.
Cache must be running.
For API details, refer to: Cache, CacheReader, executeReader, getBulk, getSearchService(), QueryCommand.
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.
To handle any unseen exceptions, refer to the Troubleshooting section.

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 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, executeReader, getBulk, 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.

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
}

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
    QueryCommand queryCommand = new QueryCommand(query);

    //Providing parameters for query
    queryCommand.getParameters().put("ProductID", 50000);

    //Executing query command through CacheReader
    CacheReader reader = cache.getSearchService().executeReader(queryCommand);

    //Check if result set is not empty
    if (reader.getFieldCount() > 0)
    {
        while (reader.read())
        {
            String result = reader.getValue(1, String.class);
            // Perform operations using the retrieved keys
        }
    }
    else
    {
        // Null query results retrieved
    }
} 
catch (OperationFailedException ex)
{

    if (ex.getErrorCode() == NCacheErrorCodes.INCORRECT_FORMAT)
    {
        // Make sure 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 IllegalArgumentException or NullPointerException
}

// This is an async method
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
    var query = "SELECT * FROM FQN.Product WHERE ProductID > ?";

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

    //Providing parameters for query
    let parameter = new Map();
    parameter.set("ProductID", 50000);
    queryCommand.setParameters(parameter);

    //Executing query command through CacheReader
    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())
        {
            var result = reader.getValue(1, Number());
            // Perform operations using the retrieved keys
        }
    } 
    else
    {
        // Null query results retrieved
    }
}
catch (error)
{
    // Handle errors
}

Additional Resources

NCache provides sample application for SQL Searching on GitHub.

SQL Search for Keys Syntax and Usage

Note

Pre-Requisites

Syntax

Additional Resources

See Also