Embedded SQL

In the early days of relational databases, the only portable interface for applications was Embedded Structured Query Language (SQL). There was no common function API and no standard Fourth Generation Language (4GL). Embedded SQL uses a language-specific Precompiler. SQL commands are embedded in a host programming language, such as C or COBOL. The Precompiler translates the embedded commands into host language statements that use the native API of the database.

The problem with using Embedded SQL is that there must be a compiled version of the database interface for each database and operating system supported. This is not efficient or useful for heterogeneous environments. Also, the developer may run into problems with each database vendor’s C API. Not all database APIs are created equal.

ODBC

When building a Web site that must connect to many different databases, the first database connectivity standard normally considered is Open Database Connectivity (ODBC). ODBC is a logical choice, because it is a standardized API. It is a set of function calls based on the SQL Access Group (SAG) function set for utilizing an SQL database system (backend system). The SAG set implements the basic functionality of Dynamic SQL. Embedded SQL commands can be translated to call ODBC. Finally, there are ODBC drivers for every major database application.

Applications access ODBC functions through the ODBC Driver Manager, which dynamically links to the appropriate ODBC driver. ODBC drivers translate ODBC requests to native format for a specific data source. The data source may be a complete RDBMS, such as FirstSQL, or it may be a simple file format, such as Xbase. In other words, most ODBC drivers are tied to a single data source. Some, like FirstSQL, support multiple data sources. The FirstSQL ODBC driver supports both a FirstSQL data source and an Xbase data source.

Though its name begins with open, implying that it is not tied to a single vendor or even to a subset of RDBMS vendors, ODBC is controlled by a single vendor: Microsoft. Microsoft defines the specification of the API and supplies the basic driver manager software used on their operating systems. This control has some good aspects and some bad for the future of ODBC.

Microsoft has made reasonable, useful extensions to the original SAG definitions in creating ODBC. Later releases have refined those extensions. Microsoft has committed to bringing future versions of ODBC more in line with SAG’s specifications and with existing standards.

OLE DB

In a major strike against ODBC, Microsoft is touting their Object Linking and Embedding Data Base (OLE DB) facility as a replacement for ODBC. OLE DB could be viewed as an object layer placed on top of ODBC, but Microsoft is likely to provide direct OLE DB drivers for their database products and to de-emphasize and perhaps discontinue ODBC drivers for their products. OLE DB is not open or portable except between Microsoft operating systems (OSs), which is now a single Windows OS NT.

Because of Microsoft’s total control of the specification and arbitrary complexities in the facility, OLE DB will not be supported by other operating systems—Operating System 2 (OS/2), Macintosh Operating System (MAC OS), and various flavors of Unix. ODBC, and Embedded SQL to a lesser degree, will remain as the only open and portable interfaces for SQL accessible databases. Unfortunately, the fate of ODBC is completely under the control of Microsoft.

Java and JDBC

Java Database Connectivity (JDBC) is an SQL-level API that allows you to embed SQL statements as arguments to methods in JDBC interfaces. To allow you to do this in a database-independent fashion, JDBC requires database vendors to furnish a runtime implementation of its interfaces. These implementations route your SQL calls to the database in the proprietary fashion it recognizes. As the programmer, though, you do not ever have to worry about how JDBC is routing SQL statements. With JDBC, you can run the same code no matter what database is present. A Java client/server application can make use of one of the following three major database architectures:

• Object database

• Object-relational database

• Relational database

The majority of today’s databases are relational databases. Thus, the JDBC API is heavily biased to relational databases and SQL. There is an architectural conflict between Java and relational databases. Java is object-oriented, whereas relational databases are not object-oriented. Therefore, mapping between the Java objects to the SQL relationship must occur. It is up to the developer to do this mapping.

The use of Java and JDBC has two distinct advantages for heterogeneous Web application development. It is database independent and facilitates distributed computing. A Java database application does not care what database engine is used. Therefore, the developer can change the database engine without having to change the Java application. In fact, the developer can write a class library that maps business objects to database entities in such a way that the application does not know that a database is in use.

Using Java for distributed computing has the advantage that the user can download the Java code as he needs it. The administrator does not have to install the software on each user’s workstation. This model is very beneficial when it comes time to update the application. The administrator does not have to reinstall software.

DBI-PERL

Practical Extraction and Reporting Language (PERL) is most likely the most common scripting language used on the Web today. It is predominantly used with the Uniplexed Information and Computing System (Unix) operating system, even though it can be used with Windows NT®. PERL is well-suited for the Web because it is a language that was written to handle text and text files. The PERL community also needed an interface to databases. Because PERL is an open source application, the Database Interface (DBI) is perfect for this task.

In simple language, the DBI interface allows users to access multiple database types transparently. So, if you are connecting to an Oracle, Informix, mSQL, Sybase, or whatever database, you don’t need to know the underlying mechanics of the 4GL layer. The API defined by DBI will work on all of these database types.

A similar benefit is gained by the ability to connect to two different databases of different vendors within the one PERL script (if you want to read data from an Oracle database and insert it back into an Informix database all within one program). The DBI layer allows you to do this simply and powerfully.