Running an application on different DBMSs

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?

    Requires Free Membership to View

    Login

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


This was first published in August 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top