Many software companies understand the importance of developing applications that do not depend on a specific database type (i.e., Oracle, SQL Server, DB2), which allows customers to choose their used platform. In general, software developers recognize that their customers are responsible for database maintenance and must use existing platforms and personnel.
Many articles have been written that describe the general differences between Oracle and SQL Server from the organizational and database administrator standpoint. In this article, I'll fill you in on the differences between SQL Server and Oracle platforms from an application perspective and discuss the possible methods of developing applications in a non-database-dependant environment.
At this time, I will not talk about the differences between the two platforms that are transparent to the application, such as table partitioning and indexing.
Defining common interfaces and languages
There are few common languages and interfaces that allow database independency within applications and supposedly can be used for any relational database in the same way:
ANSI is defined as the American National Standards Institute is a voluntary membership organization (run with private funding) that develops national consensus standards for a wide variety of devices and procedures. In the database area, ANSI defines the standard for writing SQL commands, giving the ability of running the command in any database without changing the command's syntax.
ODBC is the Open Database Connectivity (ODBC) interface by Microsoft, which allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data. ODBC permits maximum interoperability, which means a single application can access different DBMS. Application end users can then add ODBC database drivers to link the application to their choice of DBMS.
OLEDB, the successor to ODBC, is a set of software components that allow a "front end" such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, MySQL etc. In many cases the OLEDB components offer much better performance than the older ODBC.
JDBC (Java Database Connectivity) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases – SQL databases and other tabular data sources, such as spreadsheets or flat files. The JDBC API provides a call-level API for SQL-based database access.
Common interfaces in the real world
Unfortunately, not all the commands at the database level are ANSI, and each database platform has its own extended functionality. ANSI, or common interface, generally means basic functionality, and therefore can mean the loss of performance competencies. For small databases and small applications it might be easy to maintain common access to the database, but as the database and/or the application become larger and more complex, you have to add functionality to the code.
Examples:
Commands written the same way in both platforms:
| Insert into Table_1 values (1,'Michelle') |
| Update Table_2 set Col_1 = 2 |
| Delete from Table_3 where Col_3 like 'Michelle%' |
Commands not written the same way in both platforms:
SQL Server
Select getdate()
Select case Fld when 1 then 'a'
When 2 then 'b'
Else 'c'
End
From Table_4
Oracle
Select sysdate from dual
Select DECODE (Fld, 1, 'a', 2, 'b', 'c')
From Table_4
The following two articles contain a list of comparisons between Oracle PL/SQL commands and T-SQL commands:
Migrating from Oracle to SQL Server
Beginning SQL: Differences between SQL Server and Oracle
Possible solutions:
I have seen three possible solutions to the database interoperability problem:
| # |
Solution Description |
Advantages |
Disadvantages |
| 1 |
Handling two versions of the application -- one for Oracle and one for SQL Server |
1. No need to handle SQL commands versioning |
1. Duplicate code -- must apply every change in both versions. |
| 2 |
Using common language (ANSI/ODBC/OLEDB/…) as much as possible and handling the different commands with IF commands in the application |
1. Handling single application |
1. If there are many non-ANSI commands, the code might be larger, which can affect application's performance.
2. Code might become complex due to many IF statements. |
| 3 |
Keeping the database commands in a database or INI files, reading them to cache when application starts |
1. No need for IF commands in application.
2. SQL commands can be changed on the fly without the need to recompile the application after they are modified. |
1. SQL commands management could become complex. |
Which solution to choose?
The answer to that question depends on the application's characteristics and platform. Each solution is easy to implement and there is no best solution here.
Conclusion:
If you would like to develop your application as database-independent, you should plan the solution carefully. Take into consideration the application's complexity at the database level and the total amount of code needed. During the planning process, it is important to think in terms of the application's future growth.
More on SearchSQLServer.com
Expert advice: SQL Server Clinic: T-SQL performance problems and solutions
Guide: T-SQL Learning Guide
Tip: Lessons Learned: Working with Oracle stored procedures, applications and data
| ABOUT THE AUTHOR: |
|
Michelle Gutzait
About the author: Michelle Gutzait works as a senior databases consultant for Itergy International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development and reporting services.
Copyright 2006 TechTarget
|
|
This tip originally appeared on SearchSQLServer.com.
