Consultancy and Programming
Advice, tips, techniques and downloads for Visual Foxpro developers.
A frequently-asked question on our VFP training courses is whether it is better to use a database container (DBC) or to stay with free tables. DBC-based tables have a number of benefits in Visual FoxPro, but they can also cause problems for the unwary. In this article, we'll weigh up the pros and cons of each approach.
First, let's look at some of the arguments in favour of the DBC:
For many developers, this is an overriding benefit. Field names in DBC-based tables can contain up to 128 characters, compared to just ten in free tables. So you can now name your fields something like Total_Sales_Previous_Period rather than the cryptic TotSalPrev. However, the names are still limited to letters, digits and underscores, and you have no control over their case.
Note that tag names are still subject to the ten-character limit. This is a trap for the unwary. If you name a field Employee_id, then flag it as being indexed (in the first page of the table designer), the index tag will in fact be Employee_i.
You can assign a user-friendly caption to any field in a DBC-bound table. Captions can contain up to 254 characters, including punctuation, spaces and mixed-case letters. Captions show up as the default column headers in browse windows and grids, and also as the default captions when you drag fields from the data environment in the form and report designers.
You can also store a comment against each field. This is very useful for recording a short description of the field, in much the same way as you would write comments to describe your code. The comment appears at the foot of the project manager window when you highlight the field. Comments are for the benefit of the developer; users do not normally see them.
The DBC can hold validation rules, formatting rules, input masks, default values and triggers. There are obvious advantages in being able to store these items centrally, rather than having to implement them individually in your code. For example, when you store a validation rule for a particular field, that rule will be universally enforced in all applications, without any special action on the part of the programmer. Also, if the rule changes, you only have to implement the change in one place.
However, not all programmers like the idea of storing business rules in the DBC - especially validation rules and triggers. One reason is that you have less control over their timing. In a data-entry form based on a buffered table, the validation rule will not be fired until just before the record is committed, that is, when the user clicks the Save button. You might prefer to validate the data as the user moves focus off the field, so that the user can deal with the error straight away. Furthermore, by relying on DBC-based validation rules and triggers, you have to write code to deal with the resulting run-time errors.
This is another strong argument in favour of DBCs - at least, in VFP 8.0 and below. In brief, a transaction is a series of updates that must be completed either in full or not at all. The classic example is the transfer of funds from a current account to a deposit account. If the application fails after the current account has been updated, but before the update to the deposit account, the accounts will no longer balance and the money will be 'lost'. It would better to roll back the updates, so that the original balances are maintained. Transaction processing lets you do just that.
Tables that are stored in a DBC can always take part in transactions. In VFP 8.0 and below, you cannot use transactions with free tables. However, VFP 9.0 introduced the MAKETRANSACTABLE( ) function, which enables transaction support for free tables.
(Note that transaction processing is not the same as buffering, which is supported for both DBCs and free tables.)
A DBC can store details of the relationships between tables. These show up as lines drawn between related fields (or, more precisely, between related index expressions) within the database designer. Unlike the relationships which you create in your code (for example, with SET RELATION), these relationships persist even when the application is not running - hence the name.
Persistent relationships have no effect on your code. If you write code to move the record pointer in a parent table, you still have to take steps to ensure that the record pointer is moved to the appropriate record in the child table. The benefit of persistent relationships is that they provide a default for the relationships created in the query designer, the data environment and various wizards. They also serve a role in documenting the database - the connecting lines in the database designer give you a quick visual overview of how the tables are related.
With all those benefits, you might be wondering why anyone would want to stay with free tables. In fact, free tables have a number of important advantages of their own.
Free tables are inherently simpler than DBCs, and give rise to fewer hassles when you are deploying your application.
With DBC-based tables, you have to go to a certain amount of trouble to ensure that the DBC stays in sync with the tables and indexes. Both the DBC and the DBF contain a record of the table's structure, and run-time errors will occur if these do not match. The situation is similar with index tags. This means that, whenever you make any structural change to any data files which have been deployed to a user's site, you have to be careful to deploy an updated copy of the database container files as well.
Another problem arises if you move databases or tables to other directories. The DBC contains a pointer to the DBFs, and each DBF in turn contains a pointer back to its parent DBC. If the relative locations of these files is changed, the pointers will be incorrect and the user will see an unexpected message whenever any of the files is opened. (This only affects the relative paths between the files; if you always keep your DBCs and DBFs in the same directory, the problem will not occur.)
Free tables suffer from none of these problems. For the most part, deploying free tables is considerably easier than deploying DBC-bound tables.
If you want to share tables between Visual FoxPro and other development tools or applications (including Foxbase, Foxpro for DOS and Foxpro for Windows), you are pretty well obliged to use free tables. In general, DBC-bound tables are not compatible with earlier products. In fact, even free tables are incompatible if they were created in VFP (unless they were created by the COPY ... TYPE FOX2X command.) However, tables created in other products can be used within VFP - provided you don't modify their structure within VFP or put them inside a DBC.
So which type of table should you use? Our own preference has always been for DBCs - except where there are compatibility issues which force us to use free tables. But your circumstances and your applications will be different from ours, and different criteria will apply. We hope that this article has given you the information you need to help you make up your own mind.
Mike Lewis Consultants Ltd. November 2003. Revised January 2006.
FoxStuff is maintained by Mike Lewis Consultants Ltd. as a service to the VFP community. Feel free to download and use any code or components, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).
The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.
© Copyright Mike Lewis Consultants Ltd.