Data Replication from MS SQL 2005 to MySQL

Classic Servoy
1

Hi.
I have a db back-ended with MS SQL 2005. My client has a web site that is running on MySQL. The solution is a migration of an FM 5.5 system that was using IWP for the web site to pick up data & used PHP to massage it into the MySQL tables (i.e. PULLing the data).
I now need to work out the best way of PUSHing the data now from Servoy to MySQL (we will be using a staging server so it won’t be live updating of the web site!).
I have easily set up a new DB Server for the MySQL db, & want to know if there is a way to use the rawSQL plugin to do something like

//copies all records from table ad_web into ad_web_copy 
plugins.rawSQL.executeSQL('aerotrack', 'ad_web',"INSERT INTO ad_web_copy (`adcode_id`,`adname`,`adcode`)SELECT `adcode_id`,`adname`,`adcode`FROM ad_web");

(from topic Copy all records - Classic Servoy - Servoy Community , thanks Rodney…)

The above SQL assumes the tables are on the same ‘server’ (aerotrack) which in my case they aren’t.
I didn’t want to have to do it with Servoy forms & loops as I felt there might be a performance impact :)

The tables have different column names, so I can’t use copyMatchingColumns :(

The old way was doing the PULL each night & deleting all records in the MySQL tables, but with my new Servoy solution, I may want to be able to do ‘instant’ updates, which could be done using relationships once the initial dump of all data has been done, but at first I just need to replicate the PULL method via a PUSH method (web guys will charge lots to do all coding changes at their end, so we want to do it from here…)

Any ideas/help will be greatly appreciated,

thanks,
Rafi

2

Hi Raffi,

In the past I have opened a mySQL database connection in Servoy and copied over records whenever the records in my internal database changed.

The big minus here is that if the website went down, the Servoy system would go down as well!

To avoid this issue, I’m now using a web service:

In my Rails web site I have defined a few web services which accept XML.

Whenever records are changed I add a record to a web update table.
This table contains the pk, table and action to be performed on the record (create, update, delete).

I have a headless client as a batch processor that check this table every minute. If it finds records, it loops through them and builds a request containing the changes as XML using the http plugin, I fire off the request and if successful, I delete the record in my web-updates table.

Since this is asyncronous process in a Servoy headless client, it performs very well - much faster than each Servoy client doing the updates. Users don’t have to wait for the website to be updated - it all happens in the background.

3

Thanks Christian,
I’m not sure how I would set that up…

In the mean time, I might have worked out a way of creating a ‘linked server’ in MS SQL that will enable me to do it via a simple SQL statement.

I’ll post me findings later.

Rafi

4

Hi,
if anyone is watching this thread I have (mostly) managed to do it using ‘Linked Servers’.
Too busy at the moment to post all details, but if anyone needs help with this before I do, please post/PM.
The issue I am stuck on at the moment is moving data in SQL Server 2005 VARCHARs into MySQL TEXT columns, but I’m working on it!
When it does work, it moves/copies VERY fast (at least locally…) and with a SINGLE line of SQL!!! (per table)

Rafi