Converting BDE applications to native InterBase with IB Objects

By: Jason Wharton

Abstract: I would like to demonstrate with an easy to follow example of how to convert an existing application (of any size) to native InterBase in merely minutes.

  Why Convert from using the BDE to IB Objects?

Besides the obvious benefit of not having to worry about distributing, installing and possibly conflicting with other versions of the BDE, there are numerous other benefits that you get when porting your application to IB Objects. Here are just a few:

InterBase 6, 7 and 7.5+
InterBase 6.x and 7.x have significant new features that most if not all versions of the BDE do not fully support. These include 64bit integers used for scaled precision numerics to avoid rounding glitches, date and time columns, quote delimited identifiers, read-only media database capability, and more. IB Objects fully supports these new features.

At the time of the 4.x release of IB Objects, Borland does not offer any free distributions of InterBase 6 for commercial use. It is possible, however, to download older, free beta binaries, an evaluation version of the current commercial desktop edition, or even to "roll your own" by downloading and building from the "Open Edition" C source code.

IB Objects emulates the BDE 100%
Every feature and capability of the BDE based components that pertain to InterBase are emulated in the TDataset based components of IB Objects. As will be witnessed later on in this guide, most existing applications will need only a global search and replace of unit and class names from the BDE based ones to equivalent IB Objects based ones. I have gone to a lot of painstaking effort to make it easy to move an app with as few changes as possible. It isn't by any means perfect but very little fuss should be required. What this means to you as a developer is you can convert your application (of any size) in minutes. I really mean this. To see for yourself, please go through this guide to walk through the conversion of two sample applications: BDE to IBO Conversion Guide

For those who continue to use the BDE with other databases including InterBase as one of them, it is possible to omit the BDE from the InterBase version by "quick porting" it to IB Objects using the conversion tool provided prior to building the EXE for the InterBase driven app. Thus, the application can still be maintained as a single base of code for all databases. There would just be two different EXE's, one for InterBase and one for the rest using the BDE.

Stability
IB Objects has been used in many environments and situations where the BDE was too unstable to perform adequately. Most notable are things like web CGI & ISAPI applications and multi-tier application servers that all require threading and/or high volume access. Mostly the BDE hurt you by leaking memory and causing the system to crash frequently in order to recover its memory. This just isn't acceptable if your services need to minimize down time.

IB Objects is running in these environments without the instabilities and memory leaks. IB Objects just gets the job done.

IB Objects is well over 10 years old (as of version 4.8.6) and has been quite stable for many years now. Its architecture is firmly rooted and not in need of any "re-thinks" to get better performance or efficiency. Thanks to thousands of developers using IB Objects and reporting bugs we continually eradicate any significant bugs as they appear. When bugs are discovered and isolated, fixes are provided sometimes in mere minutes or hours, not months.

Performance
IB Objects out performs the BDE in most all benchmarks I have seen run, quite considerably in some. A performance advantage of 200 to 500 percent can be expected from a benchmark that emulates a typical application. In particular IB Objects has been designed to perform best when used over a LAN or even a slower network connection.

One benchmark that focused on preparing and opening queries showed that IB Objects took 1/20th of a second longer to prepare a query 20 times using a local connection. But using a remote connection IB Objects was well over 2 seconds faster at 2.2 seconds and the BDE at just under 5 seconds.

IB Objects's design does add a little bit more complexity and richness to the client for the sake of improved performance over slower connections in a remote environment. My reasoning is that in a local environment the cost of complexity will not be significant. In the case above, I doubt that you are going to miss the small fraction of a second longer it took to prepare and open 20 queries. It's really easy to appreciate a 2 second pause instead of a 5 second pause when using a remote server.

Where the performance really pays off is in the number of optimizations and server-side capabilities that IB Objects taps into through its sophisticated SQL processing. These capabilities make it possible to do things that otherwise you simply would not consider for performance reasons.

Transaction Handling
The BDE keeps much of InterBase's transaction capability hidden underneath a restrictive layer. Being an engine that attempts to "flatten out" many different systems into a generic interface it has to operate using the lowest common denominator. This excludes much of what InterBase is capable of doing.

With IB Objects you get full and complete access to InterBase transaction capabilities. This includes the ability to setup multiple concurrent transactions for a single connection and also transactions that span multiple databases concurrently. Thus, you can easily take advantage of the TPC (two-phase commit) capability of InterBase that was previously impossible with the BDE.

You get the full range of transaction parameters and apply them even on a table-by-table basis. IB Objects fully supports the four different isolation levels and parameter combinations that InterBase supports, along with capabilities to wait or deadlock immediately for conflict resolution, utilize server-side auto commit and soft commits, and more.

The BDE would struggle to handle large datasets and often impose FetchAll situations that would cripple a server performance-wise. It also uses very inefficient techniques to try to avoid holding a transaction open. IB Objects provides a very comprehensive and efficient mechanism for avoiding "stuck" transactions without imposing FetchAll's and other quirky behaviors.

With IB Objects you have full control over how transactions are used in an application. This applies to the physical (handle allocation from the server), logical (user's logical unit of work) and explicit (programmer defined units of work) transaction aspects. The ease of use that the BDE's abstraction provided when working with explicit transactions is not lost when you move to IB Objects, unlike all of the other native InterBase solutions, IBX to mention one.

Server-side processing of Filter, Locate, Lookup and RecordCount
The BDE often had quite a bit of trouble handling certain SELECT statements and would resort to bringing all the records of a table to the client and attempt to process the information locally. After all, the BDE's roots are tied heavily into the local processing of data. The trouble with this is it often creates performance hangups as people's databases grow over time and more and more records need to be pulled to the client and maintained there.

IB Objects's design keeps 99% of all processing on the server where it belongs in a client/server application. This does involve quite a bit of complex SQL parsing and construction of various derived cursors to perform the various functions required but in IB Objects it is all built-in and automatic. If it cannot determine SQL so that a function can run on the server it will still bring information to the client and get the job done as the BDE did.

So, the worst-case scenario is a transparent shift to more BDE-like thinking and the best case is you get to use your database server and network in the most appropriate manner. Wherever possible, IB Objects will deliver functionality from the server rather than asking the client to do it.

  • Filters are all processed on the server with the option (in IB Objects 4.x and higher) to perform efficient client-side filtering.
  • Client-side filters are to use the OnFilterRecord event.
  • BDE filter syntax is fully supported with wild cards using FilterOptions.
  • FindFirst, FindNext, FindPrior and FindLast with Filtered either true or false is supported.
  • Locate(), Lookup() and RecordCount...
    • ... only become difficult when aggregate queries are used.
    • ... are not performed on the server if the query already has sufficient records in the buffer.
    • ... work on the server in conjunction with all the other capabilities such as filters and other more advanced capabilities that IB Objects provides.
    • ... work in conjunction with CachedUpdates, Filter, Filtered, SetRange, etc.
  • Filter, Locate(), and Lookup() are all integrated into the case insensitivity features that can be easily setup in a global spot.
  • SetRange and master-detail all work in conjunction with the other dataset capabilities.


More live dataset conditions
The BDE often has trouble delivering live result sets. For many queries it is unable to figure out how to allow the user to make changes. IB Objects is able to deduce data manipulation conditions for almost any SELECT statement. With a little tinkering it is even possible to make JOIN-based datasets live.

Of course you can use the InsertSQL, DeleteSQL and EditSQL to make any dataset live. IB Objects just makes it so that it is rare that you need to hook in custom update SQL statements.

Cached Updates
Using CachedUpdates in the BDE is very limiting in what you can do with the datasets. If you close a dataset, all updates for it are cancelled. If you have a master-detail relationship then you can only work with a single master record at a time or you lose the changes made in the detail records. You also have much less control over the order in which the updates are applied.

Because the BDE uses Paradox tables to cache the updates, it has to go through a fairly complex process to keep the dataset in sync with the update buffers. It is very common to hear of bugs and quirks that trip people up when working with CachedUpdates. No more satisfactory implementation of them was to be found. For these reason and others I have long waged a war against the use of CachedUpdates.

With IB Objects CachedUpdates are handled natively in the dataset buffers so things are much more efficient to maintain and there is a lot more control over how they are applied. All of the BDE limitations mentioned above are overcome as well. It is possible to refresh, close and open datasets, resort records, etc. and not lose your cached update buffers. They remain intact in the secondary cache buffers of the dataset. For Release 4 I have done a lot of work to improve the harmonization of CachedUpdates with the server-centric mechanisms of the IB Objects data access foundation classes.

Refreshing datasets
Using the BDE it is not possible to refresh a TQuery. IB Objects provides three options for refreshing both tables and queries through the RefreshAction property: raOpen, raKeepRowPos and raKeepDataPos. No more silly code all over the place storing, setting and freeing bookmarks. (which don't work reliably anyway when using the BDE, IBX, and most other third party datasets)

Inserts into queries
When inserting records into the TQuery component they would disappear from the buffer and so it necessitated a refresh of the dataset for each insert performed. This led to very slow performance and quirky behavior of user's applications.

In IB Objects it is possible to insert into both my queries and table components and the insert remains in the buffer where inserted. It is internally flagged such that it won't mess up other algorithms designed to assume that records are in a particular order. It still requires a refresh in order to get into the proper sort order according to how the server would return it in the dataset but I leave it up to you to do the refresh or not. In the future I plan to put it in the right place if it can figure out where it belongs among the records on the client.

Meaningful bookmarks
When using bookmarks with the BDE what you get is an arbitrary integer that identifies a record's ordinal position in a dataset. But, as soon as you close that dataset the bookmark can no longer be considered accurate. It also cannot be used to synchronize with another dataset that may be returning the same data for the same reasons. Nor is possible to configure the bookmark in any way to accomplish these things.

In IB Objects it is possible to configure the bookmarks and determine their format and content. Thus, by using meaningful data like a primary key column (or columns), you can have permanently accurate bookmarks that can be used between datasets and even from one transaction to another.

This technology also serves as a foundation for many other time saving and elegant solutions.

For example, to efficiently keep dataset buffers in sync with changes in other datasets, bookmarks and actions are optionally broadcast so that other datasets can respond to them and make appropriate responses. There are three levels or scopes that are considered in this system:

  1. Datasets within the same transaction context.
  2. Datasets in other transaction contexts but in the same connection context.
  3. Datasets in the applications of other users currently logged into the same database.


This way datasets can stay current without having to refresh the entire dataset each time it becomes invalid. It is very easy to configure this in your applications.

Native Components and Controls
Along with the TDataset realm of Delphi and the BDE emulation components are the native components and controls also available in IB Objects. I call then "native" because they are all designed from TComponent, TCustomEdit, TCustomGrid, etc. & up. They have their own proprietary architectural foundation and have a rich interface for working with InterBase to produce the ultimate client/server applications.

Some of the features are:

  • Built-in QBE (Query By Example) criteria entry for most visual controls making powerful search built-in to your application data entry forms.
  • Centralized column/domain based properties to provide data dictionary-like ease.
  • Specialized client/server friendly searching mechanisms.
  • Higher-level controls for very RAD applications.


Revised for IB Objects 4.x

Server Response from: ETNASC01