This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-14. For the latest, interactive version please visit https://mariadb.com/kb/.

CONNECT DBF Table Type

Overview

A table of type DBF is physically a dBASE III or IV formatted file (used by many products like dBASE, Xbase, FoxPro etc.). This format is similar to the FIX type format with in addition a prefix giving the characteristics of the file, describing in particular all the fields (columns) of the table.

Because DBF files have a header that contains Meta data about the file, in particular the column description, it is possible to create a table based on an existing DBF file without giving the column description, for instance:

create table cust engine=CONNECT table_type=DBF file_name='cust.dbf';

To see what CONNECT has done, you can use the DESCRIBE or SHOW CREATE TABLE commands, and eventually modify some options with the ALTER TABLE command.

The case of deleted lines is handled in a specific way for DBF tables. Deleted lines are not removed from the file but are "soft deleted" meaning they are marked as deleted. In particular, the number of lines contained in the file header does not take care of soft deleted lines. This is why if you execute these two commands applied to a DBF table named tabdbf:

select count(*) from tabdbf;
select count(*) from tabdbf where 1;

They can give a different result, the (fast) first one giving the number of physical lines in the file and the second one giving the number of line that are not (soft) deleted.

The commands UPDATE, INSERT, and DELETE can be used with DBF tables. The DELETE command marks the deleted lines as suppressed but keeps them in the file. The INSERT command, if it is used to populate a newly created table, constructs the file header before inserting new lines.

Note: For DBF tables, column name length is limited to 11 characters and field length to 256 bytes.

Conversion of dBASE Data Types

CONNECT handles only types that are stored as characters.

SymbolDBF TypeCONNECT TypeDescription
BBinary (string)TYPE_STRING10 digits representing a .DBT block number.
CCharacterTYPE_STRINGAll OEM code page characters - padded with blanks to the width of the field.
DDateTYPE_DATE8 bytes - date stored as a string in the format YYYYMMDD.
NNumericTYPE_INT TYPE_BIGINT TYPE_DOUBLENumber stored as a string, right justified, and padded with blanks to the width of the field.
LLogicalTYPE_STRING1 byte - initialized to 0x20 otherwise T or F.
MMemo (string)TYPE_STRING10 digits representing a .DBT block number.
@TimestampNot supported8 bytes - two longs, first for date, second for time. It is the number of days since 01/01/4713 BC.
ILongNot supported4 bytes. Leftmost bit used to indicate sign, 0 negative.
+AutoincrementNot supportedSame as a Long
FFloatTYPE_DOUBLENumber stored as a string, right justified, and padded with blanks to the width of the field.
ODoubleNot supported8 bytes - no conversions, stored as a double.
GOLETYPE_STRING10 digits representing a .DBT block number.

For the N numeric type, CONNECT converts it to TYPE_DOUBLE if the decimals value is not 0, to TYPE_BIGINT if the length value is greater than 10, else to TYPE_INT.

For M, B, and G types, CONNECT just returns the DBT number.

Reading soft deleted lines of a DBF table

It is possible to read these lines by changing the read mode of the table. This is specified by an option READMODE that can take the values:

0Standard mode. This is the default option.
1Read all lines including soft deleted ones.
2Read only the soft deleted lines.

For example, to read all lines of the tabdbf table, you can do:

alter table tabdbf option_list='Readmode=1';

To come back to normal mode, specify READMODE=0.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.