Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Aug 2, 2001

Beginning SQL Programming: Pt. 3

By DatabaseJournal.com Staff

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.

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM