Beginning SQL Programming: Pt. 3

Connecting the Front and Back Ends

Having established a front end and a DBMS back end, we then need to connect them through some
type of software. The connection functions include: defining which datastore to use, how to transfer
SQL statements and results, and a host of settings that control the above processes. For example, in VB
we may want to work with data in an Access database. We need to establish a connection to pass SQL
statements to Access. We will want to establish how to handle multiple-user conflicts as well as whether
to read the data as fast as possible or with more flexibility. Once these parameters are set for the data
communication, we then need a way of actually sending our SQL statement to the DBMS.

By analogy, if the front and back ends are like telephone handsets, then the connection is like the
telephone company equipment and wires. When starting a call we first establish a connection by dialing
the other party and waiting for the phone company to connect the correct wires. Then we can begin
using English across the connection. In the same way, we create a connection between our front- and
back-end software, then we use SQL statements to communicate across that connection.

In this section we present an overview of four connection methods. We then move on to demonstrate
the actual code for creating a connection (with tricks and traps) in Chapter 2.

An older and lower-level connection is ODBC (Object Database Connectivity), which was a result of
collaboration throughout the industry in the early 1990s. Although not optimized for any particular
database, it has enjoyed widespread use up until the late 90s. ODBC is not well suited to non-relational
data. ODBC uses a driver for each type of datastore to hold communication specifics.

Chronologically next came OLEDB, a set of COM (Component Object Model) interfaces that can
interact with most data stores.

COM objects are packages of code that have a standard interface for interacting with
other code. Instead of thousands of programmers writing and troubleshooting
duplicate code to achieve the same objectives, the task can be solved once by some
experts and the result encapsulated into a COM object. The object can then be used
by anyone that buys the object. You can think of COM objects as mini-applications
that are available to programmers.

COM depends on an Application Programming Interface (API) optimized for talking with C++.
OLEDB uses providers to connect directly to the datastore. Alternatively, OLEDB can connect through
the older ODBC and use the ODBC Drivers.

ODBC uses Drivers while OLEDB uses Providers.
Both are “middleware” that contain instructions for how to talk to a specific kind of
datastore.

Now Microsoft offers the ActiveX Data Objects (ADO), which are COM objects for talking with data. In
a sense, ADO encapsulates OLEDB into an object-based model. Instead of writing lengthy (and
difficult) code in OLEDB, a programmer can simply instantiate one of the ADO objects and then set its
properties to make a connection. An ADO object can send a SQL statement through that connection
and receive back results.

For more information on ADO see the ADO 2.6 Programmers Reference (ISBN 186100463x) by David Sussman from Wrox Press.

Another popular technique to connect front and back ends is using the JDBC (Java Database
Connectivity) object. As with ADO, experts have encapsulated into a set of classes and interfaces all the
Java code needed to pass SQL statements from a front end to a back end. JDBC allows developers to
create connections entirely within a pure Java API. With a JDBC solution you not only have the ability
to use SQL to access any back end, but you write in Java so you can run the code on most front ends.

Other software vendors have solutions for connecting the front and back ends. Allaire’s Cold Fusion
uses a proprietary set of HTML tags. Some DBMS vendors have developed specialized connections
specific to front ends and DBMS that they offer.

The techniques discussed in this section offer many connection options in order to satisfy a wide range
of users. Some situations justify writing a custom coded connection. Although expensive and difficult to
create, these custom connections can gain in performance by eliminating features that are not used.

Latest Articles