An application accesses a database by using a driver (in this case, the ODBC driver), which is specifically designed to interface with that particular database. The driver’s front end, the side that goes to the application, rigidly adheres to the ODBC standard. It looks the same to the application, regardless of what database engine is on the back end. The driver’s back end is customized to the specific database engine that it’s addressing. With this architecture, applications don’t have to be customized to — or even be aware of — which back-end database engine actually controls the data they’re using. The driver masks the differences between back ends.
Using ODBC to interface with SQL databases.The ODBC interface for SQL servers
The ODBC interface is essentially a set of definitions, each of which is accepted as standard. The definitions cover everything needed to establish communication between an application and a database. The ODBC interface defines the following:- A function-call library
- Standard SQL syntax
- Standard SQL data types
- A standard protocol for connecting to a database engine
- Standard error codes
To perform an operation on a database, include the appropriate SQL statement as an argument of an ODBC function call. As long as you use the ODBC-specified standard SQL syntax, the operation works — regardless of what database engine is on the back end.
Components of ODBC
The ODBC interface consists of four functional components, referred to as ODBC layers. Each component plays a role in making ODBC flexible enough to provide transparent communication from any compatible front end to any compatible back end. The four layers of the ODBC interface are between the user and the data that the user wants, as follows:- Application: The application is the part of the ODBC interface that’s closest to the user. Of course, even systems that don’t use ODBC include an application. Nonetheless, including the application as a part of the ODBC interface makes sense. The application has to know that it’s communicating with its data source through ODBC. It must connect smoothly with the ODBC driver manager, in strict accordance with the ODBC standard.
- Driver manager: The driver manager is a dynamic link library (DLL), which is generally supplied by Microsoft. It loads appropriate drivers for the system’s (possibly multiple) data sources and directs function calls coming in from the application to the appropriate data sources via their drivers. The driver manager also handles some ODBC function calls directly and detects and handles some types of errors. Although Microsoft originated the ODBC standard, it is now universally accepted, even by open-source hardliners.
- Driver DLL: Because data sources can be different from each other (in some cases, very different), you need a way to translate standard ODBC function calls into the native language of each data source. Translation is the job of the driver DLL. Each driver DLL accepts function calls through the standard ODBC interface and then translates them into code that is understandable to its associated data source. When the data source responds with a result set, the driver reformats it in the reverse direction into a standard ODBC result set. The driver is the crucial element that enables any ODBC-compatible application to manipulate the structure and the contents of an ODBC-compatible data source.
- Data source: The data source may be one of many different things. It may be a relational DBMS and an associated database residing on the same computer as the application. It may be such a database on a remote computer. It may be an indexed sequential access method (ISAM) file with no DBMS, either on the local or a remote computer. It may or may not include a network. The myriad different forms that the data source can take require that a custom driver be available for each one.
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 example, 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 specific back end on the server. The actual 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, with a minimum of delay.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 sometime in the future, then using an ODBC API now could save you a great deal 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, SQL 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.
A client/server system versus a web-based database system.Server extensions
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.
A web-based database system with a server extension.
Client extensions
Applications such as Microsoft Access 2019 are designed to operate either on data that is stored locally on the user’s machine, on a server located on a local or wide area network (LAN or WAN), or out on the Internet in the cloud. Microsoft’s cloud repository is called OneDrive. It’s also possible to access an application in the cloud using nothing more than a web browser. web browsers were designed — and are now optimized — to provide easy-to-understand and easy-to-use interfaces to websites of all kinds.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.
ActiveX controls
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.Scripts
Scripts are the most flexible tools for creating client extensions. Using a scripting language, such as the ubiquitous JavaScript or Microsoft’s VBScript, gives you maximum control over what happens at the client end. You can put validation checks on data-entry fields, thus enabling the rejection or correction of invalid entries without ever going out onto the web. This can save you time as well as reduce traffic on the web, thus benefiting other users as well. 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, SQL, and an Intranet
An intranet is a local- or wide-area network that operates like a simpler version of the Internet. 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 (and the appropriate client and server extensions) 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.Want more SQL success? Check here for our ten SQL tips.