Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ON CONFLICT / Replication

10 posts in Trigger Last posting was on 2007-01-16 22:42:48.0Z
Rick Dowling Posted on 2006-12-15 01:25:15.0Z
Reply-To: "Rick Dowling" <rick@barcode.com.au>
From: "Rick Dowling" <rick@barcode.com.au>
Newsgroups: Advantage.Trigger
Subject: ON CONFLICT / Replication
Date: Fri, 15 Dec 2006 12:25:15 +1100
Lines: 27
Organization: Barcode Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 138.130.114.80
Message-ID: <4581f875@solutions.advantagedatabase.com>
X-Trace: 14 Dec 2006 18:20:53 -0700, 138.130.114.80
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!138.130.114.80
Xref: solutions.advantagedatabase.com Advantage.Trigger:293
Article PK: 1136358

Hi,

I am trying to replicate between two sites. Each site shares products and
hence im replicating when they are added at either site. To identify the
products, they have an autoinc as their unique primary key.

The issue/senario i am attempting to handle (and am not having much luck
atm) is where the sites are offline with each other (as they could be in
different states and going over the internet), and both of them add a new
product, and because they are both offline they assign the same autoinc
value (e.g. 5) to the different products both sites are adding.

What i would like (if possible) is to setup an ONCONFLICT tigger to handle
this and treat SITE A as the more important repositry (so leave its record
intact), and when SITE B attempts to send its record through, i give it the
next available autoinc value, and delete the record from SITE B. (Site B
will get the new record forwarded to it by site A).

Any help would be appreciated.
--

Rick Dowling
Analyst Programmer

Barcode Solutions


Joachim Duerr (ADS Support) Posted on 2006-12-15 08:14:20.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: ON CONFLICT / Replication
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 10.56.66.118
Message-ID: <4582595c@solutions.advantagedatabase.com>
Date: 15 Dec 2006 01:14:20 -0700
X-Trace: 15 Dec 2006 01:14:20 -0700, 10.56.66.118
Lines: 34
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.118
Xref: solutions.advantagedatabase.com Advantage.Trigger:294
Article PK: 1136360


Rick Dowling wrote in <4581f875@solutions.advantagedatabase.com>:

> I am trying to replicate between two sites. Each site shares products
> and hence im replicating when they are added at either site. To
> identify the products, they have an autoinc as their unique primary
> key.
>
> The issue/senario i am attempting to handle (and am not having much
> luck atm) is where the sites are offline with each other (as they
> could be in different states and going over the internet), and both
> of them add a new product, and because they are both offline they
> assign the same autoinc value (e.g. 5) to the different products both
> sites are adding.
>
> What i would like (if possible) is to setup an ONCONFLICT tigger to
> handle this and treat SITE A as the more important repositry (so
> leave its record intact), and when SITE B attempts to send its record
> through, i give it the next available autoinc value, and delete the
> record from SITE B. (Site B will get the new record forwarded to it
> by site A).
>
> Any help would be appreciated.

Rick,
ON CONFLICT is only for UPDATE and DELETE conflicts. On inserts you'd
get a unique key violation. I'd suggest to create your own keys instead
of using autoinc - or combine the autoinc field with a sitekey field
for being true unique in your environment.

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de


Rick Dowling Posted on 2006-12-18 04:26:08.0Z
Reply-To: "Rick Dowling" <rick@barcode.com.au>
From: "Rick Dowling" <rick@barcode.com.au>
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com>
Subject: Re: ON CONFLICT / Replication
Date: Mon, 18 Dec 2006 15:26:08 +1100
Lines: 64
Organization: Barcode Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: 138.130.114.80
Message-ID: <45861767@solutions.advantagedatabase.com>
X-Trace: 17 Dec 2006 21:21:59 -0700, 138.130.114.80
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!138.130.114.80
Xref: solutions.advantagedatabase.com Advantage.Trigger:295
Article PK: 1136361

Hi, I have a few further problem im trying to resolve now.

1. On Conflict - Comparing the old and new to see which has the latest
version.
The error i am getting is:
Error 7200: AQE Error: State = 42000; nativeerror-2115; [Extended
systems ][Advantage SQL Engine]Expected
lexical element not found: your statement must start with CREATE, DROP,
SELECT, INSERT, UPDATE, ALTER,
EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, SET or a
script statement.

Here is what i have in my ONCONFLICT Update window In Advantage Data
Architect. (Using Test SQL button).
I have tired a whole host of things (such as putting a SELECT statement
first e.g. Select * from __new, to see if this
would let me get around this, but nothing seems to work. What am i doing
wrong ? I cant seem to get the anwser in the help. Im fairly new to this.

DECLARE @newDate DATE, @oldDate DATE;

@newDate = (SELECT DATE_MOD FROM __new);
@oldDate = (SELECT DATE_MOD FROM __old);

IF @newDate > @oldDate THEN;
UPDATE Master
SET ISBN=(SELECT ISBN FROM __new)
,DATE_MOD=(SELECT DATE_MOD FROM __new)
,CATALOG=(SELECT CATALOG FROM __new)
,TITLE=(SELECT TITLE FROM __new)
,AUTHOR=(SELECT AUTHOR FROM __new)
WHERE ISBN_CODE = (SELECT ISBN_CODE FROM __new)
ENDIF;

2. In the same ON ONFLICT above (in the end ill be putting them together) i
would like to have some code where before point 1, it checks to see if the
title is there to update, if it is not, do add it to the table. I need to
ensure records are at all sites. All sites will be doing centralised
ordering and we need to make sure that HO and all sites replicate the
products (without duplicating them) and so if any for some reason dont et
added, it needs to handle this.
The syntax says its correct, but in the error log it lists that its trying
to do an update, with all my fields initally saying e.g. EntryID=1,
Subscription=Master - Store 2, Table=MASTER, Record=2, SQL=UPDATE "MASTER"
SET "ISBN_CODE" = ?, "ISBN" = ?, "DATE_MOD" = ?, "CATALOG" = ?, "TITLE" = ?,
"AUTHOR" = ?, and so on. Below all this it shows the fields where i have
values for (e.g. Values=(HO1 ,12345 ,2006-12-18
00:00:00.000,Product 1 Catalogue ,Product 1 ) which are ok, and the
identifying field values.
How can i trouble shoot this one out. See code below. (Ive been trying to do
this one on its own). Im getting error code 7137.

DECLARE @itemCode STRING;
@itemCode = (SELECT (SELECT(ISBN_CODE FROM __new) FROM __old);
IF @itemCode IS NULL THEN
INSERT INTO Master SELECT * FROM __new;
ENDIF;

Any help would be greatly appreciated.

Thanks
Rick


Joachim Duerr (ADS Support) Posted on 2006-12-18 12:50:15.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: ON CONFLICT / Replication
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com> <45861767@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 130.214.79.11
Message-ID: <45868e87@solutions.advantagedatabase.com>
Date: 18 Dec 2006 05:50:15 -0700
X-Trace: 18 Dec 2006 05:50:15 -0700, 130.214.79.11
Lines: 69
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!130.214.79.11
Xref: solutions.advantagedatabase.com Advantage.Trigger:296
Article PK: 1136362


Rick Dowling wrote in <45861767@solutions.advantagedatabase.com>:

> 1. On Conflict - Comparing the old and new to see which has the
> latest version. The error i am getting is:
> Error 7200: AQE Error: State = 42000; nativeerror-2115; [Extended
> systems ][Advantage SQL Engine]Expected lexical element not found:
> your statement must start with CREATE, DROP, SELECT, INSERT, UPDATE,
> ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK,
> SAVEPOINT, SET or a script statement.

test SQL might not work since __new and __old are not present in design
time.

> Here is what i have in my ONCONFLICT Update window In Advantage Data
> Architect. (Using Test SQL button). I have tired a whole host of
> things (such as putting a SELECT statement first e.g. Select * from
> __new, to see if this would let me get around this, but nothing seems
> to work. What am i doing wrong ? I cant seem to get the anwser in the
> help. Im fairly new to this.
>
> DECLARE @newDate DATE, @oldDate DATE;
>
> @newDate = (SELECT DATE_MOD FROM __new);
> @oldDate = (SELECT DATE_MOD FROM __old);
>
> IF @newDate > @oldDate THEN;
^^?? remove the semicolon

> UPDATE Master
> SET ISBN=(SELECT ISBN FROM __new)
> ,DATE_MOD=(SELECT DATE_MOD FROM __new)
> ,CATALOG=(SELECT CATALOG FROM __new)
> ,TITLE=(SELECT TITLE FROM __new)
> ,AUTHOR=(SELECT AUTHOR FROM __new)
> WHERE ISBN_CODE = (SELECT ISBN_CODE FROM __new)
> ENDIF;
>
> 2. In the same ON ONFLICT above (in the end ill be putting them
> together) i would like to have some code where before point 1, it
> checks to see if the title is there to update, if it is not, do add
> it to the table. I need to ensure records are at all sites. All sites
> will be doing centralised ordering and we need to make sure that HO
> and all sites replicate the products (without duplicating them) and
> so if any for some reason dont et added, it needs to handle this.
> The syntax says its correct, but in the error log it lists that its
> trying to do an update, with all my fields initally saying e.g.
> EntryID=1, Subscription=Master - Store 2, Table=MASTER, Record=2,
> SQL=UPDATE "MASTER" SET "ISBN_CODE" = ?, "ISBN" = ?, "DATE_MOD" = ?,
> "CATALOG" = ?, "TITLE" = ?, "AUTHOR" = ?, and so on. Below all this
> it shows the fields where i have values for (e.g. Values=(HO1
> ,12345 ,2006-12-18 00:00:00.000,Product 1 Catalogue
> ,Product 1 ) which are ok, and the identifying field values. How can
> i trouble shoot this one out. See code below. (Ive been trying to do
> this one on its own). Im getting error code 7137.
>
> DECLARE @itemCode STRING;
> @itemCode = (SELECT (SELECT(ISBN_CODE FROM __new) FROM __old);
> IF @itemCode IS NULL THEN
> INSERT INTO Master SELECT * FROM __new;
> ENDIF;

this is a lack of ADS rpelication: if the record to update does not
exist in the destination table, you'll get an error.

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de


Rick Dowling Posted on 2006-12-20 01:39:40.0Z
Reply-To: "Rick Dowling" <rick@barcode.com.au>
From: "Rick Dowling" <rick@barcode.com.au>
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com> <45861767@solutions.advantagedatabase.com> <45868e87@solutions.advantagedatabase.com>
Subject: Re: ON CONFLICT / Replication
Date: Wed, 20 Dec 2006 12:39:40 +1100
Lines: 199
Organization: Barcode Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 138.130.114.80
Message-ID: <4588938e@solutions.advantagedatabase.com>
X-Trace: 19 Dec 2006 18:36:14 -0700, 138.130.114.80
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!138.130.114.80
Xref: solutions.advantagedatabase.com Advantage.Trigger:297
Article PK: 1136363

Thanks, that worked, however im getting annoyed with something and i cant
work out why its happening.

Following getting the timestamp check to work to see if it should update or
not, i played around a little more with other triggers (not changing that on
conflict trigger). The problem i have now is that it wont fire an error.

I tired removing the subscripitons/publication changing the idendifying
fields (to all, to primary and Unique key, to primary + timestamp etc),
re-adding the publication/subscription. I even (as the code below) created a
whole new connection/database from scratch on both machines no no avail.

What i have setup is a table with a primary key (use code below to run), and
it also has the onconflict tigger included in code. I then setup a
Publication (using my table and using all fields as identifiers), then a
subscription from one machine to the other. Log is set, ignore replication
errors is not ticked (see code below for my setup also).

When i create a record on this machine, it replicated ok, but when i then
change the primary key on the destination machine, (or even all 3 fields) it
updates all the fields. (my onconflict tigger does not change the primary
key, and ive had it try to update with an older date, to no avail). It was
working yesterday, but for some reason would after about 15 mins of good
solid testing with it (and i looked at the settings i had and it seemed
pretty straighforward....)

Why wont it raise an error when modifying the publication identifying values
? Please tell me what im obviously missing.

Thanks
Rick


CODE
Build table
CREATE TABLE master (
I_CODE CIChar( 10 ),
TITLE Char( 10 ),
STAMP TimeStamp);
EXECUTE PROCEDURE sp_CreateIndex(
'master',
'master.adi',
'I_CODE',
'I_CODE',
'',
3,
512 );


EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
'Table_Auto_Create',
'True', 'APPEND_FAIL', 'masterfail');

EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
'Table_Primary_Key',
'I_CODE', 'APPEND_FAIL', 'masterfail');

EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
'Table_Permission_Level',
'2', 'APPEND_FAIL', 'masterfail');

EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
'Table_Memo_Block_Size',
'8', 'APPEND_FAIL', 'masterfail');

EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
'Triggers_Disabled',
'False', 'APPEND_FAIL', 'masterfail');

CREATE TRIGGER conflict
ON master
CONFLICT
UPDATE
BEGIN
IF (SELECT DATE_MOD FROM __new) > (SELECT DATE_MOD FROM __old) THEN
UPDATE master
SET TITLE=(SELECT TITLE FROM __new)
,DATE_MOD=(SELECT DATE_MOD FROM __new)

WHERE I_CODE = (SELECT DISTINCT I_CODE FROM __new);

ELSE
UPDATE master
SET TITLE= 'Didnt update older record'
WHERE I_CODE = (SELECT DISTINCT I_CODE FROM __new);
ENDIF
END
PRIORITY 1;



Publication Code

EXECUTE PROCEDURE
sp_CreatePublication (
'branch',
'',
0 );
EXECUTE PROCEDURE
sp_CreateArticle (
'branch',
'master',
'I_CODE;TITLE;STAMP;',
'',
0 );




Subscription Code

EXECUTE PROCEDURE
sp_CreateSubscription (
'Branch 2',
'branch',
'\\192.168.0.3:6262\Store 2\branch.add',
'ADSSYS',
'',/* YOUR PASSWORD GOES HERE */
'C:\Head Office\Branch 2.adt',
False,
'',
10 );

"Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de> wrote in message
news:45868e87@solutions.advantagedatabase.com...
> Rick Dowling wrote in <45861767@solutions.advantagedatabase.com>:
>
>> 1. On Conflict - Comparing the old and new to see which has the
>> latest version. The error i am getting is:
>> Error 7200: AQE Error: State = 42000; nativeerror-2115; [Extended
>> systems ][Advantage SQL Engine]Expected lexical element not found:
>> your statement must start with CREATE, DROP, SELECT, INSERT, UPDATE,
>> ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK,
>> SAVEPOINT, SET or a script statement.
>
> test SQL might not work since __new and __old are not present in design
> time.
>
>> Here is what i have in my ONCONFLICT Update window In Advantage Data
>> Architect. (Using Test SQL button). I have tired a whole host of
>> things (such as putting a SELECT statement first e.g. Select * from
>> __new, to see if this would let me get around this, but nothing seems
>> to work. What am i doing wrong ? I cant seem to get the anwser in the
>> help. Im fairly new to this.
>>
>> DECLARE @newDate DATE, @oldDate DATE;
>>
>> @newDate = (SELECT DATE_MOD FROM __new);
>> @oldDate = (SELECT DATE_MOD FROM __old);
>>
>> IF @newDate > @oldDate THEN;
> ^^?? remove the semicolon
>
>> UPDATE Master
>> SET ISBN=(SELECT ISBN FROM __new)
>> ,DATE_MOD=(SELECT DATE_MOD FROM __new)
>> ,CATALOG=(SELECT CATALOG FROM __new)
>> ,TITLE=(SELECT TITLE FROM __new)
>> ,AUTHOR=(SELECT AUTHOR FROM __new)
>> WHERE ISBN_CODE = (SELECT ISBN_CODE FROM __new)
>> ENDIF;
>>
>> 2. In the same ON ONFLICT above (in the end ill be putting them
>> together) i would like to have some code where before point 1, it
>> checks to see if the title is there to update, if it is not, do add
>> it to the table. I need to ensure records are at all sites. All sites
>> will be doing centralised ordering and we need to make sure that HO
>> and all sites replicate the products (without duplicating them) and
>> so if any for some reason dont et added, it needs to handle this.
>> The syntax says its correct, but in the error log it lists that its
>> trying to do an update, with all my fields initally saying e.g.
>> EntryID=1, Subscription=Master - Store 2, Table=MASTER, Record=2,
>> SQL=UPDATE "MASTER" SET "ISBN_CODE" = ?, "ISBN" = ?, "DATE_MOD" = ?,
>> "CATALOG" = ?, "TITLE" = ?, "AUTHOR" = ?, and so on. Below all this
>> it shows the fields where i have values for (e.g. Values=(HO1
>> ,12345 ,2006-12-18 00:00:00.000,Product 1 Catalogue
>> ,Product 1 ) which are ok, and the identifying field values. How can
>> i trouble shoot this one out. See code below. (Ive been trying to do
>> this one on its own). Im getting error code 7137.
>>
>> DECLARE @itemCode STRING;
>> @itemCode = (SELECT (SELECT(ISBN_CODE FROM __new) FROM __old);
>> IF @itemCode IS NULL THEN
>> INSERT INTO Master SELECT * FROM __new;
>> ENDIF;
>
> this is a lack of ADS rpelication: if the record to update does not
> exist in the destination table, you'll get an error.
>
> --
> Joachim Duerr
> Senior Product Support Analyst (Advantage Database Server)
> iAnywhere Solutions / Extended Systems
> advantage[AT]extendsys.de


Rick Dowling Posted on 2007-01-15 21:49:23.0Z
Reply-To: "Rick Dowling" <rick@barcode.com.au>
From: "Rick Dowling" <rick@barcode.com.au>
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com> <45861767@solutions.advantagedatabase.com> <45868e87@solutions.advantagedatabase.com> <4588938e@solutions.advantagedatabase.com>
Subject: Re: ON CONFLICT / Replication
Date: Tue, 16 Jan 2007 08:49:23 +1100
Lines: 210
Organization: Barcode Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 138.130.114.80
Message-ID: <45abf5e7@solutions.advantagedatabase.com>
X-Trace: 15 Jan 2007 14:45:11 -0700, 138.130.114.80
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!138.130.114.80
Xref: solutions.advantagedatabase.com Advantage.Trigger:300
Article PK: 1136365

Can anyone help me with this !!!


Thanks
Rick.

"Rick Dowling" <rick@barcode.com.au> wrote in message
news:4588938e@solutions.advantagedatabase.com...
> Thanks, that worked, however im getting annoyed with something and i cant
> work out why its happening.
>
> Following getting the timestamp check to work to see if it should update
> or not, i played around a little more with other triggers (not changing
> that on conflict trigger). The problem i have now is that it wont fire an
> error.
>
> I tired removing the subscripitons/publication changing the idendifying
> fields (to all, to primary and Unique key, to primary + timestamp etc),
> re-adding the publication/subscription. I even (as the code below) created
> a whole new connection/database from scratch on both machines no no avail.
>
> What i have setup is a table with a primary key (use code below to run),
> and it also has the onconflict tigger included in code. I then setup a
> Publication (using my table and using all fields as identifiers), then a
> subscription from one machine to the other. Log is set, ignore replication
> errors is not ticked (see code below for my setup also).
>
> When i create a record on this machine, it replicated ok, but when i then
> change the primary key on the destination machine, (or even all 3 fields)
> it updates all the fields. (my onconflict tigger does not change the
> primary key, and ive had it try to update with an older date, to no
> avail). It was working yesterday, but for some reason would after about 15
> mins of good solid testing with it (and i looked at the settings i had and
> it seemed pretty straighforward....)
>
> Why wont it raise an error when modifying the publication identifying
> values ? Please tell me what im obviously missing.
>
> Thanks
> Rick
>
>
> CODE
> Build table
> CREATE TABLE master (
> I_CODE CIChar( 10 ),
> TITLE Char( 10 ),
> STAMP TimeStamp);
> EXECUTE PROCEDURE sp_CreateIndex(
> 'master',
> 'master.adi',
> 'I_CODE',
> 'I_CODE',
> '',
> 3,
> 512 );
>
>
> EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
> 'Table_Auto_Create',
> 'True', 'APPEND_FAIL', 'masterfail');
>
> EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
> 'Table_Primary_Key',
> 'I_CODE', 'APPEND_FAIL', 'masterfail');
>
> EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
> 'Table_Permission_Level',
> '2', 'APPEND_FAIL', 'masterfail');
>
> EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
> 'Table_Memo_Block_Size',
> '8', 'APPEND_FAIL', 'masterfail');
>
> EXECUTE PROCEDURE sp_ModifyTableProperty( 'master',
> 'Triggers_Disabled',
> 'False', 'APPEND_FAIL', 'masterfail');
>
> CREATE TRIGGER conflict
> ON master
> CONFLICT
> UPDATE
> BEGIN
> IF (SELECT DATE_MOD FROM __new) > (SELECT DATE_MOD FROM __old) THEN
> UPDATE master
> SET TITLE=(SELECT TITLE FROM __new)
> ,DATE_MOD=(SELECT DATE_MOD FROM __new)
>
> WHERE I_CODE = (SELECT DISTINCT I_CODE FROM __new);
>
> ELSE
> UPDATE master
> SET TITLE= 'Didnt update older record'
> WHERE I_CODE = (SELECT DISTINCT I_CODE FROM __new);
> ENDIF
> END
> PRIORITY 1;
>
>
>
> Publication Code
>
> EXECUTE PROCEDURE
> sp_CreatePublication (
> 'branch',
> '',
> 0 );
> EXECUTE PROCEDURE
> sp_CreateArticle (
> 'branch',
> 'master',
> 'I_CODE;TITLE;STAMP;',
> '',
> 0 );
>
>
>
>
> Subscription Code
>
> EXECUTE PROCEDURE
> sp_CreateSubscription (
> 'Branch 2',
> 'branch',
> '\\192.168.0.3:6262\Store 2\branch.add',
> 'ADSSYS',
> '',/* YOUR PASSWORD GOES HERE */
> 'C:\Head Office\Branch 2.adt',
> False,
> '',
> 10 );
>
>
>
>
> "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de> wrote in message
> news:45868e87@solutions.advantagedatabase.com...
>> Rick Dowling wrote in <45861767@solutions.advantagedatabase.com>:
>>
>>> 1. On Conflict - Comparing the old and new to see which has the
>>> latest version. The error i am getting is:
>>> Error 7200: AQE Error: State = 42000; nativeerror-2115; [Extended
>>> systems ][Advantage SQL Engine]Expected lexical element not found:
>>> your statement must start with CREATE, DROP, SELECT, INSERT, UPDATE,
>>> ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK,
>>> SAVEPOINT, SET or a script statement.
>>
>> test SQL might not work since __new and __old are not present in design
>> time.
>>
>>> Here is what i have in my ONCONFLICT Update window In Advantage Data
>>> Architect. (Using Test SQL button). I have tired a whole host of
>>> things (such as putting a SELECT statement first e.g. Select * from
>>> __new, to see if this would let me get around this, but nothing seems
>>> to work. What am i doing wrong ? I cant seem to get the anwser in the
>>> help. Im fairly new to this.
>>>
>>> DECLARE @newDate DATE, @oldDate DATE;
>>>
>>> @newDate = (SELECT DATE_MOD FROM __new);
>>> @oldDate = (SELECT DATE_MOD FROM __old);
>>>
>>> IF @newDate > @oldDate THEN;
>> ^^?? remove the semicolon
>>
>>> UPDATE Master
>>> SET ISBN=(SELECT ISBN FROM __new)
>>> ,DATE_MOD=(SELECT DATE_MOD FROM __new)
>>> ,CATALOG=(SELECT CATALOG FROM __new)
>>> ,TITLE=(SELECT TITLE FROM __new)
>>> ,AUTHOR=(SELECT AUTHOR FROM __new)
>>> WHERE ISBN_CODE = (SELECT ISBN_CODE FROM __new)
>>> ENDIF;
>>>
>>> 2. In the same ON ONFLICT above (in the end ill be putting them
>>> together) i would like to have some code where before point 1, it
>>> checks to see if the title is there to update, if it is not, do add
>>> it to the table. I need to ensure records are at all sites. All sites
>>> will be doing centralised ordering and we need to make sure that HO
>>> and all sites replicate the products (without duplicating them) and
>>> so if any for some reason dont et added, it needs to handle this.
>>> The syntax says its correct, but in the error log it lists that its
>>> trying to do an update, with all my fields initally saying e.g.
>>> EntryID=1, Subscription=Master - Store 2, Table=MASTER, Record=2,
>>> SQL=UPDATE "MASTER" SET "ISBN_CODE" = ?, "ISBN" = ?, "DATE_MOD" = ?,
>>> "CATALOG" = ?, "TITLE" = ?, "AUTHOR" = ?, and so on. Below all this
>>> it shows the fields where i have values for (e.g. Values=(HO1
>>> ,12345 ,2006-12-18 00:00:00.000,Product 1 Catalogue
>>> ,Product 1 ) which are ok, and the identifying field values. How can
>>> i trouble shoot this one out. See code below. (Ive been trying to do
>>> this one on its own). Im getting error code 7137.
>>>
>>> DECLARE @itemCode STRING;
>>> @itemCode = (SELECT (SELECT(ISBN_CODE FROM __new) FROM __old);
>>> IF @itemCode IS NULL THEN
>>> INSERT INTO Master SELECT * FROM __new;
>>> ENDIF;
>>
>> this is a lack of ADS rpelication: if the record to update does not
>> exist in the destination table, you'll get an error.
>>
>> --
>> Joachim Duerr
>> Senior Product Support Analyst (Advantage Database Server)
>> iAnywhere Solutions / Extended Systems
>> advantage[AT]extendsys.de
>
>


Mark Wilkins Posted on 2007-01-16 16:32:02.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com> <45861767@solutions.advantagedatabase.com> <45868e87@solutions.advantagedatabase.com> <4588938e@solutions.advantagedatabase.com>
Subject: Re: ON CONFLICT / Replication
Date: Tue, 16 Jan 2007 09:32:02 -0700
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.24.38.228
Message-ID: <45acfcfe@solutions.advantagedatabase.com>
X-Trace: 16 Jan 2007 09:27:42 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.Trigger:301
Article PK: 1136367

Hi Rick,

It sounds like you are saying that after you modify the record at the
destination, then a modification at the source will not cause the conflict
trigger to fire. Assuming I understood correctly, the explanation then is:
The identification fields include all fields in the table. Therefore, it
means an update at the source will not be able to find the target record if
the target has been modified. It searches for the target record using all
of the fields. If any field is different, then it won't find it. The ON
CONFLICT trigger is only fired if it finds the target record and determines
that it does not match the original source record. The result of this is
that I think you would get a 7137 error at the source.

In order for an ON CONFLICT trigger to fire, the identification columns
probably should only include the primary key field(s).

Mark Wilkins
Advantage R&D

"Rick Dowling" <rick@barcode.com.au> wrote in message
news:4588938e@solutions.advantagedatabase.com...
> Thanks, that worked, however im getting annoyed with something and i cant
> work out why its happening.
>
> Following getting the timestamp check to work to see if it should update
> or not, i played around a little more with other triggers (not changing
> that on conflict trigger). The problem i have now is that it wont fire an
> error.
>


Rick Dowling Posted on 2007-01-16 21:50:09.0Z
Reply-To: "Rick Dowling" <rick@barcode.com.au>
From: "Rick Dowling" <rick@barcode.com.au>
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com> <45861767@solutions.advantagedatabase.com> <45868e87@solutions.advantagedatabase.com> <4588938e@solutions.advantagedatabase.com> <45acfcfe@solutions.advantagedatabase.com>
Subject: Re: ON CONFLICT / Replication
Date: Wed, 17 Jan 2007 08:50:09 +1100
Lines: 41
Organization: Barcode Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 138.130.114.80
Message-ID: <45ad4792@solutions.advantagedatabase.com>
X-Trace: 16 Jan 2007 14:45:54 -0700, 138.130.114.80
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!138.130.114.80
Xref: solutions.advantagedatabase.com Advantage.Trigger:302
Article PK: 1136368

Ive had it working, but using the same setup it would not work. I tired with
only the primary key, a combination of 2 fields and even all fields. It just
wont fire for some reason. Its really frustrating because at first it was
firing all the time and when i figures out the issues i was having and got
stuff to update, from then on it wouldnt not fire any more.

"Mark Wilkins" <mark@no.email> wrote in message
news:45acfcfe@solutions.advantagedatabase.com...
> Hi Rick,
>
> It sounds like you are saying that after you modify the record at the
> destination, then a modification at the source will not cause the conflict
> trigger to fire. Assuming I understood correctly, the explanation then
> is: The identification fields include all fields in the table. Therefore,
> it means an update at the source will not be able to find the target
> record if the target has been modified. It searches for the target record
> using all of the fields. If any field is different, then it won't find
> it. The ON CONFLICT trigger is only fired if it finds the target record
> and determines that it does not match the original source record. The
> result of this is that I think you would get a 7137 error at the source.
>
> In order for an ON CONFLICT trigger to fire, the identification columns
> probably should only include the primary key field(s).
>
> Mark Wilkins
> Advantage R&D
>
> "Rick Dowling" <rick@barcode.com.au> wrote in message
> news:4588938e@solutions.advantagedatabase.com...
>> Thanks, that worked, however im getting annoyed with something and i cant
>> work out why its happening.
>>
>> Following getting the timestamp check to work to see if it should update
>> or not, i played around a little more with other triggers (not changing
>> that on conflict trigger). The problem i have now is that it wont fire an
>> error.
>>
>
>


Mark Wilkins Posted on 2007-01-16 22:16:15.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com> <45861767@solutions.advantagedatabase.com> <45868e87@solutions.advantagedatabase.com> <4588938e@solutions.advantagedatabase.com> <45acfcfe@solutions.advantagedatabase.com> <45ad4792@solutions.advantagedatabase.com>
Subject: Re: ON CONFLICT / Replication
Date: Tue, 16 Jan 2007 15:16:15 -0700
Lines: 17
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.24.38.228
Message-ID: <45ad4dad@solutions.advantagedatabase.com>
X-Trace: 16 Jan 2007 15:11:57 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.Trigger:303
Article PK: 1136370

Hi,

Does it log a 7137 error?

Mark Wilkins
Advantage R&D

"Rick Dowling" <rick@barcode.com.au> wrote in message
news:45ad4792@solutions.advantagedatabase.com...
> Ive had it working, but using the same setup it would not work. I tired
> with only the primary key, a combination of 2 fields and even all fields.
> It just wont fire for some reason. Its really frustrating because at first
> it was firing all the time and when i figures out the issues i was having
> and got stuff to update, from then on it wouldnt not fire any more.
>


Rick Dowling Posted on 2007-01-16 22:42:48.0Z
Reply-To: "Rick Dowling" <rick@barcode.com.au>
From: "Rick Dowling" <rick@barcode.com.au>
Newsgroups: Advantage.Trigger
References: <4581f875@solutions.advantagedatabase.com> <4582595c@solutions.advantagedatabase.com> <45861767@solutions.advantagedatabase.com> <45868e87@solutions.advantagedatabase.com> <4588938e@solutions.advantagedatabase.com> <45acfcfe@solutions.advantagedatabase.com> <45ad4792@solutions.advantagedatabase.com> <45ad4dad@solutions.advantagedatabase.com>
Subject: Re: ON CONFLICT / Replication
Date: Wed, 17 Jan 2007 09:42:48 +1100
Lines: 29
Organization: Barcode Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 138.130.114.80
Message-ID: <45ad53e6@solutions.advantagedatabase.com>
X-Trace: 16 Jan 2007 15:38:30 -0700, 138.130.114.80
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!138.130.114.80
Xref: solutions.advantagedatabase.com Advantage.Trigger:304
Article PK: 1136371

Will have to get back to you on that, i havent touched it since before xmas
since i hadnt received a reply.

When i get a chance to do it again ill let you know. Thanks for your prompt
response.

"Mark Wilkins" <mark@no.email> wrote in message
news:45ad4dad@solutions.advantagedatabase.com...
> Hi,
>
> Does it log a 7137 error?
>
> Mark Wilkins
> Advantage R&D
>
> "Rick Dowling" <rick@barcode.com.au> wrote in message
> news:45ad4792@solutions.advantagedatabase.com...
>> Ive had it working, but using the same setup it would not work. I tired
>> with only the primary key, a combination of 2 fields and even all fields.
>> It just wont fire for some reason. Its really frustrating because at
>> first it was firing all the time and when i figures out the issues i was
>> having and got stuff to update, from then on it wouldnt not fire any
>> more.
>>
>
>