Insert Into MySQL tables in different connections .. you can

Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Fri Dec 08, 2017 10:54 pm

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
Enrrique Vertiz Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby nageswaragunupudi » Sat Dec 09, 2017 9:13 am

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

Code: Select all  Expand view
  // 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. )

 
Regards

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

Re: Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Sat Dec 09, 2017 4:05 pm

Thanks to all

Mr. Rao, I Try, Thank You
Enrrique Vertiz Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Thu Dec 14, 2017 10:06 am

Mr. Rao

Excellent, is very fast, thank you
Enrrique Vertiz Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby Busmatic_wpb » Sat Dec 16, 2017 2:38 am

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.
Regards.
S.I.T.U.
Sistemas Inteligentes de transporte urbano
http://www.situcr.com
oscarchacon@Situcr.com
Desarrollos BA4/B4j androide
User avatar
Busmatic_wpb
 
Posts: 162
Joined: Wed Feb 22, 2017 2:19 am

Re: Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Mon Dec 18, 2017 3:46 am

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 Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Mon Dec 18, 2017 3:47 am

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
Enrrique Vertiz Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby nageswaragunupudi » Mon Dec 18, 2017 5:19 am

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
Regards

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

Re: Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Thu May 03, 2018 12:20 am

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
Enrrique Vertiz Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby nageswaragunupudi » Thu May 03, 2018 4:22 am

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  Expand view

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  Expand view

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  Expand view

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

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

Re: Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Thu May 03, 2018 6:38 am

Thank you very much, Mr. Rao
I try
Enrrique Vertiz Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby Enrrique Vertiz » Tue May 08, 2018 5:30 am

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
Enrrique Vertiz Pitta
Lima-Peru
xHb 1.23.1026X, Fwh 23.04, MySQL 8.0.X, SQLLIB 1.9m, SQLRDD
Enrrique Vertiz
 
Posts: 511
Joined: Fri Oct 07, 2005 2:17 pm
Location: Lima - Peru

Re: Insert Into MySQL tables in different connections .. you can

Postby nageswaragunupudi » Tue May 08, 2018 5:50 am

That could mean there are no rows to be exported.
Please try
Code: Select all  Expand view
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
Regards

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


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 15 guests