SQL Anywhere Mobile - mobilink: duplicate records synchronization

Author

mobilink: duplicate records synchronization

Alex Naumenko

2005-11-15, 8:25 pm

ASA 9.0.2.3207

Hi,

I have remote database synchronizing with consolidated database (2 way
synchronization).
I have following tables in both databases (with different DATABASE ID for
global autoincrement):

Table 1

create table Mailbox (
MBXID INTEGER not null DEFAULT GLOBAL AUTOINCREMENT(214748
360),
...
, constraint MAILBOX primary key (MBXID) )

Table 2

create table MbxAddresses (
ADDRID INTEGER not null DEFAULT GLOBAL AUTOINCREMENT(214748
360),
MBXID INTEGER not null,
ADDRESS VARCHAR(255) default '' not null,
ADDRTYPE SMALLINT default 0 not null,
...
, constraint MbxAddresses primary key (ADDRID) )

As you see table MbxAddresses references table Mailbox by MbxID. My
internal logic is: when Mailbox created (in table Mailbox) there are some
default Mailbox addresses created (in table MbxAddresses) by trigger. My
problem is that after synchronization from remote to consolidated I will
have 2 sets of mailbox addresses in MbxAddresses table in consolidated
database (one set trigger create when new Mailboxes got synchronized from
remote (I have the same trigger logic), and another set from actual
synchronization of MbxAddresses tables).

If I will create UNIQUE constraint like the following

alter table MbxAddresses
add constraint uc_MailboxAddress UNIQUE(MbxID,Address
,AddrType)

as I understand database will throu the exception and all the changes will
be rolled back.

Same true if synchronization is from consolidated to remote database.

Could you advise what I should do in this case? I just need one set of
mailbox addresses. I can not disable triggers because user can create
mailboxes on every database (remote or consolidated).

Thank you,
Alex.

Reg Domaratzki

2005-11-16, 9:24 am

Assuming you have an ASA consolidated, you can create a variable in the
begin_connection script and then attempt to reference the variable in your
trigger. For example :

call ml_add_connection_sc
ript( 'v1', 'begin_connection', 'create variable
@ml_running tinyint');

create trigger au_t1 after update on t1
referencing new as nr for each row
begin
if not varexists(@ml_runnin
g) then
-- do trigger actions
end if;
end;

That way, your triggers will not execute when MobiLink is running and
applying operations from remotes.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Certified SQL Anywhere Associate
Please reply only to the newsgroup

ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL and click "GO"

"Alex Naumenko" <alexn@esna.com> wrote in message
news:437a657c@forums
-1-dub...
> ASA 9.0.2.3207
>
> Hi,
>
> I have remote database synchronizing with consolidated database (2 way
> synchronization).
> I have following tables in both databases (with different DATABASE ID for
> global autoincrement):
>
> Table 1
>
> create table Mailbox (
> MBXID INTEGER not null DEFAULT GLOBAL AUTOINCREMENT(214748
360),
> ...
> , constraint MAILBOX primary key (MBXID) )
>
>
> Table 2
>
> create table MbxAddresses (
> ADDRID INTEGER not null DEFAULT GLOBAL AUTOINCREMENT(214748
360),
> MBXID INTEGER not null,
> ADDRESS VARCHAR(255) default '' not null,
> ADDRTYPE SMALLINT default 0 not null,
> ...
> , constraint MbxAddresses primary key (ADDRID) )
>
>
> As you see table MbxAddresses references table Mailbox by MbxID. My
> internal logic is: when Mailbox created (in table Mailbox) there are some
> default Mailbox addresses created (in table MbxAddresses) by trigger. My
> problem is that after synchronization from remote to consolidated I will
> have 2 sets of mailbox addresses in MbxAddresses table in consolidated
> database (one set trigger create when new Mailboxes got synchronized from
> remote (I have the same trigger logic), and another set from actual
> synchronization of MbxAddresses tables).
>
> If I will create UNIQUE constraint like the following
>
> alter table MbxAddresses
> add constraint uc_MailboxAddress UNIQUE(MbxID,Address
,AddrType)
>
> as I understand database will throu the exception and all the changes will
> be rolled back.
>
> Same true if synchronization is from consolidated to remote database.
>
> Could you advise what I should do in this case? I just need one set of
> mailbox addresses. I can not disable triggers because user can create
> mailboxes on every database (remote or consolidated).
>
> Thank you,
> Alex.
>
>

Alex Naumenko

2005-11-16, 9:24 am

Thank you, Reg

Could you advise how to do the same trick for synchronization from
consolidated to remotes?

Thank you,
Alex

"Reg Domaratzki" <nospam.please@sympatico.ca> wrote in message
news:437b3739$1@foru
ms-2-dub...
> Assuming you have an ASA consolidated, you can create a variable in the
> begin_connection script and then attempt to reference the variable in your
> trigger. For example :
>
> call ml_add_connection_sc
ript( 'v1', 'begin_connection', 'create variable
> @ml_running tinyint');
>
> create trigger au_t1 after update on t1
> referencing new as nr for each row
> begin
> if not varexists(@ml_runnin
g) then
> -- do trigger actions
> end if;
> end;
>
> That way, your triggers will not execute when MobiLink is running and
> applying operations from remotes.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Certified SQL Anywhere Associate
> Please reply only to the newsgroup
>
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL and click "GO"
>
>
> "Alex Naumenko" <alexn@esna.com> wrote in message
> news:437a657c@forums
-1-dub...
>
>

David Fishburn

2005-11-16, 1:24 pm

"Alex Naumenko" <alexn@esna.com> wrote in news:437b48ef$1@foru
ms-2-dub
of sybase.public.sqlanywhere.mobilink:

AN> Could you advise how to do the same trick for synchronization from
AN> consolidated to remotes?

That one is much easier.

This shows dbmlsync extended options:
dbmlsync -l

The one you are interested in is:
FireTriggers (ft) extended option

See Help
MobiLink Clients
Adaptive Server Anywhere Client Synchronization Parameters
dbmlsync options
dbmlsync extended options
FireTriggers (ft) extended option

--
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]

Sponsored Links