|
InterBase Connectivity for Delphi/C++Builder Clients Back to Home Page Back to Really Useful Page Date: 4 July 2000 Author: Helen Borrie Contributors: Dalton Calford, Jeff Overcash, Jason Wharton In this paper, we discuss two main issues for developers writing InterBase client applications in Delphi or Borland C++ Builder (BCB): 1) Is direct access to InterBase's Application Programming Interface (API) better than using the BDE and, if so, why? 2) Two major suites of Delphi database components are available for InterBase - the InterBase Express set (IBX), that shipped as a Beta with Delphi 5, and the IB Objects set (IBO), which has been in production releases since 1997. Are they equivalent? If not, how do they differ? We present the background of the two suites along with a discussion of the functionality supported by each. This paper's scope is database connectivity. Component support for server operations is a topic for another paper in its own right. The commonalities and differences between the four connectivity options concerned - BDE, InterBase Express, and the two separate IB Objects strains, are tabulated in summary form at the end.
First, it will be helpful to examine the standard database support which is provided by the native Delphi VCL and the Borland Database Engine (BDE). 1.1 The Borland Database Engine The BDE has a life stream that goes back many years before the advent of RAD tools, predating even 16-bit Windows by some years. It first came to prominence as the engine behind Paradox 3.5 and Quattro Pro, was modified a little later to provide an API for DBaseIII-Plus, FoxBase (later DBaseIV and FoxPro) and some other ISAM databases, as well as an API to SQLLinks pass-through drivers for several SQL engines in the pre-Windows era. It moved onto the Windows platform with the first, unhappy, premature Paradox for Windows release. A little after that, at version 3.something, it redeemed its glorious DOS reputation with a very fast, stable release accompanying the 16-bit Paradox 5 for Windows. From Delphi 1 forward, the BDE has continued to grow as an API layer between the RAD platform and native or open drivers for an increasing number of database platforms. Although it has not been free of aches and pains, it has had a significant part to play in the success of Delphi in the database arena over the past five years. Deploying database applications that need the BDE makes the installation footprint huge. Licensing restrictions require that it be deployed intact, with full support for the Paradox engine. These restrictions exist partly because the BDE creates Paradox tables for caching and, sometimes, other purposes, even when Paradox is not the back-end of the application. 1.2.2 Fragility and Cost for Internet Deployment Other deployment problems occur when the BDE is used over the database back-end of a web application. ISP support for system-level services often comes at very high cost. The overhead of the BDE, with its large footprint and its reputation for fragility - often justified through developer neglect of its configuration requirements - can negate the advantage that InterBase's small footprint and "hands-free" operation give it over competitors. The BDE can be an unacceptably costly choice. 1.2.3 "Lowest Common Denominator" Economic considerations apart, the much more significant down-side of using the BDE is that it necessarily cripples the capacity of any database to utilize all of its features. The purpose of the BDE is to flatten out the differences between the feature sets of the diverse range of databases it supports. In order to "dummy out" transaction support and two-phase commit for databases that don't have them, it must limit client applications to one transaction per connection. This rules out asynchronous processing and multi-server connections. In Delphi applications, it cripples even Paradox and DBase by flattening out client language access to a limited SQL statement set which sacrifices the grace and speed of their native Query-by-Example language. Ironically, that sparse SQL actually gets translated into QBE in the driver. In general, though, the BDE's support for native SQL databases is reasonably acceptable for low-end client access, notwithstanding some notable exceptions in certain driver implementations. 1.2.4 Performance Considerations Several factors about a BDE implementation inhibit the developer from optimizing InterBase for performance and effectiveness. The BDE-plus-driver combination adds two layers of interface code to slow down the connection between the client application and InterBase's own API layer. The deprecation of multi-transaction and multi-server capabilities, discussed earlier, also deprives InterBase of much of its capacity for load-distribution. The generalization of the VCL data components to fit a generic scenario has resulted in a connectivity architecture with definite limits. At the small end, that inhibits scaleability and performance. At varying points up the scale, as the limits begin to slow down operations, developers face the need for a direct interface, better tailored to the database's features. As the porting of Delphi's and BCB's RAD technology to new operating systems approaches - Linux this year, others possibly soon to follow - it is clear that Borland will be looking to produce a connectivity layer which is released from its Paradox legacy and its exclusive Windows OS binding. The BDE is going to be supplanted by a new, lighter, more flexible API layer between Delphi/BCB and databases. This change will pass the balance of the connectivity burden over to fatter drivers on the one hand and fatter components on the other. The new, cross-platform dbDirect database engine, in combination with the dbCLX data access objects, will eventually replace the BDE for all database connectivity. Views around the newsgroups are that Borland has produced the last release of the BDE, despite Borland's assurances that it is here to stay. The BDE was ported to Linux for Corel's Paradox for Linux release. Early releases of Delphi and BCB for Linux may ship with this BDE port but experiences so far show that some difficult hacking will be necessary to make it work with InterBase. Clearly, a dependable, BDE-free connectivity solution is on the immediate Agenda for RAD developers of InterBase clients, whether they are eyeing up the possibilities of a transition to Linux as the platform for either server or client, or considering building portable server tools. 2. Custom Connectivity: The InterBase Advantage InterBase starts its new life in the Open Source arena with a strong advantage over perhaps any other two-tier client/server back-end proposition. The two leading development environments for Windows clients - Delphi and BCB - are soon to become the first and only RAD environments for Linux client development. Native InterBase connectivity components already exist for both Windows environments: and not just one set, but two. Delphi/BCB for Linux versions are planned for at least one set. That is not a bad start. It is not the function of this paper to explain details of the Application Programming Interface (API) which are wrapped by the Delphi data access components discussed here. Except where stated otherwise, it is assumed that the components implement the API functions and pass the required arguments as expected. During the period when Delphi 3 and 4 were the current releases, Greg Deatz and a group of helpers and testers developed the FreeIBComponents suite of data access classes, including datasets inheriting from the native VCL's TDataset, for connecting directly to the InterBase API. Database and transaction components were new base classes that mimicked the native Delphi TDatabase in functionality. It was an Open Source initiative that carried the goodwill of the community and a free exchange of participation through newsgroups and an email list server. Borland/Inprise owned InterBase for many years and has shipped it on the distribution CDs, with a developer license, to install with the high-end editions of Delphi and BCB. Before Delphi 5, Delphi versions had "lip-service" support for InterBase, in the form of the TIBEventAlerter component, which was designed to give applications the capability to respond to database EVENTs defined in the server. Towards the end of 1998, with Delphi 5 on the drawing board, Borland/Inprise decided to license the rights to the FreeIB suite's data access classes and develop them to ship as components of the new version's VCL. The somewhat transformed FreeIB became part the "Kinobi" project for InterBase 6. The developer was Ravi Kumar. A beta of the components, renamed InterBase Express, shipped with Delphi 5 in the Fall of 1999. Development and beta testing continued until early in December, when unexpected events shut down the Kinobi project and left InterBase 6 in suspension for nearly two months and, along with it, plans to complete IBX. Development resumed with both InterBase and IBX in passage of ownership between Borland/Inprise and the new InterBase Corporation. Ravi moved to other duties in Borland R & D. IBX development was taken up by a Team B community support member, Jeff Overcash. Since then, Jeff has released two updates for the beta and announced his intention to aim for six-weekly updates. The comments in this article refer to the most recent (at July 4 date of writing) 4.1 Beta update, released 5 May 2000. It should be noted that IBX is still work-in-progress. Where missing or incomplete functionality is observed, effort has been made to indicate whether or when it will be addressed. 3.2 How IBX Implements the Connectivity with InterBase 3.2.1 TIBDatabase and TIBTransaction Replace TDatabase TCustomConnection is the foundation of the native Delphi VCL's architecture for managing datasets. The native Delphi (BDE) TDatabase inherits from TCustomConnection and encapsulates a one-transaction connection to a database. IBX also inherits its TIBDatabase from TCustomConnection, but exposes new properties for multiple transaction contexts. The TIBDatabase must be associated with at least one TIBTransaction in order for a database connection to occur. Thus IBX supports multiple transactions per connection and the involvement of multiple databases within a single transaction. IBX can support an implicit transaction that is started when the first dataset is placed in Edit mode. The transaction is ended when the same dataset commits or rolls back changes. The dataset will close and, with it, all datasets in the same transaction context. If the database grants only restricted privileges to certain users, it will be necessary to write code to pre-empt the default implicit transaction being autostarted when the first dataset opens, because it triggers a Prepare on all datasets, including those to which some users have restricted privileges. This typically raises security exceptions whenever those users open a form. To users reporting unexpected behavior from IBX implicit transactions, Jeff Overcash recommends always explicitly starting and committing all transactions. No information available. Not supported. IBX inherits TIBCustomDataset from TDataset, adding properties to fetch and handle sets of data from InterBase databases. It spawns four descendants to implement specific behaviors: TIBDataset executes SQL statements and returns a buffered, scrollable dataset. It has properties to access a TIBSQL object, a high-performance class for executing DDL and dynamic SQL statements as well as some DML that is unavailable through the BDE components. TIBDataset itself has separate SQL properties for selecting, refreshing, inserting, modifying and deleting. It encapsulates each of these properties in its own TIBSQL for direct run-time access to the underlying tables, achieving the effect of making a read-only dataset behave as if it were updatable. It is the general-use SQL component to populate UI controls for editing and searching. Cached updates are available. TIBTable does a fetch-all query (all columns, all rows) on a database table or view, emulating the "fat client" approach of programming desktop databases with the native Delphi TTable. Record subselection is by in-memory filtering or through views. Column subselection can be achieved only through views or through the properties and methods of the GUI controls. Cached updates are available. Although a TTable emulation component seems unnecessary and undesirable for implementing the interface to a server database, it can be presumed that its inclusion is intended to simplify a desktop-to-client/server conversion for the programmer, allowing a two-stage transition from TTable to TIBQuery or another TIBCustomDataset class. Jeff Overcash recommends dropping the TIBTable component right now because it is much slower than the other datasets and has some intrinsic problems. TIBQuery emulates the native Delphi TQuery. It can be supported by a TIBUpdateSQL, for assigning SQL to update the underlying tables of read-only data sets. Cached updates are available. TIBQuery can be used for any SELECT query, including "selectable" stored procedures and views, with or without input parameters. TIBStoredProc is for connecting to stored procedures which do not return data sets. Its Params property accepts input arguments. When execution completes, any values returned will replace the input arguments in Params. TIBDataset and TIBQuery present a comprehensive range of InterBase's capabilities for returning sets of data through SQL. TIBSQL facilitates singleton selects for refreshing lookup fields. Because more than one database can be involved in a single transaction context, access to datasets across database boundaries is possible. IBX does not implement any facility for client-side indexing of datasets, however, so that sorts, unions or joins across database boundaries in some useful sort order may still be impracticable. The Help text recommends using TClientDataset for datasets involving distributed queries. IBX does not have a specialized version of TClientDataset. IBX does not support the use of double-quoted keywords as column names in query strings, e.g. SELECT BEGINNING, MIDDLE, "END" FROM MYTABLE. Support in the dataset editor for double-quoting symbols is dysfunctional and should be avoided until it is fixed in a future release. Dialect 3 case-sensitivity of table and column names appears to be unsupported. (c) Updating, Inserting and Deleting The IBX components offer a range of options for editing data sets. Insert, Append, Update and Delete methods are provided for use with TIBTable, which connects to tables and views. Live datasets are otherwise not supported. All other datasets require the developer to provide SQL for these operations. TIBUpdateSQL is available for TQuery; TIBDataset embeds separate TIBSQL (SQL Update) objects for each editing operation, which the developer can access through the dataset editor once the SELECT SQL has been defined. Inserts are achieved by appending rows to the buffer. The effect is that the user will not see inserted rows except by scrolling to the end of the dataset, even if the dataset is committed using COMMITRETAINING (q.v.). Because of the underlying memory model for storing rows on the client side, insert-in-place cannot be feasibly implemented. A complex, new memory model is planned to fix this in the forthcoming months. It is proposed to incorporate the testing of work-in-progress into the Delphi 6/Kylix beta program. Upgrades will be publicly released. Beta versions may be available. The recommended workaround for now is to tie the IBX dataset to a TClientDataset. Cached updates are available to TIBTable, TIBDataset and TIBQuery. IBX handles cached updates internally and does not require the BDE for any operations. IBX follows the TUpdateSQL practice of clearing the cache after updates have been applied. TIBQuery Params containing the InterBase 6.x INT64 data type will return errors because the TDataset's Params property does not support it. Jeff Overcash recommends using the TIBDataset for all DML. IBX follows the TDataset model for refreshing datasets. A Refresh method is available for TIBTable. Other datasets are refreshed by closing and reopening queries. An internal TIBSQL object is available for setting SQL at run-time to restrict the rows returned, as filtering and SetRange would do in a BDE application. Event handlers are required in all cases, including TIBTable, before and after any refresh, to enable the buffer cursor to be repositioned on the "current" row. IBX's TIBTransaction makes COMMITRETAINING available as a commit action, thus surfacing the InterBase client's capability to keep the current transaction context after a commit. (Sometimes referred to as "soft commit", this option is not available under the BDE). IBX also supports the new InterBase 6.0 ROLLBACKRETAINING to do "soft rollbacks". However, in surfacing the soft commit and rollback isolation options, IBX is not capable of implementing any functionality to locate the cursor on the most recently inserted row. For TIBTable, you have the same options for master-detail relationships as with the BDE components. For the other datasets, the BDE's familiar "follow-the-master" behavior by setting the Datasource property in the detail set is not implemented in the code - it is necessary to close the dataset, set the new parameters and requery the server for the next detail subset. For Dialect 1 databases,IBX follows the same rules as the BDE access components with regard to setting parameters. Existing code that uses ParamByName or Params[n] for WHERE clauses will work for Dialect 1. For applications that are to be moved over to use InterBase 6.x Dialect 3, TQueries should be replaced by TIBDataset, rather than TIBQuery, to avoid data type errors. Note, however, that the intention is for TIBSQL and TIBDataset to use the IBSQLVAR and IBXSQLDA data type structures to support IB 6.0 data types for parameters. The main drawbacks to these two data type structures is that they are not currently supported in the IDE integration and they do not follow the TParams calling convention. Both of these limitations will be addressed in future updates. For Dialect 1 databases, IBX fully emulates BDE functionality for stored procedures in TIBStoredProc, with the limitations described above with respect to the Dialect 3 datatypes. SPs which take or return arguments that are Int64 or any of the other new data types should be avoided until support becomes available in a future upgrade. TIBSQLMonitor component works in concert with the TIBDatabase and TIBTransaction components to trace various dynamic SQL operations during tuning and debugging. It has a public OnSQL event for handling the monitor's output. (a) Sessions and Multi-threaded Transactions IBX does not implement a class equivalent to the native Delphi VCL's TSession to isolate multi-threaded transactions. A TIBBCDField is available to emulate TBCDField and replace the BCD internals that the field type would otherwise pick up from the 'BCD' setting in the BDE driver. A TIBBlobStream is implemented for reading and writing blobs. Maximum size of a Blob read in from a text file appears to be 60Kb. IBX adds no IB-specific client side attributes at field level for capturing or encapsulating special InterBase datatype characteristics or to generalize the treatment of domains. There is no encapsulated mechanism for identifying a field as Boolean or for accessing generator values. InterBase Array columns are not supported. (c) Methods and Program Events The IBX implementation deprecates the BDE's implicit transaction control to the extent that it may be impracticable for applications which utilize InterBase user privileges. New methods have been added to the data access components to facilitate explicit transaction control and to support multiple database connections and multiple concurrent transactions. Inspection of the source code and help file tends to confirm that many methods, both public and private, are work-in-progress or awaiting implementation. The TIBEvents component replaces TIBEventAlerter from earlier Delphi versions, for registering interest in and responding to InterBase EVENTs. Up to 15 events can be included; events can be registered and unregistered during run-time. The QueueEvents property switches the client into and out of EVENT WAIT condition, to start or stop listening for an event. Currently, it is still a developer's task to code the synchronization of events passed back from different threads. Datasets inherit TDataset's Locate method and LocateOptions. Buffering of TIBQuery and TIBDataset can be bi-directional. Only TIBTable has buffers for primary keys. Wildcard searching is not supported. IBX data set components support the native Delphi 5 TDBNavigator; same capabilities and restrictions with navigation methods (Locate, Findkey, etc.) as for any TDataset object. No InterBase-specific navigation facilities have been added; but the TIBSQL object will be useful when writing handler code to do quick singleton selects to examine a table for existence of a specific row. TIBCustomDataset returns a buffer cache with a default size of 1000 rows, which descendants and event handlers can override via the BufferChunks property. The RefreshSQL property of TIBDataset and TIBUpdateSql allows refreshing the buffer with alternative sort orders. Buffering does not suppress whitespace in the in-memory buffers, which will have performance implications where datasets contain large varchars that typically store small actual data. IBX buffering appends inserted rows to the end of datasets. In-memory filtering is not supported except in TIBTable, where FilterOptions for case insensitivity and partial matches are not implemented. Integration of the filtered set with the FindFirst, FindNext, FindPrior and FindLast methods has not been implemented. Filtering will need to be done through event handler code and the RefreshSQL property. The filter methods and properties are available to the other datasets and the code will compile. Attempts to invoke them at run-time trigger a ShowMessage dialog 'This feature is not supported'. Bidirectional cursoring is possible with TIBDataset and TIBQuery. Supported in TIBTable only. IBX adds a simple SQL Wizard to the TIBQuery and TIBSQL, a nice Stringlist editor for maintaining the Events list in the TIBEvents component and additional design-time property editors to integrate with the Delphi 5/BCB 5 Data Module Designer. It has component editors for the IBDatabase and IBTransaction components, to simplify setting up connection strings and transaction isolation level. Most new, InterBase-specific features are surfaced in the data access objects as writeable properties. Client event handling through introduced published events is adequate for most needs. IBX adds behaviour to enable handling of multiple transactions, multiple connections, transaction isolation/concurrency and commit options. There do not appear to be any classes, internal code or surfaced properties to support multi-threading of transactions or ISAPI-style isolation. IBX dataset components work with all native Delphi 5 data-aware controls and the Infopower(TM) controls. No InterBase-specific controls have been added. At present, no results are available for tests involving production-quality data in networked environments or involving any of the processes that would be typical of a production-capable client application. The following results were produced from a performance bench originally written by Ravi Kumar to compare the "raw" performance of IBX and the BDE. It ran over tables of 50,000 records, each having two or three columns. The most dramatic advantage was on query inserts - IBQuery 720%, IBDataset 1000%, IBSQL 2100% speed improvement. Open/close table 10 times: Queries 23% faster, IBTable 2.25 times slower than TTable. Table scans, 50,000 rows: First to last, natural scan: IBQuery and IBDataset 33% faster, IBSQL 53% faster; IBTable 24% faster than TTable. First to last, indexed scan: IBQuery and IBDataset 43% faster, IBSQL 60% faster; IBTable 49% faster than TTable. Last to first, natural scan: queries 23% faster, tables 14% faster. Last to first, indexed table: queries 24% faster, tables 39% faster. IBX compared poorly on table locates - IBTable was 9 times slower than BDE, doing slightly worse on indexed locates than unindexed. IBTable indexed random edit was 12% slower than either a BDE indexed random edit or an IBX unindexed random edit. IBTable insert nearly three times slower than TTable. IBX was three times faster executing a Stored Procedure. It was eight times faster on Blob select and 12 times faster on Blob Insert. 3.5 Converting a BDE Application to IBX 3.5.1 Converting a BDE-based client/server application may be a painstaking transition. Jeff Overcash reported that it took him and John Kaster (Inprise Developer Relations) 10 days to recode the Code Central application in IBX. A good understanding of working with transactions is necessary, as dependency on implicit transaction handling will be a significant area of rework. The BDE's TDatabase shields the developer from physical transaction issues. IBX implements a less sophisticated architecture which requires explicit attention to coding details formerly hidden by TDatabase. Substantial reworking will be required if the existing application used live queries or TTables, because of transaction issues and virtual ancestor methods which have not been implemented or have been implemented only partly. The use of TClientDataset, linked to a TIB dataset as provider, has been widely recommended as a means to recoup some of the deprecated functionality or to solve problems related to unfinished functionality. Controls should work; there will be some unexpected behavior in grids which will require extra code to restore to normal; existing filters will need to be reworked (see below). 3.5.2 BDE apps that relied on TTables will be painful to convert. Performance will be disappointing. Their use is not recommended. 3.5.3 Applications that used filtering and/or SetRange will require substantial extra work. TIBTable filtering does not implement FilterOptions nor integrate with the FindFirst, FindNext, FindPrior and FindLast methods of filtered sets. Other datasets do not support in-memory filtering at all. IBX replaces it with IBSQL objects, exposed as RefreshSQL in IBDataset and through a TIBUPdateSQL for TIBQuery. These objects will require run-time handlers in order to parse filter parameter inputs and apply the Refresh method. 3.5.4 Implementation in the IDE and naming standards for new attributes and methods are generally consistent with those to which users of the native data access components are accustomed. 3.5.5 On-line documentation is very sparse, with inaccuracies and gaps, possibly occurring because of changes since the help was written. A set of conversion guidelines appears to be urgently needed to highlight the "gotchas" of conversion. The changes required for implementing a database connection are not immediately obvious, for example. Users of the components may get some help from the Developer's Guide included in the beta documentation for IB 6.x. 3.5.6 The supplied demo application is viewed as a very poor sample of client/server and multi-user programming and does not use the client/server capabilities of the components at all. Bugs have been reported. Jeff Overcash has promised new demo applications. 3.5.7 Developers who approach the conversion to IBX simultaneously with scaling up to InterBase from a desktop database can expect to encounter a lot of problems if they do not first get fully in touch with the significant differences between the desktop and client/server application models. An interim transition via TIBTable will probably be more useful in this situation, to help them up the learning curve; but developers should not hold their breath for a dramatic improvement in performance! 3.6.1. The IBX dataset classes are founded on the native Delphi TDataset, maintaining compatibility with native and third-party data-aware controls. The ownership of InterBase Express source code will pass to ISC and will become part of the InterBase source code tree. Source code for the beta version is freely available under the terms of the InterBase Public License. No deployment licensing will apply. At time of writing (4 July 2000) the current IBX version is Beta release 4.1 of 5 May 2000. IBX data access components are for use with
Not known. Jason Wharton, author of InterBase Objects, discovered InterBase in the course of an exploration of client/server database products for a huge Point-of-Sale system. Against his recommendation for InterBase, the company chose MSSQL Server 6.5. Jason sought out an employer which used InterBase and Delphi and moved on. 4.1.1 TIB_ - the Native IBO Components Not long after the release of Delphi 2, Jason discovered that the best capabilities of InterBase were virtually inaccessible to the RAD developer because of the constraints imposed by the BDE. In 1996 he determined to start his own suite of components and data-aware GUI controls to bypass the BDE and work directly with the InterBase API. He chose to develop a data access hierarchy that was independent of the TDataset and the standard Delphi data-aware visual controls, thus affording himself the opportunity to design a library of components and visual controls specifically for the client/server architecture, which could be fully customized through succeeding releases of InterBase. 4.1.2 TDataset-Based Components Once Delphi 3 came out with the new TDataset virtual class, Jason was able to embrace the best of both worlds. He put a thin layer around his IB Objects-native dataset and brought a BDE-free InterBase TDataset-compatible component suite to life. At that point, he was faced with deciding whether to continue making components based on his IBO datasets, already quite advanced, or to turn his focus to the new TDataset opportunity. Seeing that both would be relevant to a variety of development requirements, he decided to continue developing both strains of components. Today, IB Objects offers the developer both the simpler TIBO (TDataset) strain and the richer, more encompassing TIB_Dataset architecture. When the simplest IBO TDataset descendant went into production in Gimbal's Marathon 1.1, it was cynically assumed by some that the final target had been met. However, after two years' more growth, the TIBO strain today covers the TDataset niche handsomely, includes custom classes to enable developers to make their own descendant classes and sits on top of a stable architecture, well-tested in heavyweight commercial environments. At times people have wanted Inprise to acquire IBO. Negotiations were tinkered with, off and on, but Jason was concerned that a corporate development environment could inhibit the growth he envisaged for the components and lead to shortcuts and deficiencies in the product. He has continued to progress IBO under his own auspices, to keep the development in touch with and responsive to his developer community. 4.1.4 Community Involvement and Support During more than three years of development and three (soon to be four) full production releases, IBO source code has been open to its user community. The result is huge user involvement in its evolution and a panel of supporting coders who know the code just about as well as its author does. Daily support for both commercial and "free" users (see the Licensing topic, below) is provided by means of a fully-monitored, public list server. 4.2 How IB Objects Implements the Connectivity with InterBase IB Objects implements the connectivity in two distinct approaches, both available whether using the TDataset-compatible strain or the fully native IBO components.
IBO's set of "BDE-equivalent" components - referred to here as the "TIBO strain" because of its class prefix - preserves full code compatibility with controls and components that rely on the BDE's TDataset. The TIBO components provide some enhanced functionality inherited from the IBO native data access objects that affects underlying behavior, enhancing performance and case insensitivity support while retaining the exact intention of the developer's interface code. In the BDE, the embedded transaction's internals are hidden and cannot be accessed except through properties published by TDatabase. However, because the TIBODatabase inherits its transaction architecture from TIB_Database, its internal transaction is the same TIB_Transaction that is used elementally by the fully-powered TIB_ strain. Its full range of properties and methods is exposed at both design time and run-time. It is also possible to drop down additional TIB_Transaction components and define additional explicit transaction contexts. The TIBO strain is 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. 4.2.2 The "TIB-" Strain - Native IBO Components The second "full-powered" approach removes TDataset compatibility entirely from the architecture and enables complete implementation of the transactional, server-centric and multi-database capabilities of InterBase. It integrates tightly with a large suite of visual controls to facilitate rapid development and standardized behavior for the client/server interface. See Section 4.4, Controls. Unless otherwise stated, the following descriptions apply to both strains. 4.2.3 TIB_Connection and TIB_Transaction Replace TDatabase TIB_Connection obtains a persistent connection to an InterBase database and wraps all of the API functions associated with connections, as well as some some server admin functions. It can deduce protocol from the server and path properties and automatically update connection strings when one of these properties changes. The developer can access any server parameters independently, through property lists, at both design and run-time. Login support can use custom settings for roles and user names, whether implemented with the standard login dialog or with a custom dialog. The dialogs have properties and methods to limit login attempts and to respond to illegal login attempts. TIB_Connection has properties for setting the data display and case-sensitivity attributes for domains that will apply globally for the connection; and resolves the behaviour of columns which the application is to treat as Boolean. It keeps a local Schema cache and has methods for flushing and renewing it when metadata change. It can also use an alternative file for messages. It has notification methods for a large number of objects and processes, including the synchronization of datasets after external DML changes. 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, involving the connection being used by the BDE. IBO uses a model in which a logical transaction (unit of work) can involve zero to many physical transactions in its context. TIB_Transaction provides all properties, methods and events necessary to have very fine-grained control over the capabilities and interactions of physical transactions. 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 at both connection and transaction levels. This process is referred to as "OAT (Oldest Active Transaction) Management". TIB_Database (TIBODatabase for a TDataset-compatible connection) emulates the model of the native Delphi TDatabase without imposing TDatabase's restriction of limiting all connected statements and datasets to use its own internally defined transaction. The IBO database classes allow multiple transactions to be assigned to their connection context. It was implemented to make BDE-to-IBO transition simple and fast and provide an initial BDE-free application that fully emulates the original without needing to change any application code. The database component can be regarded as a staging-point toward a more complete implementation of the InterBase functionality. It is also useful for those who want to maintain a single code base for an application across different database systems. They can develop using the BDE to work with the other databases and use the quick convert templates to generate an IBO-based, BDE-free version for InterBase. TIB_Transaction has an array property, Connections[], to store a reference to each individual connection. The methods AddConnection() and RemoveConnection() are available for assigning and releasing connections. A ConnectionCount property is provided for enumerating the array. The component exposes three prioritized IB_Connection properties to simplify management of the most common multi-connection scenarios. BLR can be used from an IBO client application. All dataset components descend from one of two base classes - TIBODataset or TIB_Statement - which handle all of the necessary API calls to allocate, prepare and execute an InterBase DSQL statement. These base classes also interpret and define memory buffers that are bound to column objects for both input and output handling. The TIBO strain dataset classes (TIBOTable and TIBOQuery) are TDataset descendants wrapped by an internal TIB_BDataset (bi-directional dataset). Thus, they have all of the TDataset functionality with much of it re-implemented and enhanced by transparent calls to the properties and methods of the internal native IBO object. TIBODataset supports the assignment of a query plan, which can be either assigned in the SQL statement or patched in during the OnPrepareSQL event. The Fields array provides pointers to a large number of other arrays surfacing properties for setting virtually every attribute that a column value can have in code and in the GUI, including display masks, Boolean behavior, labels, case-insensitive and validation masks, field trimming rules, alignment, et al. Statement-level and dataset-level properties provide for the storing of design-time or runtime inputs to the SQL parser routines which support the complex system of buffer and index management underlying IBO's "smart" searching, locating, updating, lookup and refreshing. This system causes virtually any dataset - including sets returned from JOINs - to behave as "live" without the addition of any handler code. Update handling for exceptional update situations is also encapsulated and surfaced as design-time or runtime properties, rather than requiring handler code. The TIB_Dataset base class inherits from TIB_Statement and has three groups of offspring to encapsulate queries which return rows: TIB_BDataset (the internal base class for the buffered dataset classes, including TIB_Query), TIB_Cursor (and its offspring) and TIB_StoredProc. The TIB_DSQL class, which implements TIB_Statement directly, is used for all DSQL statements that do not output multiple rows - "executable" stored procedures, batched updates or inserts, performing DDL or DML statements, et al. TIBO datasets implement SetRange and smart filtering for both queries and tables, with correct resulting behavior in other dataset capabilities, including key-dependent methods such as FindKey(), FindNext(), etc. and master-detail dependency. 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. (c) Updating, Inserting and Deleting LIVE DATASETS INSERTING With IBO's TIB_Dataset components (both queries and tables) the inserted row remains in the buffer where inserted. It is internally flagged to avoid messing up other algorithms whose behaviors depend on an assumed row ordering. CACHED UPDATES Updates can be applied to the database at any level from a single row through to multiple datasets. Under the BDE, TDataset.Refresh method is not implemented for TQuery. IBO introduces implementations to refresh both tables and queries, with options to determine the state and cursor position after the refresh. BOOKMARKS IBO implements a Bookmark that can be configured for format and content. Meaningful data, such as a primary key column, can thus be stored as persistent bookmarks which can be applied to other datasets using the same keys, or broadcast so that other datasets can respond to them and synchronize their buffers when changes are posted. This buffer synchronization can be scoped to datasets within the same transaction context, those in other transaction contexts within the same connection context or to datasets in the applications of other users currently logged into the same database. 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 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. A GeneratorLinks array property, implemented in all datasets, allows the developer to link a column name to a generator name, causing the generated value to be fetched into an inserted row and made available to the application. TIB_Statement descendants all contain a Params property equivalent to that of the BDE TDataset. TDataset's Params property is directly available to the TIBO components. Additionally, various "--Links" and other properties of TIB_ datasets can be set up to assign parameters or load values into WHERE clauses by referring to columns in other datasets. It is possible to assign parameters which refer to columns not present in the dataset. TIB_StoredProc and TIBOStoredProc provide for any executable stored procedure which may or may not return a result row. Input arguments are handled automatically via the metadata, and are stored in the Params array. Results may be accessed in either the Params or the Fields array. TIBOStoredProc is also able to emulate the Params behavior of the virtual TDataset class, storing both the input and output arguments in the Params array without losing the input parameters after execution completes. Procedures that execute DML can (and should) be flagged to trigger resynchronization of datasets affected by the transaction. "Selectable" stored procedures should generally be assigned to a TIBOQuery or TIB_Query, if a buffered dataset is required. An unbuffered dataset can be returned by the "native" TIB_StoredProc when a fast, unidirectional cursor is wanted. 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 which are, in turn, used by all of the components in IB Objects. Statement plans and precise timings and the count of rows affected are automatically output, for immediate feedback on performance in applications. 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. (a) Sessions and Multi-threaded Transactions 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 provides a dataset editor in which a comprehensive range of field properties and attributes can be applied within the dataset object itself, obviating the need to set them in each individual control which uses the field. It includes an SQL editor similar to that of the native VCL and facilities to fetch and display the live data before any control objects have been set down. Property lists are provided here for setting virtually every attribute that a column value can have in code and in the GUI, including display masks, labels, case-insensitive and validation masks, field trimming rules, alignment, et al. Boolean fields can be identified, together with their True and False values, to predetermine automatically their behavior in the application and to have them display as checkboxes when they occur in grid columns. (Note, if a Boolean domain is defined in the database, this behavior can be set globally in the IB_Connection object.) IBO provides full, transparent Blob handling with no limits. InterBase ARRAY columns are fully supported, including getting and putting of slices. GeneratorLinks, described earlier, provides linking between generator columns and their generators. (c) Methods and Program Events A huge number of public methods and interface events is available to surface all of the functions of the InterBase API and to support very fine-grained access to the parsing engine, callbacks, database events, navigation operations, state changes, etc. 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. 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 provides a number of facilities, by way of dataset property settings, specialized controls, and smart buffering, to effect fast, case-insensitive, incremental searching on large datasets. Grid controls have embedded navigation tools tied to certain dataset properties, for user-configured searching. Wildcard searching is available. QUERY-BY-EXAMPLE (QBE) The controls have built-in capability to treat their contents as search criteria when the underlying dataset's state changes to dssSearch. These controls embed configurable options to change appearance when the mode changes. They are supported by a search toolbar (analogous to the TDBNavigator) with buttons for switching, ordering, saving, recalling and clearing search criteria in and out of a buffer, and for counting matching rows to the criteria entered. Searching is optionally case insensitive per column and can be configured to work with incremental searching and nearest match enabled or disabled. It is also possible to set-up automatic SOUNDEX and METAPHONE search criteria. Locate and keyed navigation methods are fully implemented in all datasets and integrated with the Filtered property. See also earlier notes on Bookmarks. Native IBO implements its own navigation bar with six button types, any of which can be invisible. All glyphs are configurable. The set includes Jump Forward and Jump Back buttons which can be configured for the number of rows per jump. There are several events to which custom handler code can be applied. Comprehensive options are supplied for buffering datasets, accessing buffer fields and navigating. Tuning for efficient buffer performance is accomplished automatically. IBO buffers all use dynamic memory allocation and suppress whitespace. Locate(), Lookup() and RecordCount() methods are optimized to work as efficiently as possible, using smart algorithms to minimize fetches. All buffer methods and mechanisms, including SetRange, master-detail and filtering are integrated with global case-sensitivity settings and with one another. Keys and whole records are allocated separately within the buffer caches. Datasets can be configured to fetch first the keys and then the remaining columns, or to fetch whole rows. The "key" approach is specifically for reducing network traffic when browsing large datasets and can be enabled or disabled as required. The buffered datasets allow forward and reverse scrolling by any increment, multi-row selects, locates, automated "follow-me" updating of lookup fields, incremental searching, et al. Although filters are all processed on the server, BDE filter syntax and FilterOptions are fully supported, with wildcards and case insensitivity. FindFirst(), FindNext(), etc. methods are integrated with filtering to navigate appropriately within the bounds of a filtered set. The OnFilterRecord event can be used to filter records within the client buffers, without losing the current dataset. TIBOQuery, TIBOTable and TIB_Query use a bi-directional cursor with a large amount of navigating capability. TIB_Cursor uses a unidirectional cursor and is capable of very fast scans of large datasets. "Smart buffering" can determine whether to use the key buffer for searches, locates and internal operations. A full-scale dataset editor is invoked by double-clicking on any dataset component. Datasets keep arrays for many properties and the editor's interface simplifies work by allowing entry of all attributes per column or all columns per attribute. Hyperlinks permit switching between these modes. All InterBase-specific features are surfaced in the data access objects as writeable properties. Comprehensive client event handling is provided through hundreds of published events and properties. IBO utilizes a local schema cache. IBO fully encapsulates handling of multiple transactions and multiple connections by clients configured for it, and all isolation/concurrency and commit options. "Pessimistic locking" can be enabled, which is accomplished by preceding a transaction with a dummy update. In almost all cases, live datasets are made transparent to developer and user. Native IBO provides a full GUI development environment of more than 50 data-aware controls designed for InterBase client development. Specialized edit controls and bars encapsulate behaviours for searches and navigation, with fine control of editing and display characteristics. Controls connect to the data access objects through the TIB_Datasource. The 'TIBO' strain of components uses a TDatasource and connects to the native Delphi data-aware controls. This strain can use the InfoPower(TM) controls, requiring the specialized TwwIBOQuery and TwwIBOTable components for earlier Infopower versions. 'TIBO' can continue to support existing third-party report and graphing tools designed for the native Delphi VCL. Several third-party component vendors provide support for the native 'TIB_' strain of data access. 4.4 Converting a BDE Application 4.4.1 Conversion from the BDE based components to the 'TIBO' strain is a matter of performing a global search-and-replace of unit and class names. Except for one rarely-used feature of TTable, IBO implements full BDE emulation. 4.4.2 Existing settings in the BDE native Interbase driver can optionally be read at connection time to have the IBO connection duplicate the BDE configuration. 4.4.3 A detailed conversion guide is available in the on-line help, which walks through each stage of a conversion and pinpoints where differences occur. The guide explains how to test that the converted application works as it should. 4.4.4 The guide includes two practice runs that demonstrate BDE-to-IBO conversion. Both of them are fully converted in less than one minute. (a) IBO employs intelligent client-side statement parsing to optimize the dialog with the server. Users who have converted production applications report excellent performance over BDE, especially on low-bandwidth connections. (b) In performance comparisons with the BDE, IBO shows speed gains of 200 to 500 percent over the BDE on equivalent operations. (c) Much of the client-side statement parsing for dataset operations involves shifting work to the server. Operations like Filter, Locate(), Lookup(), FindKey(), GotoKey(), RecordCount, etc. can offload whole tasks, or significant parts, to the server to minimize network I/O. 4.6.1 BDE-based Delphi and C++Builder applications can be fully converted and working in minutes without code changes, retaining compatibility with native Delphi and third-party data-aware controls. 4.6.2 The "IBO 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. 4.6.3 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, etc. 4.6.4 Datasets are transparently "live" to both developer and user, with custom update SQL required only in rare conditions. 4.6.5 IBO ships with a comprehensive help file and many sample applications, accompanied by documentation, to demonstrate a representative range of configurations, component combinations and techniques. Source code and binaries are distributed under the Computer Programming Solutions Trustware License. Registered users receive full source code. Users become registered by one or more of (a) contributing actively to source code or documentation development (b) developing and deploying a client application for use by a non-profit organization (c) contributing to IB Objects source code base while using IB Objects in a fully Open Source development project, whether commercial or not (d) paying a license fee. Unless (c) applies, the license fee is payable for any successful commercial deployment, including situations where registrations were previously granted under (a) or (b). Managers of Open Source projects can get rights to distribute the IBO sources along with their projects if they make sure that the terms of the Trustware license are distributed and understood. The production release of IBO at time of writing is 3.5. IBO components are for use with
Under development, expected Winter 2000. |