FWH 17.03 Work with Remote + Embedded using ADO

FWH 17.03 Work with Remote + Embedded using ADO

Postby nageswaragunupudi » Mon Mar 20, 2017 11:38 am

Let us consider this situation.
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

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

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
 
Regards

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

Re: FWH 17.03 Work with Remote + Embedded using ADO

Postby Carles » Mon Mar 20, 2017 12:06 pm

Mr. Nageswaragunupudi,

Great !

Then you can syncronizate both tables maybe ( server & local ) ?

Again good work :D
Salutacions, saludos, regards

"...programar es fácil, hacer programas es difícil..."

UT Page -> https://carles9000.github.io/
Forum UT -> https://discord.gg/bq8a9yGMWh
Skype -> https://join.skype.com/cnzQg3Kr1dnk
User avatar
Carles
 
Posts: 1082
Joined: Fri Feb 10, 2006 2:34 pm
Location: Barcelona

Re: FWH 17.03 Work with Remote + Embedded using ADO

Postby nageswaragunupudi » Mon Mar 20, 2017 12:12 pm

Update:

In our tests it is possible to connect to both embedded server and also any number of main servers in the same application.
Requirements:
1) Link with libmysqld.lib instead of libmysql.lib
2) Connect to a local embedded server first before connecting to any remote server.

We welcome testing by users and feed back.
Regards

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

Re: FWH 17.03 Work with Remote + Embedded using ADO

Postby nageswaragunupudi » Mon Mar 20, 2017 12:51 pm

Then you can syncronizate both tables maybe ( server & local ) ?

Yes
FWMYSQL makes a lot of things very easy, if only we use its features.
Regards

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

Re: FWH 17.03 Work with Remote + Embedded using ADO

Postby hmpaquito » Mon Mar 20, 2017 1:06 pm

Sorry, but that syncronizacion is not real syncronization but a simple copy.
Insert cSql querys is very heady when tables with many records.
:cry:
hmpaquito
 
Posts: 1482
Joined: Thu Oct 30, 2008 2:37 pm

Re: FWH 17.03 Work with Remote + Embedded using ADO

Postby Carles » Mon Mar 20, 2017 1:20 pm

Hmpaquito,

Maybe you can use FEDERATED Storage Engine -> https://dev.mysql.com/doc/refman/5.7/en ... ngine.html
Salutacions, saludos, regards

"...programar es fácil, hacer programas es difícil..."

UT Page -> https://carles9000.github.io/
Forum UT -> https://discord.gg/bq8a9yGMWh
Skype -> https://join.skype.com/cnzQg3Kr1dnk
User avatar
Carles
 
Posts: 1082
Joined: Fri Feb 10, 2006 2:34 pm
Location: Barcelona

Re: FWH 17.03 Work with Remote + Embedded using ADO

Postby nageswaragunupudi » Mon Mar 20, 2017 1:21 pm

hmpaquito wrote:Sorry, but that syncronizacion is not real syncronization but a simple copy.
Insert cSql querys is very heady when tables with many records.
:cry:

If you are serious user of FWH MYSQL and you follow our recommended approach, synchronizing tables even with million records should be easy and fast enough.
Regards

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

Re: FWH 17.03 Work with Remote + Embedded using ADO

Postby nageswaragunupudi » Mon Mar 20, 2017 1:27 pm

Carles wrote:Hmpaquito,

Maybe you can use FEDERATED Storage Engine -> https://dev.mysql.com/doc/refman/5.7/en ... ngine.html


One way is to use synchronization between servers. That is the best. This is what we do in large organisations. My experience was with Oracle and MySql and both work well.

In this forum we are considering smaller volumes. FWH MySql provides enough functionality to do one way sync even on large tables fast enough, again if you adopt our recommended table structures and maintenance methods. Sync or copy let us not go in to language niceties. Finally both involve copying in some way or other.

That is the point.
Regards

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


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 14 guests