DELDBE (DATA Component) Foundation

The DatabaseEngine DELDBE manages ASCII files in delimited format. The default file extension is TXT. The delimited format is unique in that it allows records and the fields within records to have variable length. Fields are separated from each other by delimiters and the default field separator is the comma. Files in delimited format do not use a structure definition and there is no explicit typing or standardizing of fields.

An implicit typing is done when the fields are read based on the format in which various data is stored. Data of the character data type is enclosed in delimiting characters (double quotation marks by default). Numeric values are not enclosed in delimiting characters, and are recognized as numeric because they start with the digits 0 to 9, or a plus/minus prefix. Numerics can be interpreted as Date values. Logical values are single alphabetical characters which are not enclosed in delimiting characters. The default values are T and F. The data type Memo is not supported by the DELDBE.

The DELDBE manages ASCII files whose rows or records have variable lengths. This DBE is used with the commands COPY TO...DELIMITED and APPEND FROM...DELIMITED. It also supports basic database operations, like DbCreate(), DbUseArea(), and DbSkip(). Filters and relations can also be set with the DELDBE. Creating an index file is not supported, even if the DELDBE is coupled with the NTXDBE. The command DELETE and the function DbDelete() delete records but have a slightly different meaning. The deletion does not occur via a deletion flag (as with the DBFDBE), but results in an immediate physical deletion. After calling DbDelete(), a record cannot be recalled when the file is managed by the DELDBE. The following table gives an overview of the database operations that are not supported by the DELDBE:

An ASCII file in the delimited format contains no structure data. Therefore, field names and file structure (more precisely, the DbStruct() array) also behave in a special way under the DELDBE. The DbStruct() array only determines the number of fields and field types. Since the length of fields is variable in the delimited format, the structure array cannot set field lengths or the number of decimal places for numeric fields. Fields of a delimited file can be accessed using an alias name. The field names begin with "FIELD" and are enumerated from 1 to FCount(). The following program code illustrates this:

USE Customer ALIAS Cust VIA DBFNTX            // open DBF file 
COPY TO Address.txt DELIMITED                 // create TXT file 

USE Address ALIAS Addr VIA DELDBE 

Addr->( DbAppend() )                          // append record 

REPLACE Addr->FIELD1 WITH "Miller"   , ;      // store data 
        Addr->FIELD2 WITH "John"     , ;      // to TXT file 
        Addr->FIELD3 WITH "0800-12345" 

In this example a TXT file in delimited format is created using the COPY TO command. The structure description is taken from a DBF file.

The DEL DatabaseEngine can be configured in specific ways using the function DbeInfo(). Specifically, the DEL file format and the operating mode of the DELDBE can be configured using DbeInfo(). The following table gives an overview of the special #define constants that can be passed to the function DbeInfo() for the DELDBE:

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

DELDBE_MODE

This constant is used to set the operating mode of the DELDBE. There are three operating modes: auto-field, multi-field and single field. The default operating mode is auto-field. In the following line the auto field operating mode is changed to single field:

DbeInfo( COMPONENT_DATA, DELDBE_MODE, DELDBE_SINGLEFIELD ) 

The three possible operating modes are described in the following sections.

Mode: DELDBE_AUTOFIELD

The DELDBE_AUTOFIELD mode is the default operating mode. The DELDBE is in this mode after it is loaded using DbeLoad(). If a TXT file is created using DbCreate() for a file in delimited format, it can be filled with data using the APPEND BLANK and REPLACE commands.

#include "DelDbe.ch" 

aStruct := { {"CHAR1","C",10,0} , ;   // create structure array 
             {"CHAR2","C",10,0} , ;   // for DbCreate() 
             {"NUM"  ,"N", 6,2} , ; 
             {"LOGIC","L", 1,0} } 

DbCreate( "Auto", aStruct, "DELDBE" ) // create TXT file 

DbeSetDefault("DELDBE")               // DbeInfo uses default DBE 
                                      // configure data types 
DbeInfo( COMPONENT_DATA, DELDBE_FIELD_TYPES, "CCNL" ) 
USE Auto VIA DELDBE 
FOR i:=1 TO 3                         // append three records 
   APPEND BLANK 
   REPLACE FIELD->FIELD1 WITH Replicate(Chr(64+i),i), ; 
           FIELD->FIELD2 WITH Replicate(Chr(96+i),i), ; 
           FIELD->FIELD3 WITH 10^i, ; 
           FIELD->FIELD4 WITH (i%2 == 1) 
NEXT 

In this code, a TXT file with four fields is created in delimited format. The fields do not correspond to the names from the structure array, but are numbered and are prefixed with "FIELD". Also, the data types for the fields are configured. After the above code runs, the file "Auto.txt" contains:

"A","a",10.00,T 
"BB","bb",100.00,F 
"CCC","ccc",1000.00,T 

These lines demonstrate the data in delimited format: character values are enclosed in quotation marks, numeric values consist only of digits, and logical values consist of one alphabetical character.

Mode: DELDBE_MULTIFIELD

The DELDBE_MULTIFIELD mode is an operating mode that was created especially for control files for form letters. Control files are used by text processing programs and contain the variable data for form letters. Generally the first line of a control file contains the field names, or variable names defined in the form letter that are replaced by values from the control file. In multi-field operating mode, the first line of the TXT file contains the field names. The following example creates a typical control file where the delimiting characters for character values and the separators for fields are changed:

#include "DelDbe.ch" 

? DbeSetDefault( "DELDBE" ) 

aStruct := { {"CHAR1","C",10,0} , ; 
             {"CHAR2","C",10,0} , ; 
             {"NUM"  ,"N", 6,2} , ; 
             {"LOGIC","L", 1,0} } 
                                      // switch to multi-field 
DbeInfo( COMPONENT_DATA, DELDBE_MODE, DELDBE_MULTIFIELD ) 
                                      // semicolon instead of 
                                      // comma 
DbeInfo( COMPONENT_DATA, DELDBE_FIELD_TOKEN, ";" ) 
                                      // no separator for 
                                      // character values 
DbeInfo( COMPONENT_DATA, DELDBE_DELIMITER_TOKEN, Chr(0) ) 
DbCreate( "Multi", aStruct, "DELDBE" ) 

USE Multi VIA DELDBE 

FOR i:=1 TO 3                         // fields have field names 
   DbAppend() 
   REPLACE FIELD->CHAR1 WITH Replicate(Chr(64+i),i), ; 
           FIELD->CHAR2 WITH Replicate(Chr(96+i),i), ; 
           FIELD->NUM   WITH 10^i, ; 
           FIELD->LOGIC WITH (i%2 == 1) 
NEXT 

In this code, a TXT file in delimited format is created containing four fields. The field names correspond to the names from the structure array and are stored in the first line of the TXT file. A semicolon is used as the field separator and character values are not enclosed in delimiting characters. After this example runs, the file "Multi.txt" contains:

CHAR1;CHAR2;NUM;LOGIC 
A;a;10.00;T 
BB;bb;100.00;F 
CCC;ccc;1000.00;T 

The first line contains the field names which correspond to the variable names of the control file. Subsequent lines contain the data for the form letter. The default action of delimiting character values is turned off. This is done by specifying the character Chr(0) as the delimiting character. The semicolon is defined as the field separator.

In "multi-field" operating mode, fields of a TXT file in delimited format can be specified by their field names, since the field names are defined in the TXT file. This is a special case and is generally used only to create control files for text processing from DBF files.

Mode: DELDBE_SINGLEFIELD

The mode DELDBE_SINGLEFIELD is an operating mode that allows any ASCII file to be opened and managed by the DELDBE. In this mode, the DELDBE treats an ASCII file as if the file contained only a single field. The field is treated as containing no delimiters, but characters indicating the end of a record are required. For an ASCII file, this generally corresponds to end of line characters (carriage return+line feed, Chr(13)+Chr(10)). In "single field" mode, a field is identical to a record and the DELDBE reads an ASCII file line by line. This allows database operations like DbSkip(), DbGoTop() or DbGoBottom() to be executed on an ASCII file. In this operating mode the DELDBE can manage many different ASCII files. Example:

#include "DelDbe.ch" 

? DbeSetDefault( "DELDBE" ) 

aStruct := { {"CHAR1","C",10,0} , ; 
             {"CHAR2","C",10,0} , ; 
             {"NUM"  ,"N", 6,2} , ; 
             {"LOGIC","L", 1,0} } 
                                      // switch to single field 
DbeInfo( COMPONENT_DATA, DELDBE_MODE, DELDBE_SINGLEFIELD ) 

DbCreate( "Single", aStruct, "DELDBE" ) 
USE Single VIA DELDBE 

FOR i:=1 TO 3 
   DbAppend() 
   REPLACE FIELD->FIELD WITH Replicate(Chr(64+i),i) 
NEXT 
DbCloseArea() 

Regardless of the structure array provided to DbCreate(), a TXT file in the single field mode is created as an empty file containing no field information. The DELDBE treats the TXT file as if it is a file with a single field. The field name is FIELD. With exception of characters that represent the end of a record, the DELDBE does not recognize any other delimiting characters in this operating mode. The contents of the file "Single.txt" created in the above example would be as follows:

A 
BB 
CCC 

Each line of the file contains only the data written into the file using REPLACE after DbAppend() added a new record.

The "single field" operating mode is suitable for viewing any ASCII file with rows that can be identified by distinct end of line character(s).

DELDBE_RECORD_TOKEN

The end of a record in a TXT file is generally identified by the two characters carriage return+line feed (Chr(13)+Chr(10). The delimiter for a record can be changed using the function DbeInfo(). The delimiter must contain at least one character and can have a maximum of two characters.

DELDBE_FIELD_TOKEN

In the delimited format (auto-field and multi-field mode), fields are set off from each other by a separator. The comma is the default separator. The following program line specifies the semicolon as the separator:

DbeInfo( COMPONENT_DATA, DELDBE_FIELD_TOKEN, ";" ) 

DELDBE_FIELD_TYPES

In auto-field mode, data types of fields are recognized according to the format data is stored in a TXT file. The default data type recognition can be overridden by specifying the data type for each field:

cSave := DbeInfo( COMPONENT_DATA, DELDBE_FIELD_TYPES, "DCNLC" ) 
USE Mydata.txt VIA DELDBE 
DbeInfo( COMPONENT_DATA, DELDBE_FIELD_TYPES, cSave ) 

In this example, five fields are available in the delimited file and their values are retrieved having the data types Date, Character, Numeric, Logical and Character. Note that values of type Date can only be retrieved when data consists of 8 digits in the format YYYYMMDD.

DELDBE_DELIMITER_TOKEN

Values of the "character" type are bracketed by certain characters in the delimited format. The default is the double quotation mark. The following call specifies the single quotation mark as the delimiting character for character values:

DbeInfo( COMPONENT_DATA, DELDBE_DELIMITER_TOKEN, "'" ) 

DELDBE_DECIMAL_TOKEN

The character used to mark decimal places in numeric values can also be specified. The default is the period. The following program line specifies the comma as the separator for decimal places in numeric values:

DbeInfo( COMPONENT_DATA, DELDBE_DECIMAL_TOKEN, "," ) 

When the character for decimal places is changed, it must not be the same as the field separator. If it is the same, places after the comma are looked at as a separate field containing another numeric value.

DELDBE_LOGICAL_TOKEN

For logical values, the two characters representing true and false can be specified. The default is "TF". If this setting is changed, a character string consisting of two characters must be specified. The first character represents true and the second character represents false. The following call specifies the character "Y" for the logical value true and the character "N" for the logical value false:

DbeInfo( COMPONENT_DATA, DELDBE_LOGICAL_TOKEN, "YN" ) 

Alphabetical characters must be indicated for logical values used with the DELDBE. The characters "1" and "0" for "true" and "false" are not permitted, since they are interpreted as numeric values.

DELDBE_MAX_BUFFERSIZE

The delimited format allows fields and records to become larger or longer. A character field containing 10 characters can be assigned a character string containing 20K characters. The memory space occupied by a field in a record must be dynamically enlarged (and also reduced) after an assignment. This automatically occurs with the DELDBE. The dynamic allocation of memory space in a file (not in memory) implies a considerable limitation of speed in database operations with the DELDBE. To optimize the speed of the DELDBE, the read buffer for a record loaded using the DELDBE is limited to 1 KB (1024 bytes). As soon as a record longer than 1024 byte is read, the read buffer is doubled. This behavior requires that an upper limit be defined, or the maximum length of a record which no record can exceed. The upper limit for the length of a record is set at 64KB by default. When any line of an ASCII file managed by the DELDBE is anticipated to be longer than 64KB characters, the maximum size of the read buffer must be increased. The following program line increases the maximum size of the read buffer of the DELDBE to 256 kilobytes:

DbeInfo( COMPONENT_DATA, DELDBE_MAX_BUFFERSIZE, 256 )