SQL Anywhere Mobile - Deletes do not get synchronized from Master->Remote DBs

Author

Deletes do not get synchronized from Master->Remote DBs

Mad Vlad

2005-11-18, 11:24 am

Hello,

Just started testing two databases - one "Host" and one "Remote" with
Mobilink. All I did was create default scripts using "SendColumnNames=ON",
of which I have then changed all "SELECT" download_cursor scripts to
"SELECT... WHERE LAST_MODIFIED >= ?". That's all I did.

I ran the client again (without SendColumnNames), scheduled to cycle the
syncs each 3 minutes, it synchronised the whole database OK, and then ran
quick syncs, and it is mostly working... except for one thing, I think:
When I delete a row from a table in Remote database, that gets synced in the
Host database OK. However, when I do the opposite, delete a row in Host
database, for some reason it remains undeleted in the Remote.

I've tried it with 2-3 tables, so it is not just a one-odd-table-situation.
Only once did output of Mobilink server give any message regarding this:
"W. 11/18 15:24:05. <1.5> [Hamburg]: Warning: [10039] Error detected while
using multi-row cursor -- retrying with single row cursor
W. 11/18 15:24:05. <1.5> [Hamburg]: Warning: [10040] 1 row(s) were ignored
in updating table client"

However, I've tried the scenario 2-3 times.

It's probably something simple, but I've only started messing with Mobilink
few days ago. Any more scripts I need to change?

Thanx

David Fishburn

2005-11-18, 11:24 am

"Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in news:437dfe2c$1@foru
ms-2-dub
of sybase.public.sqlanywhere.mobilink:

Please ALWAYS include version and MORE importantly BUILD number
with EACH post (dbeng9 -v).

MV> I ran the client again (without SendColumnNames), scheduled to cycle
MV> the syncs each 3 minutes, it synchronised the whole database OK, and
MV> then ran quick syncs, and it is mostly working... except for one
MV> thing, I think: When I delete a row from a table in Remote database,
MV> that gets synced in the Host database OK. However, when I do the
MV> opposite, delete a row in Host database, for some reason it remains
MV> undeleted in the Remote.

In order for deletes on the consolidated database to be sent to the
remote databases you must write a download_delete_curs
or script.

It is almost identical to the download_cursor, but you only select the
primary key columns for the table.

There is a larger issue here though. Since the download_delete_curs
or
is a SELECT statement, this means YOU must store the primary keys of
which rows were deleted somewhere.

The remote stores this information in the database transaction, hence it
is uploaded.

The consolidated could be Oracle, Microsoft, DB2, ASE and ASA, you
access those via SQL statements, not log scanning.

I suggest you start here with the 902 docs:
MobiLink Administration Guide
Writing Synchronization Scripts
Writing scripts to download rows
Writing download_delete_curs
or scripts

Also we have posted a number of samples on this forum.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Mad Vlad

2005-11-21, 7:24 am

Thank you very much, this is just what I'm looking for.

Sorry about the build number, I am always on the last build (9.0.2.3207) so
I went out of habit of putting that, but I will do so in future.

"David Fishburn" <fishburn_spam@off.ianywhere.com> wrote in message
news:Xns971277260C53
3fishburnsybasecom@1
27.0.0.1...
> "Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in news:437dfe2c$1@foru
ms-2-dub
> of sybase.public.sqlanywhere.mobilink:
>
> Please ALWAYS include version and MORE importantly BUILD number
> with EACH post (dbeng9 -v).
>
> MV> I ran the client again (without SendColumnNames), scheduled to cycle
> MV> the syncs each 3 minutes, it synchronised the whole database OK, and
> MV> then ran quick syncs, and it is mostly working... except for one
> MV> thing, I think: When I delete a row from a table in Remote database,
> MV> that gets synced in the Host database OK. However, when I do the
> MV> opposite, delete a row in Host database, for some reason it remains
> MV> undeleted in the Remote.
>
> In order for deletes on the consolidated database to be sent to the
> remote databases you must write a download_delete_curs
or script.
>
> It is almost identical to the download_cursor, but you only select the
> primary key columns for the table.
>
> There is a larger issue here though. Since the download_delete_curs
or
> is a SELECT statement, this means YOU must store the primary keys of
> which rows were deleted somewhere.
>
> The remote stores this information in the database transaction, hence it
> is uploaded.
>
> The consolidated could be Oracle, Microsoft, DB2, ASE and ASA, you
> access those via SQL statements, not log scanning.
>
> I suggest you start here with the 902 docs:
> MobiLink Administration Guide
> Writing Synchronization Scripts
> Writing scripts to download rows
> Writing download_delete_curs
or scripts
>
> Also we have posted a number of samples on this forum.
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD number with
> EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
> [url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

Mad Vlad

2005-11-21, 11:24 am

Just two more quick questions, please:

For a table "client" (primary key "code", lots of other fields), I created
table "client_shadow" (two fields, "code" and "last_modified", both making
up a primary key), and I put in a trigger to insert all deleted rows from
"client" into "client_shadow".

My delete_download_curs
or for "client" is

"SELECT CODE FROM client_shadow WHERE last_modified >= ?"

And that is ALL I added. I tried to delete one row from the consolidated,
and it indeed got deleted in the remote, great!

But,

1. Does it matter if for delete_download_curs
or, syntax is "WHERE
last_modified >= ?" or "WHERE last_modified > ?" - I found conflicting
examples. All my download_cursors have ">=", so why would
delete_download_curs
or be different and have just ">" like in the examples?

2. Is there anything wrong with the design I used above? Just a simple
"shadow" table with PK fields and "last_modified" timestamp added to the
primary key in the "shadow" table - and that small "SELECT" syntax for
delete_download_curs
or - is that all I need to know about the basics?

Thanks

"Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in message
news:43819660@forums
-1-dub...
> Thank you very much, this is just what I'm looking for.
>
> Sorry about the build number, I am always on the last build (9.0.2.3207)
> so I went out of habit of putting that, but I will do so in future.
>
>
> "David Fishburn" <fishburn_spam@off.ianywhere.com> wrote in message
> news:Xns971277260C53
3fishburnsybasecom@1
27.0.0.1...
>
>

Reg Domaratzki $iAnywhere Solutions$

2005-11-21, 11:24 am

Answers inline.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in message
news:4381ee4f@forums
-2-dub...
> Just two more quick questions, please:
>
> For a table "client" (primary key "code", lots of other fields), I created
> table "client_shadow" (two fields, "code" and "last_modified", both making
> up a primary key), and I put in a trigger to insert all deleted rows from
> "client" into "client_shadow".
>
> My delete_download_curs
or for "client" is
>
> "SELECT CODE FROM client_shadow WHERE last_modified >= ?"
>
> And that is ALL I added. I tried to delete one row from the consolidated,
> and it indeed got deleted in the remote, great!
>
> But,
>
> 1. Does it matter if for delete_download_curs
or, syntax is "WHERE
> last_modified >= ?" or "WHERE last_modified > ?" - I found conflicting
> examples. All my download_cursors have ">=", so why would
> delete_download_curs
or be different and have just ">" like in the
examples?

You should always have "WHERE last_modified >= ?" for all your download
events. If you found an example in the docs (or an online document), please
let us know where it is.

> 2. Is there anything wrong with the design I used above? Just a simple
> "shadow" table with PK fields and "last_modified" timestamp added to the
> primary key in the "shadow" table - and that small "SELECT" syntax for
> delete_download_curs
or - is that all I need to know about the basics?

There is nothing wrong with what you've done above. At some point, you may
need to think about cleaning out your shadow tables when you can guarantee
that all your remotes have received the delete, but for now, you've covered
the basics of downloading deletes.

> Thanks
>
>
>
>
> "Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in message
> news:43819660@forums
-1-dub...
news:437dfe2c$1@foru
ms-2-dub[color=darkred]
cycle[color=darkred]

and[color=darkred]
database,[color=dark
red]
it[color=darkred]
>
>

Mad Vlad

2005-11-21, 11:24 am

> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set filter to "Display ALL platforms IN ALL MONTHS"
> You should always have "WHERE last_modified >= ?" for all your download
> events. If you found an example in the docs (or an online document),
> please
> let us know where it is.
>
> There is nothing wrong with what you've done above. At some point, you
> may
> need to think about cleaning out your shadow tables when you can guarantee
> that all your remotes have received the delete, but for now, you've
> covered
> the basics of downloading deletes.

Thank you very much for replies.

As for the examples where code is different than "where last_modified > ?",
I found a couple on page 181 of Mobilink Synchronization Reference, but that
is the example of " download_delete_curs
or" EVENTS. Quote:

"This example is taken from the Contact sample and can be found in
Samples\MobiLink\Con
tact\build_consol.sql. It deletes from the remote

database any customers who have been changed since the last time this user

downloaded data (Customer.last_modified > ?), and either"

&

WHERE Customer.last_modified > ? AND

( SalesRep.ml_username != ? OR Customer.active = 0 )')

Sponsored Links