We are working for a client. Presently his database is on SQL Server. Now he wants to make his application flexible so that it works on any database. So far the target is SQL server, MySql and Oracle. I suggested that we can have a small module that will detect the type of database from the Windows Registry and when the user connects for the first time, the appropriate scrpt will run and create the database. In this case we wll have three scripts complete with stored procedures. One of my collegues suggested of having a generic script that will run for all the databases. I can't figure how this can be done. What do you suggest?
If you want everything to be generic, then you have to make sure that each and every SQL statement complies with ANSI SQL standards and that it will run on every RDBMS vendor's platform that you wish to support. ANSI SQL standard SQL statements are not enough because each RDBMS vendor implements different levels of the ANSI SQL standard. But doing this means that you will not be able to leverage any of the strengths of each RDBMS vendor's database. You may find that one portion of your application works well on SQL Server, but not well on Oracle. And another portion of your application may work well on Oracle and not well on SQL Server.
The best products that I've seen that work well on multiple RDBMS platforms leverage the strenghts of each of those platforms. In some cases, the application code is different due and you install the application software for that specific RDBMS vendor. In other cases, the application software detects the RDBMS vendor and it is the application software that issues the appropriate, vendor-specific SQL statements.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.