|
I like to sync and process any change to any table of a SQLAnywhere DB and send the changes as JSON to my messaging system. What are my best options
I feel mobilink might be the better option as it's a more "out of the box' option ? Does anyone have experience with this scenario? Any ideas? BTW, I ignored SQL remote as the delta changes/ the data sent by SQL remote is not easily machine readable. Thanks a lot in advance Frank |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 20 Oct '16, 06:24
question was seen: 1,622 times
last updated: 21 Oct '16, 08:39
Presumably a MobiLink solution would treat your SQL Anywhere database as a remote database, and use the dbmlsync.exe synchronization client to capture all the changes from your SQL Anywhere database transaction log and send them to the mlsrv16.exe synchronization server for processing. That's where it gets interesting: in your scenario, you don't need a consolidated database, just some MobiLink scripts to process the stream coming from dbmlsync.exe... but AFAIK mlsrv16.exe won't run without connecting to a consolidated database, so I'm guessing you'll need a second SQL Anywhere database to act as consolidated... it may be more trouble than it's worth since you won't be using much of MobiLink's functionality, but you will have to write custom code for each and every table naming each and every column.
In addition to Breck:
You have raised a few answers w.r.t. technologies like SQL Remote and Mobilink or topics like "log translation", and it seems our answers have only partially helped.
I would therefore recommend that you tell more about what you are exactly trying to achieve... - Is it auditing, is it an "export" based on each and every change?
And what exactly should be transfered to the message system: The original DML statement, a list of the values of a particular row before/after a change, whatever?
In my understanding, if you are primarily interested in capturing the changes done to one (or a few) tables, the trigger-based approach as mentioned by Breck is the simplest and most flexible way, and if you are more interested in all changes, I would still recommend to process the log via the DBTranslateLog() API I hinted at.
As to Mobilink:
Note: Mobilink is "smart" in the way that it consolidates all changes to the same row between two syncs, i.e. if you insert a row, update it several times and delete it afterwards and all that happens between two syncs, that row is not part of ML's synchronization stream at all. That's also true for an existing row with several updates between two syncs: Only the last state will be regarded. Therefore I would think that ML is not really usable for "auditing" purposes. (That's even true when you use MobiLink with option -tu (transactional uploads).)
FWIW I recently helped a client implement a MobiLink synchronization setup to replace a Replication Server setup that depended on each and every operation being synchronized separately. Triggers captured every row change in separate shadow table rows, and those shadow tables were transmitted in both directions and re-applied to the base tables. It was quite complex and had some performance issues. As Volker points out, MobiLink transactional upload is not the same as an operational (or change audit) upload. Using MobiLink for operational synchronization is not recommended as the first choice for any application... it's a last resort IMO.