Using Advanced Btrieve Features with Clarion Database Developer
by Wayne Freeman
The Btrieve data management engine provides developers with many powerful features that we generally don't find in other desktop data management systems: Data compression, blank truncation, index compaction, and manual keys, to name a few. Clarion Database Developer 3.0 provides Btrieve support through the Btrieve Replaceable Database Driver; unfortunately, the current release of CDD (3008) does not support the full range of Btrieve functionality. That's the bad news. The good news is that you can use almost ali of these powerful features in your CDD applications today, by applying a little forethought when you create the file.
Clarion has committed to providing support for the full range of Btrieve features in future releases of CDD. I don't know when the features I will be discussing in this article are scheduled to be supported by CDD. By the time you read this, all, some, or none of these fea- tures may be supported. Therefore, while my primary goal is to show how to make these features available to your applications now, l'Il also discuss why and when you might want to use them in your applications, so you'lI have a good background in their usage when CDD does provide full Btrieve support.
The guiding principle that makes all this possible may sound paradoxical (no pun intended) when I present it: The Btrieve feature set that can actually be used in your CDD applications is not limited to the Btrieve feature set supported by CDD. I know that may not seem logical or possible, but it's true. Read on!
The Myth of the "Dumb" Btrieve File
Many developers who are used to xBase, when confront- ed with their first Btrieve file, will complain about how "dumb" it is. They base this assertion on the fact that Btrieve files carry no information about the fields in the record as do xBase files, and then extrapolate from this the generaI statement that "Btrieve files carry no information about themselves." l've heard and read this statement many times.
The truth is however, Btrieve files carry quite a lot of information about themselves, and the lack of embedded field information provides more flexibility than limitation (but that's another article). The Btrieve file Control Record, or Header Page, contains ali the information about the file, such as the record size, page size, and alternate collating sequence (if any). It also contains information about all the keys, such as what type, how many and the number and size of each segment. The Btrieve Record Manager inserts ali this information into the Header Page at file create ti me. Since the Btrieve Record Manager is a TSR (DOS) or DLL (Windows), it performs ali file operations independently of your program, allowing your program to be blithely ignorant of what Btrieve needs to know to do its job. It is only the program that actually creates the file that needs to know everything about the file.
This is why you can create Btrieve files that provide features to your applications that CDD doesn't supporto In order to do that, however, you have to use a non-CDD-based program or third-party tool tO create the file originally. Y ou must prevent your CDD-based program from creating the file, as well.
Btrieve Technologies (recently spun off from Novell) and third-party vendors supply many tools for creating and managing Btrieve files: Butil (Btrieve), Xtrieve (Btrieve), BTFiler(Access MicroSystems) and DDFBuilder (SmithWare), to name a few. I have created example scripts for this article using only Butil. BTFiler, Xtrieve and DDFBuilder are more interactive in their approach and don't generate code listings, so they don't lend themselves well to the purposes of this article, although they do everything that Butil can.
Using Data Compression and Blank Truncation to Manage Memos
Why?
CDD, by default, stores memo fields in a separate memo file. The structure of the file stores one memo field per record, thus allowing you to define multiple memo fields for a file. I find the key structure of the memo file to be somewhat problematic, however. The key to the file consists of a long integer, which is the physical record pointer of the record to which the memo belongs; followed by a short integer, which is the sequence number of that memo field. Therefore, you are required to use a CDD program to access your memo file.
I have personally had no reliability problems related to this scheme; however, I have heard from others who have. When corruption does occur, it is absolutely necessary that you rebuild the file using a CDD program that contains the Build() statement, in order to maintain the relationship between the parent record and its memo record(s). This is because only the CDD file driver knows enough about the relationship between the files to do that accurately. If you were to use a separate Btrieve utility to rebuild the two files, you would destroy the fragile record-pointer-based relationship, thus totally losing your memos.
When?
I believe that the CDD Btrieve driver handles the memo file situation reliably, but we ali know that many things can happen that can necessitate the rebuild of a given data file. For highly sensitive situations using large memo fields and high volumes of data, I prefer to keep my memo fields inside the data records. Fortunately, the CDD Btrieve driver supports this, using the driver string "/MEMO=SINGLE." Unfortunately, this only allows one memo field per record. If you need to define more than one memo field in a record and keep those memo fields embedded in the record, CDD can't help you now. There's a solution, though.
How?
First, define your memos at the end of the record, in ascending order of size and descending order of probability of use. Give more weight to the size attribute, so that the largest memo stays at the end of the record. If the memos are nearly the same size, give more weight to probability of use. Arrange things to maximize the likelihood of the record ending with a large number of blanks. Define the memo fields as type "String" rather than "Memo." This will disable the built-in memo handling, thus allowing Btrieve to do ali the work. See Listing 1 for an example.
myfile FILE,PRE(myf),DRIVER('Btrieve'),NAME('myfile.dat')
primary_key KEY(+myf:key_field)
RECORD RECORD
key_field STRING(@nO8b)
field1 LONG
field2 STRING(8)
memo1 STRING(200)
memo2 STRING(1200)
. .
|
|---|
Notice that I left out the "CREATE" file attribute and did not use the driver string "/COMPRESS=ON." That's because we're now going to create the file using the Create function of Butil. Since Butil will assign the "compress" attribute to the file permanently, it's unnecessary to specify this attribute in the CDD file specification.
It's probably also a good idea to eliminate the "CREATE" attribute from any files that you intend to create externally, in order to avoid having your CDD program inadvertently create the file without the attributes that you intend it to have.
Next, you will need to create the file with all the proper attributes. These attributes include:
- Blank Truncation,
- Variable Length Records, and
- Data Compression
BUTIL -CREATE myfile.dat myfile.des
where "myfile.dat" is the data file to be created and "myfile.des" is the description file. You can deal with memo fields created this way exactly the same as you would if you had created the file through CDD, either in designer or hand-code. Simply specify that the field is a text field when placing it on a form, for instance, and CDD will handle it as such. Printing a large text field is more of a challenge, and beyond the scope of this article, but it's stili true that you would print it the same way as you would normally print a same-size memo field.
record=16 'Begin MyFile definition variable=y 'Set Variable Record Length attribute truncate=y 'Set Blank Truncate attribute compress=y 'Set Data Compression attribute key=1 page=4096 replace=n fthreshold=20 position=1 length=8 duplicates=y modifiable=y type=string alternate=n segment=n |
|---|
Using Manual Keys to Create Dynamic Subsets
A Btrieve Manual Key is a form of automatic subset. If you specify a key as a manual key at file-create time, then if every byte of any one segment contains what you specify as the "null" value, Btrieve will exclude the key from the index. This lets you use a manual key segment as an indicator as to whether the key is to be indexed. By setting the key segment to its null value, you instruct Btrieve to exclude the record from the index. Conversely, by setting the key segment to something other than its null value, you instruct Btrieve to include the record in the index.
Why?
Judicious use of manual keys can both simplify your program code and speed up processing. When you use a nunual key as an access method to a file, Btrieve does your major filtering task for you, and at the server level, as if it were SQL!
When?
Let's say you have an order file containing several thousand pending order records, and that you want to bill out lO of those orders so that you can print invoices. Most billing processes set a flag in the order file to indicate that the order has been billed and is ready for invoicing. Without manual keys, the invoice printing procedure would have to read the order file and apply a filter condition in order to extract those records that have the "billed flag" set. Using a manual key, our billing process would not change. The difference is, we have created the file specifying a key (let's call it "BiIlKey") containing Order Number and Billed Flag, and set the "manual" attribute to 20 hex. This index, then, would normally be empty, until you run the billing process, setting the Billed Flag to "Y" in those lO records, at which time Btrieve inserts those lO records in BillKey. Subsequently, the invoice printing procedure reads BillKey, thus having to handle a total of only lO records. Your invoicing procedure, in this case, contains no filtering code.
How?
Listing 3 shows the CDD file specification for the order file example I used in the "Why?" paragraph. I have included two keys: one for standard access by OrdNbr, and the second, which will be the manual key, for access by OrdNbr and BillFlag.
OrderFile FILE,PRE(ord),DRIVER('Btrieve'),NAME('order.dat')
OrderKey KEY(+ord:OrdNbr)
BillKey KEY(+ord:OrdNbr,+ord:BillFlag)
RECORD RECORD
OrdNbr STRING(@n08b)
BillFlag STRING(1)
RestOfOrder GROUP
FillerField STRING (100)
. . .
|
|---|
Listing 4 shows the Butil Description File that cre- ates OrderFile, specifying BillKey as a manual key. Please note that Butil Description Files do not allow embedded comments, so the comments that I have included are for purposes or this article only; don't try this at home!
record=109 ' Begin OrderFile file definition.
variable=n
truncate=n
compress=n
key=2
page=4096
replace=n
fthreshold=20
position=1 ' Begin OrderKey definition, no manual key.
length=8
duplicates=n
modifiable=n
type=string
alternate=n
segment=n ' End OrderKey definition.
position=1 ' Begin BillKey definition, segment 1,
' with manual key attribute.
length=8
duplicates=n
modifiable=n
type=string
alternate=n
manual=y ' Set manual key attribute.
' Must be in every key segment.
value=20 ' Define value of null key.
segment=y ' End BillKey segment 1 definition.
position=9 ' Begin BillKey definition, segment 2,
' with manual key attribute
length=1
duplicates=n
modifiable=n
type=string
alternate=n
manual=y ' Set manual key attribute
value=20 ' Define value of null key.
' Must be the same in every key segment.
segment=n ' End BillKey segment 2, BillKey,
' and OrderFile definition.
|
|---|
The syntax for creating a file using the description file in Listing 3 is:
BUTIL -CREATE order.dat order.des
Traversing this file via OrderKey will always give you every record in the file. Traversing it via BillKey will give you only those records for which BillFlag is not blank. Remember, your CDD programs, including those produced by AppGen, can use this feature, too, even though CDD doesn't support it.
Using Data-only Files and Supplemental Indices: The No Append/Build" Myth Debunked
Why?
A few months ago, an article in fnfo World rating several of the top database development systems gave Clarion very low marks in the area of batch file updates. This generated quite a lot of discussion in the Clarion community, especially in the Clarion Forum on CompuServe. Much of this discussion centered around the notion that Btrieve was the cause of this poor performance, since Btrieve manages its index tables dynamically and it is not possible to perform a two-stage Append/Build using Btrieve.
It is absolutely true that Btrieve manages its index tables dynamically. While it does this very efficiently, especially on a Novell server, it can't possibly do that as fast as if it did not have to manage the index tables at all, as is usually the case when you perform an Append in other data management systems. Everybody knows that you can't do that with Btrieve, right? Wrong.
How?
Listing 5 shows a program that performs a batch import from a text file to a Btrieve master file, using the Append/Build method. The secret to using the Append/Build method is to define the master file twice using the same external name, first as a Btrieve data-only file and second as the "real" file, with all keys defined. You perform the Append phase on the dataonly instance of the file, close it, then perform the Build.
Program
icd9text File,Pre(ict),Driver( 'Ascii', '/FILEBUFFERS=20' ),|
Name( 'icd.txt')
Record Record
ID String(5)
Description String(50)
. .
!
! This is the data-only definition of the master file
|
|---|
Listing 6 shows the Butil description files necessary to build the two supplemental indices. You will notice that I have chosen to do a "Runsmall" on Butil to build the indices. The reason is because at the time of writing this article, the CDD Build() command using the Btrieve driver had some problems. You should be able to use one "Build(icdfiI2)" instead of the two invocations of Butil, but as of the writing of this article, the command "Build(icdfiI2)" simply results in turning the drive light on until you get tired of watching it.
Index1.des: position=1 length=5 duplicates=no modifiable=yes type=string alternate=no segment=no Index2.des: position=56 length=10 duplicates=no modifiable=yes type=string alternate=no segment=yes position=1 length=5 duplicates=no modifiable=yes type=string alternate=no segment=no |
|---|
Now, what about performance? Here are the results of my testing of the program in Listing 5:
| Number of records in import file "icd.txt" | 4,653 |
| Time to import and append ali records | 28 seconds |
| Time to build both indices | 1 minute, 16 seconds |
| Total time to import | 1 minute, 44 seconds |
| Rate of import (Append & Build) | 8,454 records per minute |
The test machine is a 33 mhz 486 with a 8-millisecond drive, running in straight DOS.
For performance comparison purposes, I also created a similar program which uses the "normal" one-step impon method, using the full file definition ("icdfile" in Listing 5). Using the same impon file, the total time to import 14,653 records was two minutes, four seconds. This means that using the two-step Append/Build method shown by Listing 5 can result in over a 17%, performance improve- ment over the one-step method. And that's including the overhead of externally running Butil twice to build the indices. When Clarion fixes the Build() function, the performance improvement should be even greater.
The fact that Clarion doesn't currently directly suppon the features that I have discussed in this article is nor a shortcoming of Clarion. Rather, it vindicates Clarion's decision to support Btrieve with Clarion Database Developer, because when you use these Btrieve features, you are extending the capabilities of CDD. This takes some of the pressurc off Clarion to provide every feature that we developers might need. There are, of course, still some Btrieve features that we need that do require Clarion's support in order to make them available to us, such as the Extended Get and Extended Put. There are more features, though, like the examples I have shown, that you can use now, and I hope I have given you some ideas for how you can use Btrieve to extend CDD in your own way.
