Promotic

Structure of the dBase III file.

The .dbf data file has a precisely defined structure. It consists of a header and a data block. The header itself consists of global data describing the dbf file and a list that includes descriptions of each item in the database file. After this list (after the end of the header) follows the data area containing the 1st record, 2nd record, ... to the last record in the database file. All records have the same length. The following table shows the structure:

Header
- Global data
- 1st column description
- 2nd column description
- ...
- Description of the last column
Data area
- Record 1 (1st column, 2nd column, ... final column)
- Record 2 (1st column, 2nd column, ... final column)
- ...
- Last record


Global data

This data area contains information about the file: existing Memo items, last update date, number of records, header size etc. This data is contained in the first 32 bytes of the database file. The following table contains the exact description:
 
Start The number of bytes Note
1 1 DBF file tag + existence of Memo items (#3 - no, #131 - yes)
2 1 Year of the last file update
3 1 Month of the last file update
4 1 Day of the last file update
5 4 The total number of records in the file
9 2 Database file header size
11 2 The size of a single record
13 20 Reserved for non-standard data of various applications


Column description

In this section you will find information related to one column of the record: for example the type of column item, the length of the column item, its position, etc. The description of each column item occupies exactly 32 bytes. The basic column types are:

- Char - string
- Date - date
- Logical - for writing yes/no
- Memo - note
- Numeric - number
Item description block:
 
Start The number of bytes Note
1 11 Column item name
12 1 Column item type - C (Char), D (Date), L (Logical), M (Memo), N (Numeric)
13 1 Column item position
17 1 Column item length
18 4 Number of decimal places (for type N)
19 2 Reserved


How to work with the table by means of the PmBuffer object.

First of all, it is necessary to read the header of the database file and process the individual data. It is probably easiest to load the header into an array of records. The record type should have the structure of the item description in the record. This gives us the necessary information about the items in the record. From the size of the header, we can easily deduce the location of the first record. Since the records have the same size, any record in the file can then be found by using a simple "address": Header_size + N x Record_size.

It is important that all item types are written in a string form.
Char - is written as an ASCII sequence, with a maximum length of 256 characters
Date - is written in the YYYYMMDD format, the whole year is used
Logical - written as "T" true, "F" false, the length of this field is always 1
Numeric - written as real, the length of the number and the number of decimal places can be found in the description of the item in the file header
Memo - omitted


If you want to mark a record as cancelled, you do this by overwriting the first byte of the record with #42. This record can be restored by overwriting the first byte with #32. New records are written to the end of the file, while making sure to write the end-of-file tag after the last record. When you finish working with a database file, it is advisable to update the file header (last modification, number of records, etc.).
 
An example that uses the PmBuffer object to create, edit and save into a file a table in the dbf format.:
This part of the script contains the main and auxiliary functions for working with the table.
 
The following examples show how to use these functions on a test table.
JavaScriptSelect and copy to clipboard

var oBuf, aColsInfo, nHeadSize, nCountRec, nRowSize, aValues, iRow, iCol;

function DbfCreateHeadInfo()
{
oBuf = Pm.CreatePmBuffer();
var d = Pm.CreatePmDateObject();
oBuf.SetUint8(-2, 3);   // Memo (#3)
oBuf.SetInt8(-2, d.GetYear());   // Year last updated
oBuf.SetInt8(-2, d.GetMonth());   // Month last updated
oBuf.SetInt8(-2, d.GetDay());   // Day last updated
oBuf.SetInt32(-2, 0);   // Total number of records
oBuf.SetInt16(-2, 0);   // Header size (nCol * 32 + 32 + 1)
oBuf.SetInt16(-2, 0);   // Size of one record (sum of "Item length" of each column + 1)
oBuf.SetStringFix(-2, null, 20, 0, 0);   // reserve
}

function DbfAddColumn(sTitle, sType, nLong, nDec)
{
nHeadSize = oBuf.GetInt16(8);
nRowSize = oBuf.GetInt16(10);
var nCol = (nHeadSize == 0) ? 1 : (nHeadSize - 1) / 32;
if (nCol > 1)
{
var nSize = oBuf.GetSize();
oBuf.SetSize(nSize -2, 1);
}
oBuf.SetStringFix(-2, sTitle, 11, 0, 0);   // Item name
oBuf.SetStringFix(-2, sType, 1, 0, 0);   // Type - C (Char), D (Date), L (Logical), M (Memo), N (Numeric)
oBuf.SetInt32(-2, 0);   // Position of the item
oBuf.SetInt8(-2, nLong);   // Item length
oBuf.SetInt8(-2, nDec);   // Number of decimal places (for type N)
oBuf.SetStringFix(-2, null, 14, 0, 0);   // reserve
oBuf.SetStringFix(-2, null, 2, 0, 0);
oBuf.SetInt16(8, nCol * 32 + 33);
oBuf.SetInt16(10, (nRowSize == 0) ? nLong + 1 : nRowSize + nLong);
}

function DbfGetColumnInfo()
{
oBuf.AutoOffset = 0;
nCountRec = oBuf.GetInt32(4);
nHeadSize = oBuf.GetInt16(8);
oBuf.AutoOffset = 32;
var nCols = (nHeadSize - 1 - 32) / 32;
aColsInfo = Pm.CreatePmArray().Create(nCols, 5);
for (iCol = 0; iCol < nCols; iCol++)
{
aColsInfo.SetItem(oBuf.GetStringFix(-4, 11, 0), iCol, 0);   // Column - item name
aColsInfo.SetItem(oBuf.GetStringFix(-4, 1, 0), iCol, 1);   // Column - Type - C (Char), D (Date), L (Logical), M (Memo), N (Numeric)
aColsInfo.SetItem(oBuf.GetInt32(-4), iCol, 2);   // Column - position of the item
aColsInfo.SetItem(oBuf.GetInt8(-4), iCol, 3);   // Column - item length
aColsInfo.SetItem(oBuf.GetInt8(-4), iCol, 4);   // Column - number of decimal places (for type N)
oBuf.GetStringFix(-4, 14, 0);   // Reserve
}
oBuf.GetStringFix(-4, 2, 0);
}

function DbfAddRow(aValues)
{
DbfGetColumnInfo();
var nCols = aValues.GetSize(1);
for (iCol = 0; iCol < nCols; iCol++)
{
var nS = Pm.ToNumber(aColsInfo.GetItem(iCol, 3));
switch (aColsInfo.GetItem(iCol, 1))
{
case "D":   // D (Date)
var oFmt = Pm.CreatePmFormat("Len:8;");
var Val = oFmt.Format(aValues.GetItem(iCol));
oBuf.SetStringFix(-2, Val, nS, 0, 0);
break;
case "L":   // L (Logical)
var Val = aValues.GetItem(iCol);
if (Val == "T" || Val == "F")
{
oBuf.SetStringFix(-2, Val, nS, 0, 0);
}
else if (Val == "true" || Val == "false")
{
oBuf.SetStringFix(-2, Val == "true" ? "T" : "F", nS, 0, 0);
}
else
{
oBuf.SetStringFix(-2, Pm.ToNumber(Val) ? "T" : "F", nS, 0, 0);
}
break;
case "N":   // N (Numeric)
var nDec = aColsInfo.GetItem(iCol, 4);
var oFmt = Pm.CreatePmFormat("Type:Float;Len:" + nS + ";DLen:" + nDec + ";");
var Val = oFmt.Format(aValues.GetItem(iCol));
oBuf.SetStringFix(-2, Val, nS, 0, 0);
break;
default:
var oFmt = Pm.CreatePmFormat("Type:String;Len:" + nS + ";IfShort:2;IfLong:2;");
var Val = oFmt.Format(aValues.GetItem(iCol));
oBuf.SetStringFix(-2, Val, nS, 0, 0);
break;
}
}

oBuf.SetStringFix(-2, null, 1, 0, 0);
nCountRec = nCountRec + 1;
DbfSetHeadInfo();
}

function DbfSetHeadInfo()
{
var oDate = Pm.CreatePmDateObject();
oBuf.AutoOffset = 0;
oBuf.GetInt8(4);
oBuf.SetInt8(4, oDate.GetYear() -2000);   // Year 22, 23, ...
oBuf.SetInt8(4, oDate.GetMonth());   // Month
oBuf.SetInt8(4, oDate.GetDay());   // Day
oBuf.SetInt32(4, nCountRec);
}

function DbfGetData(bTitle)
{
DbfGetColumnInfo();
var nRows = bTitle ? nCountRec + 1 : nCountRec;
var nCols = aColsInfo.GetSize(1);
var aData = Pm.CreatePmArray().Create(nRows, nCols);
var nS, Val;
var nTitle = 0;
if (bTitle)
{
nTitle = 1;
for (iCol = 0; iCol < nCols; iCol++)
{
aData.SetItem(aColsInfo.GetItem(iCol, 0), 0, iCol);
}
}
for (iRow = 0; iRow < nRows; iRow++)
{
for (iCol = 0; iCol < nCols; iCol++)
{
nS = Pm.ToNumber(aColsInfo.GetItem(iCol, 3));
Val = oBuf.GetStringFix(-4, nS, 0);
switch (aColsInfo.GetItem(iCol, 1))
{
case "N":
Val = Pm.ToNumber(Val);
break;
case "L":
Val = (Val == "F") ? false : true;
break;
default:
break;
}
aData.SetItem(Val, iRow + nTitle, iCol);
}
oBuf.GetStringFix(-4, 1, 0);
}
return aData;
}

function DbfFindValue(sColName, FindVal)
{
DbfGetColumnInfo();
var nOffset = oBuf.AutoOffset;
nRowSize = oBuf.GetInt16(10);
var nCols = (nHeadSize -1 -32) / 32;
var nColSearch;
var nPozVal = 0;
var nRec = 1;

for (iCol = 0; iCol < nCols; iCol++)
{
if (sColName == aColsInfo.GetItem(iCol, 0))
{
nColSearch = iCol;
break;
}
nPozVal = nPozVal + Pm.ToNumber(aColsInfo.GetItem(iCol, 3));
}

var nS = Pm.ToNumber(aColsInfo.GetItem(nColSearch, 3));
for (iCol = 0; iCol < nCountRec; iCol++)
{
var Val = oBuf.GetStringFix(nOffset + nPozVal, nS, 0);
if (Val == FindVal)
{
break;
}
nPozVal = nPozVal + nRowSize;
}
return iCol;
}

function DbfEditRow(nRowEdit, aValues)
{
DbfGetColumnInfo();
var nOffset = oBuf.AutoOffset;
nRowSize = oBuf.GetInt16(10);
var nPoz = nOffset + (nRowSize * nRowEdit);
var nCols = aValues.GetSize(1);

for (iCol = 0; iCol < nCols; iCol++)
{
var nS = Pm.ToNumber(aColsInfo.GetItem(iCol, 3));
switch (aColsInfo.GetItem(iCol, 1))
{
case "D":   // D (Date)
var oFmt = Pm.CreatePmFormat("Len:8;");
var Val = oFmt.Format(aValues.GetItem(iCol));
oBuf.SetStringFix(nPoz, Val, nS, 0, 0);
break;
case "L":   // L (Logical)
var Val = aValues.GetItem(iCol);
if (Val == "T" || Val == "F")
{
oBuf.SetStringFix(nPoz, Val, nS, 0, 0);
}
else if (Val == "true" || Val == "false")
{
oBuf.SetStringFix(nPoz, Val == "true" ? "T" : "F", nS, 0, 0);
}
else
{
oBuf.SetStringFix(nPoz, Pm.ToNumber(Val) ? "T" : "F", nS, 0, 0);
}
break;
case "N":   // N (Numeric)
var nDec = aColsInfo.GetItem(iCol, 4);
var oFmt = Pm.CreatePmFormat("Type:Float;Len:" + nS + ";DLen:" + nDec + ";");
var Val = oFmt.Format(aValues.GetItem(iCol));
oBuf.SetStringFix(nPoz, Val, nS, 0, 0);
break;
default:
var oFmt = Pm.CreatePmFormat("Type:String;Len:" + nS + ";IfShort:2;IfLong:2;");
var Val = oFmt.Format(aValues.GetItem(iCol));
oBuf.SetStringFix(nPoz, Val, nS, 0, 0);
break;
}
nPoz = nPoz + nS;
}
}

function DbfSaveToFile(sFile)
{
oBuf.SaveToFile(0, "#data:" + sFile, 0, -1);   // save to dbf file
}

function DbfLoadFromFile(sFile)
{
oBuf = Pm.CreatePmBuffer();
oBuf.LoadFromFile(-2, "#data:" + sFile, 0, -1);
oBuf.AutoOffset = 0;
}
Example2:
An example of creating a new table with five columns and the first three records, which is saved into the file named "Test.dbf" into the "Data" folder in the application
JavaScriptSelect and copy to clipboard

// -------------------- Create dBase table --------------------------
var aValues;
DbfCreateHeadInfo();
// AddColumn(sTitle, sType, nLong, nDec)
DbfAddColumn("Test", "C", 9, 0);   // 1 column
DbfAddColumn("State", "L", 1, 0);   // 2 column
DbfAddColumn("ValD", "N", 12, 2);   // 3 column
DbfAddColumn("ValN", "N", 10, 0);   // 4 column
DbfAddColumn("Note", "C", 40, 0);   // 5 column

aValues = Pm.Array1("Test1", true, 45786.21, 786, "Note1");
DbfAddRow(aValues);

aValues = Pm.Array1("Test2", false, 3333.33, 4568, "Note2");
DbfAddRow(aValues);

aValues = Pm.Array1("Test3", true, 4567.45, 72, "Note3");
DbfAddRow(aValues);

DbfSaveToFile("Test.dbf");
Example3:
An example for obtaining a 2-dimensional array of all records in the table. The parameter of the "DbfGetData" method determines whether the column names are also part of the data array.
JavaScriptSelect and copy to clipboard

// ----------------- Get all records -------------------------
DbfLoadFromFile("Test.dbf");
var aData = DbfGetData(true);   // (true/false) yes/no column names
Pm.Debug(aData);
Example4:
An example of adding additional records to the end of an existing table.
JavaScriptSelect and copy to clipboard

// ----------------- Add Record -------------------------
DbfLoadFromFile("Test.dbf");

aValues = Pm.Array1("Test4", false, 17.33, 111, "Test");
DbfAddRow(aValues);

aValues = Pm.Array1("Test5", true, 0.29, 10, "Note5");
DbfAddRow(aValues);

aValues = Pm.Array1("Test6", true, 75.5, 21, "Note6");
DbfAddRow(aValues);

aValues = Pm.Array1("Test7", true, 487.53, 20, "Note7");
DbfAddRow(aValues);

DbfSaveToFile("Test.dbf");
Example5:
This example searches the "Test" column for the value "Test4" and returns the row number of this record. The "DbfEditRow" method then edits the record values on this found row.
JavaScriptSelect and copy to clipboard

// ----------------- Search record and change values -------------------------
DbfLoadFromFile("Test.dbf");

var nRow = DbfFindValue("Test", "Test4");
// var nRow = DbfFindValue("ValD", 17.33);
DbfEditRow(nRow, Pm.Array1("Test4", true, 17.33, 111, "Note4"));

DbfSaveToFile("Test.dbf");
See also:
- Data types in dBase
- PmBuffer
PROMOTIC 9.0.27 SCADA system documentation MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r.o.