Changes From Version 3.x

The following items have been changed in Version 4.x from Version 3.x:

The physical table format has changed for version 4 and all tables in 3.x and earlier formats will require upgrading to the current format using the TDBISAMTable UpgradeTable method or the new UPGRADE TABLE SQL statement. Please see the Upgrading Tables topic for more information.

The major changes to the format include:

Change

Description

Table Signatures	Every table is now stamped with an MD5 hash that represents the hash of a "signature" that is specified in the EngineSignature property of the TDBISAMEngine component. In order to access any table, stream, or backup created with a specific engine signature other than the default requires that the engine be using the same signature or else access will be denied. Please see the Customizing the Engine topic for more information.
Locale IDs	The language ID and sort ID values (Word values) for a table in 3.x and lower have been replaced with one single locale ID (Integer value). This causes a change in the TDBISAMTable RestructureTable method, which has been renamed to the AlterTable method to maintain consistency with the ALTER TABLE SQL statement (see below). Also, the LanguageID and SortID properties of the TDBISAMTable component are now one LocaleID property. Finally, the SQL LANGUAGE ID and SORT ID keywords have been replaced with the single LOCALE keyword in SQL statements, and some of the language identifiers (string values) have been modified to reflect the change to a locale instead of a language identifier.
Table Encryption	The default table encryption in prior versions of DBISAM was weak XOR encryption and, although it was fast, it was also easily broken. The table encryption in version 4 is Blowfish encryption that is not easily broken. All table passwords are stored as MD5 hashes encrypted with the same Blowfish encryption. Please see the Encryption topic for more information.
System Fields	There are two new "system" pseudo-fields in every table called "RecordID and "RecordHash". These fields can be indexed, filtered, etc. but do not show up in the field definitions for the TDBISAMTable or TDBISAMQuery components. RecordID is an integer value (4 bytes) representing the fixed "row number" of a given record. RecordHash is an MD5 binary value (16 bytes) that represents the hash of a given record. If you upgrade a table that already has a field named the same as either of these fields, your field will be automatically renamed by the UpgradeTable method or the UPGRADE TABLE SQL statement to '_'+OldFieldName. In other words, an underscore will be added to the front of the existing field name.
Auto Primary Index	In version 3.x and earlier you could have a table without a primary index. In version 4, if you do not define a primary index when creating or restructuring a table, DBISAM will automatically add a primary index on the system RecordID field mentioned above.
BLOB Compression	You may now specify compression for BLOB fields when creating or restructuring a table. The compression is specified as a Byte value between 0 and 9, with the default being 0, or none, and 6 being the best selection for size/speed. The default compression is ZLib, but can be replaced by using the TDBISAMEngine events for specifying a different type of compression. Please see the Compression and Customizing the Engine topics for more information.
Maximum Field Size	The maximum size of a string or bytes field is now 512 bytes instead of 250 bytes.
FixedChar Fields	String fields that are of the ftFixedChar type do not automatically right-trim spaces from strings assigned to them as they have in the past. String fields that are of the type ftString still treat strings like VarChars and right-trim the strings assigned to them. For example, assigning the value 'Test ' to the two different field types would result in the following: ftString='Test' ftFixedChar='Test ' This is useful for situations where you want to keep trailing spaces in string fields.
GUID Fields	GUID fields are now supported and are implemented as a 38-byte field containing a GUID in string format.
AutoInc Fields	Auto-increment fields are now always editable and you may have more than one autoinc field per record, with each field incrementing independently. Because these fields are editable, the SuppressAutoIncValues property has been removed from both the TDBISAMTable and TDBISAMQuery component and the NOAUTOINC clause has been removed from the SQL statements. The way autoinc fields work now is that they will auto-increment if a value is not specified for the field before the Post operation (field is NULL), and will leave any existing value alone if one is already specified before the Post operation. If you do not want an end user to modify any autoinc fields directly then it is extremely important that you mark any autoinc fields as read-only by setting the TField ReadOnly property to True before the user is allowed to access these fields.
Descending Index Fields	You may now specify which fields are ascending or descending in an index independently of one another. This change also modifies the AddIndex method of the TDBISAMTable component slightly as well as the TDBISAMIndexDef objects used in creating and altering the structure of tables. With SQL you can simply place an appropriate ASC or DESC keyword after each field specified for an index definition in a CREATE TABLE or CREATE INDEX statement.
Index Page Size	You may now specify the index page size when creating or altering the structure of tables. This changes the TDBISAMTable AlterTable method slightly as well as the CREATE TABLE SQL statement syntax. Also, there is a new IndexPageSize property for the TDBISAMTable component. The minimum page size is 1024 bytes and the maximum page size is 16 kilobytes. The index page size affects the maximum key size that can be specified for an index, so if you try to index very large string fields you may get an error indicating that the index key size is invalid. Also, regardless of page size the maximum key size for any index is 4096 bytes. Finally, the maximum number of fields that can be included in a given index has been expanded from 16 to 128 fields. However, the number of indexes per table is still only 30 indexes and has not changed.

The TDBISAMTable RestructureTable method is now called the AlterTable method to be more in line with the name of the ALTER TABLE SQL statement. Also, the TDBISAMTable OnRestructureProgress event is now called the OnAlterProgress event.

The TDBISAMTable OnDataLost event will now fire when adding unique secondary or primary indexes that cause key violations. Also, the ContinueRestructure parameter to this event is now called the Continue parameter in order to be more in line with its new dual-purposes.

The TDBISAMQuery OnQueryProgress event is now of the type TAbortProgressEvent to reflect the fact that it will be used for more than just the OnQueryProgress event in the future.

The addition and subtraction of dates, times, and timestamps in filter and SQL expressions have changed slightly. Please see the SQL Reference Operators topic for more information.

There are also new filter and SQL functions for converting milliseconds into the appropriate number of years, days, hours, etc. Please see the New Features in Version 4.x and the SQL Reference Functions topics for more information.

The index compression/de-compression code has been vastly improved so as to be much more efficient, especially when there are a large number of duplicate keys in the index and the compression is set to duplicate-byte or full compression.

The DBISAM table stream format has changed completely. It is now more similar to a binary import/export format and can now include just a subset of fields from the original table and does not include index information that previously caused many problems with loading streams saved from query result sets into tables, etc.

Like tables themselves, streams are signed with the current engine signature to ensure that only the current engine signature, or the default engine signature, can access the stream. Also, even though the table that a stream is created from is encrypted, the resultant stream will never be encrypted and you must make sure to take extra caution if you do not want to expose data improperly. Please see the Loading and Saving Streams with Tables and Query Result Sets topic for more information.

The table locking in DBISAM has changed completely in order to streamline transaction locking, prevent deadlocks during transactions, and improve the performance of the table and transaction locking. Previously table locking was done at the individual table level, so if you started a transaction on a database with 50 physical tables opened for that database, DBISAM would have to place a transaction lock on all 50 open tables before starting the transaction. It would also have to subsequently write lock them during a commit and then unlock everything for each table after the transaction was committed or rolled back. Now all table locking is centralized in one hidden file called "dbisam.lck" (by default) and located in the physical database directory. In case anyone mistakes this for a Paradox-style lock file, it is definitely not anything close. The lock file in DBISAM version 4 is just an empty "container" used to perform byte offset locking at the operating system level and the existence of the file is strictly optional - it will automatically be created by DBISAM as needed. Likewise, if the file is left there (which it will be since DBISAM prefers not to have to constantly recreate it when needed) it will not cause any harm, unlike with a Paradox lock file. With this new type of locking, DBISAM only needs to place one lock call to the OS when a transaction is started (instead of the previous scenario of 50 calls), one write lock call during a commit, and one unlock call during a commit or rollback. It also completely eliminates deadlocks during transaction locking since this architecture makes it impossible to get a deadlock. Please see the Locking and Concurrency and Transactions topics for more information.

The default lock file name "dbisam.lck" can be modified to any file name desired by modifying the TDBISAMEngine LockFileName property.

A few TDBISAMSession properties have been modified slightly to reflect some changes in the remote access. The RemoteType property has been removed and been replaced with the RemoteEncryption, RemoteEncryptionPassword, and RemoteCompression properties. The RemoteEncryption property specifies that any comms requests or responses should be encrypted using the strong crypto in the engine, and the RemoteEncryptionPassword specifies the password to use for the encryption. This password must match the password used by the server engine to encrypt/decrypt comms on its end. Also, in version 4 *all* administrative access requires the use of RemoteEncryption=True. You cannot log into the administrative port on a server without encryption turned on and the password set to the proper password for the server that you are accessing. In addition to this, all login information is automatically encrypted using the RemoteEncryptionPassword, so regardless of whether RemoteEncryption is turned on or not, the password must still match that of the server or you won't be able to log in using a non-encrypted connection either. The RemoteCompression property allows you to dynamically change the compression for the comms at any time before, during, or after logging into a database server. Each request and response is tagged with a specific compression level, thus allowing unlimited flexibility in determining how much/little compression to use. The property is specified as a Byte value between 0 and 9, with the default being 0, or none, and 6 being the best selection for size/speed. Because of these property changes, the TDBISAMSession GetRemoteSessionInfo method has been modified to reflect whether the session is encrypted or not instead of the type of session (rtInternet or rtLAN previously).

The TDBISAMSession method GetRemoteLog for retrieving the server log from the server has been removed and replaced with two different methods, one for retrieving the total number of log entries called GetRemoteLogCount, and one for retrieving a specific log entry from the server based upon its ordinal position in the log called GetRemoteLogRecord. This change is due to the abstraction of the log storage in the TDBISAMEngine component when running as a server (EngineType=etServer). Previously the log storage was a "black box" text file that was maintained by the server. Now the log storage is abstract and is handled via the OnServerLogEvent event in the TDBISAMEngine component. A TLogRecord record is passed to an event handler for this event and the event handler is free to store this data in whatever way it deems appropriate. Likewise, the OnServerLogCount event is triggered in the TDBISAMEngine component when the client session calls the TDBISAMSession GetRemoteLogCount method and the OnServerLogRecord event is called when the TDBISAMSession GetRemoteLogRecord method is called.

By default, the server application that comes with DBISAM uses event handlers for these events to simply write out these log records as binary records in a log file.

Please see the Customizing the Engine topic for more information.

The following types have been changed or removed:

Type

New Type

TDBISAMPasswordEvent	TPasswordEvent
TDBISAMDatabaseRight	TDatabaseRight The TDatabaseRight type has also been expanded to include new rights for backup (drBackup) and restore (drRestore) of a database, as well as rights for performing maintenance (drMaintain) on a database like repairing and optimizing tables and renaming objects in a database (drRename).
TDBISAMDatabaseRights	TDatabaseRights

The following constants have been changed or removed:

Constant

New Constant(s)

DBISAM_LOCKTIMEOUT

DBISAM_READLOCK
DBISAM_READUNLOCK
DBISAM_WRITELOCK
DBISAM_WRITEUNLOCK
DBISAM_TRANSLOCK
DBISAM_TRANSUNLOCK

This was done to give the developer more control over which condition he/she was responding to, especially when it comes to transaction lock timeouts.

The RestructureFieldDefs and RestructureIndexDefs have been removed and replaced with common TDBISAMFieldDefs and TDBISAMIndexDefs objects. These new objects allow the TDBISAMTable CreateTable method to be changed so that it is identical to the AlterTable method (used to be called RestructureTable), thus eliminating the need for the old way of creating a table and then immediately altering its structure in order to add DBISAM-specific features to the table. These objects are assignment-compatible with their TDataSet cousins TFieldDefs and TIndexDefs.

There is one important change in the TDBISAMFieldDefs Add method that is different from the standard TFieldDefs Add method. The TDBISAMFieldDefs Add method is overloaded to allow for the direct specification of the FieldNo of the TDBISAMFieldDef being added. This is to allow for moving fields around without losing any data with the AlterTable method. Also, the TDBISAMFieldDefs object has an additional Insert method that allows for the insertion of a TDBISAMFieldDef object in a specific position in the TDBISAMFieldDefs. Please see the Creating and Altering Tables topic for more information.

The TDBISAMTable and TDBISAMQuery BlockReadSize property functionality has been modified so that it behaves like the TDBISAMTable and TDBISAMQuery RemoteReadSize property, which does not have the limitations that the BlockReadSize property used to have and can also very easily optimize C/S access so that records are retrieved from the server in batches.

The TDBISAMTable RecordIsLocked and TableIsLocked methods no longer attempt to make locking calls in order to determine whether a record or table is locked, and only reflect whether the current table cursor has a given record or table locked. If you want to edit a record you should just edit the record and respond accordingly to any locking exceptions that occur if a table or record is already locked.

The TDBISAMTable and TDBISAMQuery Locate method implementation has internally been moved into the engine itself, which should result in some faster performance for Locate calls, especially when accessing a database server. Also, the Locate method can now take advantage of indexes in live query result sets (as well as canned result sets) when optimizing its searching. These changes to Locate do not cause any code changes in your application.

All DBISAM error strings are now marked with the resourcestring directive and are located in a new unit (Delphi) or header file (C++) called dbisamst.

The TDBISAMQuery Params property is no longer the standard TParams object, but rather is now a custom TDBISAMParams object. This also holds true for the individual TParam objects contained within the Params property, as they are now TDBISAMParam object. This was done to fix a bug in the parsing of parameters in SQL statements in the TParams object, as well as to enable the use of a common set of objects for both queries, custom SQL and filter functions, and server-side procedure calls. Also, with this change we have added the TDBISAMParam AsLargeInt property to allow you to retrieve and assign 64-bit integer parameters.

The TDBISAMQuery component now processes SQL scripts client-side so as to allow for the use of parameters with scripts. A new OnGetParams event is fired whenever a new SQL statement is prepared. This allows one to execute an SQL script and populate the parameters in a step-by-step fashion. However, it does come at a price when executing large SQL scripts using a remote session. Previously with 3.x the entire script was executed on the database server, but with version 4 each individual SQL statement is parsed and sent to the server independently, so this can result in much more network traffic. The work-around is to send any very large SQL scripts to the server to be executed in the context of a server-side procedure, which will keep the processing of the script entirely on the server but still allow for parameters in the script.

SQL statements and filter expressions now require all constants to be enclosed in single quotes as opposed to double-quotes. Identifiers such as table names and column names can still be (and must be) enclosed in double quotes or brackets. This allows DBISAM's parser to distinguish properly between identifiers and constants, which previously would confuse the parser, especially with expressions like this:
```
MyColumName="MyColumnName"
```
where the parser didn't know whether to treat "MyColumnName" as a constant or a column value.

The use of the asterisk (*) as a wildcard along with the equality (=) operator in SQL statements is no longer supported. Instead, you must use the LIKE operator and the percent (%) wildcard character like this:
```
MyColumName LIKE 'Test%'
```

The SQL aggregate and distinct processing, as well as the result set ordering, has been improved so as to reduce the amount of I/O used to perform these functions. The results should be fairly improved over 3.x, especially with large source tables. In addition, the MIN and MAX aggregate functions can now take advantage of indexes when SQL statements like the following are used:
```
SELECT max(MyField) FROM MyTable
```
where MyTable has an index on MyField. You can also now use the MIN and MAX aggregate functions with string fields. Finally, the SQL SELECT statement's TOP clause can now take advantage of indexes to optimize its performance quite a bit over 3.x.

The MEMORY keyword has been removed from SQL statements and should be replaced with a database specification of "Memory\". For example, in 3.x you would specify the following SQL SELECT statement to retrieve data from an in-memory table:
```
SELECT * FROM MEMORY biolife
```
In version 4 you should use:
```
SELECT * FROM "\Memory\biolife"
```

The WITH LOCKS clause has been removed from the SELECT SQL statement. To ensure that data does not change during the course of a SELECT statement you should wrap the statement in a transaction.

The SQL and filter LIKE operator now accepts an ESCAPE clause to specify an escape character:
```
SELECT * FROM MyTable WHERE MyColumn LIKE '100\%%' ESCAPE '\'
```
In the above example the backslash serves as the escape character indicating that the character after it, the percent sign (%), should be interpreted literally and not as a wildcard like it normally is. The above SQL statement will find all records where MyColumn begins with '100%'.

More Support Options

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Incident Reports

Roadmap