Re: portability Oracle <=> MySQL

[Mickael Besson]

Another idea would consist in the separation of source code and SQL queries.
For example, SQL queries would be stocked in a configuration file and would contain entire list of queries of the application. And each query would be associated with multiple SGBD syntaxes.
Ex :
[sqlmode]
mode=mysql

[oracle]
q1=SELECT C_1 || C_2 FROM T_1
q2=INSERT ....
q3=SELECT ...
q4=UPDATE ...

[mysql]
q1=SELECT CONCAT(C_1,C_2) FROM T_1
q2=INSERT ....
q3=SELECT ...
q4=UPDATE ...

In the application, you have to read this configuration file to obtain the good SQL syntax.


+ : simple to deploy
+ : application portable on all DB
+ : can change DB of an application without redeploying it(text configuration file)
+ : application faster thanks to specific SQL syntax(no ANSI)

- : applicative code (configuration SQL) not reusable; but it's not very hard to adapt if you have the table of correspondences Oracle/MySQL and you don't use complex syntaxes :)
- : there's a litle (big) problem when you have to generate dynamic queries...

Solution ?
Good idea ?
Others?