DBase format information
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.
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
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.
The number type will be translated into number(length,decimal count).
The float type will be translated into float(length,decimal count).
The number and float types will be translated identically to numeric(length,decimal count).
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
|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.
Here is a short overview from http://www.techonthenet.com/sql/datatypes.php:
|Data Type||Syntax||Explanation (if applicable)|
|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.|
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)
Submitted by rutger on Tue, 2007-03-06 09:33.