Hi,

please give me a help.

LOAD UPDATES doesn't work - without Errors

That is what i do:
On a local PC i create an update with :
     SQLQuery.SQL.Clear;
     SQLQuery.SQL.Add('SAVE UPDATES FOR DATABASE "BASE_DB"');
     SQLQuery.SQL.Add('AS UPDATE1');
     SQLQuery.SQL.Add('TO STORE "ImExport"');
     SQLQuery.SQL.Add('TABLES "Alias","Beschlagart","Blob","CInfo"');
     SQLQuery.SQL.Add(',"Einbauart","Frontart","Geometrie","Geraet"');
     SQLQuery.SQL.Add(',"GeraetColor","Geraeteart","Hersteller","HerstellerColor"');
     SQLQuery.SQL.Add(',"Meldung","Nation","Quelle","Streams","Varianten","User"');
     SQLQuery.SQL.Add('COMPRESSION 9');
     SQLQuery.SQL.Add('IF NOT EMPTY');
     SQLQuery.Prepare;
     SQLQuery.ExecSQL;
It work's fine.
The StatusMessages from TEDBQuery are:
> Saving updates for the database ZENTRA_BASE_DB...
> The database ZENTRA_BASE_DB was saved successfully

Now i copy the created update-file to an File-Server in Internet (by an automatic-routine from my application)

The next step:
An other PC download the update-file into a local store.
Then i try:

       SQLQuery.SQL.Clear;
       SQLQuery.SQL.Add('LOAD UPDATES FOR DATABASE "BASE_DB"');
       SQLQuery.SQL.Add('FROM UPDATE1');
       SQLQuery.SQL.Add('IN STORE "ImExport"');
       SQLQuery.SQL.Add('TABLES "Alias","Beschlagart","Blob","CInfo"');
       SQLQuery.SQL.Add(',"Einbauart","Frontart","Geometrie","Geraet"');
       SQLQuery.SQL.Add(',"GeraetColor","Geraeteart","Hersteller","HerstellerColor"');
       SQLQuery.SQL.Add(',"Meldung","Nation","Quelle","Streams","Varianten","User"');
       SQLQuery.SQL.Add('IGNORE MISSING UPDATES');
       SQLQuery.SQL.Add('DISABLE TRIGGERS');
       SQLQuery.Prepare;
       SQLQuery.ExecSQL;

It needs a few seoconds.
It seems it works:
The StatusMessages from TEDBQuery are:
> Loading updates for the database ZENTRA_BASE_DB...
> The database ZENTRA_BASE_DB was loaded successfully

But it doesn't work. No changes, no inserts. no deletes, no errors
On both PC i used, for the test, the role "administrator"
The used engine is 2.18 Build 3
The application is created with Delphi XE4
All of the used tables are active, not readonly and not filtered
I tried this with very small updates with 30 kb up to bigger updates with 80 mb

What do i wrong??

Thanks for your help

On 4/25/2015 7:14 AM, Martin Pflug wrote:

>> Saving updates for the database ZENTRA_BASE_DB...
>> The database ZENTRA_BASE_DB was saved successfully

Do your tables actually have any updates ?

SAVE UPDATES saves only changes since last SAVE UPDATES. You did mention
that you have updates from 30K to 80MB so i will assume your updates do
include data but just something to keep in mind - if you run multiple
save updates in a row then latter ones might be empty.

> But it doesn't work. No changes, no inserts. no deletes, no errors

How about creating a table containing the update file content to see
what's in it (i.e. CREATE TABLE "MyUpdates" FROM UPDATES "UPDATE1" IN
STORE "ImExport"). That would be my starting point to see what is going on.

The only other thing i can suggest is to check that updates are loaded
in the order they were saved so don't load a newer update first and then
older since the internal timestamp has already been updated so none of
the older updates will apply.

Final option i can think of is to start fresh with the table you're
loading the updates into.

The article on replication has some good general steps  :
http://www.elevatesoft.com/articles?action=view&category=edb&article=building_sales_quote_replication_system_elevatedb

Raul

if you don't want to read all this, look at the end, there is the solution

@ Raul ... Thank you

> CREATE TABLE "MyUpdates" FROM UPDATES "UPDATE1" IN
> STORE "ImExport"
now i have create this table. Thanks for this tip. It looks good.
All of my insert, update and delete are there.

But still it doesn't work

For better understanding:

My application is like a catalog.
Changes are made only at one PC.
First the data at the clients are identical. (a copy of the DataBase)
Then i change some data. (Pictures included).
After that (every day) i create an update (with: SAVE UPDATES FOR DATABASE ...)
It works fine.
I create a copy of this update at a file-server in internet

Now the client download this update.
I'm sure they (uplodaded file and downloaded file) are identical.
After that the client have to import the update (with: LOAD UPDATES FOR DATABASE ...)
The StatusMessage: "The database ZENTRA_BASE_DB was loaded successfully"

But no, it isn't sussessfully!!
Nothing is changed, deleted or insert. And there are no errors. I tried it also in DebugMode with Delphi XE4. It's the same.

The Manifest (a field in the update-table) and the puplished_id at both computer are the same. Maybe that's the problem? The client Data are at first time only a copy of the original. Therefore there are also published. I don't need that, but because it is a copy ...

I have tried it. That's the solution.
the tables they should be updated ... must not be set to status published

Thank you

Martin

When you wish to use replication in EDB database the process is not totally simple. Some key steps must be taken:

1. Set up "master database".

2. UNPUBLISH this database (if it is published at that moment) so that any updates which are present in that database are expunged and a "clean" database with no update-data is saved to a Backup File.

(Note if you have a live system, and you want not to lose data between step 1. and 2. you must also call SAVE UPDATES to generate an update file, otherwise changes in the master database will not be replicated to other databases in the replication network)

3. RESTORE the database from step 2., now comes the critical step: The RESTORE statement must include the section: RESET PUBLISHED TABLES. This step changes the GUIDs used in the update process so that the new database is recognised as different from the "master".

4. Call "PUBLISH" on the new database.

5. Call "PUBLISH" on the "master".

Now updates from master and new database should save with different manifests, ensuring that when they are LOADED the new contents from each will update the other.

--

Other pointers:

* It is really important that all update files are loaded in date order, never apply an update from today before one from yesterday.

* Try to ensure that the database structure itself is conscious of the update process. Tables with fairly few fields and more rows are easier, with the focus on adding rows when data is added. The is because multiple users editing 1 row in 1 table on different machines and then trying to replicate will lead to clashes.

I hope this is useful.

Good luck

One more thing.

Be very careful with CONSTRAINTS, Primary and Unique Keys.

Say the user on the master adds a record in the "People" table with a "Name" = 'Adam Brett',

Before the next round of updates a user on the new database does the same thing.

If the People table has a Unique Key or other unique CONSTRAINT on the Name field of the People table the LOAD UPDATE will fail, as the row from the new database will create a conflict.

Or say there is a CONSTRAINT which links 'Adam Brett' to a record in another table in the database. If the master table deletes 'Adam Brett's' record (and the child records he is linked to) but at the same time the new database adds a child record with the FOREIGN KEY 'Adam Brett' when updates are saved the LOAD UPDATE statement will fail.

Hope this is useful.

Adam Brett wrote:

<<One more thing.

Be very careful with CONSTRAINTS, Primary and Unique Keys.

Say the user on the master adds a record in the "People" table with a "Name" = 'Adam Brett',

Before the next round of updates a user on the new database does the same thing.

If the People table has a Unique Key or other unique CONSTRAINT on the Name field of the People table the LOAD UPDATE will fail, as the row from the new database will create a conflict.

Or say there is a CONSTRAINT which links 'Adam Brett' to a record in another table in the database. If the master table deletes 'Adam Brett's' record (and the child records he is linked to) but at the same time the new database adds a child record with the FOREIGN KEY 'Adam Brett' when updates are saved the LOAD UPDATE statement will fail.

Hope this is useful.>>

Adam,

Those are good points to consider.
To summarize, you're saying if a constraint is violated during the Load Update, the entire Load Update is rolled back?

1) When that happens, how do you fix it so the rest of the data that doesn't violate a constraint gets loaded? (All the rows are in the same update file)

I would have preferred an option so that when executing a Load Update that an exception Update file is created for only those rows that failed and for the rest of the updates to be applied to the database.

2) Does anyone know how other databases handle constraint violations during replication? Is it all or nothing?

Barry

Barry,

<< 1) When that happens, how do you fix it so the rest of the data that
doesn't violate a constraint gets loaded? (All the rows are in the same
update file)

I would have preferred an option so that when executing a Load Update that
an exception Update file is created for only those rows that failed and for
the rest of the updates to be applied to the database. >>

LOAD UPDATE triggers are what you use to handle this:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=CREATE_TRIGGER

You can use the ABORT statement to tell the update loading to ignore the
current incoming update.

However, you need to be careful with this - one can easily cause a series of
cascading failures if subsequent updates rely on the current update being in
place (INSERT->UPDATE or DELETE).

Tim Young
Elevate Software
www.elevatesoft.com