SQL Search for Keys: Syntax and Usage
Note
This feature is only available in NCache Enterprise.
NCache provides you with the ability to query indexed cache data through its own SQL-like querying mechanism. It lets you search for keys fulfilling the given criteria and then returns the keys of the result set. ExecuteReader()
enables you to retrieve keys only by setting getdata
flag to false.
Note
No matter which projections you use in your query, the result set will only return keys when getdata
is set to false.
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.
- Searchable objects and their attributes must be indexed by either configuring indexes or defining indexes programmatically.
- For API details, refer to: ICache, ICacheReader, ExecuteReader, QueryCommand, SearchService, ExecuteNonQuery.
Syntax
Here is a query sample that returns the keys fulfilling the criteria based on ProductID
.
Important
Make sure the instance of ICacheReader
is always closed after execution, as it is necessary to clean the resources.
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, false, 0);
// Check if the result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
string result = reader.GetValue<string>(0);
// 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
}
Note
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 on GitHub.
See Also
.NET: Alachisoft.NCache.Client.Services namespace.
Java: com.alachisoft.ncache.runtime.caching namespace.
Node.js: Cache class.
Python: ncache.client.services class.