Cache Data Dependency on Oracle Database
Note
This feature is only available in NCache Enterprise Edition.
NCache provides OracleCacheDependency for notification based dependencies with
Oracle. Internally, NCache uses OracleDependency
to register data change
notifications with the Oracle database server. Hence you need to understand the
limitations and working mechanisms of OracleDependency
while using this
dependency. See the following Oracle Documentation for more detail.
Oracle Dependency is provided by NCache for synchronizing cache with Oracle
database. Item expires if result of the command (based on command text) changes.
Oracle Dependency is available for Oracle database 11g or later.
Also make sure that Oracle Data Providers for .NET (version 10.1.0.2.0 or later)
is installed.
The database change notifications are object based. This means
that change notifications will be fired if any row is modified in an object.
Therefore, it is recommended to check rowID to confirm if the altered row is the
one for which the event was registered. RowIDs cannot be retrieved unless
explicitly included in query. So the user has to specifically include rowID in
the query that is being registered with OracleDependency
, otherwise the change
notification will be fired if any row is modified in table.
When rowID is included in a query such as – Select rowID, productID,
productname, unitprice from Products where ProductID = 220
- NCache will save
the rowIDs of rows for which the change notification is registered. When it
receives any change notification, NCache will compare the rowIDs to determine
whether the row changed is the one for which the rowID is registered. Otherwise
NCache will have no way to check against this and items for which change
notification is registered may be removed if any row in the table changes.
Prerequisites
- Set up database environment before using Oracle Dependency.
- Install the following NuGet package in your application:
- Include the following namespace in your application:
Alachisoft.NCache.Client
Alachisoft.NCache.Runtime.Dependencies
Alachisoft.NCache.Runtime.Exceptions
- Cache must be running.
- Make sure that the data being added is serializable.
- For API details, refer to: ICache, CacheItem, Dependency, OracleCacheDependency.
- 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>x.x.x</version>
</dependency>
- Import the following packages in your application:
import com.alachisoft.ncache.client.*;
import com.alachisoft.ncache.runtime.exceptions.*;
- Cache must be running.
- The application must be connected to cache before performing the operation.
- Make sure that the data being added is serializable.
- For API details, refer to:Cache,CacheItem, setDependency, OracleCacheDependency.
- 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.
- Install and include the following module in your application:
- Enterprise:
const ncache = require('ncache-client')
- Cache must be running.
- The application must be connected to cache before performing the operation.
- For API details, refer to: Cache, CacheItem, setDependency, OracleCacheDependency.
- 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.
- Install the NCache Python client by executing the following command:
# Enterprise Client
pip install ncache-client
- Import the NCache module in your application.
- Cache must be running.
- 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-scala-client</artifactId>
<version>x.x.x</version>
</dependency>
- Import the following packages in your application:
import com.alachisoft.ncache.scala.client.*;
import com.alachisoft.ncache.scala.runtime.dependencies.*;
- Cache must be running.
- The application must be connected to cache before performing the operation.
- 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.
Adding Data with Oracle Dependency
Altering
data after addition with Oracle Dependency will remove it from the cache. OracleCacheDependency
is used to specify the dependency criteria and then item is added in the cache using the Add/Insert method.
The following example adds data with OracleCacheDependency
using the Insert method. The Insert
method adds a new item with dependency and if the item already exists in the cache it overwrites its properties.
try
{
// Creating a connection string to establish connection with the database
// Connection String is in <AppSettings>
string connectionString = ConfigurationManager.AppSettings["connectionstring"];
// Create the query which selects the data on which key is dependent
string query = "SELECT ROWID, ProductID, ProductName, UnitPrice FROM Products WHERE ProductID > :productID";
var param = new OracleCmdParams();
param.Type = (OracleCmdParamsType.Int16);
param.Value = 1020;
param.Direction = OracleParameterDirection.Input;
// Adding the populated parameter to a dictionary
Dictionary<string, OracleCmdParams> oracleParam = new Dictionary<string, OracleCmdParams>();
oracleParam.Add("productID", param);
// Create Oracle Dependency
var oracleDependency = new OracleCacheDependency(connString, query, OracleCommandType.Text, oracleParam);
// Get Product from database against given product ID
Product product = FetchProductFromDB(param.Value);
// Generate a unique cache key for this product
string key = $"Product:{product.ProductID}";
// Create a CacheItem and add Oracle dependency to it
var cacheItem = new CacheItem(product);
cacheItem.Dependency = oracleDependency;
//Add cache item in the cache with Oracle Dependency
cache.Insert(key, cacheItem);
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
}
catch (OperationFailedException ex)
{
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
catch (Exception ex)
{
// Any generic exception like ArgumentNullException or ArgumentException
// Exception may occur due to incorrect query format or invalid connection string
}
try
{
// Creating a connection string to establish connection with the database
String connString = "";
Connection connection = DriverManager.getConnection(connString);
// Create query which selects data on which cache data is dependent
String query = "SELECT rowID, productName FROM Products WHERE productID = ?";
QueryCommand queryCommand = new QueryCommand(query);
queryCommand.getParameters().put("ProductID", productId);
// Get product from database against productId
Product product = fetchProductFromDb(productID);
// Create unique cache key for this product
String key = "Product:" + product.productID;
// Create oracle dependency
OracleCacheDependency oracleCacheDependency = new OracleCacheDependency(String.valueof(connection), query);
// Create new cache item and add oracle dependency to it
CacheItem cacheItem = new CacheItem(product);
cacheItem.setDependency(oracleCacheDependency);
// Add cache item in the cache item with oracle dependency
cache.insert(key, cacheItem);
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
}
catch (OperationFailedException ex)
{
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
catch (Exception ex)
{
// Any generic exception like IllegalArgumentException or NullPointerException
}
try
{
// Creating a connection string to establish connection with the database
var connection = new ActiveXObject("adodb.connection");
let connString = "...";
connection.Open(connString);
var result = new ActiveXObject("ADODB.Recordset");
let query = "Select RowID, ProductId, ProductName, UnitPrice FROM dbo.Products WHERE ProductId = ?";
let queryCommand = new QueryCommand(query);
queryCommand.getParameters().put("CustomerID", this.customerId);
result.Open(query, connection);
// Get product from database against productId
let product = this.fetchProductFromDb(this.productId);
// Create unique cache key for this product
let key = "Product:" + product.ProductID;
// Create oracle dependency
var oracleCacheDependency = new ncache.OracleCacheDependency(connString, query);
// Create new cache item and add oracle dependency to it
let cacheItem = new ncache.CacheItem(product);
cacheItem.setDependency(oracleCacheDependency);
// Add cache item in the cache item with oracle dependency
this.cache.insert(key, cacheItem);
result.close();
connection.close();
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
}
catch (error)
{
// Handle any errors
}
try:
# Creating a connection string to establish connection with the database
conn_string = "your_connection_string"
query = "Select RowID, ProductId, ProductName, UnitPrice FROM dbo.Products WHERE ProductId = ?"
query_command = ncache.QueryCommand(query)
query_command.set_parameters({"CustomerID": "ALFKI"})
# Get product from database against productId
product = fetch_product_from_db("1001")
# Create unique cache key for this product
key = "Product:" + product.get_product_id()
# Create oracle dependency
oracle_cache_dependency = ncache.OracleCacheDependency(conn_string, query)
# Create new cache item and add oracle dependency to it
cache_item = ncache.CacheItem(product)
cache_item.set_dependency(oracle_cache_dependency)
# Add cache item in the cache item with oracle dependency
cache.insert(key, cache_item)
# For successful addition of item with Oracle Dependency
# Update the record in the database and check if key is present
except Exception as exp:
# Handle errors
try {
// Creating a connection string to establish connection with the database
val connString = "YourConnString"
val productID = "1001"
// Create query which selects data on which cache data is dependent
val query = "SELECT rowID, productName FROM Products WHERE productID = ?"
val queryCommand = new QueryCommand(query)
queryCommand.setParameters(Map("ProductID" -> productID))
// Get product from database against productId
val product = fetchProductFromDb(productID)
// Create unique cache key for this product
val key = "Product:" + product.getProductId
// Create oracle dependency
val oracleCacheDependency = OracleCacheDependency(connString, query)
// Create new cache item and add oracle dependency to it
val cacheItem = new CacheItem(product)
cacheItem.setDependency(oracleCacheDependency)
// Add cache item in the cache item with oracle dependency
cache.insert(key, cacheItem)
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
}
catch {
case exception: Exception => {
// Handle any 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.
Add Data with Oracle Dependency using Stored Procedure
NCache provides you with an ability to provide Oracle Dependency on an item using stored procedure. You can also specify the parameters to be passed along with the stored procedure using the OracleCacheDependency
method.
The following example adds item to the cache with Oracle Dependency through stored procedure using the Insert method. The Insert
method adds a new item with dependency and if the item already exists in the cache it overwrites its properties.
try
{
// Create a connection string to establish connection with the database
// Connection String is in <AppSettings> in App.config
string connectionString = ConfigurationManager.AppSettings["connectionstring"];
// The name of the stored procedure the item is dependent on
string storedProcName = "GetProductByID";
OracleCmdParams param = new OracleCmdParams();
param.Type = (OracleCmdParamsType.Int16);
param.Value = 1020;
param.Direction = OracleParameterDirection.Input;
var oracleCmdParams = new Dictionary<string, OracleCmdParams>();
oracleCmdParams.Add("productID", param);
// Create Oracle Dependency
var oracleDependency = new OracleCacheDependency(connString, storedProcName, OracleCommandType.StoredProcedure, oracleCmdParams);
// Get product from database against given product ID
Product product = FetchProductFromDB(param.Value);
// Generate a unique cache key for this product
string key = $"Product:{product.ProductID}";
// Create a new cacheitem and add Oracle dependency to it
var cacheItem = new CacheItem(product);
cacheItem.Dependency = oracleDependency;
// Add cacheitem in the cache with Oracle Dependency
cache.Insert(key, cacheItem);
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
}
catch (OperationFailedException ex)
{
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
catch (Exception ex)
{
// Any generic exception like ArgumentNullException or ArgumentException
// Exception may occur due to incorrect query format or invalid connection string
}
try
{
// Create connection with the database
String connString = "your_connection_string_here";
Connection connection = DriverManager.getConnection(connString);
// Name of the stored procedure the item is dependent on
String storedProcedureName = "GetProductByID";
// Specify the Product ID passed as parameters
Map<String, OracleCmdParams> oracleCmdParamsMap = new HashMap<>();
oracleCmdParamsMap.put("ProductID", productId);
// Create oracle dependency with stored procedure
OracleCacheDependency oracleCacheDependency = new OracleCacheDependency(connString, storedProcedureName,
OracleCommandType.StoredProcedure, oracleCmdParamsMap);
// Get product from database against productId
Product product = fetchProductFromDb(productId);
// Generate a unique cache key for this product
String key = "Product:" + product.productId;
// Create a new cache item and add oracle dependency to it
CacheItem cacheItem = new CacheItem(product);
cacheItem.setDependency(oracleCacheDependency);
// Add cache item in the cache with oracle dependency
cache.insert(key, cacheItem);
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
}
catch (OperationFailedException ex)
{
// 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
{
// Create connection with the database
var connection = new ActiveXObject("adodb.connection");
let connString = "your_connection_string_here";
connection.Open(connString);
// Name of the stored procedure the item is dependent on
let storedProcedureName = "GetProductByID";
// Specify the Product ID passed as parameters
let oracleCmdParamsMap = new Map();
oracleCmdParamsMap.set("ProductID", this.productId);
// Create oracle dependency with stored procedure
var oracleCacheDependency = new ncache.OracleCacheDependency(connString, storedProcedureName,
OracleCommandType.StoredProcedure, oracleCmdParamsMap);
// Get product from database against productId
let product = this.fetchProductFromDb(this.productId);
// Generate a unique cache key for this product
let key = "Product:" + product.ProductID;
// Create a new cache item and add oracle dependency to it
let cacheItem = new ncache.CacheItem(product);
cacheItem.setDependency(oracleCacheDependency);
// Add cache item in the cache with oracle dependency
await this.cache.insert(key, cacheItem);
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
connection.close();
}
catch (error)
{
// Handle any errors
}
try:
# Create a connection string to connect with the database
conn_string = "your_connection_string_here"
# Name of the stored procedure the item is dependent upon
stored_procedure_name = "GetProductByID"
# Specify the Product ID passed as parameters
oracle_cmd_params = {"ProductID": "ALFKI"}
# Create oracle dependency with stored procedure
oracle_cache_dependency = ncache.OracleCacheDependency(conn_string, stored_procedure_name,
ncache.OracleCommandType.STORED_PROCEDURE,
oracle_cmd_params)
# Get product from database against productId
product = fetch_product_from_db("ALFKI")
# Generate a unique cache key for this product
key = "Product:" + product.get_product_id()
# Create a new cache item and add oracle dependency to it
cache_item = ncache.CacheItem(product)
cache_item.set_dependency(oracle_cache_dependency)
# Add cache item in the cache with oracle dependency
cache.insert(key, cache_item)
# For successful addition of item with Oracle Dependency
# Update the record in the database and check if key is present
except Exception as exp:
# Handle errors
try {
// Create connection with the database
val connString = "YourConnectionString"
val productId = "1001"
// Name of the stored procedure the item is dependent on
val storedProcedureName = "GetProductByID"
// Specify the Product ID passed as parameters
val param = OracleCmdParams()
param.setValue(productId)
val oracleCmdParamsMap: Map[String, OracleCmdParams] = Map("ProductID" -> param)
// Create oracle dependency with stored procedure
val oracleCacheDependency = OracleCacheDependency(
connString,
storedProcedureName,
OracleCommandType.StoredProcedure,
oracleCmdParamsMap
)
// Get product from database against productId
val product = fetchProductFromDb(productId)
// Generate a unique cache key for this product
val key = "Product:" + product.getProductId
// Create a new cache item and add oracle dependency to it
val cacheItem = CacheItem(product)
cacheItem.setDependency(oracleCacheDependency)
// Add cache item in the cache with oracle dependency
cache.insert(key, cacheItem)
// For successful addition of item with Oracle Dependency
// Update the record in the database and check if key is present
}
catch {
case exception: Exception => {
// Handle any 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.
Additional Resources
NCache provides sample application for Oracle dependency on GitHub.
See Also
Sync Cache with SQL Server
Cache Data Dependency on OleDB
CLR Procedures in SQL Server with Cache
Locking Data For Concurrency Control
Cache Data Dependency on External Source