How to pull 1 table from Progress into MYSQL

[cruzer076]

Hey Guys,
I have a mysql database and a progress database. There is one table in Progress I would like to sync live (if possible) or scrap the information from on a timed basis to my mysql database.

Is there some kind of live connection I can create between the two databases? If so what do I need to have in place? Do I need a script of some kind?

Also I don't want the mysql database to be able to change anything on the progress database at all, do I need to create a separate user? If so how do I do that? I have looked on here and have found references to looking into a knowledge base article but I don't know where that is? (I used the search feature on here and it didn't come up)

Any help you can offer this noobie would be great.

 

[medu]

Look for an ETL tool, there are some open-source ones that does exactly what you want... Kettle (part of Pentaho), Talend or even Apatar (much lighter).

For that you'll have to set-up SQL broker on the Progress side, add a 'sync' user and give him the privileges that you need (might be only read on that specific table). Once that is done start the ETL tool and define the ETL sequence, it's pretty straight forward once you have the JDBC connections for the two databases... simply map fields from the two tables that you want to sync.

Normally depending on the sync requirements and the size of the table you want to replicate it might help if you can have some last-update timestamp field to easily select only changes from the last sync... also logical delete might be needed or you'll loose the records that were deleted in source table. If the table is not that large and you can afford to simply copy it each time then just drop the mysql table as the first step of the transformation and insert everything as new (not bother with upserts).

Most of the ETL tools do have a scheduler in place so you can set the ETL sequence to run periodically, have it send emails when something fails and so on...