Database Engines:dbespec

FOXDBE (DATA component) Foundation

The DatabaseEngine FOXDBE manages DBF/FPT table files compatible with Visual FoxPro, FoxPro 2.x, Comix and SIX Clipper RDDs. Their structure complies with the DBF format but they may contain additional field types other than Character, Date, Logical Numeric or Memo. FoxPro specific field types are automatically converted to Xbase++ compatible data types when an Xbase++ application accesses tables created by FoxPro.

By default the FOX DatabaseEngine is configured to automatically recognize the correct mode of operation when opening an existing DBF/FPT file. However the FOX DatabaseEngine creates by default Visual-FoxPro compatible files, and guarantees concurrent file usage between Visual FoxPro and Xbase++.

One of the differences between the DBFDBE is the file format for memo files which are managed more efficiently. Memo files of the FOXDBE have FPT as their default file extension, not DBT. With the FPT file format, it is possible to store any binary data in memo fields, while only text can be stored in memo fields using the DBT file format. In addition, the block size which represents the minimum space used for data stored in a memo field can be defined. With the DBT format the block size is 512 bytes while the FOXDBE supports block sizes in the 33 bytes to 64 kb range.

The FOX DatabaseEngine accounts for FoxPro specific features of the DBF format plus other factors associated with the concurrent operation with FoxPro applications. The following table gives an overview:

Specifications for FoxPro compatible DBF files
Element Specification
Table file size Limited to 2^31 bytes (2 gigabytes)
Max. number of fields 2038 *)
Max. number of records (2^31 - Header() - 1) / RecSize()
Data types for FoxPro fields B, C, D, F, G, I, L, N, M, T, Y, Q, V
Data types for Xbase++ fields F, C, D, N, O, I, L, N, M, T, Y, V, X
Memo file size Theoretical limit is 16 Terabyte or 2^31 blocks. Default block size of 64 bytes leads to a maximum memo file size of 128GB
Memo block size 64 Bytes (adjustable between 33 bytes and 64 kb)
SET CHARSET The character set selected with SET CHARSET upon file creation defines how text data is stored
  1. Xbase++ versions ealier than 1.90 support only 255 fields per table

Visual FoxPro field data types

The FOX DatabaseEngine supports various data types for fields when creating a table. A desccription and the FoxPro field type is shown in the table below. The Xbase++ DDL uses the same letters for some of the field types. However, in some cases they are different to FoxPro - the Xbase++ field type is shown in the forth column. Because there are more data types for fields in a table than for variables the corresponding Xbase++ ValType() is shown in the most right column.

Mapping of FoxPro field types in the Xbase++ DDL
Description Field type Length Field type Valtype()
FoxPro Xbase++
Double B 8 F N
Character (text) *) C 1-254 C C
Character (binary) C 1-254 X C
Date D 8 D D
Float F 1-20 N N
Generic G 4 O M
Long signed integer I 4 I N
Logical L 1 L L
Memo (text) *) M 4 or 10 M M
Memo (binary) M 4 or 10 V M
Numeric N 1-20 N N
Time stamp T 8 T C
Currency Y 8 Y N
VarChar *) V 1-254 R C
VarBinary Q 1-254 Z C
Integer (autoinc) I 4 S N
  1. Data is converted according to SET CHARSET

If a numeric field of the type Double is to be created in a new table file, whose field type is specified by the letter B in FoxPro, Xbase++ however uses the letter F for declaring a field as Double.

To create a table using the DbCreate() function a structure definition must be passed as an array. Each array element describes the name, type, length, decimals and an optional token as outlined below:

{ "FieldName" , "Xbase++ FieldType" , Length, Decimals, "optional token" } 

Nullable fields

Using the literal "nullable" as an optional token when specifying a field of a Visual FoxPro table activates NULL support for that field. Nullable fields have always an initial value of NIL , indicating no data. Null value support simplifies the task of representing unknown data and makes it easier to work with other DBMS sources that may contain null values.

Sequence field type

With the sequence datatype 'S' an auto incrementing integer field is added to the table. Sequenced fields contain values that increment automatically. A table can contain multiple sequence fields that contain automatically incrementing values.

For sequenced datatypes the decimal and length elements of the field structure array are used to determine the start and step value for the auto increment type.

{ "FieldName" , "S" , Next-Value , Step-Value } 

The field structure array below, defines a field "Cust_Id", which is of field type integer (Valtype() is 'N') with a next value of 1000 and a step value of 10.

{ "Cust_Id"   , "S"    , 1000       , 10         } 

A field that contains automatically incrementing values becomes read-only and cannot be changed with an REPLACE or field assignment operation. Attempting to update such a field raises a runtime error. See the FieldInfo() constant FOXFLD_READONLY for a more detailed discussion about the read-only attribute of sequenced fields.

When using a sequenced field type 'S' value for a field, the start and incremental values Next Value and Step are stored in the table header in a unused portion of the field subrecord for the specified field. Next Value is stored as a 4-byte integer. The Step value is stored as a 1-byte integer with a maximum value of 255.

Versions prior to Visual FoxPro 8.0 cannot recognize tables that use automatic incrementing field values.

Variable length data type

To include data in fields without automatic space padding or truncating trailing spaces, the VarChar or VarBinary field types can be used. The VarChar type provides a convenient method for mapping ODBC/SQL VarChar column types to Xbase++ field types.

The length value of the field structure array defines the maximum length allowed for the field value. The actual length of the data stored in the field can be between 0 and length value. The following code illustrates this:

// FirstName is a Character field with length 30 
// 
FIELD->FIRSTNAME := "Speedy" 
? Len(FIELD->FIRSTNAME)           // 30 

// LastName is a VarChar field with length 30 
// 
FIELD->LASTNAME  := "Gonzales" 
? Len(FIELD->LASTNAME)            //  8 

If VarChar or VarBinary field types in index expressions are used, we recommend the application of the PADR() function to ensure a fixed length of all index keys. Otherwise the index engine will raise a runtime error "Invalid key length".

The following code creates a table using extended Xbase++ field types as supported by the FOX DatabaseEngine and outlined in the table above:

// 
// creates a Visual FoxPro table 
// 

PROCEDURE Main() 
  LOCAL aStructure := {} 

  // VFP auto increment, start-value 1, increment by 1 
  // 
  AAdd( aStructure , { "CUSTID"  , "S" ,  1 , 1 } ) 

  // VFP VarChar, maximum size is 30 characters 
  // 
  AAdd( aStructure , { "COMPANY" , "V" , 30 , 0 } ) 

  // VFP Currency type, 5th element holds token "nullable" to 
  // mark table field as being able to store NULL/NIL value 
  // 
  AAdd( aStructure , { "REVENUE" , "Y" ,  8 , 0, "nullable" } ) 

  // VFP Memo type 
  // 
  AAdd( aStructure , { "NOTE"    , "M" ,  4 , 0 } ) 

  DbCreate("vfp_table" , aStructure ) 
RETURN 

FOXDBE and SET CHARSET

The character set used for storing text data depends on the SET CHARSET setting when a DBF file is created by the FOXDBE. This information is stored in the file header and cannot be changed after file creation. If a different character set is selected via SET CHARSET, character data will be subject to an automatic ANSI-OEM codepage conversion according to the changed setting. Note that the FOXDBE distinguishes text from binary data for character fields and memo fields. Only text is automatically converted, while binary data is read from or written to a database without conversion.

Configuration of the FOXDBE with DbeInfo()

The DatabaseEngine can be configured in specific ways using the function DbeInfo(). For example, the file extension for database files can be specified, or the block size for memo fields can be set. The following table gives an overview of the #define constants which can be passed to the function DbeInfo() for the FOXDBE:

Constants for DbeInfo() with the FOXDBE
Constant *) Value Data type Description
DBE_EXTENSION a DBF C Extension for DBF file
FOXDBE_MEMOFILE_EXT a FPT C Extension for memo file
FOXDBE_MEMOBLOCKSIZE a 64 N Block size for memo fields
FOXDBE_LOCKMODE a #define N Locking mode for concurrent file access
FOXDBE_CREATE_2X a .F. L Create FoxPro 2.x file
FOXDBE_LIFETIME a 50 N Lifetime of records in local cache
  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 FOXDBE can be changed.

DbeInfo( COMPONENT_DATA, DBE_EXTENSION, "FOX" ) 

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

FOXDBE_MEMOFILE_EXT

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

FOXDBE_MEMOBLOCKSIZE

This constant returns or changes the minimum block size used to store data in a memo file. The default block size for the FOXDBE is 64 bytes. Within a memo file, texts or character strings are stored in multiple blocks each of which is 64 bytes. When text which is only 100 bytes long is stored, it still occupies 128 bytes in the memo file.

FOXDBE_LOCKMODE

This setting allows for selecting a compatible locking scheme for concurrent file access when Xbase++ applications share databases with FoxPro or Clipper applications. Constants from the next table can be used to alter the default setting:

Constants for locking schemes
Constant Description
FOXDBE_LOCKMODE_AUTO *) Xbase++ detects the appropriate locking scheme
FOXDBE_LOCKMODE_2X Enforce locking scheme of FoxPro 2.x
FOXDBE_LOCKMODE_VISUAL Enforce locking scheme of Visual FoxPro
FOXDBE_LOCKMODE_CLIPPER Enforce locking scheme of Clipper Comix or Six
  1. default

By default the FOXDBE_LOCKMODE_AUTO is active, this allows Xbase++ to detect which locking scheme must be used according to the signature of the table header.

If the Comix RDD for Clipper is used the FOXDBE_LOCKMODE must be set according to the Comix setup within your DOS Clipper application. As a general rule, FOXDBE_LOCKMODE_CLIPPER must be set if Comix is used right out of the box, if Comix plus the cmxFox52.obj is used within your Clipper application then FOXDBE_LOCKMODE_2X has to be used.

FOXDBE_CREATE_2X

The FOXDBE can manage database files in FoxPro 2.x/Comix or Visual FoxPro format. The following lines show how to select the DBF format:

// The following two lines configure the FOXDBE to create 
// FoxPro 2.x compatible files and use a FoxPro 2.x compatible 
// locking scheme. 
// 
DbeInfo( COMPONENT_DATA, FOXDBE_CREATE_2X, .T. ) 
DbeInfo( COMPONENT_DATA, FOXDBE_LOCKMODE , FOXDBE_LOCKMODE_2X ) 


// The following two lines configure the FOXDBE to create 
// FoxPro 2.x compatible files and use a Comix/Clipper compatible 
// locking scheme. 
// 
DbeInfo( COMPONENT_DATA, FOXDBE_CREATE_2X, .T. ) 
DbeInfo( COMPONENT_DATA, FOXDBE_LOCKMODE , FOXDBE_LOCKMODE_CLIPPER ) 

// The following two lines configure the FOXDBE to create 
// Visual FoxPro compatible files and use a Visual FoxPro compatible 
// locking scheme. (default configuration) 
// 
DbeInfo( COMPONENT_DATA, FOXDBE_CREATE_2X, .F. ) 
DbeInfo( COMPONENT_DATA, FOXDBE_LOCKMODE , FOXDBE_LOCKMODE_VISUAL ) 

It is recommend to setup new tables using the Visual FoxPro compatible file format and locking scheme. This will ensure the best performance when working with DBF tables.

FOXDBE_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 FOXDBE 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 FOXDBE 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.

Retrieve information about the FOXDBE with DbInfo()

When a DBF file is opened, an instance of the FOXDBE is created. The instance is a database object (DBO) and includes all settings of the FOXDBE. 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 DBO (the work area). 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 FOXBE are listed in the following table:

Constants for DbInfo() with the FOXDBE
Constant *) Value Data type Description
DBO_FILENAME ro C Complete file name
FOXDBO_MEMOBLOCKSIZE ro 64 N Block size for memo fields
FOXDBO_CODEPAGE ro 850,1252,... N Returns the code page of the table
  1. ro=READONLY , a=ASSIGNABLE

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

Constants for the function FieldInfo()

Whenever a DBF file is opened with the FOXDBE, additional information for each field can be retrieved using the function FieldInfo(). A #define constant identifying what specific information is required, must be passed on to the function. The #define constants are defined in the dmlb.ch include file and begin with the prefix FLD_. In addition to these generic constants, which are valid for all Xbase++ DatabaseEngines, the following FOXDBE-specific constants can be passed to the function FieldInfo(). These FOXDBE-specific constants are defined in the #include file foxdbe.ch.

Constants for FieldInfo() with the FOXDBE
Constant *) Data type Description
FOXFLD_READONLY rw L Field read-only state
FOXFLD_SEQUENCE_NEXTVAL rw N Next value for sequenced field
  1. ro=READONLY , rw=READ/WRITEABLE

FOXFLD_READONLY

This constant returns or changes the read-only state of a field. By default all fields except sequenced/auto increment fields are read/writeable. Using this constant a writeable field can be made read-only as shown in the sample below.

#include "foxdbe.ch" 

// Load only storage DatabaseEngine FOXDBE 
// 
PROCEDURE DbeSys() 
DbeLoad("FOXDBE") 
DbeSetDefault("FOXDBE") 
RETURN 

PROCEDURE Main() 
LOCAL aStru := {} 

AAdd( aStru , { "NAME"    ,"C", 10 ,  0 } ) 
AAdd( aStru , { "CUST_ID" ,"N",  8 ,  0 } ) 

DbCreate("TEST",aStru,"FOXDBE") 
USE TEST EXCLUSIVE 

? FieldInfo( 1 , FOXFLD_READONLY ) 
DbAppend() 
FIELD->CUST_ID := 2006 
FIELD->NAME    := "CapeHorn" 
DbCommit() 

? FIELD->CUST_ID,FIELD->NAME 

// Set field CUST_ID to read-only to avoid future write access 
// 
FieldInfo( FieldPos("CUST_ID") , FOXFLD_READONLY , .T. ) 

// Any attempt to write to the field CUST_ID raises a runtime error 
// 
FIELD->CUST_ID := 2007 

USE 
RETURN 

Using this constant, read-only fields such as sequenced fields can be made writeable. If a sequenced field is made writeable the DatabaseEngine automatically maintains the related header information according to the rule outlined in the pseudo code below. It should be noted that assigning a value to a sequenced field is an resource intensive operation due to the necessary header update.

IF( field-value-assigned > header-next-value ) 
header-next-value := field-value-assigned + header-step-value 
update-header() 
ENDIF 

FOXFLD_SEQUENCE_NEXTVAL

Using this constant the default value of the next append operation for the specific sequenced field can be determined or configured. However great care must be taken to ensure integrity, the database engine is not able to verify whether the value specified as the next default value of the sequenced field has already been used. This has to be guaranteed by the developer. The sample below illustrates the usage and semantics of the FOXFLD_SEQUENCE_NEXTVAL constant.

#include "foxdbe.ch" 

PROCEDURE DbeSys() 
DbeLoad("FOXDBE") 
DbeSetDefault("FOXDBE") 
RETURN 

PROCEDURE Main() 
LOCAL aStru := {} 
LOCAL cbOld 
LOCAL oE 

// create table with sequenced field 
// 
AAdd( aStru , { "CUST_ID"  ,"S", 100 , 10 } ) 
AAdd( aStru , { "NAME" ,"C", 10 ,  0 } ) 
DbCreate("TEST",aStru,"FOXDBE") 
USE TEST EXCLUSIVE 


// 
// show initial next-value, append record and 
// display new next-value reserved for next append operation 
// 
? "CUST_ID next-value:", FieldInfo( 1 , FOXFLD_SEQUENCE_NEXTVAL ) 

DbAppend() 
FIELD->NAME := "CapeHorn" 
? "Record:",FIELD->CUST_ID,FIELD->NAME 
DbCommit() 

? "CUST_ID next-value:", FieldInfo( 1 , FOXFLD_SEQUENCE_NEXTVAL ) 
? ""  

// 
// explicit set new next-value for next append operation 
// and append new record 
// 
FieldInfo( 1 , FOXFLD_SEQUENCE_NEXTVAL , 2000 ) 
DbAppend() 
FIELD->NAME := "Cape Pillar" 
DbCommit() 
? "Record:",FIELD->CUST_ID,FIELD->NAME 

// 
// close table, re-open it, show next-value for next 
// append operation, perform append and show next-value 
// 
USE 
USE TEST EXCLUSIVE 

? "Next Value:", FieldInfo( 1 , FOXFLD_SEQUENCE_NEXTVAL ) 
DbAppend() 
FIELD->NAME := "North Cape" 
DbCommit() 
? "Record:",FIELD->CUST_ID,FIELD->NAME 
? "CUST_ID next-value:", FieldInfo( 1 , FOXFLD_SEQUENCE_NEXTVAL ) 

USE 
RETURN 

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.