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.