Itgenisr006: issue with synchronize & except?

[sda]

Having the following SQL-statement:

synchronize eol_count@inmemorystorage
to          timestamp_table@inmemorystorage
with update all except $rowid
identified by Division;

raises:

itgenisr006:
It is not allowed to update the primary key column on in-memory table ‘TIMESTAMP_TABLE’.

The only primary key column is $rowid, and should be excepted.

This has been running for months.

Since recent upgrade to QueryTool 24.0.389 it seems the update all except $rowid might be broken?

the full script is here sharing-an-incremental-script-for-sync-between-exact-online-and-sql-server

[forums]

Is it possible to make a smaller reproduction scenario like for instance this one?

create or replace table src@inmemorystorage
as
select rge.value Division
,      random() Something
from   range@DataDictionary(10) rge

create or replace table dst@inmemorystorage
as
select rge.value Division
,      random() Something
from   range@DataDictionary(5) rge

synchronize src@inmemorystorage
to          dst@inmemorystorage
with update all except $rowid
identified by Division

This script does not raise an error on 24.0.392.

[sda]

This does not raise error on 24.0.389.

but more interesting :

synchronize src@inmemorystorage
to          dst@inmemorystorage
with update all 
identified by Division

doe not raise error neither.

[sda]

The same issue appears on the newly released Invantive Data Hub net8.0 24.0.445.

My previous (working) version was Invantive Data Hub net8.0 24.0.109

Some deviation has been introduced in between ?

[forums]

The scripts in the linked topic have been puzzled together, and dummy tables were created out of them to factor out Exact Online. This is the script used:

create or replace table timestamp_table@inmemorystorage 
( sql_lastTimestamp      int64
, sql_count              int
, eol_count              int
, eol_lastTimestamp      int64
, insertupdate           int
, to_insert              int
, to_update              int
, deleted                int
, to_delete              int
, to_modify              int
, division               int
, count_records_deleted  int
, count_records_inserted int
);

insert
into   timestamp_table@inmemorystorage
( sql_lastTimestamp
, sql_count
, eol_count
, eol_lastTimestamp
, insertupdate
, to_insert
, to_update
, deleted
, to_delete
, to_modify
, division
, count_records_deleted
, count_records_inserted
)
values
( 1234
, 100
, 200
, 1235
, 1
, 2
, 3
, 4
, 5
, 6
, 1234
, 8
, 9
);

create or replace table eol_count@inmemorystorage
as
select 1234 division
,      5678 eol_count
from   dual@datadictionary
;

synchronize eol_count@inmemorystorage
to          timestamp_table@inmemorystorage
with update all except $rowid
identified
by          division
;

This indeed reproduced the itgenisr006 error as you indicated.

Upon further inspection, a typo was found in the column name in the except clause. The primary key column name of in-memory tables is rowid$, not $rowid as in the script. Changing the name in the except resolves the error.

We will investigate if we can improve the error message given that sync supports both uni-directional and bi-directional synchronization, so the column name specified in the except might not be found on both ends.

[sda]

thanks !

raising an error message if column is not found in one or both end might be usefull for helping young Padawans not to make such typos errors