|
I am in process of doing a SQL Anywhere 5 to SQL Anywhere 12 upgrade. The client uses SQL Remote to synchronize between a consolidated database and roughly 40 remote databases. In v5, when a given record is updated both at the consolidated database and at the remote database, an error is thrown (good) when DBREMOTE.EXE is run at the consolidated database. In contrast, in v12, no error is thrown (bad) when DBREMOTE.EXE is run at the consolidated database and the latest version of the record is automatically applied. I consider this “bad” because manual intervention is required to determine what the record should actually contain. When the same row is updated in two places, the update of that record needs to be blocked when DBREMOTE is run and manual intervention is required to determine what the record needs to contain. So, I need help with: 1) forcing an update conflict situation - what options can I set out of the box to force the update conflict? Is there a UI screen or table to show conflicts? 2) what methods do I need to implement to throw an error and have it display in the DBREMOTE window (as was the case in v5)? 3) I need a sample of code, if I need to implement triggers, etc. Thanks, Brendan |
|
What version of v5 are you using? Have you set the VERIFY_ALL_COLUMNS option to 'ON'? FWIW, I'm not aware that the handling of update conflicts has changed between v5.5.05 and current versions at all (but experts like Reg will know better!). To cite the v5.5.05 docs:
AFAIK, it's still the simple model as following (I assume each row is identified by a PK):
Note, there's no sample for a "Report the conflict into a table" handling but we could surely give hints. However, in order to stop SQL Remote when detecting a conflict (if that really is a requirement) instead of an automated conflict handling, I think you would then have to raise an exception from within the UPDATE RESOLVE trigger. Comment Text Removed
I have tested what you have indicated regarding raising an exception from within the UPDATE RESOLVE trigger and it works. Many thanks. The issue is that changes from the consolidated database to the remote site overwrite the changes at the remote site. How can this be similarly blocked? I know that the documentation states that "at remote databases, no RESOLVE UPDATE triggers are fired when a message from a consolidated database contains an UPDATE conflict". I guess the way to avoid situations where the same record is updated at two locations, only one location should really be making a change between syncs.
(06 Jun '12, 13:44)
Brendan
Replies hidden
Caveat Emptor: My permit to work on SQL Remote expired some time ago :) Perhaps you could figure out how to use a regular trigger on the remote database, if there is a timestamp column. Some of the intricacies of triggers-versus-SQL-Remote are discussed here: http://dcx.sybase.com/index.html#1201/en/sqlremote/wrtrig.html
(06 Jun '12, 16:02)
Breck Carter
1
...and ask your new question as a new question, since there are some SQL Remote gurus around here who might think this question's been answered.
(06 Jun '12, 16:03)
Breck Carter
I guess the effect you are seeing is expected (but I have not tested this!): The exception in the UPDATE RESOLVE trigger will prevent the UPDATE from the remote happening at the cons, and the conflicting UPDATE from the cons (or another remote) will reach the remote and update the values there. Therefore the remote update will get lost (although altogether, this should still lead to consistent data). If you really want to solve such conflicts "manually", I would think you will have to track them in a particular table at the remote (by means of the trigger) and "correct them" afterwards by a manual UPDATE. This update from the cons will then be sent to all remotes as usual. - Another means would be SQL Remote hook procedures... Note: I still would expect a general rule would be easier: An UPDATE RESOLVE trigger should usually be able to decide itself if the cons or the remote values (or a combination of both) are "as wanted" - and then just continue without an exception. That would be a preferred situation IMHO.
(07 Jun '12, 07:43)
Volker Barth
|
AFAIK in V5.5, conflicts are not detected as errors unless you have coded RESOLVE UPDATE triggers.
Do you have RESOLVE UPDATE triggers in your 5.5 implementation that somehow did not get upgraded?
I have coded no such RESOLVE UPDATE triggers. If they are instituted automatically and I wasn't aware of that, how would I find that out? I didn't find any property for that in Sybase Central v5.5
Maybe said another way, can I create RESOLVE UPDATE triggers and perform a SIGNAL exception to force the error and fail the update?