T-SQL and PL/SQL common languages for database-independent applications

Common languages in T-SQL and PL/SQL exist to allow developers to write applications in database-independent environments. Common languages and interfaces such as ANSI and ODBC offer solutions to the database interoperability problem.

This Content Component encountered an error

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:

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


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.

This was first published in October 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

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

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

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close