Beginning SQL Programming: Pt. 3
August 2, 2001
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 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.
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.
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.