The Borland's Database Engine
Buy DataBase books from Amazon
The BCB programmer usually makes use of visual components to manage his
database applications: drop a TDataSource on the form, and connect it to
a TTable, and to some data controls... Here you'll learn how to use BDE
without the visual approach.
Despite visual components are easy to use, an in-depth
knowledge of BDE is recommended for many good reasons. For example there is
no component to create, pack, index, reindex, etc. tables.
First let me give you some forewords. BDE provides the programmer with
the definition of a few structures and with a series of
functions to be called from within the application program. All these
functions make an API (Application Programming Interface).
The structures are used to define all of the objects related to database
management tasks: fields, tables, indexes, etc.. Functions are used to
manipulate these objects. Each function has a number of parameters: some of
them are input parameters, and the other are output
parameters. Input parameters must be provided by the programmer
for the function to accomplish its job. Output parameters are passed to
the function without any particular value: the function itself will
fill them in, and the programmer will later make use of their values for
the appropriate use.
Structures and functions are (poorly) documented in the printed book which
came with my C++ 4.5 & database tools. This book does not come togheter into
the BCB bundle, so the programmer must refer to BDE32.HLP (search it into
your computer resources, you will find it, otherwise download the latest
BDE from Borland's site).
A thing to remember when dealing with BDE's documentation is that a table
is often referred to as a cursor. I can't remember exactly what
does "cursor" mean. We can assume cursor as a synonim of table.
The following explanation is related to managing DBF tables and NDX indexes
by means of BDE APIs. I think that the example code provided here will be
useful, since I never found anything similar nor in Borland's documentation
neither on the web.
Topics covered in this document are:
Installing BDE is a recursive subject in the BDE newsgroup. I unsuccessfully
tryed a few times to manually copy a number of files I thaught were
necessary for this scope. After these experiments I know that BDE must
be installed directly from my original Borland C++ Builder 3 Pro CD-ROM.
It is the safe and easy way to do it.
IDAPI01.DLL is the core file to BDE. I am mentionning it here because it comes
in two "editions": the first is good for normal operations, and the second
must replace the first when tracing BDE operations. See
Starting the database engine for further details, and search for
DbiDebugLayerOptions in BDE.HLP to learn about renaming these
DLLs in different circumstances.
Before successfully compiling my first application that made use of BDE
I had to put a simple patch in the header of the unit that manages the
database tables. The patch is surely needed by BCB 3 Professional. I don't
know whether it is applicable to other versions of BCB. The patch is the
typedef CHAR far *pCHAR;
typedef BYTE far *pBYTE;
# define UINT16 unsigned short
# define UINT16 unsigned int
Error catching when using BDE functions is of vital importance. BDE documentation
describes a macro to call functions that provides an invaluable benefit: in
case of error a message is given containing all of the info needed to trace what
happened, and where.
The macro is named DBIEXEC. Instead of calling SomeFunction(parameters);
you should call DBIEXEC(SomeFunction(parameters));. In other words you
should enclose the function call into the round brackets of DBIEXEC. You
will soon understand why it is powerful.
The macro I am providing here
is a modified version of the BDE's original
one. Obviously the original macro is better. I don't know why I modified
it. Anycase my one is slightly more simple but equally effective.
For the macro to operate you should put the following into the header file:
DBIResult DBIExec ( pCHAR, UINT16, pCHAR, DBIResult );
#define DBIEXEC( parm ) DBIExec( __FILE__, __LINE__, #parm, parm );
Don't leave spaces in DBIEXEC( and DBIExec( in the #define statement.
An useful debugging tool is provided by DbiDebugLayerOptions
(see Starting the database engine).
When using BDE the programmer should perform some initialization before
start manipulating data and tables.
The example code opens a standard type database,
and contains the following function calls:
Note 1 - The 4th parameter sets the sharing mode of tables in the database.
If you choose dbiOPENEXCL there will not be later any need to lock
records when writing to the tables, but nobody else will ever access those
tables until DbiExit function is called. If you choose dbiOPENSHARED,
on the contrary, anybody can get access to the tables unless you explicity
lock them at writing time.
- SetHandleCount: tells the operating system to allow up to 68
file handles at the same time
- DbiInit: is a prerequisite for the next function calls
- DbiDebugLayerOptions: useful call to trace all BDE operations
into a file for debugging purposes; it can be left always in place; it
will only work when the appropriate edition of IDAPI01.DLL is installed
- DbiOpenDatabase: see the notes 1 and 2 below for the 4th and
the last parameters
- DbiSetDirectory: sets the current path for BDE to properly
locate tables and indexes, see note 3 below; you can switch between
different paths during the execution of the program if you need to,
without closing and reopening the database
Note 2 - The last parameter (named hDB, type: phDBIDb) is a handle to the
database. This handle will be used for some subsequent operation within the
database, such creating or opening tables, until DbiExit function is
Note 3 - Don't try to pass the path to a table (or index) togheter with
the table (or index) name as parameter to a BDE function call. Always
pass the table (or index) name alone, and specify the path by means of a
DbiSetDirectory function call.
The first exercise to demonstrate the capabilities of BDE is creating a
table. Please note this is something you can *not* do by means of any
The example table we are going to create by means of a
bunch of statements is named MYTABLE.DBF, it is made of three fields
(MYFIELD_1, MYFIELD_2 and MYFIELD_3), and it is indexed on the second of these
FLDDesc is the structure used to define a field. Since tables are
made of more than one field it is necessary to define an array of these
structures. The array is newed and its pointer (pFieldDescription) is the
pointer that will be passed into a member (pfldDesc) of the structure
that will be used to define the table.
Since the array is newed its initial content is pure garbage. So a first
good rule to remember is to fill in *all* the members of FLDDesc
structures. Don't forget any field. Unused fields must be assigned
a NULL, a zero, or anything suggested in BDE.HLP. Otherwise you will get a
GPF at least. Please refer to BDE.HLP to understand the meaning of each
structure member. When you don't know how to fill a particular member take
a look at the code provided here, it is taken from a working program.
Before creating the table we need to define its index too. IDXDesc
is the structure to be used. In my example only one index is defined, so
there is no need to instantiate an array of descriptions, a single
structure is required and used. The structure is newed, and its pointer
(pIndexDescription) will be passed to the appropriate member of the
structure that will be used to define the table. Again remember to fill
in all of the members of the structure.
One member of the index definition structure, aiKeyFld, is used to specify
the field that will be used to index the table. It is an ordinal number that
represent the position of the field as it appears in the table record
starting from left: 1 represents the first field, 2 the second, and so forth.
In the example provided above the index key field is MYFIELD_2, the second field,
so the number is 2 and it is passed to aiKeyFld by means of the
single-element array named aiKeyFieldNum. The prepending ai in
front of aiKeyFld stands for array of integers.
The aiKeyFieldNum is a single-element because the index key is composed of
only one field. I suppose, I'm not sure, that more elements in the array
need to be defined when the desired index key is the concatenation of more
than one field.
Now it is time to fill in the structure CRTabDesc that is used to
describe the table. Please note that there are members to accomodate the
following informations: number of fields (iFldCount) and number of indexes
(iIdxCount). Without these members the API that will create the table will
never know how many fields and indexes need to be defined.
Once all of the required structures are properly filled in with the
required data the task of creating the table is accomplished by means
of a single API call. The first parameter in DbiCreateTable is
the handle to the database, a global variable retured by DbiOpenDatabase
called in the BDEStart function defined above.
Once the table is created memory can be freed by means of some delete
statements. A second good rule to remember is to delete the pointers
exactly in the reverse order into which they were newed. This is
mandatory when arrays of data have been newed (and remember that nor the
compiler neither the linker will warn you). Following this rule will
avoid you unpleasant hours of bug hunting.
Also remember to use the delete operator to delete arrays.
This example code opens MYTABLE.DBF and
its MYINDEX_2.NDX index. The last parameter of DbiOpenTable (named
hTable, type: phDBICur) is a handle to the table. This handle will be
used for any subsequent operation within the table until it will be closed.
In my programs these handles are global for them to be seen from every
block of code.
Note that the table is locked or not according to the 8th parameter. See
also Locking a table or a record.
Opening a table is not sufficient to access its contents. The next step
is to retrieve the table's properties: number and type of fields, record
length, number of opened indexes, and so forth. All these properties, and
more, are contained in a CURProps structure (see BDE.HLP). So we
need to instantiate a CURProps structure and call DbiGetCursorProps
to have its members filled in with their current values. The
DbiGetCursorProps API needs an input parameter (the handle to the table)
and an output parameter (the structure).
If the call goes right then ErrorFlag is false. This means that properties
have been correctly retrieved. The next step is defining a record buffer,
not more nor less than an array of characters, that will be used as a
bridge between the application program and the actual record in the table.
The buffer is usually newed in the heap, and its length is given by the
iRecBufSize member of the CURProps structure.
To open a table that has been indexed on more than one field you should first
call DbiOpenTable passing a NULL as the 4th parameter. Then call:
DBIEXEC ( DbiOpenIndex ( hTable, pszIndexName, 0 );
as many times as the indexes count. That's all.
To get the number of records contained into the table is as simple as
placing a function call:
int iRecCount = 0;
Preparing the contents of a record and appending it can be done as showed
in this example code.
DBIEXEC ( DbiGetRecordCount ( hTable, iRecCount ) );
The code is self explanatory: a first call to DbiInitRecord and
subsequent calls to DbiPutField will prepare the contents of the
record into the record buffer. Then a call to DbiAppendRecord
will actually write the table.
You can use DbiInsertRecord as well. Infact DbiInsertRecord and
DbiAppendRecord both insert the new record at the end of the table. The
difference is that DbiInsertRecord has one parameter more, that is used
to lock the record before writing it, differently the function will fail.
When using DbiAppendRecord I suppose that the whole table should have been
When access to tables in the database must be granted to many users the
time has come for the programmer to distinguish between read and write
operations, and to regulate an appropriate locking strategy.
Anybody can always open a table for reading purposes, provided nobody else
already opened and locked it for writing purposes. And the table can be
obviously locked by one client at a time.
A whole table can be locked, or a single record, depending on the operation
the client is going to perform. To append and fill in a record, or to modify
records, usually the single interested record must be locked, leaving the
rest of the table for other clients to read. Other operations might require
a whole table lock (restructuring or reindexing the table, and so forth).
To lock a whole table you should do it at DbiOpenTable time, passing
dbiOPENEXCL as the 8th parameter to the function. If you don't want
to lock the table pass dbiOPENSHARED instead. In both cases if the
table is already locked by someone else a DBIERR_LOCKED return value
will be given upon return, and you should retry a couple of seconds later.
To append and lock a single record, after the table has been opened in a
dbiOPENSHARED mode, do it at DbiInsertRecord time:
DBIEXEC ( DbiInsertRecord ( hTable, dbiWRITELOCK, pRecordBuffer ) );
To lock and modify a single record, after the table has been opened in a
dbiOPENSHARED mode, do it like this:
DBIEXEC ( DbiGetRecord ( hTable, dbiWRITELOCK, pRecordBuffer, pRecordProperties ) );
// now modify the pRecordBuffer and then actually modify the record
DBIEXEC ( DbiModifyRecord ( hTable, pRecordBuffer, false ) );
Useful functions when dealing with record and table locks are the following:
DbiIsRecordLocked, DbiIsTableLocked, DbiRelRecordLock
and DbiRelTableLock. Anycase closing the table releases any lock.
Positioning to a record within a table is accomplished by a
DbiSetToRecordNo function call. The two input parameters required
are the handle to the table and the number of the requested record:
int iTableRecNo = 0;
DBIEXEC ( DbiSetToRecordNo ( hTable, iTableRecNo ) );
Tip: if one or more indexes are opened DbiSetToRecordNo works fine
unless one or more indexes are corrupted or not updated.
Seeking a record requires an index to be opened for the field to search on.
Search DbiSetToKey into BDE.HLP and have a look at the possible
values for the second parameter. In the example below a keySEQRCHEQ
value has been passed, meaning that we are looking for an exact match of
the key. If the exact match exists the record pointer of the table will be
positioned to the first matching record, and the function will return
DBIERR_NONE. If the exact match does not exist the function will
return anything but DBIERR_NONE.
char* pszKey = "FooBar";
Once the record pointer in the table is positioned at the required record
it is possible to retrieve the record's number.
RetCode = DbiSetToKey ( hTable, keySEARCHEQ, TRUE, 0, 0, pszKey )
This example function returns the record number
of the first record that matches a search condition upon an indexed field.
Zero is returned if the seek is unmatched.
The record number of a record is the iPhyRecNum member of the
RECProps structure, which in turn is filled in by a
DbiGetNextRecord function call.
Once the record pointer in the table is positioned at the required record
reading the contents of a record is quite straightforward.
This example code
The subsequent DbiGetRecord initializes the record buffer,
and one or more (in this case one) further calls to DbiGetField
will retrieve each required field content.
Please note that the programmer must specify to DbiGetField which
field to retrieve by means of the ordinal number of the required field
as it appears in the record from the left. In the example provided the
second parameter of DbiGetField is a "1", indicating BDE that the
field to get is the first.
To modify a record you should first position the record pointer at the
desired position into the table (see Positioning
to a record or Seeking a record).
Then you should prepare the record (just as described in Appending
and filling a record):
DBIEXEC ( DbiInitRecord ( hTable, pszRecBufTable ) );
DBIEXEC ( DbiPutField ( hTable, 1, pszRecBufTable, ( pBYTE ) szMYFIELD_1 ) );
DBIEXEC ( DbiPutField ( hTable, 2, pszRecBufTable, ( pBYTE ) szMYFIELD_2 ) );
DBIEXEC ( DbiPutField ( hTable, 3, pszRecBufTable, ( pBYTE ) szMYFIELD_3 ) );
Finally you can modify the record calling the following function:
DBIEXEC ( DbiModifyRecord ( hTable, pRecBufTable, false ) );
To delete a record you should first position the record pointer at the
desired record in the table, then call this function:
DBIEXEC ( DbiDeleteRecord ( hTable, NULL ) );
To close a table you must call DbiCloseCursor, passing it the handle
to the table to close. Then remember to delete the record buffer
that has been allocated when the table has been opened:
DBIEXEC ( DbiCloseCursor ( hTable ) );
To pack a table you should first open it (see Opening
a table). Then the table is packed by means of the following function
DBIEXEC ( DbiPackTable ( hDB, hTable, "MYTABLE.DBF", NULL, true ) );
To reindex a table you should first open it (see Opening
a table and one index). Then the index can be regenerated by means of the following
DBIEXEC ( DbiRegenIndex ( hDB, hTable, NULL, NULL, "MYINDEX_2.NDX", NULL, 0 ) );
If the table has more than one index you should call DbiRegenIndex
as many times as the indexes count. Before each call close the table
and reopen it with the only one index to regenerate.
To close the database engine simply call the following functions:
DBIEXEC ( DbiDebugLayerOptions ( 0, NULL ) );
DBIEXEC ( DbiExit ( ) );