Icon Appendix A - Differences from the BDE

There are several key differences between the BDE and DBISAM that should be taken into account, especially when converting an existing application that uses the BDE over to DBISAM.

Information All comparisons below assume usage of only the Paradox and dBase/FoxPro local table formats available in the BDE. For numerous reasons Access is not included in this comparison.


DifferenceFurther Details
BLOBsThe BDE allows (using Paradox tables) a portion of BLOB fields to be stored in the actual records in addition to storing the BLOB fields in a separate physical BLOB file. DBISAM does not support this and more closely resembles the dBase table format where all of the BLOB fields are stored in a separate physical BLOB file. Also, DBISAM always buffers BLOBs in memory when records are being added or edited. This is in contrast to the BDE, which will write overflow BLOB data to temporary files on disk when they exceed an acceptable amount of memory consumption (acceptable is determined internally by the BDE).
Batch MovesDBISAM does not currently contain a batch move component, however bulk inserts and updates can be accomplished via SQL INSERT and UPDATE statements. DBISAM also allows for importing and exporting tables to text files via the ExportTable and ImportTable methods of the TDBISAMTable and TDBISAMQuery components.
Cached UpdatesDBISAM supports cached updates, although there are some differences between the way the BDE handles cached updates and DBISAM handles cached updates. DBISAM caches the entire source dataset immediately upon beginning cached updates, whereas the BDE caches records "on demand" as the dataset is updated. Also, the reconciliation event is different for DBISAM, although the basic principles of reconciliation are the same.
Concurrency (Multi-User Usage)The BDE in general, and Paradox tables in particular, can be difficult to set up for proper multi-user usage. There is the .NET file, .LCK files, and the Local Share setting to deal with and improper settings can cause data refresh problems and in the worst case, data corruption and loss of data. With DBISAM, all locking is done through the operating system and does not involve any external files that must be configured. There is absolutely nothing extra that you must do when writing an application for single-user or multi-user use. All of the hard work is done for you, transparently, and the only task left up to you is copying the tables onto the network file server. Please see the Locking and Concurrency topic for more information.
DistributionDBISAM can be compiled completely into your application and does not require any other DLLs or external components. Runtime package support is also provided with the dbxxxxxr.bpl (Delphi and C++Builder) or bpldbxxxxx.so (Kylix) package that is distributed with DBISAM (the XXXXX is replaced with a 3-digit DBISAM version number, for example 300 is version 3.00, and a d for Delphi, a c for C++Builder, or a k for Kylix, along with a single digit version number such as 5 for Delphi 5).
Encryption and User SecurityThe BDE includes support for user security through encryption and passwords with the Paradox table format. DBISAM also supports encrypting a table with a password as well. Whenever you attempt any operation on an encrypted table in DBISAM you will be prompted for the password. You should be extremely careful with this functionality since you will not be able to open an encrypted table if you lose the password. The encryption in DBISAM is a strong 8-byte block cipher called Blowfish and can be replaced with the block cipher of your choice. Please see the Starting Sessions and Opening Tables topics for more information.

In addition to this basic security, the DBISAM database server allows for a more complete security model by offering complete user-based security along with database-level rights that can be assigned on a per-user basis. Also, all communications in the with the DBISAM database server can be encrypted with the same strong Blowfish encryption technology to prevent any data from being "sniffed" on the network. By default, all administrative access and all login and password information are automatically encrypted and are never sent in an unencrypted fashion over any network. Please see the Encryption, Server Administration, and Customizing the Engine topics for more information.
Error TrappingDBISAM uses its own exception class called EDBISAMEngineError, not the BDE-specific EDBEngineError exception class. The behavior between the the two exception classes is similar, however the EDBISAMEngineError exception class only contains an ErrorCode property (and some additional context information) whereas the EDBEngineError exception class contains an array of error classes, each with its own error code. The reason for this is that the BDE can raise multiple error codes in one exception, whereas DBISAM only raises one error per exception. Please see the Exception Handling and Errors topic for more information.
FiltersBoth the BDE and DBISAM provide expression filters through the Filter, FilterOptions, and Filtered properties, and a callback-based filter mechanism exposed as the OnFilterRecord event that allows you to filter on any type of arbitrary data.

DBISAM uses straight SQL syntax for its filters, including the ability to use SQL functions and extended operators such as IN, LIKE, and BETWEEN. Because of this, filters in DBISAM are strongly-typed and you cannot mix strings with integers, and vice-versa. The BDE uses a pseudo-SQL filter syntax that allows some implicit type conversions such as this. However, other than this difference the two syntaxes are almost exactly the same for basic expressions that do not use SQL-specific extensions.

The expression filters in both DBISAM and the BDE are optimized by the database engine, which means that whenever an index (either primary or secondary) is available that satisifies a portion or all of the filter criteria, then that index is used to locate the appropriate records instead of scanning top to bottom through the actual data records. Please see the Filter Optimization topic for more information.

The record sequencing when filters are in effect is identical to that of the BDE as well as the record counts. One of the main differences between the BDE and DBISAM is the inclusion of NULL values when performing less than (or equal to) comparisons. The BDE will include NULLs in the result of such a filter, but DBISAM will not. The standard behavior for SQL selection is to not include NULLs, which is the reason behind this difference.

Please see the Setting Filters on Tables topic for information.
Free Space ManagementThe BDE practices free space recycling in the Paradox table format but not in the dBase or FoxPro table formats. In these formats free space is not recycled and after many inserts and deletes the tables tend to get bloated. This is primarily because with these formats the records are not physically deleted, only marked for deletion, and their corresponding index keys are kept in the indexes. DBISAM recycles all free space in data records, indexes, and BLOBs transparently and without any user intervention required. When a record is deleted, the space is marked as free and available for re-use immediately. An OptimizeTable method is also provided with the TDBISAMTable component that allows you to optimize a table for a particular index order (i.e. "clustering" the table), optimize BLOB access, and also remove any free space from the data records, indexes, and BLOBs. Please see the Optimizing Tables topic for more information.
In-Memory TablesThe BDE allows for the creation of in-memory tables that behave somewhat like a regular table, but are severely limited in several areas such as the ability to add and use indexes (both primary and secondary) and the ability to use BLOB fields. Also, an in-memory table created through the BDE is automatically destroyed upon closing and cannot be shared by multiple TTable components. The low-level BDE calls to take advantage of in-memory tables are also quite confusing to most novice developers. DBISAM, on the other hand, overcomes all of these limitations and even allows in-memory tables to be shared by multiple TDBISAMTable components. Also, to use in-memory tables in DBISAM is as simple as setting the DatabaseName property to "Memory" for the TDBISAMTable component. Please see the In-Memory Tables and Opening Tables topics for more information.

Information Because in-memory tables in DBISAM act like regular disk-based tables, you must first create the table using the TDBISAMTable CreateTable method and delete the table using the TDBISAMTable DeleteTable method to get rid of the table. You can also use the SQL CREATE TABLE and DROP TABLE statements to perform the equivalent functions using only SQL.
IndexesThe BDE supports several different indexing schemes for local databases through separate drivers. DBISAM most closely resembles the Paradox table format in that it supports primary and secondary indexes, but it does offer some features that are found in the FoxPro and dBase index formats also. The following are notes on the differences between the BDE local database index formats and DBISAM's index format:

• Primary Indexes

Paradox allows for tables with no primary index defined. DBISAM also supports tables without a primary index defined by the user or developer, but will automatically define a primary index if one is not defined explicitly. This automatically defined primary index is based upon the special, non-changing, RecordID psuedo-field found in every DBISAM record.

• Case-Insensitivity

Paradox supports case insensitive indexes for secondary indexes. DBISAM also support case-insensitive indexes, but for both primary and secondary indexes.

• Secondary Indexes

Paradox stores secondary index definitions in separate files, one file for each separate index. This is in addition to a separate file for the primary index. DBISAM stores all primary and secondary indexes in one .idx file, which cuts down on file handle usage and headaches associated with distribution. This is similar to the index formats of FoxPro and dBase.

• Key Compression

The Paradox and dBase index formats do not include any type of index key compression at all, while the FoxPro index format includes very good index key compression. DBISAM allows you to choose from duplicate-byte compression, trailing-byte compression, or full compression (both types of compression combined). The Foxpro index format always uses full compression and does not allow you to choose the compression method.

• Descending Indexes

The Paradox index format allows for descending secondary indexes in version 7.0 and above. DBISAM allows for descending indexes also, but for both primary and secondary indexes. Both the Paradox and DBISAM index formats allow for individual fields within an index to be marked as descending so that you an mix ascending with descending fields in the same index.

In addition to the index formats, the BDE supports several features for searching on indexes and setting ranges that are noteworthy:

• Partial Field Search

The BDE will allow you to search on a partial field count of the entire active index. For example, if you had a primary index on a Paradox table that consisted of CustomerNum, OrderNum, and LineNum, the BDE would allow you to search on just CustomerNum to find the record you are looking for. DBISAM also supports this feature in the exact same manner as the BDE.

• Partial Field Range

The BDE also allows you to use this same principle when applying ranges to a table, and again DBISAM fully supports this method of setting ranges.

• Logical Record #'s

When using the Paradox table format with the BDE, you can retrieve a logical record number based upon the currently active index. DBISAM supports this feature.

• Exact Record Count

Paradox allows you to get an instantaneous and exact record count even when a range is currently set. DBISAM also supports this feature.
International SupportBoth the BDE and DBISAM provide international support in the form of proper collation and sorting of indexes for tables that use a non-US language. DBISAM provides international support for Delphi and C++Builder through the Windows locale support provided in the operating system. The locale can be specified on a table basis and multiple tables with multiple locales can be used in the same application. For Kylix applications, the only locale supported currently is the default ANSI Standard locale provided by DBISAM. DBISAM will raise an exception if a table is attempted to be opened or created on a machine that does not have the proper support installed in the operating system for a desired locale, including Linux. Please see the Creating and Altering Tables and Opening Tables topics for more information. In contrast, the international support in the BDE is provided via custom language drivers that are provided by Borland with the BDE.

Information DBISAM does not support international date, time, or number formats in filter or SQL statements, but rather uses a standard ANSI format for all dates, times, and numbers.
Low-Level API CallsThe BDE requires that you must use low-level API calls to accomplish certain tasks. Restructuring tables, providing progress information to users during batch processes, and copying tables require lengthy and cryptic amounts of code to accomplish the desired task. DBISAM provides well-documented and easy-to-use properties, methods, and events via the various DBISAM components for these purposes and completely removes the need to make any API calls at all.
Memory UsageDBISAM does not pre-allocate memory for data, index, and BLOB buffers like the BDE does. In contrast, DBISAM only allocates memory for caching on an as-needed basis and is restricted to the following limits by default:

• Data Record Buffers

32 kilobytes is the maximum amount of record buffer cache and 8192 record buffers are the maximum that will be cached at one time

• Index Page Buffers

64 kilobytes is the maximum amount of index page buffer cache and 8192 index page buffers are the maximum that will be cached at one time

• BLOB Block Buffers

32 kilobytes is the maximum amount of BLOB block buffer cache and 8192 BLOB block buffers are the maximum that will be cached at one time

Information These figures are on a per physical table basis within the same session. Tables opened in different sessions use different caches, hence they use additional memory. In contrast, if you open the same physical table multiple times within the same session DBISAM will only use one cache for the table and share this cache among all of the open table instances. Also, if the number of records updated in a transaction exceeds the above figures then the memory consumption will automatically be expanded to accomodate the transacton and reset back to the default values when the transaction commits or rolls back.

These figures can be changed via the following properties in the TDBISAMEngine component, so they are only default values:

MaxTableDataBufferCount property
MaxTableDataBufferSize property
MaxTableIndexBufferCount property
MaxTableIndexBufferSize property
MaxTableBlobBufferCount property
MaxTableBlobBufferSize property
NULL SupportDBISAM includes complete NULL support that behaves identically to that of Paradox, although it is implemented in a safer and more thorough manner. The rules for NULL support in DBISAM are as follows:

• If a field has not been assigned a value and was not defined as having a default value in the table structure, it is NULL.

• As soon as a field has been assigned a value it is not considered NULL anymore. String, FixedChar, GUID, Blob, Memo, and Graphic fields are an exception this rule. When you assign a NULL value (empty string) to a String, FixedChar, or GUID field the field will be set to NULL. When the contents of a Blob, Memo, or Graphic field are empty, i.e. the length of the data is 0, the field will be set to NULL.

• If the Clear method of a TField object is called the field will be set to NULL.

• NULL values are treated as separate, distinct values when used as an index key. For example, let's say that you have a primary index comprised of one Integer field. If you had a field value of 0 for this Integer field in one record and a NULL value for this Integer field in another record, DBISAM will not report a key violation error. This is a very important point and should be considered when designing your tables. As a general rule of thumb, you should always provide values for fields that are part of the primary index.

• The BDE will include NULL values when performing less than (or equal to) comparisons in filters or SQL queries, but DBISAM will not. The standard behavior for SQL selection is to not include NULLs, which is the behavior that DBISAM uses.
PerformanceDBISAM handles automatic change detection differently than the BDE, and this can cause differences in performance. With the Paradox table format, the BDE only checks for changes on disk (and subsequently refreshes its local buffers) when it needs to read data physically from the disk and when also when a record lock is acquired. With the dBase and FoxPro table formats, the BDE will never refresh local buffers unless it is forced to read data physically from the disk or a record lock is acquired. By default DBISAM uses the same type of change detection as the BDE does with the Paradox table format. This is controlled by the StrictChangeDetection property of the TDBISAMSession component. The default value is False, indicating that lazy change detection is in effect. If this property is set to True, indicating that strict change detection is in effect, DBISAM will always check for changes by other users before any read operation and will always ensure that its local buffers contain the most up-to-date data. Please see the Change Detection topic for more information.

Finally, the BDE has a Local Share setting that determines whether writes are cached for shared tables. Setting the Local Share setting to False can lead to very disastrous results if the application is unexpectedly terminated since it can cause all updates to be lost forever. DBISAM, on the other hand, never caches writes for shared tables. If you wish to ensure that the operating system subsequently physically flushes the updates to disk (some operating systems such as Windows 95/98/ME/NT/2000/XP may cache writes for a short period of time) you may use the TDBISAMTable or TDBISAMQuery FlushBuffers method or the TDBISAMSession ForceBufferFlush property to do so. Please see the Buffering and Caching topic fore more information.

Information Opening a table exclusively in DBISAM will cause DBISAM to cache all writes, which will result in excellent performance. However, an unexpected termination of the application can cause data loss similar to setting Local Share to False with the BDE. Please see the Opening Tables topic for more information.
Queries (SQL and QBE)DBISAM includes complete support for queries using SQL SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX statements that is almost entirely compliant with the Local SQL syntax available in the BDE. The features that are currently not supported in DBISAM's implementation of these SQL statements include:

• FULL OUTER JOIN clause

DBISAM does not provide for a FULL OUTER JOIN clause, although LEFT OUTER JOINs and RIGHT OUTER JOINs are fully supported.

• ANY or EXISTS operators for sub-selects

DBISAM does not provide for the ANY or EXISTS operators for specifying sub-select predicates in WHERE clauses. However, DBISAM does allow for using the IN operator for sub-select queries within an SQL SELECT statement.

• DISTINCT clause with aggregates

DBISAM does not support the use of the DISTINCT clause with the COUNT, MIN, MAX, AVG, SUM, or RUNSUM aggregate functions.

DBISAM also includes many additional features and enhancements that are not found in the Local SQL syntax provided by the BDE. Please see the SQL Reference Overview topic for more information.

DBISAM also does not support using QBE for queries.

The BDE issues cryptic SQL error messages that usually provide little or no indication of where an error exists in an SQL statement. DBISAM always provides for complete and descriptive error messages that indicate exactly where an error exists in the SQL syntax.

DBISAM supports the use of multi-statement SQL scripts in the TDBISAMQuery component, complete with parameter events for each statement, progress events, and error-trapping events. DBISAM SQL statements such as ALTER TABLE and CREATE INDEX fire progress and status events through the TDBISAMQuery component. Please see the Executing SQL Queries topic for more information.
Stored ProceduresDBISAM does not support stored procedures in the traditional sense or per the SQL standards. However, DBISAM does provide for server-side procedures implemented via the TDBISAMEngine.OnServerProcedure event in the TDBISAMEngine component, and this event can very easily launch any type of scripting language on the server to implement a scripted server-side procedure similar to a traditional stored procedure. You can also use the TDBISAMEngine OnServerScheduledEvent event to implement scheduled processes that run on the server in the same fashion as a server-side procedure. Please see the Customizing the Engine topic for more information.

Also, DBISAM provides support for DDL and DML SQL statements in scripts within the SQL property of the TDBISAMQuery component. This allows you to write scripts that contain multiple SQL statements. The only requirement is that each SQL statement is separated by a semicolon. Please see the Executing SQL Queries topic for more information.
Transaction SupportTransaction support in the BDE for local databases is somewhat limited in the amount of records that can participate in a given transaction. The highest limit currently imposed is 255 records and this depends upon whether you're using the dBase/Foxpro or Paradox table formats. DBISAM does not impose any limits on transactions, and you may have as many records participating in a transaction as available memory will allow. Unlike the BDE, which uses a log-based transaction system for local database formats, DBISAM implements transactions completely in memory and buffers all updates during a transaction. These updates are not written to disk until the transaction is committed, and are discarded if the transaction is rolled back. Also, the BDE uses a dirty-read transaction isolation level for transactions on local databases, whereas DBISAM always uses a read-committed transaction isolation level for all transactions. Please see the Transactions topic for more information.

Information Neither the BDE or DBISAM offer fail-safe transactions, so do not rely on them to prevent data inconsistencies with complicated transactions that affect multiple files unless you can guarantee that the application will not get unexpectedly interrupted. DBISAM is slightly more immune to these type of problems due to its buffered transaction design, but problems may still arise if the application is unexpectedly interrupted during the process of committing a transaction.
Image