Does anyone have any idea as to how to implement a DB solution that can be ported across different vendors ?
How can u write and create SQL code that can independent of the vendor specific SQL so that it can work for different DBs ?
Any books ? Any references ?
Thanks for the help.
Our application supports multiple RDBMS (Oracle, DB2, Informix and Sybase). This is how we implement it:
- Programmers write SQL base on an internal pre-defined SQL syntax.
- When compiling the programs, the precompiler translates the SQL into the proper SQL statement for each RDBMS using macros. e.g. oracle.mac, db2udb.mac, sybase.mac ... etc and then compile the programs.
The advantage of this approach is portability between RDBMS. The major drawback is the limitation of the SQL as it needs to complie with the pre-defined rules. In addition, it may not utilises all the features of each RDBMS SQL. e.g. Transat-SQL, PL/SQL ... etc.
It really depends on what is more important and what is your primary requirement. Moreover, it needs a lot of works for the pre-defined SQL and to support the macros.
Hope the info helps.