Mysql to Oracle communication

MySQLoraclePHPtrigger

I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server).
The orders from the website are inserted into the Master Mysql database and which are replicated into the Slave Mysql database. These orders need to be pushed to the Oracle Db. I have arrived on 4 methods to do this.

  1. Use mysql UDF for http communication that would send the rows on a Insert Trigger on the slave to the Oracle webservices on Oracle server

  2. Use cron jobs(with a low interval may be 5 mins) with Php script that would get the new orders from mysql and send to the Oracle db via Oracle services/Php services on Oracle hosted server.

  3. Use sys_exec() udf to invoke php script that inserts into Oracle db

  4. Use memcached with MySql and let Php poll the memcached to retrieve data and send it to Oracle server, but unsure whether we could migrate existing Mysql version to new version Mysql 5.6

I already have the UDF's in place and tested them, they are good to go. but still in dilemma regarding data integrity and reliability in case of using UDF's with triggers.

Is there a better method for doing this. Or else which method shall I follow to do the same.

I am aware of the security threats of UDF's, you can't restrict it to any user

One more thing I am not allowed to introduce new changes to the existing website php code for the operation.

Best Answer

Try using heterogenous Connectivity to link the Oracle database to the mySQL database. It would be easier to directly query for what you want from Oracle, rather than trying to replicate the data to Oracle. I tried doing this on Redhat 5.6, but I had issues with the ODBC driver that was written for Redhat 5.6. I later tries it with Redhat 6.3 and it worked much better. The issue dealt with how the ODBC driver read unicode characters. It is pretty easy to setup, you can install HAS as a different Oracle Home, or use the same Oracle home. Linux has a built in ODBC test program that will help you to get it working.

http://docs.oracle.com/cd/B28359_01/server.111/b28277/toc.htm

Related Question