Comparing two databases

[Igor de Oliveira Couto]

On 29/04/2013, at 7:58 AM, Magicgate Software - Skip Kimpel <skip at magicgate.com> wrote:

> What would be the best approach for this in LC?  One
> is a remote MySQL DB and the other is a local Access DB.

I have not done any work with Access, so I don't know what its capabilities are. Nevertheless, here are a couple of suggestions:

1) If both databases have an SQL interface, you can actually do this entirely in SQL - that is: you can write SQL scripts that do the table comparison and update you need, and then get your LiveCode app/script to just run it, when needed. This would be a good solution *if* both your databases understand SQL, and you are a reasonably good SQL programmer.

2) A more often-used technique, however, is to dump the data from both databases into files, and just do a file comparison to see what data needs to be updated. Every database has a way of exporting its data in *some* simple text format - ie, SQL, Excel, or even plain-old tab-delimited text. Whatever the format, it is then fairly trivial - even fun - for you to write a comparison routine that compares the files according to your needs, and generates an update file for you. This solution has the advantage of being infinitely flexible: you can create a custom interface to configure your update routine, you can alter and 'massage' the data before you update it, add even combine it with data from other sources - and LiveCode is a great tool for that.

I hope this helps!

--
Igor Couto
Sydney, Australia