• Webinars
  • Docs
  • Download
  • Blogs
  • Contact Us
Try Free
Show / Hide Table of Contents

Query Data in Cache Using SQL

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 querying mechanism. It lets you search for keys fulfilling the given criteria and then returns the keys of the result set. You can use ExecuteReader() or ExecuteScalar() to retrieve this data.

Note

No matter which projections you use in your query, the result set will only return keys when getdata is set to false.

Prerequisites

  • .NET/.NET Core
  • Java
  • Scala
  • Node.js
  • Python
  • 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.
  • 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: Cache, CacheReader, executeReader, getSearchService, QueryCommand, executeNonQuery.
  • 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.
  • Indexing for searchable objects and their attributes need to be configured first as explained in Configuring Query Indexes in Administrator's Guide.
  • For API details, refer to: Cache, CacheReader, SearchService, QueryCommand.
  • 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.
  • Indexing for searchable objects and their attributes need to be configured first as explained in Configuring Query Indexes in Administrator's Guide.
  • For API details, refer to: Cache, CacheReader, executeReader, getSearchService, QueryCommand, executeNonQuery.
  • 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.
  • Indexing for searchable objects and their attributes need to be configured first as explained in Configuring Query Indexes in Administrator's Guide.
  • For API details, refer to: Cache, CacheReader, execute_reader, get_search_service, QueryCommand, execute_non_query.

Using ExecuteReader To Query Data in Cache

ExecuteReader performs the query on the cache based on the query specified. It returns a list of key-value pairs in a data reader which fulfills the query criteria. This key-value pair has a cache key and its respective values.

SQL Query For Keys

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.

  • .NET/.NET Core
  • Java
  • Scala
  • Node.js
  • Python
try
{
    // Pre-condition: Cache is already connected
    // Items are already present in the cache
    // Create a query which will be executed on the dataset
    // 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
}
try
{
    // Pre-condition: Cache is already connected
    // Items are already present in the cache
    // Create a query which will be executed on the dataset
    // 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, false, 0);

    //Check if result set is not empty
    if (reader.getFieldCount() > 0)
    {
        while (reader.read())
        {
            String result = reader.getValue(0, 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
}
try {
    // Pre-condition: Cache is already connected

    // Items are already present in the cache
    // Create a query which will be executed on the dataset
    // Use the Fully Qualified Name (FQN) of your own custom class
    val query = "SELECT * FROM FQN.Product WHERE ProductID > ?"

    // Use QueryCommand for query execution
    val queryCommand = new QueryCommand(query)

    //Providing parameters for query
    queryCommand.setParameters(Map("ProductID" -> 50000))

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

    //Check if result set is not empty
    if (reader.getFieldCount > 0) while (reader.read) {
      val result = reader.getValue(1, classOf[String])
      // Perform operations using the retrieved keys
    }
    else {
      // Null query results retrieved
    }
}
catch {
    case exception: Exception => {
      // Handle any errors
    }
}
// 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 dataset
    // 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, false, 0);

    //Check if result set is not empty
    if (reader.getFieldCount() > 0)
    {
        while (reader.read())
        {
            var result = reader.getValue(0, Number());
            // Perform operations using the retrieved keys
        }
    }
    else
    {
        // Null query results retrieved
    }
}
catch (error)
{
    // Handle errors
}
try:
    # Pre-condition: Cache is already connected
    # Items are already present in the cache

    # Create a query which will be executed on the dataset
    query = "SELECT * FROM FQN.Product WHERE product_id > ?"

    # Use QueryCommand for query execution
    query_command = ncache.QueryCommand(query)

    # Providing parameters for query
    parameter = {"product_id": 50000}
    query_command.set_parameters(parameter)

    # Executing query command through CacheReader
    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():
            result = reader.get_value(str, 1)
            # Perform operations using the retrieved keys
            print(result)
    else:
        # None query results retrieved
        print("Query result is None")
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.

Using the SQL IN Operator

To query the cache using SQL-like query format with the IN operator, you can specify multiple attribute values after the IN keyword. Here's an example that retrieves ProductName and UnitsInStock from the cache having either 10, 20, or 30 UnitsInStock using ExecuteReader.

  • .NET/.NET Core
  • Java
  • Scala
  • Node.js
  • Python
string query = "SELECT ProductName, UnitsInStock From FQN.Product WHERE UnitsInStock IN (?, ?, ?)";

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

ArrayList unitsArray = new ArrayList();
unitsArray.Add(10);
unitsArray.Add(20);
unitsArray.Add(30);

queryCommand.Parameters.Add("UnitsInStock", unitsArray);

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

// Read results if result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        string productName = reader.GetValue<string>("ProductName");
        int unitsInStock = reader.GetValue<int>("UnitsInStock");

        Console.WriteLine($"Product '{productName}' has {unitsInStock} Units In Stock.");
    }
}
String query = "SELECT productName, UnitsInStock From FQN.Product WHERE UnitsInStock IN (?,?,?)";

// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
ArrayList parameters = new ArrayList();
parameters.add(10);
parameters.add(15);
parameters.add(20);
parameters.add(25);
queryCommand.getParameters().put("UnitsInStock", parameters);

// Executing QueryCommand through CacheReader
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
        String ProductName = reader.getValue(1, Product.class);
        int UnitsAvailable = reader.getValue(2, Integer.class);
    }
}
else
{
    // Null query result set retrieved
}
val query = "SELECT productName, UnitsInStock From FQN.Product WHERE UnitsInStock IN (10,20,30)"

// Use QueryCommand for query execution
val queryCommand = new QueryCommand(query)

// Executing QueryCommand through CacheReader
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 ProductName = reader.getValue(1, classOf[String])
    val UnitsAvailable = reader.getValue(2, classOf[Int])
}
else {
    // Null query result set retrieved
}
var query = "SELECT productName, UnitsInStock From FQN.Product WHERE UnitsInStock IN (?,?,?)";

// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
//Adding Parameters
let units = [10, 15, 20, 25];
let parameter = new Map();
parameter.set("UnitsInStock", units);
queryCommand.setParameters(parameter);

// Executing QueryCommand 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())
    {
        // Get the value of the result set
        var ProductName = reader.getValue(1, Number());
        var UnitsAvailable = reader.getValue(2, Number());
    }
}
    else
    {
    // Null query result set retrieved
}
query = "SELECT product_name, units_in_stock From FQN.Product WHERE units_in_stock IN (10,20,30)"

# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)

# Executing query command through CacheReader
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():
        product_name = reader.get_value(str, 1)
        units_available = reader.get_value(int, 2)
        # Perform operations
        print("Name: " + str(product_name))
        print("Units: " + str(units_available))
else:
    # None query results retrieved
    print("Query result is None")

Using the SQL LIKE Operator

NCache allows you to query for a specific pattern in a column through SQL-like query format using the LIKE operator.

The two wildcards used with the LIKE operator are:

  1. *: Used as a substitute for zero or more characters in the string.

  2. ?: Used as a substitute for a single character in the string.

Here's an example that searches the cache and retrieves ProductName and UnitPrice using ExecuteReader.

  • .NET/.NET Core
  • Java
  • Scala
  • Node.js
  • Python
string query = "SELECT ProductName, UnitPrice From FQN.Product WHERE ProductName LIKE ?";


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

queryCommand.Parameters.Add("ProductName", "choco*");

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

// Read results if result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        string productName = reader.GetValue<string>("ProductName");
        double unitPrice = reader.GetValue<double>("UnitPrice");

        Console.WriteLine($"Product: {productName}, Unit Price: {unitPrice}");
    }
}
else
{
    Console.WriteLine($"No product found having a name that starts with 'choco'.");
}
try
String query = "SELECT * FROM FQN.Product WHERE ProductName LIKE ? AND Category LIKE ?";

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

// Providing parameters for query
queryCommand.getParameters().put("ProductName", "P*");
queryCommand.getParameters().put("Category", " Beverage?");

// Executing QueryCommand through CacheReader
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
        String result = reader.getValue("productID", String.class);

        // Perform operations
    }
}
else
{
    // Null query result set retrieved
}
val query = "SELECT * FROM FQN.Product WHERE ProductName LIKE ? AND Category LIKE ?"

// Use QueryCommand for query execution
val queryCommand = new QueryCommand(query)

// Providing parameters for query
queryCommand.setParameters(Map("ProductName" -> "Shamp*", "Category" -> "Househo*"))

// Executing QueryCommand through CacheReader
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("productID", classOf[String])
    // Perform operations
}
else {
    // Null query result set retrieved
}
var query = "SELECT * FROM FQN.Product WHERE ProductName LIKE ? AND Category LIKE ?";

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

// Providing parameters for query
let parameter1 = new Map();
parameter1.set("ProductName", "P*");
queryCommand.setParameters(parameter1);

let parameter2 = new Map();
parameter2.set("Category", " Beverage?");
queryCommand.setParameters(parameter2);

// Executing QueryCommand 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
    }
}
else
{
    // Null query result set retrieved
}
query = "SELECT * FROM FQN.Product WHERE product_name LIKE ? AND category LIKE ?"

# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)

# Providing parameters for query
parameter1 = {"product_name": "Shamp*"}
parameter2 = {"category": "Househo*"}
query_command.set_parameters(parameter1)
query_command.set_parameters(parameter2)

# Executing query command through CacheReader
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():
        result = reader.get_value(str, columnname="product_name")
        # Perform operations
        print(result)
else:
    # None query results retrieved
    print("Query result is None")

Using the SQL GROUP BY Operator

NCache provides you with the ability to group the data according to the given criteria through SQL-like query format using the GROUP BY clause.

Important

Note that the GROUP BY clause cannot be used without an Aggregate function.

The following example retrieves all the products based on their UnitPrice and groups them according to their Category and Count.

  • .NET/.NET Core
  • .Java
  • Scala
  • Node.js
  • Python
string query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice < ? Group By Category";

// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("UnitPrice", 100.0);

// Executing QueryCommand through ICacheReader
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
        string category = reader.GetValue<string>("Category");
        int count = reader.GetValue<int>("COUNT()");

        Console.WriteLine($"Category '{category}' has '{count}' affordable products.");
    }
}
else
{
    Console.WriteLine($"No category contains affordable products.");
}
String query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice > ? Group By Category";

// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Providing parameters for query
queryCommand.getParameters().put("UnitPrice ", 10);

// Executing QueryCommand through CacheReader
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
            int result = reader.getValue(1, Integer.class);
            String category = reader.getValue(0, String.class);
        }
    }
    else
    {
        // Null query result set retrieved
    }
val query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice > 5 Group By Category";

// Use QueryCommand for query execution
val queryCommand = QueryCommand(query);

// Executing QueryCommand through CacheReader
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[Int]);
    val category = reader.getValue(2, classOf[String])
    }
}
var query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice > ? Group By Category";

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

//Adding Parameters
let parameter = new Map();
parameter.set("UnitPrice", 10);
queryCommand.setParameters(parameter);

// Executing QueryCommand 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())
    {
        // Get the value of the result set
        var result =  reader.getValue(1, ncache.JsonDataType.Object);
        var category =  reader.getValue(0, Number());
    }
}
else
{
    // Null query result set retrieved
}
query = "SELECT category, COUNT(*) FROM FQN.Product WHERE unit_price > 5 Group By category"

# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)

# Executing query command through CacheReader
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():
        result = reader.get_value(int, 1)
        category = reader.get_value(str, 0)
        # Perform operations
        print("Count: " + str(result))
        print("Category: " + str(category))
else:
    # None query results retrieved
    print("Query result is None")

Using the SQL ORDER BY Operator

NCache provides you with the ability to sort the data in ascending or descending order, according to the given criteria, through SQL-like query format using the ORDER BY clause. The following example retrieves ProductName and UnitPrice, where ProductName is greater than 100, and sorts them in descending order using ExecuteReader.

  • .NET/.NET Core
  • Java
  • Scala
  • Node.js
  • Python
string query = "SELECT ProductName,UnitPrice FROM FQN.Product WHERE UnitPrice < ? ORDER BY UnitPrice DESC";

// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
//changes
queryCommand.Parameters.Add("UnitPrice", 100.0);

// Executing QueryCommand through ICacheReader
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
        string productName = reader.GetValue<string>("ProductName");
        string unitPrice = reader.GetValue<string>("UnitPrice");

        Console.WriteLine($"Product: {productName}, Unit Price: {unitPrice}");
    }
}
else
{
    Console.WriteLine($"No products found which are cheaper than 100.");
}
String query = "SELECT * FROM FQN.Product WHERE UnitPrice > ? ORDER BY UnitsInStock DESC";

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

// Providing parameters for query
queryCommand.getParameters().put("UnitPrice ", 100);

// Executing QueryCommand through CacheReader
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
        int result = reader.getValue("UnitsInStock", Integer.class);
    }
}
else
{
    // Null query result set retrieved
}
val query = "SELECT * FROM FQN.Product WHERE UnitPrice > 100 ORDER BY UnitsInStock DESC"

// Use QueryCommand for query execution
val queryCommand = new QueryCommand(query)

// Executing QueryCommand through CacheReader
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("UnitsInStock", classOf[Int])
}
else {
    // Null query result set retrieved
}
var query = "SELECT * FROM FQN.Product WHERE UnitPrice > ? ORDER BY UnitsInStock DESC";

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

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

// Executing QueryCommand 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())
    {
        // Get the value of the result set
        var result = reader.getValue(1, Number());
    }
}
else
{
    // Null query result set retrieved
}
query = "SELECT * FROM FQN.Product WHERE unit_price > 100 ORDER BY units_in_stock DESC"

# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)

# Executing query command through CacheReader
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():
        result = reader.get_value(int, columnname="units_in_stock")
        # Perform operations
        print("Units: " + str(result))
else:
    # None query results retrieved
    print("Query result is None")

Using the SQL DateTime Operator

NCache provides you with ease of searching the cache by providing parameters in various formats. You can use many functions in a single query for faster cache searching. DateTime can be used in your query to search the cache concerning a particular date or time. For more detail on date formats, please refer to the Microsoft DateTime Struct Docs.

Note

Make sure that you specify the date in the correct format.

The following example executes a query that searches for all the orders in the cache whose OrderDate is as specified using ExecuteReader.

  • .NET/.NET Core
  • Java
  • Scala
  • Node.js
  • Python
string query = "SELECT OrderID FROM FQN.Order WHERE OrderDate = ?";

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

// Define order date
DateTime orderDate = new DateTime(2022, 01, 01);

// Providing parameters to query
queryCommand.Parameters.Add("OrderDate", orderDate);

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

// Read results if result set is not empty
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        // Get the value of the result set
        int orderID = reader.GetValue<int>("OrderID");
        Console.WriteLine($"Order: {orderID} was placed on 1st January, 2022.");
    }
}
else
{
    Console.WriteLine($"No orders were placed on 1st January 2022.");
}
String query = "SELECT * FROM FQN.Order WHERE OrderDate < ?";

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

// Providing parameters to query
queryCommand.getParameters().put("OrderDate", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));

// 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())
    {
        // Get the value of the result set
        int result = reader.getValue("OrderID", Integer.class);

        // Perform operations
    }
}
else
{
    // Null query result set retrieved
}
val query = "SELECT * FROM FQN.Order WHERE OrderDate < ?"

// Use QueryCommand for query execution
val queryCommand = new QueryCommand(query)

// Providing parameters to query
queryCommand.setParameters(Map("OrderDate" -> LocalDateTime.now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))))

// 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
}) { // Get the value of the result set
    val result = reader.getValue("OrderID", classOf[Integer])
    // Perform operations
}
else {
    // Null query result set retrieved
}
// Use the Fully Qualified Name (FQN) of your own custom class
var query = "SELECT * FROM FQN.Order WHERE OrderDate < ?";

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

// Providing parameters to query
var date = new Date("2021-02-10");
let map = new Map();
var parameters = map.set("orderDate", date);
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())
    {
        // Get the value of the result set
        var result = reader.getValue(1, Number());

        // Perform operations
    }
}
else
{
    // Null query result set retrieved
}
query = "SELECT * FROM FQN.Order WHERE order_date < ?"

# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)

# Providing parameters for query
parameter = {"order_date": datetime.strptime('Jan 1 2022  1:33PM', '%b %d %Y %I:%M%p')}
query_command.set_parameters(parameter)

# Executing query command through CacheReader
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():
        order_id = reader.get_value(int, 1)
        # Perform operations
        print("Order ID: " + str(order_id))
else:
    # None query results retrieved
    print("Query result is None")

Using the SQL Logical Operator

NCache provides logical operators AND, OR, and NOT, to specify more than one condition after the operator in your query through SQL-like query format. Here's an example showing how to use the combination of AND and OR operators in a single query within the WHERE clause using ExecuteReader.

  • .NET/.NET Core
  • Java
  • Node.js
  • Python
  • Scala
string query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)";

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

//Adding Parameters
ArrayList categoryList = new ArrayList();
categoryList.Add("Beverages");
categoryList.Add("Produce");
queryCommand.Parameters.Add("Category", categoryList);
queryCommand.Parameters.Add("UnitsInStock", 100);
queryCommand.Parameters.Add("UnitPrice", 100);

// 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())
    {
        // Get the value of the result set
        string result = reader.GetValue<string>("ProductID");
    }
}
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
}
String query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)";

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

//Adding Parameters
ArrayList categoryList = new ArrayList();
categoryList.add("Beverages");
categoryList.add("Produce");
queryCommand.getParameters().put("Category", categories);
queryCommand.getParameters().put("UnitsInStock", 100);
queryCommand.getParameters().put("UnitPrice", 100);

// 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())
    {
        // Get the value of the result set
        String result = reader.getValue("productID", String.class);
    }
}
else
{
// Null query result set retrieved
}
var query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)";

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

//Add parameters
let categoryList = Array();
categoryList.push("Produce");
categoryList.push("Beverages");
let parameter = new Map();
parameter.set("Category", categories);
parameter.set("UnitsInStock", 100);
parameter.set("UnitPrice", 100);
queryCommand.setParameters(parameter);

// 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())
    {
        // Get the value of the result set
        var result = reader.getValue(1, Number());
    }
}
else
{
    // Null query result set retrieved
}
query = "SELECT * FROM FQN.Product WHERE (category = 'Beverages' AND units_in_stock > 0) OR (category = 'Produce' AND unit_price > 100)"

# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)

# Executing query command through CacheReader
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():
        product_id = reader.get_value(str, 1)
        # Perform operations
        print("Product ID: " + str(product_id))
else:
    # None query results retrieved
    print("Query result is None")
// Pre-condition: Cache is already connected
// Provide Fully Qualified Name (FQN) of your custom class
val query = "SELECT * FROM FQN.Product WHERE (Category = 'Beverages' AND UnitsInStock > 0) OR (Category = 'Produce' AND UnitPrice > 100)"

// Use QueryCommand for query execution
val queryCommand = new QueryCommand(query)

// Executing QueryCommand through ICacheReader
val reader = cache.getSearchService.executeReader(queryCommand)

// Check if the result set is not empty
if (reader.getFieldCount > 0) while (reader.read) {
    // Get the value of the result set
    val result = reader.getValue("ProductID", classOf[String])
}
else {
    // Null query result set retrieved
}

Using ExecuteScalar to Search Data in Cache

ExecuteScalar executes the query, and returns the first column of the first row in the result set returned by the query, any additional columns or rows are ignored.

  • .NET/.NET Core
string query = "SELECT AVG(UnitPrice) FROM FQN.Product WHERE Category = ?";

// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("Category", "Beverages");

// Executing QueryCommand through ICacheReader
decimal average = (decimal)cache.SearchService.ExecuteScalar(queryCommand);

Console.WriteLine($"Average price of Beverages is {average}.");

Additional Resources

NCache provides sample application for SQL Queries on GitHub.

See Also

Query Cache with LINQ
SQL Refrence

Back to top Copyright © 2017 Alachisoft