Database Engines:dbespec

DBFDBE (DATA component) Foundation

The DatabaseEngine DBFDBE manages files in the DBF format. This file format is used by all Xbase dialects to store data. In a DBF file, data is stored in the form of a table where a record represents a row and a field represents a column. Rows in the table have a fixed length, and each field stores values which always have the same data type.

The DBFDBE is used as a DATA component of the compound DBE DBFNTX which is created each time an Xbase++ program starts. This occurs in the function DbeSys() whose source code is contained in the file ..\SOURCE\SYS\DBESYS.PRG. The DBFDBE performs all database operations that can be executed on a DBF file. This includes navigation using DbSkip() as well as the definition of filters and marking records for deletion using DbDelete(). In addition, the DBFDBE manages memo files which contain the text of memo fields.

Database operations that require an index cannot be performed by the DBFDBE. For these operations, the DatabaseEngine must be combined with an ORDER component to create a compound DBE. Xbase++ provides the NTXDBE which manages index files (see the next section).

The DBFDBE has some limitations which are caused by the format of DBF files or by factors associated with the concurrent operation with Clipper applications. The following table gives an overview:

Specifications for DBF files
Element Specification
File size Limited to the offset for record locks, default is 1 GB (10^9 bytes)
Max. number of fields Not limited
Max. number of records (Offset for record locks - Header() - 1) / RecSize()
Data types C, D, L, N, M
Character values Max. 64 KB
Date values Fields always 8 bytes
Logical values Fields always 1 bytes
Numeric values Fields contain max. 19 bytes, including decimal point and negative signed prefix
Decimal character Period
Memo Fields always 10 bytes; contents of the fields (text) stored in memo files and the length of the text in memo fields is not limited
Size of a memo file Limited only by system resources
SET CHARSET Character values are always stored using the OEM character set

Fields in a DBF file are strongly typed and have a fixed length. This is predefined in the file structure. The maximum value that can be stored in numeric fields depends on field length and decimal point. For negative values, a minus sign limits the smallest value additionally. Any attempt to store a numeric value too large or too small to fit into a database field results in a runtime error. Therefore it is necessary to consider the range of numeric data to be stored when numeric fields are specified.

In case of character fields, the field length limits the maximum number of characters that can be stored. If the number of characters in a string exceeds the field length, the character string is truncated and no runtime error is raised.

Using DBFDBE there is no limitation on the length of memo fields and memo files, as there is with the DOS-based 64 KB limit. Memo fields can store text longer than 64 KB characters, and memo files (DBT files) can become larger than 32 MB. However, these issues must still be considered when Xbase++ applications use the same database as DOS Xbase applications.

The Clipper compatible memo file format (DBT) uses the special character Ctrl-Z / chr(26) to terminate a memo. Therefore, it is not possible to store binary information, such as Bitmaps, in DBFDBE memo files. Alaska Software recommends the usage of the FOXDBE DatabaseEngine whenever binary data must be dealt with.

Character strings are stored in character or memo fields using the OEM character set. If the ANSI character set is selected in a program using SET CHARSET, an automatic ANSI-OEM conversion occurs when a field value is read from or written to a DBF file.

The maximum file size for a DBF file depends on the offset used for record locks during concurrent operation (see the description of DBFDBE_LOCKOFFSET below). Under Clipper, the offset varies from version 5.01 to 5.2 and can be set to a matching value in Xbase++. This allows Xbase++ to operate concurrently with existing Clipper applications in a heterogeneous network where some of the work stations run under DOS and others under a 32bit operating system. The maximum number of records is dependent on both the offset for records and on the length of records. Generally, the maximum number of records can be calculated as the offset for records locks divided by the length of a record.

The DBFDBE uses _LOCK as an reserved identifier for field variables. _LOCK is used for automatic record locking. If the DBFDBE runs in autolock mode, the DBF file must have a _LOCK field (see below).

Configuration of the DBFDBE with DbeInfo()

The DBF DatabaseEngine can be configured in specific ways using the function DbeInfo(). For example, the file extension for database files can be specified, or the operating mode for record locks can be set. The following table gives an overview of the #define constants that can be passed to the function DbeInfo() for the DBFDBE:

Constants for DbeInfo() with the DBFDBE
Constant *) Value Data type Description
DBE_EXTENSION a DBF C Extension for DBF file
DBFDBE_MEMOFILE_EXT a DBT C Extension for memo file
DBFDBE_MEMOBLOCKSIZE ro 512 N Block size for memo fields
DBFDBE_LOCKOFFSET a 10^9 N Offset for record locks
DBFDBE_LIFETIME a 50 N Lifetime of records in local cache
DBFDBE_ANSI a .F. L If .T., DBF/DBT charset is ansi
DBFDBE_LOCKMODE a DBF_NOLOCK N Mode for record locks
  1. ro=READONLY , a=ASSIGNABLE

The default values are shown in the column "Value".

DBE_EXTENSION

This constant is valid for all DBEs and defines the default file extension for files which are managed by the DBE. The default file extension DBF for the DBFDBE can be changed. This is especially useful in protecting data. For example, some users use the DIR command to search for all files with a DBF extension. Simply changing the extension is enough to keep these users from attempting to manipulate the file outside the application which uses it.

DbeInfo( COMPONENT_DATA, DBE_EXTENSION, "FBD" ) 

This call to DbeInfo() sets the default file extension for DBF files to "FBD".

DBFDBE_MEMOFILE_EXT

This constant is used to determine or change the default file extension for memo files (DBT files).

DBFDBE_MEMOBLOCKSIZE

This constant returns the minimum block size used to store text in a memo file. The default block size for the DBFDBE is 512 byte. It cannot be changed. Within a memo file, texts or character strings are stored in multiple blocks each of which is 512 bytes. When text that is only 100 bytes long is stored, it still occupies 512 bytes in the memo file.

DBFDBE_LOCKOFFSET

This constant returns or changes the offset for record locks. The offset for record locks determines the maximum size of the DBF table. The default offset for record locks is 1.000.000.000 or approx. 1GB. This corresponds to the maximum size of a Clipper-compatible DBF table.

To increase the storage capacity of DBF files to the current maximum of ~2.4GB, a locking offset of 0x80000000 must be configured. Changing the DBFDBE_LOCKOFFSET allows to extend the maximum file capacity of DBF tables beyond Clipper's capacity. However these DBF tables cannot be shared safely with Clipper due to the different locking offsets used to syncronize concurrent operations. The maximum value for DBFDBE_LOCKOFFSET is 0xFFFFFFFF.

DBFDBE_LIFETIME

This constant returns or changes the cache lifetime in 1/100 seconds. The default value is 50 milliseconds. The cache lifetime is an internal threshold of the DBFDBE DatabaseEngine. A cache lifetime of 0 forces the database engine to read each record directly from the disk. A cache lifetime of 50 milliseconds allows the DatabaseEngine to read a record via the cache provided that the data held by the cache is not older than 50 milliseconds. The internal record cache of the DatabaseEngine is used to avoid re-reading of records from disk if the cache lifetime is not exceeded.

The cache lifetime should be set to 0 if the DBFDBE is used in a multithreaded application with one of the following characteristics. If multiple threads open the same DBF table and update the data in the table in a timeframe which is smaller than the cache lifetime. If you develop applications using the Web Application Adaptor (WAA) or application server solutions, then the cache lifetime ought to be set to 0 to guarantee data correctness. This is necessary, as the update frequency is not predictable and depends on the workload of the Web solution.

DBFDBE_ANSI

This constant returns or changes the storage format of the DBF and DBT files. By default, DBF and DBT files are handled as OEM storage. If DBFDBE_ANSI is .T. (true), DBF/DBT files are treated in ANSI format.

DBFDBE_LOCKMODE

The DBFDBE supports record locks to allow concurrent operation of database applications. The DBFDBE has two operating modes for locking records. One operating mode is compatible with Clipper and requires explicit record locking and unlocking to be performed when any records are updated. The other mode uses the extended locking mechanisms of Xbase++ which support automatic record locking.

Mode: DBF_NOLOCK

The mode DBF_NOLOCK is the default operating mode. The DBFDBE is in this operating mode after it is loaded using DbeLoad(). This is also the mode of the compound DBE DBFNTX available each time an Xbase++ program starts. In concurrent operation in this operating mode, a record lock must be explicitly set using RLock() or DbRLock() before a value in a field can be changed. If new values are assigned to fields or field variables during concurrent operation without a record lock being set, a runtime error occurs. This behavior is compatible with Clipper.

Mode: DBF_AUTOLOCK

In the DBF_AUTOLOCK mode, the DBFDBE automatically executes a record lock when a record or a field is changed during concurrent operation. The lock is automatically removed after the change has been written to the file. This mode is not compatible with the lock mechanism implemented in Clipper. This means that this operating mode cannot be used in concurrent operation between Xbase++ and Clipper programs. The problem arises because the DBF_AUTOLOCK mode uses the lock mechanisms from the operating system instead of logical record locks set using a virtual offset. The operating system permits locking a record directly.

The automatic record lock smoothly handles a potential conflict that can occur during concurrent operation: the problem of the "lost update". This problem can occur when a record is processed at the same time by two programs A and B. Program A reads a record into memory, edits the values of the fields and writes the changes back to the database. Program B reads the record at the same time as program A, changes the values and writes its own set of changes back into the database after program A has just written its data. This situation causes a "lost update" scenario, because the data written into the database by program A is overwritten by the data of program B and is therefore lost.

The problem of the "lost update" is solved in the DBF_AUTOLOCK mode because only the first set of data is automatically written to the file when two programs process the same record at the same time. When program B reads a record at the same time as program A, only the data from the program that first writes its changes back to the database is generally valid.

In order for the DBFDBE to recognize and avoid the scenario of the "lost update", a database must have a field _LOCK of type "C" with the field length 4. It is used to store a counter in binary format. The DBF_AUTOLOCK operating mode does not function without the field _LOCK. The DBFDBE uses the value of the field _LOCK to recognize this potential problem situation and avoid the "lost update" scenario. Example:

When program A reads a record, the value of _LOCK might be 100. At the same time program B reads the same record. The value of _LOCK for program B is also 100. Program A changes the data and writes the changes back into the record. In performing this update, the DBFDBE locks the record, writes the values into the fields and increments the value in the field _LOCK. The DBFDBE then unlocks the record. The value of _LOCK is now 101. When program B attempts to update the record with its own changes, the operation fails because the value of _LOCK is still 100 for program B. It does not match the value of _LOCK in the database. Program B wants to change data that has already been changed by program A. The data from program A is retained and the attempt by program B to gain write access causes a recoverable runtime error.

Such a runtime error should always be handled using the control structure BEGIN SEQUENCE...ENDSEQUENCE and an error code block. In this case, it is the responsibility of the programmer to decide whether the first or last change should be valid in the "lost update" scenario. An example of how the problem of the "lost update" can be handled is shown in the following program lines:

USE Customer 

cLName   := Customer->LName           // read fields into 
cFName   := Customer->Fname           // memory variables 

@ 10,10 SAY " Last Name" GET cLName   // edit memory 
@ 11,10 SAY "First Name" GET cFName   // variables 
READ 

DO WHILE .T. 

   bError  := ErrorBlock( {|e| Break(e)} ) 
   BEGIN SEQUENCE 
      REPLACE Customer->LName   WITH cLName , ; 
              Customer->FName   WITH cFName 
      COMMIT 
   RECOVER 
      ErrorBlock( bError ) 
      i := Alert( "Data has been changed by another station", ; 
                 {"Do not save", "Save anyway"} ) 
      IF i==2 
         LOOP 
      ENDIF 
   ENDSEQUENCE 
   ErrorBlock( bError ) 

   EXIT 

ENDDO 

In this example, the runtime error that occurs in the case of the "lost update" scenario is captured using RECOVER and the decision whether the changed data should still be stored is the responsibility of the user. If the "lost update" runtime error occurs, the field _LOCK is reread and the value in the data buffer (memory) matches the value in the database field. It is then sufficient to rewrite the modified values into the database. In the example, this is accomplished using the LOOP statement which is only reached if there is an error.

Retrieve information about the DBFDBE with DbInfo()

When a DBF file is opened, an instance of the DBFDBE is created. The instance is a database object (DBO) and includes all settings of the DBFDBE. The DBF database object represents the work area in which the DBF file is opened and it manages the open DBF file. The function DbInfo() can be used to retrieve information about the DBF DBO. A #define constant which identifies what specific information is required must be passed to the function. The constants that can be passed to function DbInfo() for the DBFDBE are listed in the following table:

Constants for DbInfo() with the DBFDBE
Constant *) Value Data type Description
DBO_FILENAME ro C Complete file name
DBFDBO_LOCKMODE ro #define N Mode for record locks
DBFDBO_LOCKOFFSET ro 1*10^9 N Offset for record locks
DBFDBO_DBFHANDLE ro 0 N File handle of DBF file
DBFDBO_DBTHANDLE ro 0 N File handle of DBT file
DBFDBO_ANSI ro .F. L If .T., DBF/DBT charset is ansi
  1. ro=READONLY , a=ASSIGNABLE

The function DbInfo() returns the settings for the instance of the DatabaseEngine DBFDBE active in a work area. Instead of DBFDBE_, the #define constants for DbInfo() begin with DBFDBO_. In the case of a database object, these settings can only be read and not be redefined (READONLY instead of ASSIGNABLE).

Feedback

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.