topic-44118

Board index FiveWin for Harbour/xHarbour Nages, 2 diferent Db's update

Posts: 883

Joined: Tue Oct 11, 2005 11:57 AM

Nages, 2 diferent Db's update
Posted: Tue Jan 16, 2024 01: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 (fw): Select all Collapse

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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Nages, 2 diferent Db's update
Posted: Wed Jan 17, 2024 06: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 (fw): Select all Collapse

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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

Adolfo

Posts: 883

Joined: Tue Oct 11, 2005 11:57 AM

Re: Nages, 2 diferent Db's update
Posted: Sun Jan 21, 2024 03: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

FiveTech Support Forums

Continue the discussion