FoxPro Programming
Realtime sync of DBF and SQL tables
Gravatar is a globally recognized avatar based on your email address. Realtime sync of DBF and SQL tables
  Larry M
  All
  May 22, 2020 @ 03:26pm

I'm not sure which forum to post this question to. We have decades worth of in-house VFP applications still using DBFs as the primary data source. After years of failed .NET re-engineer efforts, and not wanting to rewrite all of the VFP code to use SQL instead of the DBF files, we are trying to find a way to keep the DBF and SQL tables synced in real time. We already read and write to SQL, but about 70% of the production data gets copied overnight using SSIS packages, and any new .NET developement has to keep both the SQL and DBF files updated. Don't know what the solution is, we are in this situation now because the .NET replacement for VFP was always "right around the corner" for the last 15 years. Do you offer any products that would facilitate the real time data sync?

Gravatar is a globally recognized avatar based on your email address. re: Realtime sync of DBF and SQL tables
  Rick Strahl
  Larry M
  May 22, 2020 @ 06:12pm

I'm not sure that such a thing exists. You can perhaps look into SQL Server replication with external data sources connected to DBF tables, but I think that'll end up being way more complicated and probably not very reliable especially with legacy data.

It sounds to me based on your description that your way forward is to migrate the data to SQL Server so that you can more easily manage data both in a FoxPro application and other non-Fox applications.

This isn't transparent obviously, but it is a way to get to a consistent data environment that's more easily shared. I don't think there are any shortcuts. Any sort of synchronization scheme is likely to have problems with data conflicts and the potential for data corruption in that scenario is pretty high. Even if there were tooling for that I'd be hard pressed to trust it - anything but a specific sync solution that knows about your business rules seems like it would be incomplete.

No easy answers here in my opinion.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Realtime sync of DBF and SQL tables
  FoxInCloud Support - Thierry N.
  Larry M
  May 23, 2020 @ 06:46am

Hi Larry,

We at FoxInCloud have something that may fill your need: a VFP database synchronisation class that works over the LAN or the Internet. So far it works with VFP tables on both ends, however the critical code that really updates data is 300 LOC out of 4,000 so it should not be so hard to adapt to a SQL database on either end.

It's been running in production for over 10 years so I can assume it's bullet-proof

Here is it does:

  • syncs the tables from the top to bottom of the table hierarchy
  • rollbacks the whole sync if any error occurs
  • matches records on primary or candidate key (the latter case being useful of both ends have different primary keys schemes)
  • handles the linked files (identify file pathes and sync the file based on size and/or date)
  • logs everything in details
  • can send e-mails in case of a fatal error
  • client and server run the same class, client initiates the sync.
  • you can ignore some tables and/or fields

Here is it what you need:

  • Each table must have (no constraint on names):
    • a primary or candidate key (unique)
    • a field "last modification date-time" T
    • a field "last modified by synchronization" L
  • Relations between tables must be declared (data integrity rules not needed)

Of course, if some table structure differ on both sides, we'll need to add some mapping rules.

You can sync as a scheduled task as often as you want. Conflicts may occur at the record level; in case of a collision, one side has the 'last word' and overwrites the other side. Combined with very frequent syncs, this usually makes no problem; If it did in your case we could log the conflicts to be fixed manually before doing a successful sync.

I can post the class signature if a closer insight is of interest to you.

years of failed .NET re-engineer efforts

With FoxInCloud you can move your VFP app. to the Web and slightly move your server to another technology. I other words, rather than moving both client and server (and perhaps database) to another tech (doomed to fail, known for decades), you can move your app piece by piece.

© 1996-2020