Nages, 2 diferent Db's update

[Adolfo]

Hi fivewinners
I have 2 Mysql Db's, same structure, same name but in separate servers.

Client needs to update or insert data from one server to another

I did it like this

Code: Select all  Expand view

FWCONNECT oSvr HOST "localhost" USER "userx" PASSWORD "passx" DB "xDb"
FWCONNECT oRem HOST "otherhost" USER "userx" PASSWORD "passx" DB "xDb"

       oDbSend:=oSvr:Rowset("select codigo,data1,data2 from table1 where condition")
        nTotal:=oDbSend:RecCount()

        If nTotal > 0
           For x=1 to nTotal
               oRem:Execute( "update table1 set data1=" + oDbSend:data1 + ", data2= + " + oDbSend:data2 + "  where codigo='" + oDbSend:codigo + "'" )
               oDbSend:Skip()
           Next
        Endif
        oDbSend:End()

 

It works fine, BUT, is there any other way, like this in FWH/MARIADB

update oRem.table1 set table1.data1=oSvr.table1.data1 where condition

I saw a sample on a mysql forum, but I can't make it work

UPDATE database_a.clients A INNER JOIN database_b.clients B ON A.id = B.id SET B.email = A.email


Any help will be appreciated

From Chile. Adolfo

[nageswaragunupudi]

If the table on both the servers has exactly the same structure, same primary key and all fields in the same order:

Code: Select all  Expand view

aData := oLocalServer:Execute( "select * from table [where <cond>]" // returns array of values)
oRemoteServer:Upsert( cTable, nil, aData )

In addition to INSERT and UPDATE, MySql provides UPSERT:
To insert if the primary key is not found and update if the primary is found,

We can do UPSERT using either of the methods:

Code: Select all  Expand view

oCn:Upsert( table, [aFieldNames], aValues)
// or
oCn:Insert( table, [aFieldNames], aValues, .T. )  // .T. indicates UPSERT

Now, this insert/update happens with all the rows (selected by where clause).
If this is a regular operation and we like to transfer from local to remote only those records inserted or modified recently, the process can be optimized if we have a timestamp fields which is updated with every insertion and modification.
In such a case, we can simply use this method and do the entire operation with a single line of code:

Code: Select all  Expand view

oLocalServer:UpdateTableToServer( cTableName, oDestServer, cTimeStampFld ) // --> lSuccess

In this case, only those records modified or inserted subsequent to previous transfer are only transferred to the remote server,

Please let us know if you like to use this approach and we will help with more information.

[Adolfo]

Thanks a lot Nages.

I'll make some tests

From Chile