|
The Borland's Database Engine 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 BDEInstalling 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. Patching BDEBefore 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 following:
typedef CHAR far *pCHAR; Error catchingError 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 ); 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). Starting the database engineWhen 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 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 called. 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. Creating a tableThe 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 visual component.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 fields. 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. Opening a table and one indexThis 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. Opening a table with more than one indexTo 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. Getting the record count of a tableTo get the number of records contained into the table is as simple as placing a function call:
int iRecCount = 0; Appending and filling a recordPreparing the contents of a record and appending it can be done as showed in this example code.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 locked before. Locking a table or a recordWhen 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 ) ); 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 recordPositioning 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; Tip: if one or more indexes are opened DbiSetToRecordNo works fine unless one or more indexes are corrupted or not updated. Seeking a recordSeeking 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"; Retrieving the record numberOnce the record pointer in the table is positioned at the required record it is possible to retrieve the record's number.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. Reading recordsOnce 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. Modifying a recordTo 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 ) ); Finally you can modify the record calling the following function:
DBIEXEC ( DbiModifyRecord ( hTable, pRecBufTable, false ) ); Deleting a recordTo 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 ) ); Closing a tableTo 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 ) ); Packing a tableTo pack a table you should first open it (see Opening a table). Then the table is packed by means of the following function call:DBIEXEC ( DbiPackTable ( hDB, hTable, "MYTABLE.DBF", NULL, true ) ); Reindexing a tableTo 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 function call: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. Closing the database engineTo close the database engine simply call the following functions:
DBIEXEC ( DbiDebugLayerOptions ( 0, NULL ) ); |