[Technical Resources/Borland Database Engine/Configuring the Borland Database Engine for Network and SQL Access]
 

Configuring the Borland Database Engine for Network and SQL Access


Table of Contents

 

The Borland Database Engine (BDE) is one thing that ties all of Borland's products together. This gives owners of Borland products a big plus because the BDE is constantly being upgraded. Every time a new version of a Borland product is released, a new version of the BDE is released with it. Installation utilities used by Borland do not install multiple copies of the BDE, rather one copy is used by all products. Installing an upgraded Borland product will generally benefit all of the other Borland products you have installed because the BDE will be upgraded for them all.

In spite of the Borland's fine implementation of a central database engine, with the number of people who are programming BDE applications using dBase, Delphi, C++ and Paradox, there seem to be very few who have a thorough understanding of the BDE itself. One of the most common misunderstandings is how to install and configure the BDE for network and SQL access. This paper will address those issues.

Table of expressions

Before continuing to discuss the BDE it will be useful to have a common vocabulary:

File Server - A computer that runs software for a Local Area Network (LAN). Users with access to a common file server can share files. A file server just reads and writes these shared files. It does not perform any processing.

Network Operating System (NOS) - The LAN software that runs a file server.

Workstation - For this discussion, a workstation is a computer on the LAN which is used by a user to access files on a file server.

Database Server - A computer that runs database server software, typically SQL database software. The database server is also referred to as the "back end".

Client - A workstation that sends commands to the database server to run queries, create tables, add or remove indexes, maintain user security, or perform any other functions of which the database is capable. The client is also referred to as the "front end".

Local tables - Tables that reside on a file server.

Remote tables - Tables that reside on a database server.

Structured Query Language (SQL) - A standardized query language used to access data. There are currently two SQL standards: ANSI 89 and ANSI 92. Paradox 5 supports ANSI 89 SQL syntax against local tables. Paradox 7 now supports ANSI 92. All current versions of the top selling SQL database software support ANSI 92 SQL. In addition they also have their own enhancements to the language. This means that in spite of the ANSI standards, SQL statements written for one SQL server software will most likely not work with a SQL software from a different vendor unless the statements adheres strictly to the ANSI standards.

Row - Synonymous with the term "record" as it pertains to dBase and Paradox tables.

Column - Synonymous with the term "field" as it pertains to dBase and Paradox tables.

Adding Drivers

There are two types of database drivers which are compatible with the BDE. They are native SQL Link drivers and ODBC drivers. All versions of the BDE come with drivers for Paradox and dBase files. Client/Server versions of Borland Products come with additional drivers for Interbase, Microsoft SQL Server, Oracle, and Sybase.


Adding SQL Link drivers is simply a case of running the installation utility that ships with the drivers.


Using ODBC drivers with the BDE involves adding the ODBC driver to Windows through the Windows control panel. Details on this process can be found in your Windows manual. Once this is completed the ODBC driver must be added to the BDE configuration file. This is done through the BDE Configuration Utility by clicking on the "New ODBC Driver" button on the drivers tab. After pressing the button a list of the ODBC drivers appears. This list includes ODBC drivers registered in that copy of Windows. The "Add ODBC Driver" dialog box appears. A name must be given to the driver. Whatever is typed in the SQL Link Driver option will be added to "ODBC_". If you type in SlowAccess, the driver will be named "ODBC_SlowAccess". The second option is "Default ODBC driver and includes a combo box that lists the ODBC drivers installed in Windows. Select the appropriate driver from this list. The last option is "Default Data Source Name". This option is the name of the application that will be source of this data.


Aliases - Use Them!

When building any BDE based application, it is always wise to use aliases when referencing an object on disk. A benefit to using aliases is that when porting an application to another location, it will not mean recoding every reference to an object on disk. It will mean nothing more than moving the files and changing the aliases. While using aliases is a great advantage in flexibility even to applications whose data resides on file servers, it is a must for any application that may one day be upsized to a client/server database. When upsizing the system to client server, the only way to reference a table that resides on a database server is through an alias. Aliases are essential for all client server projects or projects that may become client server.


As far as a BDE application is concerned, an alias is a variable that points to a location for data. The location is either a database server, an ODBC connection or a folder. Configuring the BDE is different for each of these connections.


Setting up an alias

Setting up an alias is done through the Aliases tab of the BDE Configuration Utility. When adding a new alias the user is prompted with the "Add New Alias" dialog box prompting for the name and the type of the alias. The name option is the name you wish to give the alias. This is the name that will be used within your BDE applications to access the tables of your database application. Type refers directly to the name of the driver as it is listed in the drivers tab of the BDE Configuration Utility. All types available are selectable from a drop down list within the "Add New Alias" dialog box.


The "Add New Alias" dialog box.

Figure 1 - Creating an alias in the BDE configuration utility


All other components are modifiable within the alias tab of the BDE Configuration Utility. Each type of alias has a different set of parameters that are appropriate to the type of data being accessed. Paradox and dBase files use the Standard driver. Aliases using the Standard driver require a value in the path parameter. The value must correspond to a folder accessible by all users needing to access the data in that location.


An alias for dBase and Paradox tables.

Figure 2 - An alias for dBase and Paradox tables.


Covering all of the driver types is beyond the scope of this paper. Each driver for each SQL server has its own set of options that are relevant for connecting to the particular server. You will find detailed information on connecting with a particular server with the documentation supplied with client server Borland products.


An alias for an Interbase database.

Figure 3 - An alias for an Interbase database.


Multi-user Issues

Private versus Data Folders

The BDE recognizes two types of folders: private and data.


Private Folders

Each user must have read/write/create access to a separate, unique location to store temporary files in a multi-user system. BDE applications require that all users have a private folder. A private folder is one that is used by the BDE, and perhaps the application directly, for temporary files. The BDE creates many temporary files and requires a private folder to ensure that it has a place to write these files without causing conflicts with other users. As soon as the user establishes a BDE connection, lock files are written into the private folder. These lock files establish the folder as being available only to that user. No other BDE users will have access to that folder in any way - not even to read files. The most common example of a file stored in the private folder is the answer table created as a result of a query.


Location of a Private Folder

One question that often occurs is where to place the private folder. Generally it is best to place the folder on the user's hard drive. Placing the private folder on a file server will mean that all writing and reading of temporary files will have to be managed by the file server. Throughput on network lines will increase accordingly. To reduce the burden on the file server, particularly in systems accessed by many users, place the private folder on the local hard drive. This will result in increased performance for all users.


Data Folders

A data folder is one that contains files, including local tables, that are accessible by more than one user. A BDE application may or may not access a data folder. Client/Server applications may be exclusively accessing data residing on database servers. If this is the case then the application will not be accessing a data folder. If the application accesses dBase or Paradox tables common to more than one user, those tables must reside in a folder on the LAN. If Paradox files are accessed, it will write special lock files in the folder where the Paradox files reside. These lock files establish the folder as a data folder. These lock files prohibit another user from establishing the folder as a private folder.


Locking Mechanisms and Paradox Tables

Paradox tables have much flexibility when it comes to field types and locking. With the latest Borland Database Engine, their more glaring weaknesses have been removed. Paradox tables now offer descending secondary indexes and unique secondary indexes. For many BDE applications accessing data locally, Paradox tables are the table type of choice. Rather than cover how the BDE handles access of all local table types, the multi-user section of this paper will focus how the BDE manages access of Paradox tables.


Table Locks

Four different types of table locks are available for Paradox tables.



The privileges of a table lock are available to all objects using the same connection to the BDE. If a pointer obtains a write lock on a table, all other objects accessing that table will be guaranteed comprehensive write access to all records in that table.


Record Locks

A record lock is automatically placed on any record being modified by a user. If the record lock cannot be placed, the modifications to the record will fail. A record lock guarantees the user the ability to write to that record. Other users will have the ability to read that record, but not write to it. For this reason a record lock is sometimes referred to as a write lock for a record.


Unlike table locks, record locks are object specific. Once a record is locked by an object, no other objects will have access to that record even if the objects are using the same connection to the BDE.


Table and Record Lock Compatibilities

The following chart displays the rights that are available to both the user holding the lock and to other users for each lock type. This table assumes that no other users have placed any locks.


Lock Type Lock Holder's Guaranteed Privileges Other User's Privileges Locks Available to Other Users
None None All All
Open Read access.

Write access possible if locks placed by other users are not greater than an open lock.		 Read and write User has a record lock - Open

User does not have a record locked - Open, read and write
Read Read access

Write access possible if locks placed by other users are not greater than a read lock.		 Read Open, read
Write Read, write Read Open
Full All None None

Locking and Locking Related Files for Paradox Tables

In order to understand the BDE and how it manages Paradox table access, it is necessary to understand the locking mechanisms used by the BDE. Three files are associated with table and record locking in Paradox tables: PDOXUSRS.LCK, PARADOX.LCK and PDOXUSRS.NET.


PDOXUSRS.NET

The PDOXUSRS.NET file tracks the users currently using BDE applications. The information it tracks includes the version of the network file and the number of BDE connections (sessions) currently accessing data on the network. It also includes a list of users. Included in the list of users are their user names, Paradox locking style (there are two styles detailed below under PARADOX.LCK) and session number. Other information is also contained in the PDOXUSRS.NET file but that detail is not directly relevant here and beyond the scope of this paper.


The information tracked in PDOXUSRS.NET.

Figure 4 - The information tracked in PDOXUSRS.NET.


The location of PDOXUSRS.NET is determined by the BDE configuration file. The location for setting the folder for the PDOXUSRS.NET file is found in the BDE Configuration Utility on the drivers tab, under the Paradox driver. The setting is titled NET DIR.

The setting for the location of the Network Control File for Paradox tables.


Figure 5 - The information tracked in PDOXUSRS.NET.


If the PDOXUSRS.NET file does not exist in the folder specified in the BDE Configuration file, it will be created. As users open BDE applications whose BDE Configuration files point to this folder, their user ID is added to the list of users. As the applications are closed, the user ID's are removed from the list. Even when the last BDE application is closed and the last user is removed from the list, the PDOXUSRS.NET file remains. It is not deleted.


The .LCK Files

PARADOX.LCK and PDOXUSRS.LCK are files that are created by the BDE as soon as a BDE application accesses a table in any way. They are placed in the same folder as the tables that are being accessed. When the last BDE application accessing tables in that folder is closed, the two .LCK files are deleted.


PARADOX.LCK

Paradox for DOS version 4.0 introduced a new type of locking for Paradox tables. Paradox 3.5 and earlier versions all used a system of locking that is now obsolete. These early versions of Paradox used the PARADOX .LCK file as part of the file locking system. Later versions of Paradox now use a different series of algorithms to control table and record locking. These new algorithms are not compatible with the older style of locking. In folders that are controlled by the current locking system, the PARADOX.LCK file serves to stop earlier versions of Paradox from attempting to work on tables in folders that are being controlled by the current locking system.


PDOXUSRS.LCK

The PDOXUSRS.LCK file tracks all locking activity on tables that reside in the same folder. It contains a reference to the path and location of the PDOXUSRS.NET file. It also contains a heading for every table that has been accessed in that folder since the file was created. The first heading is a reference to the lock that is placed on the folder. All subsequent headings are for tables being accessed by BDE applications. The headings are knows as sections. As locking activity takes place on a table, that information is logged under the appropriate section for that table. A section header is never deleted even after there are no longer any applications accessing the table. The section is reused when new locking activity takes place on the table. When the last table being accessed is closed, the lock file is deleted.


The section header is the header for all locking information pertaining to the table associated with the section header. Each lock is tracked, as is the session number and user ID of the owner of the lock. In the case of record locks, the record number is also tracked.


The information tracked in the PDOXUSRS.LCK file.

Figure 6 - The information tracked in the PDOXUSRS.LCK file.


When changes are made to the linking values of master records in a referential integrity relationship, the BDE creates a group lock on all detail records. Rather than creating individual record locks on all linked detail records, the BDE logs the linking value(s) of the master record in the sections of all detail tables. Registering the linking value in a detail table effectively creates a record lock for all records with a matching value. This is done only while the master record is being posted. The locking of all of the details ensures that the changes to the linking value can be cascaded across those records.


Communication between PDOXUSRS.LCK and PDOXUSRS.NET

With DOS version 3.1 the ability to lock just parts of files was introduced. This is a DOS function called FLOCK. FLOCK requires a beginning and a starting address to serve as coordinates designating the section of the file to be locked. The BDE takes advantage of this DOS function only when accessing the PDOXUSRS.NET and PDOXUSRS.LCK files. With the exception of Novell's Netware, the DOS FLOCK is used on all network operating systems. This makes the BDE compatible with all DOS compatible networks. In the case of Netware, the BDE makes some Netware API calls to perform the equivalent FLOCK. While the principals are the same as using FLOCK, the Netware API calls result in slightly better performance and flexibility.


Whenever the BDE registers a new connection, it logs the user and session number in the PDOXUSRS.NET file. It also places a FLOCK on a single byte in the net file at an address starting at the user's session number plus 8000Hex. Remember that it is the operating system that controls the FLOCK. When the network registers that a user is no longer logged in, all FLOCKs for that user are removed by the network operating system.


Whenever any locking activity takes place on a table, the BDE accesses the PDOXUSRS.LCK file and places a FLOCK on the section header for that table. While that FLOCK is in place no access is possible on that table by other users on the network. Prior to writing in the new lock entries, the BDE accesses the list of other users who have locks on the table in question. It checks the PDOXUSRS.NET file to make sure that these users are registered and their FLOCKS are in place in the .NET file. The BDE then adds and removes lock information as appropriate and then releases the FLOCK for the section header for the table. The next user with locking activity on that table may now access the lock file.


If, while the BDE is accessing the PDOXUSRS.NET file it finds the user's FLOCK does not exist, the BDE knows the user is no longer logged into the network. This means the user's locks are invalid. It removes the users entry in the .NET file and all of that user's locks in the PDOXUSRS.LCK file.


It is important to understand that all other applications attempting to access a particular table will not be able to do so as long a section header has a FLOCK placed on it. If a flock is in place on the section header of a table, those applications requiring access to that table will queue up sequentially waiting for their turn to place their own FLOCK. Fortunately this process is extremely fast and the queuing rarely involves any kind of perceptible delay.


Performance Considerations based on .LCK and .NET locking

Whenever a BDE application is first opened there is a performance hit that results from adding the user ID and session number to the PDOXUSRS.NET file. Whenever the first table is accessed in a given folder there is a performance penalty because the PDOXUSRS.LCK file must be written to the folder. The first time additional tables are accessed in the same folder, there is a more minor penalty as a section header is added to the .LCK file.


Due to queuing that occurs as users wait to access a table, it is possible with applications with high transaction rates, that if many users are accessing many different tables, they could experience better performance than the same number of users all accessing the same two or three tables. The reason because if many users are accessing the same table, it is more likely that a FLOCK will be in place during an access attempt and the BDE will have to wait.


Common Errors

Network Initialization Failed

Network Initilization Failed. Try logging into the network before starting Paradox.

Figure 7 - Network Initilization Failed.  Try logging into the network before starting Paradox.


Potential causes:


Multiple .NET files in use

This happens when a user attempts to access a table in a folder that has already been accessed by another user whose BDE session is controlled by a different PDOXUSRS.NET file.


Intermittent crashes when unlocking records

Except for Novell networks, the BDE uses the DOS path referenced in the BDE Configuration File to denote what .NET file is controlling a given .LCK file. On a non-Novell network, this means that two users who have two different .NET files that reside in two different locations but with the same path can "fool" the BDE into thinking they each have the same network control folder. For example:


A user on server "A" has G:\PDOXNET as the path mapped for their network control folder. G:\PDOXNET is a folder on server "A".


A user on server "C" has G:\PDOXNET as the path mapped for their network control folder. G:\PDOXNET is a folder on server "C".


User "A" opens a BDE application that accesses data on yet another server, server "B". That user locks a record. While the user is working on that record the user from server "C" opens a BDE application and attempts to access the same table. This is acceptable because the path registered in the PDOXUSRS.LCK file created by the user on server "A" is the same as the one registered in user "C"'s BDE Configuration. The BDE checks back to the PDOXUSRS.NET file and does not see user "A" registered in the PDOXUSRS.NET file on server "C". The BDE determines that the locks left in the .LCK file are obsolete and removes them. When user "A" attempts to unlock the record, the BDE does not find the table and record locks that it had placed in the file. This results in an application error or possibly data corruption.


This problem does not occur on Novell networks because the BDE makes Netware specific calls to recognize physical locations rather than just relying on the DOS path.


The BDE and Client/Server Access

Optimistic Locking

When the BDE accesses Paradox tables, modifying a record causes Paradox to lock that record. This ensures that the record cannot be modified by another user and therefore the ability to save the changes is guaranteed.


Locking a record on a table on a SQL database is something to avoid. On many database servers locking a record locks not just the one record, but a page of records. This ties up valuable resources. Record locking on a remote table also has a strong negative impact on performance.


When a record from a SQL table is touched, the BDE does not lock the record. Instead it uses a technique called optimistic locking. It takes a snapshot of the record prior to its being modified. When the record is unlocked, Paradox queries the server for the record, locks it, checks each field value by value to see if any changes have taken place since the user touched the record. If the state of the record has not been changed, the old record is updated with the new values and the lock is released. If the record on the database server has been changed since the record was last retrieved, the new record is returned and the update does not take place.


For these reasons testing for changes by other users is reversed from what is normally done with file server databases. On a file server database, a record is locked before modifications are made. A successful lock means that the ability to save the record is guaranteed. For this reason code is written to test for the success of the lock before modification of the record begins. When working with records from SQL tables, this is reversed. Saving the record is never guaranteed. Since records are not locked and the saving of a record could fail, testing is done as the records are being saved, not as they are touched or locked.


Tuning BDE Settings for Optimal Performance

Below are listed some configurable options in the BDE. Specifically those that will impact performance are listed. It is quite possible that making changes to the BDE as outlined here can have a positive impact on your BDE application. The options are broken down by the notebook tab they fall under.


The Drivers Tab

The Paradox Driver - the Block size option

All databases segment their files into fixed chunks of equal sizes called blocks. Paradox tables are also stored in blocks. A greater block size means the table can be larger. Larger block sizes result in superior performance for tables stored on local hard disks. Larger blocks also can benefit performance for tables that reside on a network server - up to a point. Once the block size of a table becomes larger than the protocol packet size supported by the network, each block will have to be split among two or more packets. This will result in poorer, rather than better performance. What this means is that while larger block sizes result in superior speed for tables stored on a local hard disk, 4K (4096 as a value in the BLOCK SIZE setting of the Paradox driver) settings tend to be optimal for most networks.


The drivers tab.

Figure 8 - The drivers tab.


System Tab

Local Share

By default the BDE does not create lock files for tables that reside on the local hard disk. Only one instance of the BDE is loaded into memory and it controls the access of tables and records for all BDE applications that are open in that copy of Windows. If all access of Paradox tables is only from the one computer and performed strictly with BDE applications, there is no reason for lock files to be written to disk.


There are two exceptions whereby the BDE should write lock files to the local hard disk. If some database applications that do not use the BDE are opened and are accessing dBase or Paradox tables at the same time a BDE application is accessing the same tables, the only way locking conflicts can be avoided is through the use of lock files. The other situation where lock files should always be written to the local hard disk is when that hard disk is on a workstation that is also being used as a network server in a peer-to-peer network. If other users are accessing tables it will be necessary for the BDE to create lock files. In any case, the creation of locks files causes some degradation in performance. While that may be undesirable, the alternative means risking table corruption.


By default the Local Share option on the System Tab of the BDE Configuration Utility is set to false. A false value means that lock files are not written to the local hard disk. In order to force the writing of lock files to the local hard disk, this option must be set to true.


The system tab.

Figure 9 - The system tab.


MinBufSize and MaxBufsSize

The BDE keeps an area of memory reserved as a buffer for tables. This is similar to a disk cache but only for table files and data from database servers. The larger this table buffer is, the greater the amount of data that can be stored in memory. This means reducing the number of disk reads required to present information to a user.


MinBufSize

MinBufSize is the setting that determines the size of the table buffer as soon as the BDE is loaded into memory. The default setting is 256K. Given that most computers these days have eight to sixteen megabytes of electronic memory, it is generally beneficial to change this setting to at least two megabytes or 2048 as a value in the MinBufSize setting on the system tab of the BDE Configuration Utility. This will mean that two megabytes will be reserved for the table buffer as soon as the BDE is loaded.


MaxBufSize

While MinBufSize determines the immediate size of the table buffer, the table buffer can grow in size as more and more table data is read. Assuming there is an adequate amount of memory, MaxBufSize is the setting that determines the maximum size obtainable by the table buffer. It defaults to two megabytes or a value of 2048. This setting is more critical than MinBufSize in terms of the impact it can have on performance. A general guideline is to set this value to a number that is between one half to two thirds the amount of electronic memory on the computer. Additional memory reported by Windows because of the swap file should not be included when using this formula.


For example, if a computer has sixteen megabytes of memory, a setting of 10 megabytes (10240) is an appropriate value for obtaining the best performance.


MaxBufSize is the one setting that is most likely to deliver marked increases in BDE performance when it is properly set.


MaxFileHandles

This setting is the maximum number of file handles that the BDE reserves when it is loaded into memory. The default is 48. As the number of files that are being used by the BDE exceed this amount, file handles have to be released to access new files and then released again to return to the previously accessed files. BDE applications using local tables will probably benefit with a higher value in this setting. Generally a value of 75 - 80 will help performance for those applications that access a great number of tables at a time. This will reduce the need of the BDE to drop a file in order to free up a file handle so that it can access another file.


An Easy Answer to Configuring the BDE

The default BDE Configuration file is frequently beyond the control of the developer. This is a quandary since getting the most out of a BDE application requires proper configuration. The best solution is not to rely on the default configuration. Create a BDE Configuration file. Create it with only those aliases required by your application. Establish the proper settings for ideal performance such as MaxBufSize and MaxFileHandles.


The BDE has a default BDE configuration file it accesses when it is first loaded. The path and name of this file are stored in the Windows registry under the setting HKEY_LOCAL_MACHINE\SOFTWARE\Borland\Database Engine\CONFIGFILE01. In Paradox and Visual dBase, a simple command line parameter allows for a different BDE Configuration file to be specified at run time (-o). Such a parameter may also be added to the command line of compiled applications written in C++ and Delphi. This solves many potential problems.


Through a custom BDE Configuration file tailored to your application it is possible to ensure that:


  1. All aliases required by your application are present (and only those required by your application).
  2. The configuration values are set for optimal performance.
  3. All users accessing your application will have a common network control directory.

Creating your own BDE Configuration file will work in most cases. It is easy to implement and it solves many possible problems before they arise. If all users run your BDE application with the same configuration file (placed in the default folder for the application), there will be no conflicts.


Additional information

If you are looking for additional information on the BDE, reference the manuals supplied with your Borland software. There are also two FAQ (Frequently Asked Questions) files available on the Borland Web site (www.Borland.com) and the BDEVTOOLS forum on Compuserve. They are BSEC04.FAQ & BSEC05.FAQ. Below are some of the more general question and excerpts from these documents. These FAQ files also contain useful information that is specific to particular dialects of SQL software.


Q: What basic steps can I follow to make my BDE application run smoothly?

A:

  1. Increase stack size to 20K.
  2. Increase the number of file handles available to your application using the Windows API function SetHandleCount.
  3. Check the return values of each and every BDE function call and provide some means for handling the result of any return value other than DBIERR_NONE. (See next question also).
  4. Take a look at TI2761.ZIP, which describes the basic steps that are required in setting up a BDE application.

      5. Q: Why is my application having problems when share is loaded?


      A: One possibility is that share could be running out of locks or file handles. The command line 'share /L:200 /F:4096' will increase the number of files that can be locked to 200 and the memory available for files to 4096 (the default is 20 and 2048). See your DOS or WINDOWS manual for more information on share.


      Q: How can I optimize BDE performance on table operations?

      A: Although there are a number of ways to improve BDE performance, some general things to try are as follows:
          1) Keep the number of maintained secondary indexes to a minimum; sometimes it is better to delete the index and recreate it than to perform a number of table operations with the indexes in place.

          2) If possible, increase the size of the swap buffer and the number of file handles that the BDE has available to it. This will decrease the Engine's need to swap resources. Note: make certain to increase the file handles available to your application using SetHandleCount, as well as increasing the number of file handles available to the BDE in IDAPI.CFG.

          3) Open the table exclusively.

          4) Batch as many opperations as possible - do not read/write records one as a time. Use DbiiBatchMove, DbiCopyTable, DbiReadBlock, and/or DbiWriteBlock.

          5) When using DbiWriteBlock, try to work in multiples of the physical block size, usually 2k or 4k.

          6) If you are opening and closing one or more tables repeatedly, consider calling DbiAcqPersistTableLock on a non-existent file after you initialize the BDE. This will create the .LCK file so that it will not have to be created each time a table is opened, created, etc. (Note: you'll also want to call DbiRelPersistTableLock before calling DbiExit).

          7) Paradox Tables Only - Work with in-memory tables when possible (Note that in-memory tables cannot be the source table to DbiBatchMove).


      Q: How do I access tables in a read-only folder (such as a table on a CD-ROM)?


      A: A folder lock needs to be placed within the folder containing the table to prevent the BDE from attempting to create a lock file (.LCK) in that folder. An example of this is included in the SNIPIT example application, in the file RDOLOCK.C.


      Q: I got "Table locked/busy" and it's neither?


      A: This error happens when the application tries to lock a table when the corresponding prevent lock already exists, or when the application tries to set a prevent lock and a conflicting lock already exists. Check for old, unused lock files. Delete any .lck files that may exist after all BDE applications have terminated. Also run the TUtility program to test for table validity.


      Q: How do I know whether my query will execute locally or remotely?  Do I have any control over this?


      A: In the BDE configuration utility, you can specify whether the default query mode is SERVER or blank. When you leave the SQLQUERYMODE box blank, SQL Link will determine which is faster, a local query or a remote query and execute the query accordingly. The setting for SQLQUERYMODE is under the Aliases button in the DBOPEN set of parameters.


      Queries will execute locally if the operation you are attempting is not supported under the SQL standard your SQL server adheres to. For example, if you are attached to InterBase 3.3B, the remote query you are trying to run must be SQL 89 compliant in order for the query to run remotely. Another gotcha you should look out for is remote tables that have names that are reserved words. This will also cause the query to run locally, even though the SQL button gives an SQL translation of the QBE query.


      Conclusion

      If you are creating a BDE application, you are taking advantage of one of the best features of working with Borland software. Developers who have difficulty with the BDE may solve many network problems and increase performance if they understand how to configure it properly. If properly setup, the BDE will give great performance and very few problems even when used in client/server and multi-user environments.

      Building systems that last...