C++ app to get notification if a table in sql anywhere 16/17 is updated

[former_SQLA_member1695065]

Can a DB event be notified to a connected C++ application if an update occurs on a table in the sql anywhere DB(not UltraLite)? Thanks.

[VolkerBarth]

Some questions:

1. Is the app itself connected to the database? If so, what API is used and what OS?
2. Should the app be informed about any updates or those on particular rows (e.g. on those rows that app currently does display in a view)?
3. Is the app running on the same machine as the database engine?

It would help to tell more what you want to achieve...

[former_SQLA_member1695065]

Hello Volker, Thanks so much for your response. 1) Yes, the cpp windows service app is connected to the database via a dll using sqlA's ODBC_API. Any windows server OS(windows 2012/6/9). 2) No, just an update on the table rows. The app just consumes the data from the rows, there is no display in a view. 3) yes the app and the db engine(as a service) is running on the same system.

What I want to achieve is that some of the triggers and stored procs update a particular table based on application input/needs. Some of the other services consuming the cache(yet to be designed) need to be notified if and when the table is updated, so that the cache(containing table's data) can be flushed out and recreated.

one way of achieving the goal is from with in the app/s as and when the funtions are called to update/insert other tables leading to this update.

I also believe that the table's after update trigger can call a function with in the DB which can accomodate an external name with the dll and the associated function(dll name.dll::className.func() with any parameter.

Please comment, I am open to any new ideas or critic the above... please also let me know if any additional clarifications are needed...

thanks so much and best regards.

[VolkerBarth]

So basically you are asking for inter process communication between the database engine and your app.

For a "push" approach your idea with a trigger-based seems reasonable, as only triggers can immediately react upon DML statements like an UPDATE. If updates to the accordings tables are infrequent, you might trigger a push notification for each update, if they are more common, you will probably use some kind of delay to prevent too frequent notifications. Using an database event (CREATE EVENT...) that runs on its own connection and may "wait a while" before doing the push notification may help here without delaying the actual UPDATEs.

• For version 17, I suggest to use a semaphore with a "producer-consumer model", i.e. the according trigger would call NOTIFY SEMAPHORE as "push" notification. The app itself would need a separate database connection that "waits" on the according semaphore. When the semaphore is notified, the app can refresh its data.
  SQL Anywhere 17 offers also mutexes, which might also do the trick.

• For earlier versions, you might use OS IPC facilities with the help of external functions. We have done this with Win32 named events: When there are UPDATES the database engine would set an event with the help of an external C function calling the Win32 SetEvent API, and the app would use a Win32 wait method like WaitForSingleObject() to get notified on the event.

Of course you could also use a "polling" approach by making the app regularly query the database state. Besides using your own "state table", you could also provide the "state" via an (immediately or manually) refreshed materialized view - the database would not necessarily require triggers to "set a state" but would do this autmatically by refreshing a view. And the app could check via SYSVIEW.mv_last_refreshed_at (or mv_last_refreshed_at_utc) whether the contents has changed.