New post
Question
· Aug 2, 2018

"Listen" to a cache db?

#Databases #Object Data Model #ODBC #Python #Caché

Hello everyone,

Im just wondering if there is any possibility to "Listen" to a cache DB? We have our cache DB somewhere else provided by a different company, we are provided the interface to connect to that cache DB so we can extract the cache DB every night.

Im just curious if theres a way to "listen" to the cache DB, so if theres any changes on the table in the cache DB, I could make a trigger to extract the table again.

I know i could just set my ETL every hour or so... but that would extract all the tables in cache DB.

Thanks a lot for any help and information.

Kind regards,

mark

Discussion (9)1
Log in or sign up to continue
Alexey Maslov · Aug 2, 2018

Hi Mark,

Several years ago we faced the similar problem. External system needed to pull new requests from our Caché DB and to push back the responses. We maintained a transition table in Caché where we placed new requests, external system polled the table each N seconds fetching the requests from the table and placing the responses back. Communication was implemented via ODBC.

You can do something like this filling transition table on remote Caché side using triggers associated with the "main" table.

0 0
Mark Anthony Manalo · Aug 2, 2018

Hi Alexey,

Thanks for your quick reply. Could you maybe elaborate how this solution works? The only problem is, i dont have any rights to do something with the source cache db. I just have the interface to the cache db to connect via ODBC.

What i want to do is write a program in python to listen to cache db and everytime a change happens to a cache table objects, is to fire a trigger to extract this table or update the record in my destination database.

Is there any documentation out there?

Thanks,

Mark

0 0
Alexey Maslov · Aug 2, 2018

Our case differed from yours as we had full access to Caché DB and did all this development on the  Caché side.

If you need to implement triggers, etc, you need full access to Caché DB.

Otherwise, if time_stamp_of_modification field presents in Caché table, the table can be queried with SQL SELECT statement based on the time of previous query on regular basis (e.g. each 5 seconds). Anyway, I have no idea how to achieve your goal without some development on the Caché side.

0 0
Robert Cemper · Aug 2, 2018

All I know about python is  "Monty Python" smiley

OK.

The table you want to control needs to get a parameter   Parameter DSTIME = "AUTO";  and a recompile

Then you can use this class to trace  changes, new, delete

/// Handle DSTIME using SQL
///  select * from OBJ.DSTIME where version = lastversion
/// to show all
Class OBJ.DSTIME Extends %Persistent [ Final, SqlRowIdPrivate ] {
Index idx On (Table, Version, RowID) [ IdKey ];
Property Version As %Integer [ ReadOnly, SqlColumnNumber = 2 ];
Property Table As %String [ ReadOnly, SqlColumnNumber = 3 ];
Property RowID As %String [ ReadOnly, SqlColumnNumber = 4 ];
Property Signal As %Integer(DISPLAYLIST = ",Modified,New,Deleted", VALUELIST = ",0,1,2")
   [Calculated, , SqlComputed, SqlColumnNumber = 5,
     SqlComputeCode = { set {*}=^OBJ.DSTIME({Table},{Version},{RowID})}];
Property LastVersion As %Integer [ Calculated, SqlComputed, SqlColumnNumber = 6,
   SqlComputeCode = { set {*}=+$G(^OBJ.DSTIME) } ];

/// to get actual last version and switch to new version
/// select top 1 LastVersion,OBJ.DSTIME_NewVersion(LastVersion) from OBJ.DSTIME
/// 
ClassMethod NewVersion(anycolumn As %String) As %Integer [ SqlProc ]
{  Quit $I(^OBJ.DSTIME) }

Storage Default {
<DataLocation>^OBJ.DSTIME</DataLocation>
<IdLocation>^OBJ.DSTIMED</IdLocation>
<IndexLocation>^OBJ.DSTIMEI</IndexLocation>
<StreamLocation>^OBJ.DSTIMES</StreamLocation>
<Type>%Library.CacheStorage</Type>
} }
 

 so get your actual changes

select * from OBJ.DSTIME where version = lastversion

and switch to next version by 

select top 1 LastVersion,OBJ.DSTIME_NewVersion(LastVersion) from OBJ.DSTIME

.

But you have to have full access to Caché as you have to make the DB "talking"  to be able to "listen"

0 0