|
Supporting multiple database engines is a common goal among software developers, and it can be accomplished with varying degrees of difficulty. As you are no doubt aware, most RDBMSes use SQL as their query language, and most RDBMSes tend to speak slightly different dialects, which vary in some way or another from standard ANSI SQL.
Now most RDBMSes understand the basics of the SQL standard: for example, you really shouldn't encounter a RDBMS that cannot parse queries such as:
SELECT mycol FROM mytable WHERE mycol = 'thisValue';
UPDATE mytable SET mycol = 'newValue' WHERE mycol = 'thisValue';
DELETE FROM mytable WHERE mycol = 'newValue';
These queries are all ANSI standard, and quite basic in nature. The problem comes when we start dealing with various vendor extensions to the ANSI SQL standard. For example, MySQL limits rows returned with a LIMIT clause:
SELECT mycol FROM mytable LIMIT 10;
This limits the rows returned to only the first ten. Microsoft SQL Server follows a different syntax:
SELECT TOP 10 mycol FROM mytable;
This accomplishes the same purpose, but uses a different syntax. In Oracle the ROWNUM keyword would be used:
SELECT mycol FROM mytable WHERE ROWNUM <= 10;
Naturally this could be a problem if we wanted to create an application that supported MySQL, MSSQL and Oracle. Let's say you are returning data to the user and you want the user to be able to choose how many rows to return based on a choice they make in a drop-down box: how do we handle the different syntax used by each RDBMS?
The answer lies in database abstraction. Instead of hard-coding your SQL queries directly into your application, create a class that acts as a proxy between your application and your database. You can create different classes, one for MySQL and one for Oracle, and instantiate the one that is relevant to the user's installation. When you need data from the database, you ask the class (i.e. "Give me the list of customers, only the first 10") and the class then runs the appropriate query as the database will expect it. This way you can add support for more databases by changing the class.
For the simplest of applications, changing the driver (ODBC, JDBC, etc.) may be all it takes, but the more complex the application becomes, the more likely it will be that you would need to make changes to your application code to account for differences such as the syntactic difference mentioned above. If you can move to a more abstracted design you will benefit greatly when the time comes to add support for a third RDBMS.
|
