We have our main MySql server either as local host or on a remoter server.
We also have a local MySql Embedded Server on our local disk in some folder.
We need to work with both at the same time.
We know the limitation of FWHMySql that it can either work with Embedded Server or the Main Server but not both at the same time. That is a known limitation at the moment.
Let us see how we beat this limiation.
In the same application, we connect to the main server with ADO and embedded server with FWHMySql and work with both data and tables at the same and even move tables between them.
Let us start with a small example. We have a table on local embedded server ( eg states ) and we want to copy to the remote server.
- Code: Select all Expand view RUN
oCn := FW_OpenAdoConnection( "<connectionspec" )
oLocal := mysql_Embed( cLocalFolder, "fwh" )
if FW_AdoTableExists( "states", oCn )
oCn:Execute( "DROP TABLE states" )
endif
cSql := oLocal:CreateTableSQL( "states" )
// Create states table
oCn:Execute( cSql ) // table created
aData := oLocal:Execute( "select * from states" )
cSql := oLocal:InsertSql( "states", nil, aData )
oCn:Execute( cSql ) // all data copied
// Check
oRs := FW_OpenRecordSet( oCn, "states" )
XBROWSER oRs
Well, a lot more is possible.
Now let us see another example, which may seem more complex to start with.
We have the same table "states" both on the Main Server and also the local server on some folder. The local table has recent modifications and insertions. We need to update the table on the main server with the modifications made to the local table and also new records inserted in the local table. In other words we want to synchronize the table on the remote server with the local table.
In fact this is very simple:
- Code: Select all Expand view RUN
oCn := FW_OpenAdoConnection( "<connectionspec" )
oLocal := mysql_Embed( cLocalFolder, "fwh" )
aData := oLocal:Execute( "select * from states" )
cSql := oLocal:InsertSql( "states", nil, aData, .t. ) // Last parameter .T. does the trick
oCn:Execute( cSql ) // all data on main server updated
// Check
oRs := FW_OpenRecordSet( oCn, "states" )
XBROWSER oRs
