Q

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?

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close