This content has been marked as final. Show 6 replies
Data is not much. but We have to sync both database thr' PHP.
We check other migration strategies like migration tool/software, db link etc. but as we want whole process automatic & not manual import/export.
So best possible option for us to connect Mysql n Oracle through PHP.
So I am newbie to PHP...so wanted help in that way...
You say you have investigated using database links but you could use the Database Gateway for ODBC (Dg4ODBC) in SQL statements in your PHP coding to move data from Oracle to MYSQL.
Have a look at this note in My Oracle Support -
Issuing Selects from PHP Using DG4ODBC Does Not Give Correct Results And Error - Ora-1002 (Doc ID 753321.1)
which gives an example. You could have code in the $SQL section which is this case is -
$sql = "select id from dbo.msql_table@dblink"; //sql statement in quotes because it's a string as variable
to copy data from Oracle to MySQL instead of the select in this example.
If you use this route then check any statements work from SQLPLUS before trying using PHP code.
There will be two main classes of issue:
1. Data management questions regarding performance, storage, locking etc.
Some planning and benchmarking will be needed.
2. Data type conversion questions. Once you have decided on the
mapping, PHP will help you here.
There are many resources for PHP & MySQL available to pick from. If
you're new to PHP OCI8 look at this free book on PHP and Oracle
and any of the commercially available books on Oracle DB and PHP.
If you already have code for connecting to both databases in PHP, all you need to do is:
1. Select data from Oracle into arrays or variables based on timestamp that you are looking for.
2. Insert data from step #1 into MySQL using the mysql connection.
It is very simple. Not sure what the complication is?. It is a simple SELECT and then INSERT Into mysql and commit. Alternatively you can use database jobs in oracle to insert data into MySQL periodically using ODBC Gateways based DBLinks. Performance won't be any worse than you selecting data from Oracle into your application and then writing it out to MySQL.