How to Use ODBC to Access SQL Data
ODBC is a standard interface between a database (such as an SQL database) and an application that accesses the data in the database. Having a standard enables any application front end to access any database back end by using SQL.
ODBC in a client/server environment
In a client/server system, the interface between the client part and the server part is called the application programming interface (API). An ODBC driver, for instance, includes an API. APIs can be either proprietary or standard. A proprietary API is one in which the client part of the interface has been specifically designed to work with one particular back end on the server.
The code that forms this interface is a driver — and in a proprietary system, it’s called a native driver. A native driver is optimized for use with a specific front-end client and its associated back-end data source. Because native drivers are optimized for both the specific front-end application and the specific DBMS back end that they’re working with, the drivers tend to pass commands and information back and forth quickly.
If your client/server system always accesses the same type of data source, and you’re sure you’ll never need to access data on another type of data source, then you may want to use the native driver supplied with your DBMS. However, if you may need to access data that’s stored in a different form in the future, using an ODBC API now could save you a loads of rework later.
ODBC drivers are also optimized to work with specific back-end data sources, but they all have the same front-end interface to the driver manager. Any driver that hasn’t been optimized for a particular front end, therefore, is probably not as fast as a native driver that’s specifically designed for that front end.
A major complaint about the first generation of ODBC drivers was their poor performance when compared with native drivers. Recent benchmarks, however, have shown that ODBC 4.0 drivers are quite competitive in performance to native drivers. The technology is mature enough that it’s no longer necessary to sacrifice performance to gain the advantages of standardization.
ODBC and the internet
Database operations over the Internet differ in several important ways from database operations on a client/server system, although the user may not notice any difference. The most visible difference from the user’s point of view is the client portion of the system, which includes the user interface.
In a client/server system, the user interface is the part of an application that communicates with the data source on the server — using ODBC-compatible SQL statements. Over the web, the client portion of the system is still on the local computer, but it communicates with the data source on the server using the HTTP standard protocol.
Anyone with the appropriate client-end software (and the appropriate authorization) can access the data that is stored out on the web. This means that you can create an application at your work computer and then access it later with your mobile device.
In the web-based system, communication between the application front end on the client machine and the web server on the server machine takes place using HTTP. A system component called a server extension translates the commands coming over the network into ODBC-compatible SQL. Then the database server acts on the SQL, which in turn deals directly with the data source.
In the reverse direction, the data source sends the result set that is generated by a query through the database server to the server extension, which then translates it into a form that the web server can handle. The results are then sent over the web to the application front-end on the client machine, where they’re displayed to the user.
The most popular browsers, Google Chrome, Mozilla Firefox, Microsoft Internet Explorer, and Apple Safari, were not designed or optimized to be database front ends. For meaningful interaction with a database to occur over the Internet, the client side of the system needs functionality that the browser does not provide. To fill this need, several types of client extensions have been developed.
These extensions include ActiveX controls, Java applets, and scripts. The extensions communicate with the server via HTTP, using HTML, which is the language of the web. Any HTML code that deals with database access is translated into ODBC-compatible SQL by the server extension before being forwarded to the data source.
Microsoft’s ActiveX controls work with Microsoft’s Internet Explorer, which is a very popular browser. However, it has recently lost market share to Google Chrome and Mozilla’s Firefox.
Of course, validation checks can also be made at the server end by applying constraints to the values that data items can take. As with Java applets, scripts are embedded in an HTML page and execute as the user interacts with that page.
ODBC and an intranet
Because an intranet is contained within a single organization, you don’t need complex security measures such as firewalls. All the tools that are designed for application development on the web operate equally well as development tools for intranet applications. ODBC works on an intranet in the same way that it does on the Internet.
If you have multiple data sources, clients using web browsers can communicate with them with SQL that passes through HTML and ODBC stages. At the driver, the ODBC-compliant SQL is translated into the database’s native command language and executed.