BDE, ADO, and ODBC… what is the difference?
Starting with dBASE PLUS 9, a new data access layer has been added to the product. The new layer has been around for some time, was developed by Microsoft and is called ADO or Active Data Objects.
Ironically, ADO is much like the BDE in the way it tackles cross-database connections. It uses a Client API layer and also a middle layer to communicate through a driver to the exact database on the backend.
The next question for many developers and users of dBASE is which one do I use? That depends on the current state of the application you are trying to deploy.
This paper will outline the architectures for BDE (Borland Database Engine), ADO (Active Data Objects), cover ODBC (Open DataBase Connectivity), and finally finish with a discussion and flowchart on which is approach is best for the users of dBASE.
In the early to mid-90’s, Borland had an abundance of database and data-access technologies. However, they wanted to come up with a one engine fits all, which was really the precursor to the ODBC standard. The BDE or Borland Database Engine as it is known, is just that product. It has native interfaces to both dBASE and Paradox files, has an ODBC Socket included, and also another “enterprise” socket used by the SQL Links product. Below is the architecture diagram that sums up the technology.
High level architecture of BDE
This was an incredibly rich interface to communicating with many underlying databases regardless of the method used, it was fast, full of functionality, and allowed for direct native access to dBASE (.DBF) and Paradox files (.DB). The BDE code is still owned by Embarcadero, the owners of the Borland codebase. In other words, dBase, LLC., does not own and cannot make changes to the BDE in any way. This means when there are bugs, limitations, or wanted features, dBase the company is unable to make changes to that technology.
The biggest problem with the BDE is that investment and development stopped back in 2001 when Borland EOL (End Of Life) the product. That being said, the BDE is still used by millions around the world and will be used into the future. There are, however some limitations that cannot be overcome at this time, the biggest being that it is 32-bit compiled, which means that the product will not be natively compiled on 64-bit machines. Therefore, sometime in the future, the BDE will not work on those machines if they do not support 32/64 bit as is found today.
So if you are using the BDE and are not having issues or have worked around a vast majority of the issues and have a working product, then it would most likely be suggested that unless you need to change databases, or have some other external requirement, the BDE is the best choice in that situation. Other situations may not have the same outcome and will be covered in the last part of the chapter.
ADO, which stands for Active Data Objects was created by Microsoft to be the solution against ODBC originally. While ADO has a wide audience, most of the ADO thunder was taken by ADO.Net in the past few years.
Just to be clear, the ADO included in dBASE PLUS 9with ADO is the ADO based on OLE DB and NOT ADO.NET.
As Microsoft defines ADO:
“Microsoft ActiveX Data Objects (ADO) enable your client applications to access and manipulate data from a variety of sources through an OLE DB provider. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.” Microsoft – MSDN
As you can see by the below architecture, it is very similar to the BDE.
High level architecture of ADO
The advantages of ADO currently consist of Microsoft supported technology, which means the ADO-layer is up to date with the latest Microsoft Operating Systems. It also means it behaves much better on 64-bit machines and of course has full 32-bit support.
ADO, much like the BDE, also supports the concept of an ODBC Socket, which allows ADO to use standard ODBC drivers that use the Socket as a conduit through the ADO layers. This means that connecting to additional databases with a standard ODBC driver is available to the technology as well.
ADO also has up-to-date network support and multi-user support built in. This gives an advantage to ADO over the BDE in most “outside” databases, meaning not .dbf and .db files respectfully.
The one drawback to the ADO approach is that it does not support native dBASE (.dbf) or Paradox (.db) files out of the box, which means you will have to use a driver for that connection. In this case, most ODBC drivers are still based on Level-5 compatibility and not the Level-7 that the BDE is used to communicating ultimately, this means that ADO is a poor choice if you plan on connecting and using native dBASE or Paradox files.
The reason why we cover ODBC is the fact that it is the only way to communicate to additional databases using BDE and ADO. Yes the BDE has SQL Links, but they are fairly old and things have changed in the database market since they were introduced. The same can be said about ADO, there are database specific drivers made using the OLE DB interface, however many now bypass that approach and use the built-in ODBC Socket in ADO for that type of interaction between data and databases across a network.
The diagram below shows the layers a common ODBC approach uses:
Taken from Microsoft / MSDN
One of the interesting facts about ODBC is the following, taken from Wikipedia:
ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver can be thought of as analogous to a printer or other driver, providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An application that can use ODBC is referred to as "ODBC-compliant.” Any ODBC-compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMSs and even for text or CSV files. [Taken from Wikipedia]
This means that using either the BDE or ADO, the dBASE developer has a wide variety of databases that can be connected to and manipulated.
CORRECT! Since dBASE does not currently support a native ODBC socket at this time, the only way to get cross-database connectivity is to use either BDE or ADO. ODBC needs to have the Socket in order to communicate.
The following is a simple flowchart to help dBASE developers ascertain which technology is best for which solution.
High level questions for picking a data-access solution
The flowchart is fairly straight forward: if you have an application using the BDE and it is working, stay with the BDE. If you are using the BDE and having issues, it may be worth investigating moving to ADO. If you were looking to start a new project, then the recommendation would be to use ADO for future development. The main reason again for this recommendation is that dBASE cannot guarantee when, or what, operating system from Microsoft will not allow it to run. ADO is currently being supported and will be for the foreseeable future.
This flowchart is meant as a helper and not an end-all, be-all, for picking a technology. Each technology decision must be made with the best information available at the time. The BDE may be the perfect choice in some cases of new development and ADO would not be the best choice.