BORLAND DATABASE ENGINE – A FEW NETWORKING ISSUES

Peter Szymiczek & Iris Radulescu

ADUG Presentation – 17 May 1999

 

What is the BDE?

Just to state the obvious – that’s how Borland / Inprise products access databases, sometimes in native mode, sometimes using proprietory connectivity either directly to backend databases or to the Microsoft Jet engine.

The Borland database engine was the product competing with the Microsoft Jet Engine / ODBC at the beginning of time, when MS had Access and VB 1.0 while Borland had Paradox 4.0 for Windows. Those were the days of the 16-bit operating system, 16-bit database engines. More history about "the beginnings" should cover ODAPI, IDAPI and the Paradox Engine.

Although Paradox changed hands (it now belongs to Corel after a tortuous route) the optimal way to access Paradox databases is still the BDE, currently operating in 32-bit. There are ODBC drivers for non-Borland compilers to access Paradox databases and these are included with the MS Data Access Pack, as well as earlier versions of the Jet Engine.

Both Jet and BDE are cumbersome and difficult things to install, configure and deploy – especially deploy programatically.

Where is the BDE?

A search of the borland.com site revealed a new-ish release of the BDE – version 5.01. (February 1999).

This is the direct link: http://www.borland.com/devsupport/bde/bdeupdate.html

According to the blurbs, this is an update-only release and will only install files if the "core" components of the BDE are already on the target system. (See sideshow Legal Issues )

It will upgrade existing BDE installation even to the extent of adding FoxPro and Access connectivity to the engine, and it will talk to Microsoft’s SQL Server 7 as well.

There is a small caution on the above Borland page: the install program may experience some browsing problems on Win98 with the Client for Netware installed. This may or may not mean something with regard to the functionality provided by the database engine.

This release has a SETUP program which works under a particular Windows build (Windows 95 version 4.00.1111). The previous release (Nov 1998) refuses to run setup.exe under this build.

What is the BDE again?

Let’s try to summarise some of the issues:

The main advantages of the BDE :

There is information about the existence of record-level locking in the latest version of Microsoft’s SQL Server 7 (in 6.5 Microsoft had record-level locks for inserts only).

To achieve table/record locking in Paradox databases, the BDE uses lock files. The locks are physically embodied in the famous .lck files – pdoxusrs.lck. See some hints about how they work in lock file hints.

 

Networking issues – a Case Study: BMW Financal Services Australia

BMW Financial Services use a mixture of 16-bit and 32-bit applications talking to Paradox tables residing on a Novell server. Thus, both the 16-bit and 32-bit BDE have to be maintained. The network protocols include both TCP/IP and IPX/SPX, thus the workstations run both the Microsoft Networking Client and the Netware client.

The issue of Paradox tables stored on NT Server has been discussed previously and the results are inconclusive. Suffice to say that tables appear to be corrupted easily by 16-bit applications where there is write-back to the tables.

Using 32-bit Delphi applications through the 32-bit BDE has not yet been proven to cause any corruption to Paradox tables on NT servers, but we have just begun porting / writing applications in 32-bit for Paradox tables (we are using Interbase for developing a new system).

The 16-bit system makes it necessary for the data tables to live on a Novell server because we cannot guarantee data integrity otherwise, despite experimenting with network redirectors (see appendix1).

The application software consists of a mixture of software written in Borland C & C++, later Delphi ver. 1. Since there are DOS programs involved, the Paradox tables need to be kept back to Paradox table format level 4 and they cannot be allowed to exceed 250 Mb.

The system has grown to about 1.7 Gb in total, with some tables constantly tottering on the edge of this 250 Mb barrier. Some of these tables contain up to 2 million records.

This Novell-based database supports in excess of 40 concomitant users with occasional slowdowns while huge reporting programs throttle the network (those are rogue programs that make repetitive passes through every record in huge tables…).

Configuration issues for programatic access to Paradox tables

16-bit Delphi programs access the Paradox tables through the 16-bit BDE interface. The BDE is configured manually to set system wide parameters including aliases, and the applications use BDE API calls that set a number of parameters, one of them being timeouts.

The IDAPI configuration is at the basis of a stable system. With the 16-bit IDAPI, it is possible to have a unique IDAPI.CFG file on the network (included in the DOS PATH) and point all workstations to it through the WIN.INI file.

INI Settings

Our WIN.INI points to the network location of the configuration file & the language drivers, and can be easily included in each new workstation’s system directory.

[IDAPI]

DLLPATH=D:\IDAPI

CONFIGFILE01=D:\IDAPI\IDAPI.CFG

[Borland Language Drivers]

LDPath=D:\IDAPI\LANGDRV

IDAPI.CFG

The IDAPI.CFG file was created and maintained with the 16 bit BDE Administrator and is now fixed. (The 32-bit BDE Administrator only modifies the IDAPI32.CFG config file, and on our system it points to the same locations). In earlier releases, there is a utility called IDAPICFG.exe which actually modifies the IDAPI.CFG file.

This config file specifies a shared "Net Directory" location. This is where Paradox places the network-wide database access control file "pdoxusrs.NET" file. This file is the Paradox way to manage user connections to a database. It would be interesting to look at these implications in the 32-bit BDE system as it adds the further complications of Tsession and multi-threading.

The system-wide access-control file "pdoxusrs.LCK" file is placed in the directory where tables are found, hence into the "Data" directory. This is where table and record locks are managed.

The last (but not least) important configuration issue is that no single BDE-based application must be allowed to start in the network folder where they are called from, or in the data folder. To sort this out, we ensure that all shortcuts specify C:\PRIVATE as the application’s working directory.

How?

Each workstation is set up with a designated directory already created. We name it "C:\PRIVATE". This folder is also included in the Paradox configuration as the location of the :PRIV private directory for the BDE. This is where the BDE will direct each user-mode lock file ("pdoxusrs.LCK"), as well as all temporary and answer tables. So-called "user—mode" lock files are created by queries which output to temporary DB files.

This avoids mixing up the user-mode lock files and system-wide lock files, as well as avoiding simultaneous access to the temporary tables created during queries (usually these have file names starting with 2 underscores).

Apart from lock file corruption during some of the particularly busy times described above, there is occasional table corruption, but the consensus and our experience proves that this is actually benign compared with other database systems.

As the system is not client-server or n-tier, table updates are workstation-driven. If the workstation crashes (power glitch, OS freezing, user reboot, etc), a crash can leave either the lock file in tatters or corrupt database tables, usually index files.

The lock file corruption is discussed in Lock file hints.

Table corruption can be repaired, usually without data loss, in one of these manners:

  1. Using the TUTILITY program from Borland. This will recreate the table in a DOS window.
    My personal experience with this utility is more on the sad side of things: lost data. You must make sure you match everything in the initial settings of the table, including the language driver and the indexes, otherwise the result is guaranteed failure.
  2. Using the Database Repair utility in Paradox 7 (not sure if earlier versions have it). Some of the above cautions apply, but it has been less prone to disaster in my experience. However, this option has a reasonably low success rate when applied to early Paradox format tables (4 and 4.5) as it reports corruption even where there is none, then it thinks it repaired it but a second Verify will show that "table still needs a rebuild".
  3. Paradox RESTRUCTURE utility – again , tested in Paradox 7. This has a huge rate of success and, coupled with the option to "Pack the database", leads to vastly improved tables. It has been known to fail recovering some data records following bad crashes.
  4. ChimneySweep – utility sold by … http:\\www.sundialservices.com

Lock file hints

The first access by a user program to a Paradox Ttable actually goes through an implied Database component. This is implied in the Paradox ALIAS used as the Database property of the Ttable.

To access the database, the user creates an entry in the Pdoxusrs.net file. It the .net file does not exist, the first access creates it. Users must have read-write access to the network folder designated as "NetDir" in the IDAPI.CFG.

Once the database is open, a read access does not require any lock, therefore users do not need to write to the lock file, therefore the pdoxusrs.lck file is not created or updated.

When a user program accesses a table in "update" mode, it locks the record for writes. The write lock is exclusive. As soon as a table is opened in update mode, the lock file is created automatically in the table location. If the lock file exists, it is temporarily locked for exclusive write and a lock record is written to it.

When the last user program releases the update lock, the lock file is automatically deleted. This again requires a temporary exclusive lock on the lock file. If you have no users on the system but the lock file is still there, this is an indication of some trouble having occurred.

To open a table in read-only mode, specify it before the OPEN statement (Mytbl.ReadOnly := True).

Otherwise, if neither read-only nor update modes are specified, the table is opened in an undefined mode and the user needs to write a table-wide non-exclusive lock into the lock file. This lock at the table level will not prevent edit, add & delete operations on records, but will prevent any attempt to lock the table exclusively, as it would need to be for a restructure, for instance.

In order to place an exclusive lock on a record, the user program first needs to obtain an exclusive lock on the lock file.

Subsequent user programs attempting to access the lock file for updates will fail and retry. The timeout parameter specifies how long the attempts should wait until an error is raised and the operation aborted.

It happens sometimes that a user kills the program because it appears to be frozen. At that stage, the lock file is not updated therefore the .lck is perpetually marked as exclusive access. All user programs get a BDE contention status and none is able to continue. The only solution is for the lock file to be fixed or deleted. The user who actually "lost" the exclusive lock has to restart the system and log on again to the network. This will release the lock placed by this user, at least in a Paradox on Novell environment.

Manual intervention is possible when it is clear that all application programs and interactive users are off the system. You simply delete the lock file pdoxusrs.lck and pdoxusrs.net.

If you do that while any application or user is interacting with the data tables, it is a recipe for disaster even though the delete operation may not complete (file is locked).

Legal BDE Issues

It is tempting to speculate a while on the motivations behind these "restrictions" on redistributing the BDE. Since the new BDE release includes connectivity to Microsoft backends of all calibers (from Access through FoxPro to SQL Server’s latest release), Borland.com appear to be mainly concerned with not beeing seen to distribute "freeware" that enables competing compilers to access these databases.

The developer’s "existing Borland license" is mentioned as being the basis on which free distribution of the BDE is allowed. "To be eligible to install and use the Software, you must own a licensed, registered copy of an Inprise product ("Inprise product") which includes files designated by Inprise as BORLAND DATABASE ENGINE." (License.txt with the BDE setup).

However, the customers may also be directed to the borland site to download the newest version – which basically means the installation program will simply check for the existence of a "core" BDE which it will upgrade regardless of the "license".

It would be interesting to get some more clear-text messages from Borland concerning this BDE distribution – for instance, I would like to know if my old Borland – Paradox developer’s license is still a valid basis for me to distribute the current BDE to any third party!

Going the other way ‘round, i.e. distributing Microsoft’s Data Access Pack (which includes ODBC drivers for Paradox databases) may be a bit of a legal problem, as the MDAC is clearly an Office product and is probably asking for confirmation of EULA before installing.

History of the BDE – any fills?

Borland’s Turbo Pascal included a "database" Toolbox; while it did not work with Paradox tables, it was the beginning of the Borland compiler add-ons that facilitated database connectivity. Then came the Paradox Engine for Windows – PXENGWIN – which could be compiled into a program to facilitate connectivity to Paradox tables.

The first DLL-based connectivity engine was ODAPI. It represented Borland’s attempt to centralise connectivity in its suite of applications which included the brand-new Paradox for Windows 4 and even Quattro. With version 4.5 / 5.0 of Paradox for Windows, this database engine was crystalised as IDAPI, which is the name persisting even inside today’s BDE.

Troubleshooting hints

The "Multiple net files in use" error is not from faulty mappings to the

database, per se, but to the NetDir. You should make the NetDir separate

from the data directory, to avoid security problems. The reason is that

all clients must have destructive rights to the NetDir and you almost

certainly won't want that for the data directory. And, in fact, if you

have them in the same directory, any attempt by a client who is denied C/D

rights to the data directory will be denied any access at all, because

every user updates Pdoxusrs.net at login time and every time a lockable

access to the database commences, a record for that client is written to

Pdoxusrs.lck - hence your 'Access is denied' problem. When that client

properly releases the lock, that client itself deletes the lock record.

(Hellen Borric – email exchanges on ADUG list )

Appendix 1

Problems in using Paradox tables residing on NT Server when the applications access the data starting from Windows95 workstations are said to be related to the network redirector. Upgrades were released but, in my experience, there has been nothing that could be verified to not cause data corruption.

> You might check out MS Knowledgebase article Q142803. It provides an

> updates VRedir for Win95 machines accessing NT servers. The article is

> titled "Locking error or Computer Hangs Accessing Network Database Files".

> My specific problem was slow access to tables, and this seems to have fixed.

More information at http://www.borland.com/devsupport/bde/ti_list/TI3342.html

Appendix 2

Code to solve lock contention and illustrate all the above concepts:

By Peter Szymiczek

"I have tried dbiIsRecordLocked without much luck either. The strategy I settled for instead is a bit more agressive, but works quite well: instead for testing (which doesn't work) I attempt edit - if it fails, it throws an exception, which is immediately handled and edit is retried until the specified timeout. Here's what I use - never fails!"

procedure LockRecord(var dbTableName: TTable; iTimeout: word);

var

bSuccess: boolean;

tStart: TDateTime;

begin

tStart := Now;

with dbTableName do

repeat

Refresh; { !!! can't be used after Edit - changes table mode back to browse !!! }

try

Edit; { attempt to lock the record }

bSuccess := TRUE;

except

{ discrete exception handling removed for clarity }

bSuccess := FALSE;

Cancel;

if (iTimeout > 0) then

if (Now > tStart + iTimeout / 86400) then

{ one could use CPU tick counter instead of system time }

raise; { re-raise the original exception }

{ this is a good place to put a delay before retry, i.e. Application.Processmessages }

end; {try}

until (bSuccess);

end;