Nages, 2 diferent Db's update

Nages, 2 diferent Db's update

Postby Adolfo » Tue Jan 16, 2024 1:39 pm

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  RUN

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
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
User avatar
Adolfo
 
Posts: 860
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile

Re: Nages, 2 diferent Db's update

Postby nageswaragunupudi » Wed Jan 17, 2024 6:06 pm

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  RUN
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  RUN
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  RUN
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.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10655
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Nages, 2 diferent Db's update

Postby Adolfo » Sun Jan 21, 2024 3:26 pm

Thanks a lot Nages.

I'll make some tests

From Chile
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
User avatar
Adolfo
 
Posts: 860
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: nageswaragunupudi and 35 guests