FWH 18.11: DBF to MySql Replication with TDataBase

[nageswaragunupudi]

TDataBase class in FWH 18.11 version provides a new feature to replicate all changes (append,edit,delete) made to the DBF to a remote MariaDB server.

While we may take backups daily, we are always at the risk of losing transactions between successive backups. This feature helps backing up each transaction as and when it occurs.

Requisites:
1) The DBF should contain an AutoIncrement field.
2) Exact replicated table with the same name should exist on the replication server. Initially, this can be easily setup by calling

Code: Select all  Expand view  RUN

oCn:ImportFromDBF( cDBF )
 

How to replicate?
1) Open connection to the MySql server

Code: Select all  Expand view  RUN

oCn := maria_Connect( server, database, user, password )
 

Open DBF with TDatabase as usual and set Replication server with the new method.

Code: Select all  Expand view  RUN

oDbf := TDataBase():Open( , cDbf )
oDbf:SetReplicationServer( oCn ) // New in FWH 18.11
 

Now the replication is automatic.

fwh\samples\dbf2sql.prg

Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local oCn      := FW_DemoDB()
   local cDbf     := "DBF2SQL.DBF"
   local aStruct  := { { "ID", "+", 4, 0 }, { "FLDCHR", "C", 20, 0 }, { "FLDNUM", "N", 8, 2 } }
   local oDbf, oRs

   local oDlg, oFont, oBold, oBrwDbf, oBrwRs

   SET DELETED ON
   RDDSETDEFAULT( "DBFCDX" )

   SetGetColorFocus()

   // Check Tables
   if !File( cDbf )
      DBCREATE( cDbf, aStruct, "DBFCDX" )
      oCn:ImportFromDBF( Lower( cDbf ) )
   endif

   oDbf  := TDataBase():Open( , cDbf, "DBFCDX" )
   oDbf:SetReplicationServer( oCn )

   // We need not open MySql table.
   // But we open it in the sample to display replication
   oRs   := oCn:RowSet( oDbf:cTable,,.t. ) // readonly

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   oBold    := oFont:Bold()

   DEFINE DIALOG oDlg SIZE 900,500 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "FWH 18.11 : DBF TO MYSQL REPLICATION DEMO"

   @ 60, 20 XBROWSE oBrwDBF SIZE 425,-20 PIXEL OF oDlg ;
      DATASOURCE oDbf ;
      AUTOCOLS CELL LINES NOBORDER FASTEDIT

   @ 60,455 XBROWSE oBrwRS SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRs ;
      AUTOCOLS LINES NOBORDER

   WITH OBJECT oBrwDbf
      :SetGroupHeader( cDBF, 1, 3, oBold )
      :nEditTypes    := EDIT_GET
      :bOnChanges    := { || oRs:ReQuery(), oBrwRS:Refresh() }
      :bOnRefresh    := { || oRs:Requery(), oBrwRS:Refresh() }
      //
      :CreateFromCode()
   END

   WITH OBJECT oBrwRs
      :SetGroupHeader( "MYSQL SERVER TABLE", 1, 3, oBold )
      :nMarqueeStyle := 0
      :bGotFocus     := { || oRs:ReQuery(), oBrwRS:Refresh() }
      //
      :CreateFromCode()
   END

   @ 10, 20 BTNBMP PROMPT "New" SIZE 100,30 PIXEL OF oDlg FLAT ;
      ACTION oBrwDbf:EditSource( .t. )

   @ 10,140 BTNBMP PROMPT "Edit" SIZE 100,30 PIXEL OF oDlg FLAT ;
      ACTION ( oBrwDBF:EditSource(), oRs:Requery(), oBrwRs:Refresh() )

   @ 10,250 BTNBMP PROMPT "Delete" SIZE 100,30 PIXEL OF oDlg FLAT ;
      ACTION oBrwDBF:Delete()

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont, oBold

   oDbf:Close()
   oRs:Close()
   oCn:Close()

return nil
 

[anserkk]

This will eliminate the need for a replication server. With this you have 2 copies of the live data on 2 different databases, with minimal code or with just one additional line of code. This will be a good solution for those looking for inexpensive data redundancy.

[Otto]

Dear Mr Rao
thank you for you work.
This new feature will make the synchronization of my off- and online- planner much easier and more real time.
The new method opens so much possibilities.
Passing data to online APPs and web pages seems to be a mega trend.


Mr Rao I think “MySql Replication with TDataBase” should be a session at the 2nd FWH + [x]Harbour 2019 international conference in Sillian - #announcement.

Best regards
Otto

[RAMESHBABU]

Nageswara Rao gaaru,

As Mr.Otto said,

This new feature will make the synchronization of my off- and online- planner much easier and more real time.
The new method opens so much possibilities.
Passing data to online APPs and web pages seems to be a mega trend.

We have mobile applications related to our software to publish data through Dashboard. Presently we are converting
data into CSV files from .DBF files and uploading to our FTP Account. As and when a new CSV is uploaded, the required
Tables in our SQL Database are updated through a Crone job. Once the SQL database is updated, all the reports are
refreshed published in Mobile Apps.

I am looking for this mechanism since a long time to avoid conversion of desktop application data into CSV Files, uploading
into FTP and updating Sql databases.

This new feature will help me alot to keep a real time backup in the web in Sql database and the same database
can be used directly to manage our Mobile apps with out running any duplicate jobs.

Thank you sir for this great feature.

I hope most of our friends here in the forum find this new feature as a very useful one.

-Ramesh Babu

[Admpss]

can I replicate part of the BD, such as file with Name, address, phone, value, local database and remotely replicate the same database, with the same name, but only the name and value fields? thanks

[nageswaragunupudi]

can I replicate part of the BD, such as file with Name, address, phone, value, local database and remotely replicate the same database, with the same name, but only the name and value fields? thanks

Not possible. Structures of both the table should be identical. Also, there should be an auto-increment field in the tables.