Enabling Notifications For Oracle

NOTE: This feature is not available in NCache Express and Professional edition.

For enabling notifications for Oracle tables, follow these steps:

Create a table 'ncache_db_sync' having four fields (cache_key VARCHAR2, cache_id VARCHAR2, modified NUMBER and work_in_progress NUMBER). Script to create the table is as follows:

//Create table'ncache_db_sync'

CREATE TABLE NCACHE_DB_SYNC (

CACHE_KEY VARCHAR2(256) NOT NULL ENABLE,

CACHE_ID VARCHAR2(256) NOT NULL ENABLE,

MODIFIED NUMBER(2,1) DEFAULT 0 NOT NULL ENABLE,

WORK_IN_PROGRESS NUMBER(2,1) DEFAULT 0 NOT NULL ENABLE,

PRIMARY KEY (CACHE_KEY, CACHE_ID) ENABLE

);

Create UPDATE and DELETE triggers, for every table on which notification is required. They set the 'modified' field of corresponding row in the ncache_db_sync table to 1.

To carry out the task, see the following sample script that creates trigger on 'Products' table:

//create trigger

create or replace trigger MYTRIGGER

AFTER

update or delete on PRODUCTS

REFERENCING OLD AS oldRow

for each row

BEGIN

UPDATE NCACHE_DB_SYNC

SET modified = 1

WHERE cache_key = (:oldRow.ProductID || ':dbo.Products' );

END TRIGGER;

Note: cache_key must be the same key that is used to add the corresponding record in the cache.

Here is an example for Oracle Dependency

Cache mycache = NCache.InitializeCache("myreplicatedcache");

mycache.ExceptionsEnabled = true;

try

{

string connectionString ="Provider=OraOLEDB.Oracle;

Data Source=(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = qctest6)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = Northwind))); User Id=Scott; Password=;";

ProductsFactory productsFactory = new ProductsFactory(connectionString);

Collection<Products> products = productsFactory.FindByCategoryID(8);

foreach (Products product in products)

{

String itemKey += ":dbo.Products";

Alachisoft.NCache.Runtime.Dependencies.CacheDependency dep =

DBDependencyFactory.CreateOleDbCacheDependency(connectionString, itemKey);

//cache key for product id 10 will be "10:dbo.Products"

CacheItem cItem=new CacheItem(product);

cItem.Dependency=dep;

mycache.Insert(product.ProductID + ":dbo.Products", cItem.Value, cItem.Dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default);

}

catch (Exception ex)

{

//throw it.

}

On clean interval, NCache DBCacheDependency does the following:

Sets the work_in_progress flag for those rows where modified flag is set. And fetches all those rows.
Removes all keys from the cache as they all have expired now.
After successfully removing all the keys from the cache, all those rows where work_in_progress flag is set are removed from the 'ncache_db_sync' table.

If a node crashes while it was removing items from the cache, the next node in the cluster will start process from step one.

Send comments on this topic.