you are not logged in

Navigation

User login

DBase format information

Introduction

This document describes the features of the DBF format and how they should be translated into SDFP by MIXED plugins.

It is not an exhaustive specification of the format. However, it aims to be a complete report of all non-trivial choices that must be made when translating the DBF data and data model into SDFP.

Status: incomplete.

Numeric Types

dBase has two numeric types, number and float. They both are characterised by two numbers, length, and decimal count.

The length denotes the maximum number of digits in a value, the decimal count denotes the maximum number of digits after the decimal point.

The maximum value of the length is 19, the decimal count should be less than or equal to the length.

The range of floats is limited to a subset of the values that can be expressed with 19 digits and one decimal point, there is no support for higher exponents.

Translation to SDFP

Types

The types will be translated into two elements, <type> and <typeOriginal>. The first one will be the closest match from the SQL 1999 types, the second one will a representation that reveals the type in the dBase context.

<typeOriginal>

The number type will be translated into number(length,decimal count).

The float type will be translated into float(length,decimal count).

<type>

The number and float types will be translated identically to numeric(length,decimal count).

Values

All numeric values are compatible with ISO 6093, and hence will be translated literally.

Translation back from SDFP

Numeric types will be translated as in the table below. Problems arise when there is no explicit precision and scale information on the source type, as DBF requires that information.

If numeric values in the source cannot be translated into the numeric types of DBF, overflow errors will be re

integer number(19,0)  
smallint number(5,0)  
numeric(p,s) number(p,s) provided p,s <= 19
decimal(p,s) number(p,s) provided p,s <= 19
real number(x,y) x,y determined from column content
double precision number(x,y) x, y determined from column content
float(p) number(x,y) x, y determined from column content

For the numeric and decimal source types, a problem occurs when p > 19. The translated type will then have a p-value of 19. Values might not fit, and such values must be signalled as overflow errors by the convertor.

For the real, double precision and float types the situation is even more complicated. The convertor must do the best job it can in determining suitable values for precision and scale.

An algorithm could be: examine all values in the corresponding column. Determine the largest and smallest precision and scale that occur, and use that for the x and y values in the new type. There will be a significant chance that these values will not meet the constraint of DBF. In that case: take the maximum allowed value, and report overflows when converting the values.

Other references

SQL datatypes

Here is a short overview from http://www.techonthenet.com/sql/datatypes.php:

 

The following is a list of general SQL datatypes that may not be supported by all relational databases.
Data Type Syntax Explanation (if applicable)
integer integer  
smallint smallint  
numeric numeric(p,s) Where p is a precision value; s is a scale value. For example, numeric(6,2) is a number that has 4 digits before the decimal and 2 digits after the decimal.
decimal decimal(p,s) Where p is a precision value; s is a scale value.
real real Single-precision floating point number
double precision double precision Double-precision floating point number
float float(p) Where p is a precision value.
character char(x) Where x is the number of characters to store. This data type is space padded to fill the number of characters specified.
character varying varchar2(x) Where x is the number of characters to store. This data type does NOT space pad.
bit bit(x) Where x is the number of bits to store.
bit varying bit varying(x) Where x is the number of bits to store. The length can vary up to x.
date date Stores year, month, and day values.
time time Stores the hour, minute, and second values.
timestamp timestamp Stores year, month, day, hour, minute, and second values.
time with time zone time with time zone Exactly the same as time, but also stores an offset from UTC of the time specified.
timestamp with time zone timestamp with time zone Exactly the same as timestamp, but also stores an offset from UTC of the time specified.
year-month interval   Contains a year value, a month value, or both.
day-time interval   Contains a day value, an hour value, a minute value, and/or a second value.

 

XBase format

Description and overview of the XBase file format which should be compatible with all DBase formats: XBase file format

The TI838D.txt attachment contains a txt-version of the DBase file format description (from Wotsit.org)

AttachmentSize
TI838D.txt18.4 KB