Generic client-to-database layers like the BDE, ODBC, dbExpress and ADO hide most of the capabilities of transactional database engines, flattening connectivity to a generic "lowest common denominator". Powerful server databases like InterBase/Firebird and Oracle are made to conform to the behaviors of desktop databases like Paradox or dBase. It takes heavy layering of client and middleware driver code between the user and the database to accomplish this flattening, while disabling essential capabilities of the server databases' engines.
Since everything in InterBase/Firebird happens inside transactions, this approach essentially kills most of the benefits of using client/server for networking mission-critical applications.
IBO cuts right through all this and connects its data access objects directly to the application programming interface (API) of the InterBase/Firebird engine. Your application gets full and complete access to InterBase transaction capabilities - multiple concurrent transactions for a single connection and transactions that span multiple databases with two-phase commit. Four levels of concurrency isolation become available and, with them, the full, flexible range of controls that InterBase provides for optimizing transaction life and resolving lock conflicts.
What about other component suites?
Other component suites can provide direct-to-API connectivity but they do so at the cost of developer control over the logical aspects of transaction-based data processing. They are bitten by the hand that feeds them. In order to implement access to the physical capabilities of the transaction engine while remaining locked into the memory dependency of the VCL's TDataset, they sacrifice the considerable benefits the BDE provided in the way of task management.
Why choose IB Objects?
From the start IBO freed itself from the restrictions of TDataset and its limiting, local database oriented memory model. From the primitive level of TComponent forward, its classes are built on a foundation dedicated solely to how an object interface needs to interact with InterBase/Firebird with greatest effect and efficiency. Along the way, IBO has succeeded in emulating and improving on the logical task environment provided by the BDE to the degree that a developer can choose to be unconcerned with the physical transaction altogether.
An important point of differentiation from other direct access components is IBO's track record of four full, industrial-strength releases and nearly five years of consistent development, spanning all 32-bit Delphi and C++Builder releases and all versions of InterBase/Firebird from InterBase 4.x up. IB Objects won the Delphi Informant Reader's Choice award for Best Database Connectivity product in both 2000 and 2001.
The TDataset-Compatible Classes
If it suits your temporary or permanent requirements best, you can choose to develop with a suite of data access classes inherited from TDataset. This suite completely replaces the VCL equivalents, while retaining complete compatibility with the VCL data-aware controls and third-party control suites through the VCL TDatasource. Additionally, the TIBOQuery and TIBOTable classes are wrapped around a "native IBO" dataset. Several highly useful server-centric features of the native datasets are surfaced for design-time or run-time use.
These components are often used as an aid to "soft" transition from the BDE to IB Objects. Although an eventual full transition to the full capabilities of InterBase client development is the course many developers wish to take, it is perfectly feasible to use the TIBO strain as an easy replacement for the BDE in an existing application, to enhance performance, stability and server-side capability.
Developers maintaining a BDE-based application code base designed to work with different databases can easily generate an optimal, IBO-based version for InterBase by preparing quick conversion templates for global search and replace of units and class names.
Converting an Existing BDE Application
Converting an existing BDE application to use these data access components instead of TDatabase, TTable and TQuery is a straight swap that can be accomplished in less than five minutes with the assistance of the free GReplace tool from the Object Software Technology site. IBO comes with a kit of instructions for performing the conversion and two trial projects which you can convert first for practice.
NEW IN RELEASE 4: Substantial changes to horizontal dataset refinement affect the entire architecture and have their most dramatic effect on navigation of TIBOTable and base queries which could output a significantly large dataset, for example, more than 100K rows. As you issue commands to the dataset to navigate among these rows IBO will automatically do the refinement necessary to pull in only those rows that are of interest.
As long as you have proper indexes, any dataset navigation commands should complete within one or two seconds. It makes using components like InfoPower totally rock with the TDataset-compatible data access classes. Incremental searching, for example, is amazingly fast.
With the TIBO* classes you also get quite a bit of control over how transactions are used in an application. The BDE struggles to handle large datasets and often imposes FetchAll situations that cripple a server's performance. Its manner of avoiding prolonged transactions is clumsy and inefficient. IBO provides a very effective mechanism for avoiding "stuck" transactions without imposing fetchalls and other quirky behaviors.
General Features of the IBO Components
With IBO there is a very intelligent abstraction layer above the API that makes transactions much more friendly to work with. IBO considers three aspects of handling transactions. There is the physical level, the API (which is as far as other IB component sets go); there is the logical level, where units of work are defined; and, lastly, there is the explicit transaction context, which the developer can determine in the application. The three aspects overlap considerably and IBO gives full control over each of them.
In code, the developer can use the explicit transaction mechanisms to control things pre-emptively; or the application can be made to react to the logical state of the transaction that results from what the user has been doing.
The interactions of transactions, in the single connection context, are managed by the IB_Connection object. The TIB_Session object allows "super-management" of the interactions of multiple connection contexts through its own properties and methods in conjunction with a TIB_SessionProps object.
IBO automates features to monitor and, if necessary, shut down long-running transactions according to configurations set on transactions. This process is referred to as "OAT (Oldest Active Transaction) Management".
At the physical level, many things can be done to make sure a transaction isn't going to be held open for too long. IBO activates timeout handlers at various stages which, in most cases, will handle freeing up the physical transaction handle automatically. In this way, server resources are conserved and the OAT can advance in a healthy manner.
If something is hanging things up, all of the properties are available to pinpoint what is going on and respond to it. It is really easy to set up comprehensive mechanisms to interact with the user for resolving prolonged transactions. If cached updates are in use, IBO always takes care of them without having to interrupt the user at all.
With the less sophisticated alternative solutions, transactions can be held up even when using cached updates, because datasets have to be opened. Borland's IBX, for example, needs to have a physical transaction open in order to have a dataset open.
With IBO it is possible to give each dataset a specific behavior when a transaction is ended, with number of different behaviors to choose from.
Such options make IBO much more flexible than the BDE's approach, which is to fetch all records for any open datasets whenever a commit or rollback is performed. With IBO you are in TOTAL control of your application. All the features and behavior described here and much more about IBO transaction handling are documented in detail in the help files and sample applications.
- You might want to have it simply fetch all of the records and then work with the entire set of data in memory.
- You might want a dataset to refresh automatically in such a way that any changes become visible to the new transaction.
- If not all of the records have been fetched, you can simply invalidate the cursor to allow the transaction to end and keep the dataset intact. The next time it is accessed, it will recognize that its cursor is invalid and automatically open a new cursor positioned right where it was before, with minimal load on the server to accomplish it.
- You might also want to have the dataset closed, which, in native IBO, causes it to go into search mode.
IBO outperforms the BDE in most benchmarks I have observed, by 2 to 5 times in cases emulating typical applications. One benchmark, focused on preparing and opening queries, showed that IBO took 0.05 seconds longer to prepare a query 20 times using a local connection. But, using a remote connection, IBO was well over 2 seconds faster at 2.2 seconds and the BDE at just under 5 seconds.
IBO's design does confer a little more complexity and richness to client/server connectivity. The returns is improved performance for remote clients, especially over slow connections. In a local environment, the small cost of complexity will not be significant, whereas it's really easy to appreciate a 2 second pause instead of a 5 second pause when communicating with a remote server.
The performance pay-off comes in the amount of optimization and server-side capability that IBO's sophisticated SQL processing can tap into. It becomes possible to do things that otherwise you simply would not consider, for performance reasons.
Processing of Filter, Locate, Lookup and RecordCount
The BDE is heavily rooted in the local processing of data. It often has trouble handling certain SELECT statements and will resort to bringing all the rows from a table to the client in order to process the set of data locally. This often creates performance hang-ups after deployment, as databases grow and ever more rows have to be pulled to the client and maintained there.
IBO's design can keep 99% of all processing on the server, its rightful place in a client/server application. Some complex SQL parsing and construction of various derived cursors are involved to perform the various functions required. Filters can be all processed on the server, with full support for the VCL filter syntax and FilterOptions, including wildcards. It is all built-in and, by default, automatic.
Release 4 introduces Client-side filtering, with the capability to change the filter logic and refresh the buffers without having to refresh the dataset from the server. A new RefreshFilteredRows method, which works exclusively with the OnFilterRecord event, will recalculate the Filtered status of all rows in the buffer and adjust the dataset accordingly, as if a refresh had been done.
FindFirst, FindNext, FindPrior and FindLast with or without Filtering are supported and datasets behave correctly in all cases.
Locate(), Lookup() and RecordCount are performed in the record buffer and will query the server only when sought rows are not present. When this happens, operations on the server work in conjunction with filters and the other more advanced capabilities of IBO. Client-side participation in these operations only becomes difficult when aggregate queries are involved.
Filters, Locates and Lookups are all integrated with any case insensitivity that has been set up globally. SetRange and master-detail behaviour are correctly controlled and integrated both within and outside Filtering constraints.
Live dataset conditions
In client/server, there is really no such thing as a "live" view of the data, since clients never touch tables physically. The question of whether an application can work with data as if it were live is determined by the ability of the connectivity mechanism to analyze the characteristics of the selected dataset and calculate accurate SQL update, insert and delete statements from them.
The BDE often has trouble delivering live result sets because its flattened interface can fail to deduce unambiguous information about the original data. IBO is able to figure out update, insert and delete statements for most datasets. In the few complex cases which might need a little tinkering, it provides additional methods and properties which you can set manually. It is even possible to make datasets involving JOINs behave as live.
IBO datasets allow you to embed optional InsertSQL, DeleteSQL and EditSQL to make any dataset live. When you need to hook in these custom update SQL statements - to make inserts or updates to all of the tables in a JOIN by running a parameterized stored procedure, for example - you can exploit the full power of server-based processing.
CachedUpdates in the BDE puts severe limits on what you can do with the datasets. Closing a dataset will cancel all the cached updates for it. When editing a master-detail relationship you can work with only a single master record at a time or you lose the changes made in the detail records. There is little control over the order in which the updates are applied.
The BDE hold the updates in Paradox tables so it has to go through a fairly complex process for synchronizing the dataset with the update buffers. Not surprisingly, it is very common to hear of bugs and quirks that trip people up when working with CachedUpdates.
I have long waged a war against the use of CachedUpdates, for these reasons and because there simply was no adequate implementation of it anywhere to be found.
CachedUpdates was implemented in IBO for compatibility. It is handled natively in the dataset buffers, making maintenance much more efficient and allowing much more control over how the updates are applied. The BDE limitations mentioned earlier were overcome in the IBO implementation - you can refresh, close and open datasets, re-sort rows, etc. and not lose your cached update buffers.
In Release 4 a lot of attention has been given to improving the integration of CachedUpdates with the server-centric nature of IBO. These two opposed models have been harmonized considerably, resulting in better accuracy and more efficient integration. A new option has been added to show cached deleted rows.
The BDE and other connectivity alternatives do not allow you to refresh a TQuery. With IBO it is possible to refresh tables and queries. You can configure a RefreshAction property to one of three options: raOpen, raKeepRowPos and aKeepDataPos.
Inserts into queries
When rows are inserted into a TQuery object, they disappear from the buffer and don't reappear without closing and reopening the dataset for each insert performed. This causes very slow performance and quirky behavior in user applications.
In IBO, inserts into either query or table components remain in the buffer where inserted. The inserted rows are internally flagged to preserve the integrity of other algorithms which are designed for an assumed row order.
Bookmarks in the BDE return an arbitrary integer that identifies a record in a currently open dataset. As soon as you close that dataset, the bookmark can no longer be considered accurate. For the same reason a BDE bookmark cannot be used to synchronize one dataset with another one that may be returning the same data. Nor is possible to configure the bookmark in any way to accomplish these ends.
IBO bookmarks are configurable in terms of both format and content. Hence, by using meaningful data like a primary key column, you can have permanently accurate bookmarks and assign them from one dataset to another.
The same technology serves as a foundation for several other time saving and elegant techniques.
For example, to keep dataset buffers efficiently in sync with changes in other datasets, bookmarks and actions are optionally broadcast, with provision for other datasets to respond to them and take appropriate actions. The scope of this notification system can be:
- Datasets within the same transaction context.
- Datasets in other transaction contexts but in the same connection context.
- Datasets in the applications of other users currently logged into the same database.
All IBO datasets can be made "generator-aware" through the use of the GeneratorLinks property. By a simple declaration in the Object Inspector, the Dataset Editor or in run-time code, a column can be linked to a generator. The effect is that, in inserts, the next generator is returned automatically to the column when the BeforeInsert event fires.
GeneratorLinks can also be set up globally, to be available to any dataset which will be inserting into the tables that use them.
At a global level (through the Connection or Database object) the application can be made "domain-aware". Client-side attributes, such as display characteristics or Boolean attributes, can then be applied to the named domains for application-wide use wherever extracted columns using those domains are used in any dataset.
IBO provides a SQL trace monitor which the developer can build into the application and check the server conversations continually for statement bugs and opportunities to identify and optimize performance bottlenecks. It is an indispensible tool to ensure you are delivering the best possible product to your customers.
The TIB_SQLMonitor component can optionally trace and display all associated information for most calls to the API. If an enabled monitor instance exists, a batch of intercept API hooks is passed to the IB_Session and are, in turn, used by all of the components in IB Objects.
Statement plans, precise timings and the count of rows affected are automatically output, for immediate feedback on application performance.
When the intercept hooks are taken out, the application regains a direct connection to the API exports. When the monitor is not enabled, there is no performance hit and sniffing of the program API activity is impossible.
The dbHandleShared property allows sharing a single connection with BDE based components. Thus IBO can augment applications which need to remain BDE based, by enabling additional concurrent transactions, including cross-database transactions, by sharing the connection being used by the BDE TDatabase component.
IB Objects caters admirably for middle-tier application server, CGI, WIN-CGI and ISAPI styles of application development, where a tight, stable connectivity core and high performance are demanded.
These components are well-proven in multi-threading applications. Under most circumstances IBO handles sessions in multi-threaded apps automatically via thread local storage mechanisms. In applications where explicit session control is required, such as an ISAPI module, IBO's TIB_Session provides the necessary isolation.
IBO 4 introduces explicit support for connection pooling, very suitable for ISAPI applications, because each web hit can close its own connection if it used one; and connection handles need not be wasted on hits that turn out not to need a connection.
Background Processing and NT Service Applications
In Release 4, the session level background processing mechanisms have been greatly enhanced by a new architecture that into the IDLE cpu message notifications from Windows to fuel things like automatic Transaction OAT management. The result is much more fluid and efficient handling of the background tasks that IBO relies upon for efficiency. The timer mechanism is retained but is now necessary just to keep the idle messages flowing during a "wait and see" condition.
Other components tied into this new architecture include the IB_Events component (see below) and all components which descend from the TIB_Process class benefit from the session's new capability to use idle CPU cycles to fuel passive processes.
For example, when an application goes idle for 15 minutes and there are no active transactions, the app can simply let the connection handle go, by disconnecting it. After it performs the reconnection it will reallocate all the statement handles and flag all queries to reprepare themselves. When the user returns and goes to use the application, s/he is notified that the application is resuming.
- A new base class has been included for writing NT Services with IBO that uses the new session architecture for passive processing to resolve some persistently difficult issues. The new architecture is a good fit with the service application's passive manner of execution.
- The new capability also allows a transparent disconnect from the connection and, when activity starts up again, a transparent reconnect. It uses timing configurations similar to the transaction OAT handling mechanisms. This gives an IBO application some tolerance to broken connections.
The TIB_Events component replaces TIBEventAlerter from earlier Delphi versions and the IBX TIBEvents from Delphi 5, for registering interest in and responding to InterBase EVENTs. It takes care of all multi-threading issues by using synchronous event notification instead of processing the events immediately in a sub-thread.
In Release 4, the IB_Events component hooks into the session's new capability to use idle CPU cycles. A WakeUp notification system take cares of the synchronization of the event notifications.
Up to 16 events can be included; events can be registered and unregistered during run-time. An interface event handler can be added to provide custom processing of event notifications; or the component's DoEventAlert() method can be overridden to provide custom logic to the process.
IBO includes a number of "Tool" components for adding extra features to client applications: data pumping, running DDL scripts, table exports, et al., with encapsulated implementation support in the supplied controls, toolbars, dialogs, etc.
Release 4 brings some new utility components to enrich server-based application development:
- "Fuzzy" Text Search components provide a top to bottom full (fuzzy) text searching solution. It is not 100% server-based, since the index needs to be maintained by a client process. It can be used by any client, even one not using IBO. You just need to have an IBO application running somewhere to maintain the index. All of the triggers to do it are built automatically. It does logging of searches too, including internal server statistics for performance metering.
- A replication utility module, using new components for one-way replication, follows the same pattern as the search index maintenance components. Some significant similarities exist between in the maintenance processes for index tables and replicated tables.
Run-time Package and Interface Features
IBO is now split into separate packages to give better support for runtime packages and to modulize some of the more complex utilities (IBOWeb, Replication, Text Search).
The use of interfaces has been introduced to make it easier to integrate controls into TIB_Grid and to bring other external processing, such as mask handling, into core IBO classes, without requiring the necessary files be compiled and hard-linked to IBO. Third-party contributors can now maintain their components in packages separately from the IBO components.
The "Native IB Objects" Components
To harness the full server-centric capabilities of IBO, you can develop database applications that are completely free of dependency on the desktop memory model of the Delphi VCL's TDataset class and employ a rich array of "native IBO" data-aware UI controls specialized for making client/server-friendly dynamic interfaces.
Many features not found in other data-aware components are built right into these. Very robust and wide-ranging search and re-order mechanisms operate at dataset level to provide for full incremental or near-match searching and on-the-fly grid reorganization, without writing a single line of application code. Controls integrate tightly with data attributes and behaviors defined in the dataset and connection objects to facilitate rapid development and standardized behavior for the client/server interface.
Display attributes and client-side behaviors of data are set and controlled in the datasets themselves, specifically through properties and methods of Column classes. Grids, for example, do not surface a Columns object; instead, grid column behavior is controlled by the Row class of the dataset and its Column objects. A dataset row column given the BOOLEAN attribute will appear in grids as a checkbox. Standard control behavior requires no special handler code or changes to property defaults.
The native data-aware controls embed the code to utilize dataset configurations and interact with changes in dataset state, e.g. the "Query-by-Example" behavior of the specialized search controls.
Select SQL can be very broadly defined at design time, i.e. 'SELECT FIELD1, FIELD2, ... FROM ATABLE' because selection and ordering criteria can be defined with very fine granularity in other design/runtime properties. Although exact criteria definition through the SQL string alone, or with Delphi parameters, is available, use of the extra properties causes the parsing engine to make best use of its server and I/O optimization capabilities.
IBO makes heavy use of primary keys ("KeyLinks") and parameters when parsing and analyzing statements to construct executable statements for updates, inserts and deletes. It reuses the same techniques when processing search criteria. A well normalized database with small keys gives best performance.
Stored procedures that execute DML can (and should) be flagged to trigger resynchronization of datasets affected by the transaction, since the client has no "knowledge" of the internals of a server-side procedure.
In Release 4, support for macro processing has been added for the SQL and other properties of statements/datasets and scripts.
Every dataset has a KeyLinks property which defines a column or column list by which a row is distinct (unique). If it is not defined explicitly, IBO will attempt to identify it automatically from the local schema cache data. Keylinks are loaded into buffers and are referred to by many linking and parsing mechanisms.
IBO's native datasets implement linking techniques which extend beyond the simple column name matching of TDataset descendants. The developer can define several different types of set links for any dataset, including master-detail hierarchies of any depth, lookup links that are refreshed automatically and join links which are used in parsing SQL when updates or inserts are performed on implicitly JOINed sets.
Developers have access to the InterBase internal DB_KEY to guarantee that selects for subqueries and joins will always be scalar.
Other link types can be defined for user-initiated sorting of sets for searches and filtering.
* Independence and low cost of ownership
One of IBO's significant benefits is that its native data access architecture is built from TComponent up. This means you you can harness the full power of IBO without the TDataset architecture that Borland provides. What this means in terms of software investment is that you can use IBO with the standard version of Delphi - VERY cheap compared to the Professional and Enterprise versions.
If you have an open source project and you really want to minimize costs, use native IBO with standard Delphi or CPPB and your cost of ownership is a fraction of what it would be if it was entailed to Borland's high-end packages. If you are not using other commercial third party components, you lose nothing by doing this. Several third-party packages, such as the award-winning Report Builder, Report Printer Pro, FastReport and its offshoot, FuzzyReport, actually do support the native IBO data access architecture.
* Trustware Licensing Model
My company, CPS, does not depend on revenue from service and support contracts to survive. My personal objective is to make IBO so nice and easy to use that people will be eager to submit trustware revenue to keep the ball rolling. Customers are not obliged to pay for IBO until they are actually using it for production work.
IBO is free for many types of users. Those willing to contribute their time and skill to development, documentation, making samples, etc., earn that right. Hobbyists just trying to brush up skills, non-profit organizations, religious groups, students, approved open source projects and even developers with commercial intentions who don't yet have the funds to afford licensing, all can apply for fee dispensation to use IBO free under Trustware licensing. I don't see IBO developers being at any disadvantage to users of "free beer" products with respect to licensing.
Commercial users invest in IBO's continued success and development by committing a modest portion of their returned revenue and productivity savings to a subscription for commercial licensing. Because these funds go into the production of professional documentation and support materials and to fund a few employees to improve the product, it gives commercial users a way to ensure that it will thrive in the future.
It is not the purpose of Trustware to make anybody rich. It is to focus the InterBase/Firebird community's effort into one strong product instead of splintering and having many weak, poorly supported products with uncertain outcomes. IBO has been around the longest and it is well proven in high-demand production environments. I am confident that it is the best possible connectivity solution for building robust, reliable client applications for InterBase/Firebird.