Insert Into MySQL tables in different connections .. you can

[Enrrique Vertiz]

Dear, greetings

I work with MySQL and I want "via program FWH" to copy / replicate a table, from a local database to a remote database, that is 2 different databases in 2 different connections.

Currently I do it via Append From (crazy, hours hours delay), I just changed to __dbTrans (I'm testing the speed, it seems faster, BUT for this case it's still slow going 30 minutes and does not go up or 5,000 records of a table with 50 columns and 13 thousand records in total).

I'm sure that any direct SQL statement would be faster, but I do not know how to do it ??

Thank you for your attention

[nageswaragunupudi]

If you have recent FWH, using built-in FWMARIADB, it is simple and faster. 13000 records is small.

Code: Select all

   // Create Table on Remote Server if not already exists
   if .not. oRemote:TableExists( cTable )
      cSql  := oLocal:CreateTableSQL( cTable )
      oRemote:Execute( cSql ) 
   endif
   
   // now copy records
   oRemote:SetAutoCommit( .f. )
   oRemote:Insert( cTable, nil, oLocal:Execute( "SELECT * FROM " + cTable ), .f. )
   oRemote:SetAutoCommit( .t. )

 

[Enrrique Vertiz]

Thanks to all

Mr. Rao, I Try, Thank You

[Enrrique Vertiz]

Mr. Rao

Excellent, is very fast, thank you

[Busmatic_wpb]

Good afternoon
in that example is for FWMARIADB, I use William MySQL's library, I could create the tables in the remote server but I can not transfer the data. some help to implement it and work, Thanks.

[Enrrique Vertiz]

Mr. Rao

It is possible to place a Meter in the following sentence :

oRemote:Insert( cTable, nil, oLocal:Execute( "SELECT * FROM " + cTable ), .f. )

Si el insert es de 1,000,000 de registros o mas, seria bueno que se muestre el proceso ...

Thank You

[Enrrique Vertiz]

Mr. Rao

It is possible to place a Meter in the following sentence :

oRemote:Insert( cTable, nil, oLocal:Execute( "SELECT * FROM " + cTable ), .f. )

If the insert is 1,000,000 records or more, it would be good to show the process ...

Thank You

[nageswaragunupudi]

It is not a good idea to deal with all records in one single statement. The data and the size of sql statement exceeds the max_allowed_packet_size of the connection and so does not work. In such cases we better split the job into smaller parts and insert in a loop.

Example
n := 1
do while .t.
aData := oLocal:Execute( "select * from table limit " + LTrim( Str( n ) ) + ",1000" )
nSize := Len( aData )
if nSize > 0
oRemote:Insert( table, nil, aData )
// display meter
endif
if nSize < 1000
exit
else
n += 1000
endif
enddo

[Enrrique Vertiz]

Hi Mr. Rao

And what would be the sentence if I wanted just to make an INSERT of the new records?

// now copy records, Copy All
oRemote:SetAutoCommit( .f. )
oRemote:Insert( cTable, nil, oLocal:Execute( "SELECT * FROM " + cTable ), .f. )
oRemote:SetAutoCommit( .t. )

I need only copy new records ...

Thank You

[nageswaragunupudi]

You want to copy new records only. In other words, you want to copy those records which exist in the local server but not in the remote server.

Method-1:

I assume you have a primary key for the table, or atleast a unique key.
In this case the above statement

Code: Select all

oRemote:Insert( cTable, nil, oLocal:Execute( "SELECT * FROM " + cTable ), .f. )
 

works. We read the entire table from the local server and send the entire table to the remote server. The remote server then checks if the primary/unique key already exists. If exists, it ignores and if not it inserts the new record.

Method-2:

The following code is similar but behaves a little diffently

Code: Select all

oRemote:Insert( cTable, nil, oLocal:Execute( "SELECT * FROM " + cTable ), .t. )
 

Here also we send the entire table to the remote server. If the primary/unique does not exist, it inserts the new record and if exists, if updates the existing record with the new data. In case any changes are made subsequently to the records already exported, these changes are also exported to the remote server.

Method-3:

Assume you have a primary key, which is an autoinc field. Also assume the name of the field to be ID.
It is possible to read and send only those records which are new. This saves lot of time and network traffic.

Code: Select all

nMaxId := oRemote:QueryResult( "select max(id) from " + cTable )
oRemote:Insert( cTable, nil, oLocal:Execute( "SELECT * FROM " + cTable + " WHERE id > ?", { nMaxId } ), .f. )
 

Method-4:
You do not have primary or unique key. (very bad programming practice)

Zap the table on the remote and export the full table again

You can now choose the method that suits your case.

[Enrrique Vertiz]

Thank you very much, Mr. Rao
I try

[Enrrique Vertiz]

Hello Mr. Rao, only in Method-3 display error :

Error description: Error BASE/1132 Error de rango: acceso al array
Args:
[ 1] = A { ... } length: 0
[ 2] = N 1

My source code :

nMaxId := oRemote:QueryResult( "select max(sql_rowid) from " + diario )
oRemote:Insert( diario, nil, oLocal:Execute( "SELECT * FROM " + diario + " WHERE sql_rowid > ?", { nMaxId } ), .f. ) // Error display in this line

Please help, Thank You

[nageswaragunupudi]

That could mean there are no rows to be exported.
Please try

Code: Select all

nMaxId := oRemote:QueryResult( "select max(sql_rowid) from " + diario )
aRows  := oLocal:Execute( "SELECT * FROM " + diario + " WHERE sql_rowid > ?", { nMaxId } )
if !Empty( aRows )
   oRemote:Insert( diario, nil, aRows, .f. ) // Error display in this line
endif