Beginning SQL Programming: Pt. 3

Thursday Aug 2nd 2001 by DatabaseJournal.com Staff

Part three of this excerpt series covers configurations for using SQL, two and three tier architecture on a LAN, n-tier architecture on the Web, mainframe and terminals, and mainframe to mainframe.

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.

Configurations for Using SQL

Before we move on we want to demonstrate some typical installations that use SQL. These drawings demonstrate a range of situations which you might encounter (or design), from a simple desktop to a complex multi-server environment.

How to Study These Configurations

Note that each section has a SQL requester, a back end and a connection. In many cases the requester is a front end (user interface) but not always. Consider the case where there are several servers between the user and the DBMS. In those scenarios, an intermediate server is the SQL requester.

Note the difference between a front end and a SQL requester. The front end deals with humans. The SQL requester is software that generates a SQL statement. Generally the requester uses information from the front end, such as which records to read or what data to write.

In most cases the back end is a DBMS, but not always. Datastores like Microsoft Exchange can also speak the SQL Language.

For most of these scenarios there are certain tasks that must be performed:

  • user interface
  • business logic (which may produce the SQL statement)
  • data storage
  • physical connections and
  • logical connections

However, in each scenario these functions are divided differently across machines and locations. Having a clear image in your mind of where various tasks are performed is particularly useful in troubleshooting. It is useful to be able to follow mentally the path of information from the user all the way through to the data store and back out again. Frequently the first step in solving problems is to isolate which step in the process is creating the problem.


Access, as we have discussed, has objects that function as parts of a front end (forms and reports) and table objects that function as a back end. In a sense Access query objects function like SQL statements, as they are one way to communicate between the front and back end objects. In addition to the query objects, any Access object property that accepts a SQL statement for a value is a SQL Requester, as is Access VBA code that executes a SQL statement. Unlike the other scenarios, Access is able to perform a connection internally between a form and a table without any external coding such as ADO or OLEDB.

Desktop DB Architecture

Two Tier Architecture on a LAN

A very simple system for smaller offices involves just user's desktop PCs, a server and a Local Area Network. The server performs the services of managing network traffic, security and holding the data. For example an office of 20 people may have an Intel-based server running Windows 2000 Server and SQL Server 2000. The front end could be an application created in Visual Basic, which runs on the user's desktop. In response to the user entering some parameters and clicking on a button, the VB code creates a request containing a SQL statement and sends it to the server via a connection such as ADO. The server receives the request and routes it to the DBMS. The DBMS executes the statement and then returns the data to the VB application for display.

Two-Tier architecture diagram

Three Tier Architecture on a LAN

As the complexities and security requirements of a company increase the IT department will move to a three (or more) tier architecture. The front end will still be the user's desktop PCs. But now the server function has been divided into two (or more) parts.

  • A data server that runs the DBMS or other data store.
  • A departmental server that usually routes traffic, controls security and does some business processing.

One of those business processes is the generation of SQL statements to communicate with the data server. Now, when the request is received by the departmental server it is sent on to the data server through a physical link between the machines, most probably a 100 Mb or 1 Gb Ethernet LAN.

As we will see in an example on the Web below, the three-tier model can expand to many more levels, called n-tier. Each additional machine is specialized to perform one part of the business process. Adding to the base of a departmental server and a data server, common additional servers include a security server and a server to handle the processing of the business logic.

Note that not all requests will be routed through all the servers. For example, a security server may perform some initial authentication and then the actual SQL requests may then be permitted to flow directly to the data server.

Three-Tier LAN Architecture

Two-Tier Architecture on the Web

Here we have the user's PC running a browser, which is connected via the web to a server. This is a lightweight website because the web server, the script processor and the database software are all held on one machine. Thus we have a two-tier system: PC/Browser and Web Server. In this case, the connection is between the script interpreter and the database.

Note that the way we use the word "connection" in this scenario is not the connection from the browser to the web site. In a two-tier system both the SQL requester and the datastore ends of the connection are on the same machine. The connection here is only a logical channel through the server's memory; there is no need for an external physical connection as in some other scenarios.

Two-tier Web Architecture

Three-Tier Architecture on the Web

As with a web server, the datastore may be on the same server or a separate machine(s). This architecture is the logical next move when the two-tier system (above) becomes overloaded. Just as with the 3-tier LAN system, we have moved the DBMS to another physical machine and have thus been able to optimize the hardware and OS to support reliability, security and scaling of a data store. The web server can also then be re-optimized for serving pages and executing scripts without having to accommodate a data service.

The front end shifts further from the SQL requester in a three-tier system. The front end (point of user interface) becomes the PC running a Browser. The SQL requester is the business logic software on the web server, not the user's PC. The web server will then pass information back and forth to the end user's PC.

Three-Tier Web Architecture

n-Tier Architecture on the Web

An n-tier system has the server tasks divided among more servers than the three-tier system. Now we have divided the web server into three parts, one to handle the web services, one to handle the sessions and a third to perform the business logic (run the bulk of the scripts). Our connection now links the business logic layer and the data store. As in every case in a "tiered" system (2-, 3-, and n-tier) the connection runs between the data server (DBMS) and the next layer up.

n-tier Web Architecture
(Click image for full size)

In the graphic below, the hardware and software is the same, but we see that more than one server in an n-tier system can establish connections and pass SQL statements to the Data Server (DBMS). Here the session server passes and receives data via SQL statements. Thus within one site we may have two or more servers creating connections and passing SQL statements to a DBMS. We expand further on this idea in the section titled Data Warehousing below.

multi-server n-tier Web Architecture
(Click image for full size)

Mainframe and Terminals

A mainframe/terminal system resembles the two-tiered system described earlier. However, the terminals and mainframe are usually in one facility and thus the connection is normally through dedicated wiring and not through the Internet. Second, the terminal has a minimum of processing power, so it is controlled by the mainframe. As of 2001, this system is mostly limited to older installations or high security systems.

mainframe/terminals architecture
(Click image for full size)

Mainframe and Terminals with a Separate Datastore

A mainframe can split off the data server in the same way that our LAN and website examples did when moving from two- to three-tier architectures. In this scenario we see that the mainframe handles the business logic but for data it sends a SQL statement out to a data server running a DBMS. The connection is from the business logic mainframe to the Data Server.

Mainframe w/terminals and a separate data store architecture

Mainframe to Mainframe

In each of the scenarios above there is a person involved. An employee (hopefully smiling and fully vested) sits at the desktop PC or a customer (hopefully with a loose wallet) sits at the other end of the Internet on a PC with a browser. But SQL can also handle communications about data between machines with no person involved. In fact SQL is a common language to establish communication between legacy systems with completely different hardware, operating systems and software (and probably incompatible administrators to boot).

In this graphic we see that an IBM mainframe is able to get information on customers from a VAX mainframe. Although there is a SQL Requester there is no front end because there are no humans involved.

Mainframe to Mainframe architecture

Mobile Site | Full Site