How do you replicate a Dbase database to MYSQL in real time?

Question

I have a massive dBase DB (Visual FoxPro vfp). I need to replicate it to a Mysql DB in real time.

To help, I made a c# service to dump the entire information from specific tables from dBase to Mysql by doing a full delete in each Mysql table and then insert everything back again from each dBase DB table. However, this can hardly be called replication and it's performs extremely poorly for larger dBase tables.

I am unable to modify the program using the VFP file directly. I can only access the database files themselves.

How can I achieve said task in an efficient way, in real or near real time?

Answer 1

Actually VFP *.DBC Database Containers do support Triggers. In the Vfp IDE you can use the Modify Database command and then act interactively in the Database Designer or

 CREATE TRIGGER ON myTable FOR DELETE|INSERT|UPDATE AS myTrigger()

in the Command Window. See also create a trigger for update, delete or insert in FoxPro Table and Create FoxPro Trigger

[Edit] Inside the Triggers' Stored Procedure code you can use FoxPro SQL Pass-Through SPT code namely VfP's SQL*() functions like SQL(String)Connect(), SQLExec() or Remote Views or CursorAdapters that would send (parametrized) SQL statements to your MySQL server.

Example:

LOCAL lcConnString
IF m.llSuccess
    lcConnString = ;
        [DRIVER=MySQL ODBC 5.1 Driver;] + ;
        [USER=] + m.lcUser + [;] + ;
        [PASSWORD=] + m.lcPwd + [;] + ;
        [DATABASE=Test;] + ;
        [SERVER=] + m.lcServer+ [;] + ;
        [OPTION=3;]
ENDIF

PUBLIC h
IF m.llSuccess
    h = SQLSTRINGCONNECT(m.lcConnstring)
    llSuccess = ( h > 0 )
    lcErrorMessage = "Connection failed."
ENDIF

Local lnPk, lnValue, lcSQL, lnSuccess, laSQLErrors[1]
IF m.llSuccess
    lnPk = 15887
    lnValue = 15
    lcSQL = "Insert Into test (primaryKey, testInt) Values (?m.lnPk, ?m.lnValue)"
    lnSuccess = SqlExec(h,m.lcSQL)
    llSuccess = ( m.lnSuccess > 0 )

    IF m.lnSuccess < 1
        AERROR(laSQLErrors)
        lcErrorMessage = ;
            TRANSFORM(laSQLErrors[1]) + ", " + ;
            TRANSFORM(laSQLErrors[2])
    ENDIF
ENDIF

IF h > 0
    SQLDISCONNECT(h)
ENDIF

As for the commercial CData 3rd party tool, they offer an xBase/ dBase driver https://www.cdata.com/drivers/xbase/ as well as a "Universal Data Pipeline" Replication tool that I have not tried myself yet: https://www.cdata.com/sync/ , https://www.cdata.com/sync/#purchase