"Listen" to a cache db?

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

Answers

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.

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

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.

in combination with DeepSee there is a class parameter DSTIME to track additions, changes deletes for this purpose.

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2IMP_ch_current

If you have just SQL access you may wrap a class around  ^OBJ.DSTIME to  select changes

Hi Robert!

Thanks a lot for the quick reply. How can i do this with python? Could you maybe point me to any resources that you know?

Thanks,

Mark

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, , SqlComputedSqlColumnNumber = 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"

for cleaning up:

/// to get rid of old versions
/// select OBJ.DSTIME_DelVersion('Table',Version)
/// 
ClassMethod DelVersion(Table As %String = " "Version As %Integer = 0) As %Integer [ SqlProc ]
{
 Quit:'$l(Table) '$$$OK
 Kill ^OBJ.DSTIME(Table,+Version)
 Quit $$$OK
}

 

Hi Mark,

I think I'd go about it using an API like SOAP or REST and get the data securely.

Regards

Ben