### 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:

Data Type | Syntax | Explanation (if applicable) |
---|---|---|

integer | integer | |

smallint | smallint | |

numeric | numeric(p,s) | Where is a precision value; p 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.s |

decimal | decimal(p,s) | Where is a precision value; p is a scale value.s |

real | real | Single-precision floating point number |

double precision | double precision | Double-precision floating point number |

float | float(p) | Where is a precision value.p |

character | char(x) | Where is the number of characters to store. This data type is space padded to fill the number of characters specified.x |

character varying | varchar2(x) | Where is the number of characters to store. This data type does NOT space pad.x |

bit | bit(x) | Where is the number of bits to store.x |

bit varying | bit varying(x) | Where is the number of bits to store. The length can vary up to x.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)

Attachment | Size |
---|---|

TI838D.txt | 18.4 KB |

Submitted by rutger on Tue, 2007-03-06 09:33.