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:

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.

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.

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 

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.

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:

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:

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.

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 ) 

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.

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:

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).

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.

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 

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