Determine if row was sent to server in MobiLink

[ArthurHCH]

HI

is there a simple way to find out in the client database if a row was sent to the server by MobiLink?

One option would be to add a column that's set on the server and sent back to client. However this would leaves a gap of a couple of seconds between sending and receiving the data.

The background is that the data is synchronized from the server to another system and some values can't be changed any more after the synchronization with the 3rd party server took place

Another option would be setting a flag manually on the client that the data is ready to be sent to the 3rd party server. This has the drawback that the user has to think to set the *ready flag".

Any suggestions are welcome
Arthur

[Vlad]

Hello Arthur,

What do you want finally to implement?

The server itself is able to determine if the sync was failed (http://dcx.sybase.com/index.html#sa160/en/mlstart/failure-ml-basics.html) and roll all changes back. If you do something on the server after the record has been uploaded, and something fails, you have to return the server and client to the previous state.

A good solution would be create the publication which should synchronize upload-only tables. And when the upload is successful, you can be definitely sure that your record was delivered.

Kind regards,
Vlad

[VolkerBarth]

Does the client somehow track the time of the last upload? (By default, the "SyncTrunc" database property will tell the last confirmed log offset but that does not easily relate to a sync time.) If so (say, by adding some housekeeping to each sync), and if the row has some kind of "lastChanged" column with DEFAULT TIMESTAMP, you might that way notice whether a particular row was inserted/modified after the last sync.

Personally, I'd think that an "it's ready" flag set by the consolidated (as you have suggested yourself) would fit conceptually as far as I understand your requirements...

[ArthurHCH]

The problem is not the synchronization between ML client and server but the asynchronous syncrhonization between the ML server and a 3rd party system.

[Breck_Carter]

Assumption: This entire discussion applies to a SQL Anywhere remote database, NOT the consolidated database.

If the row on the remote database had a TIMESTAMP DEFAULT TIMESTAMP column that was not used for anything else (and presumably not included in the upload since that would violate the "not used for anything else" clause), then that column could be checked against the timestamp for the last successful upload for the publication that uploads this table. I think "the timestamp for the last successful upload" is to be found in SYSSYNC.last_upload_time column.

IF table.last_updated < SYSSYNC.last_upload_time THEN the-row-has-been-sent-to-the-server;

Can the row be updated on the remote after the earlier version is uploaded but before SYSSYNC.last_upload_time is set? ...the answer to that question may determine whether this suggestion will work, or can be made to work, or is just a load of hooey 🙂

-- SYS.ISYSSYNC (table_id 46) in SQL Anywhere 16 Demo - autostart and connect - Sep 24 2014 8:50:39AM - Print - Foxhound © 2014 RisingRoad
CREATE TABLE SYS.ISYSSYNC ( -- empty
   sync_id              /* PK        */ UNSIGNED INT NOT NULL,
   type                 /*         X */ CHAR ( 1 ) NOT NULL,
   publication_id       /*    FK U   */ UNSIGNED INT NULL,
   progress                             UNSIGNED BIGINT NULL,
   site_name            /*       U X */ CHAR ( 128 ) NULL,
   "option"                             LONG VARCHAR NULL,
   server_connect                       LONG VARCHAR NULL,
   server_conn_type                     LONG VARCHAR NULL,
   last_download_time                   TIMESTAMP NULL,
   last_upload_time                     TIMESTAMP NOT NULL DEFAULT 'jan-1-1900',
   created                              UNSIGNED BIGINT NULL,
   log_sent                             UNSIGNED BIGINT NULL,
   generation_number                    INTEGER NOT NULL DEFAULT 0,
   extended_state                       VARCHAR ( 1024 ) NOT NULL DEFAULT '',
   script_version                       CHAR ( 128 ) NULL,
   subscription_name    /*       U   */ CHAR ( 128 ) NULL,
   server_protocol                      UNSIGNED BIGINT NULL,
   CONSTRAINT PRIMARY KEY (
      sync_id )
 );
-- Parents of SYS.ISYSSYNC
-- SYS.ISYSPUBLICATION
-- Children
-- none --
ALTER TABLE SYS.ISYSSYNC ADD CONSTRAINT ISYSPUBLICATION FOREIGN KEY (
      publication_id )
   REFERENCES SYS.ISYSPUBLICATION (
      publication_id )
   ON UPDATE RESTRICT ON DELETE RESTRICT;
CREATE UNIQUE INDEX pub_site ON SYS.ISYSSYNC (
   publication_id,
   site_name );
CREATE UNIQUE INDEX subscription_name ON SYS.ISYSSYNC (
   subscription_name );
CREATE INDEX sync_site_name ON SYS.ISYSSYNC (
   site_name );
CREATE INDEX sync_type ON SYS.ISYSSYNC (
   type );

[VolkerBarth]

Ah, I forgot the SYSSYNC table, besides that, that's exactly what I tried to suggest in my comment on the question...

Can the row be updated on the remote after the earlier version is uploaded but before SYSSYNC.last_upload_time is set?

In other words: How does the (non-default) "LockTable" work here, does it just block the according tables while their upload is built (resp. their download is applied) or until the upload is acknowledged/whatsover...?

[VolkerBarth]

A further detail:

last_upload_time (TIMESTAMP): Indicates the last time (measured at the MobiLink server) that information was successfully uploaded. The default is jan-1-1900.

So one might need to cope with different time sources here (ML client vs. ML server), making time calculations difficult. One more reason that a "ready flag" kept at the consolidated might be an easier (or working...) solution...

[ArthurHCH]

I think I should go with a combination of the "ready flag" and the automatic lock based on ISYSSYNC. I haver ordes and order lines to synchronize. As the synchronization is running in the background every x minutes and the users saves orders while working on it using a "ready flag" on this table would make sense to prevent a "locked" object while working on it. The order lines are quite simple and when some columns are entered the won't be changed again quickly and could use the ISYSSYNC table to "lock" them. Here some seconds delay or offset between server and client should be something that we can work with.

[Breck_Carter]

Suggestion: Set up a local test starting with empty consolidated and remote databases for the sole purpose of checking your method. Experience shows that surprises may lie within the nether regions of MobiLink, and it would be a shame to discover a flaw after running for months in production.

Plus, once you have worked out your method, post it on this forum so others can learn AND critique it (some MobiLink proctologists lurk here 🙂

[VolkerBarth]

MobiLink proctologists

Ah, the ones focussed on the remote site, apparently:)