The best way to import(merge)-export java db database

Question

I have let's say two pc's.PC-a and PC-b which both have the same application installed with java db support.I want from time to time to copy the data from the database on PC-a to database to PC-b and vice-versa so the two PC's to have the same data all the time. Is there an already implemented API in the database layer for this(i.e 1.export-backup database from PC-a 2.import-merge databases to PC-b) or i have to do this in the sql layer(manually)?

Answer 1

Hi,

I guess you are using Java DB (aka Derby) - in which case, assuming you just can't use a single instance, you can do a backup/restore.

Answer 2

Why dont you have the database on one pc. and have all other pc's request data from the host pc

Answer 3

As you mention in the comments that you want to "merge" the databases, this sounds like you need to write custom code to do this, as presumably there could be conficts - the same key in both, but with different details against it, for example.

Answer 4

In short: You can't do this without some work on your side. SalesLogix fixed this problem by giving everything a site code, so here's how your table looked:

Customer: 
   SiteCode varchar,
   CustomerID varchar, 
   .... 
   primary key(siteCode, CustomerID)

So now you would take your databases, and match up each record by primary key. Where there are conflicts you would have to provide a report to the end-user, on what data was different.

Say machine1:

   SiteCode|CustomerID|CustName     |phone         |email 
 1 XXX     |0001      |Customer1    |555.555.1212  |[email protected]

and on machine2:

   SiteCode|CustomerID|CustName     |phone         |email 
 2 XXY     |0001      |customer2    |555.555.1213  |[email protected] 
 3 XXX     |0001      |customer1    |555.555.1212  |[email protected]

When performing a resolution:

• Record 1 and 3 are in conflict, because the PK matches, but the data doesnt (email is different).
• Record 2 is unique, and can freely exist in both databases.

There is NO way to do this automatically without error or data corruption or referential integrity issues.