CODEWITHSUNDEEP
c#mysqlvisual-foxprodbase
Hikaros
Hikaros

Reputation: 121

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

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?

Upvotes: 1

Views: 838

Answers (2)

Juliano Terterola
Juliano Terterola

Reputation: 41

A long time ago, I've made something like this using the following approach: I created triggers to log to a table the ID of the records manipulated. Then I set a periodic routine to run every few minutes, reading the log e sending the actual records values to a Postgres destination database in the cloud.

First, check if the tables in DBC have some way to identify each record uniquely, you can try to create triggers on these tables, capturing every time they have been changed (adding, updating, or deleting records) and logging the identification of that record (probably the value of the table's primary key) to a LOG table. This log table could be like this:

SYNC_LOG.DBF fields: table (Char) key (Char or Int or what else the system uses) action (Char) "Insert" or "Update" or "Delete" Processed Boolean (false or true)

So, every time a record in the same table is inserted, updated, or deleted, the triggers log the action to SYNC_LOG.DBF setting the "processed" field to FALSE.

If a log for that record already exists and it is NOT processed yet, the trigger doesn't need to create a new log. Defining this rule guarantees the log file won't get bigger indefinitely.

This is the first part. The second is to create a routine that will check de log file periodically and send commands to update the de-destination database.

All these can be done easily 100% in VFP, but I don't know your knowledge with the fox.

Upvotes: 1

Stefan Wuebbe
Stefan Wuebbe

Reputation: 2149

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

Upvotes: 2

Related Questions