Using BTR2SQL’s PERMANENT_INT option with B_CREATE

Background

The Btrieve API B_CREATE offers a very crude definition of a file – simply the record length and optionally indexes on portions of the data. There are no names given to the fields that are effectively defined by the indexes, and the remainder of the record buffer is undefined (more indexes can be defined later using B_BUILDINDEX). This does not translate well into an SQL schema definition that is usable by other sources.

During migration, Mertech’s BTR2SQL migration utility obtains the proper definition of the file from the DDFs. Field names and data types are translated directly to an SQL schema and are saved in an INT file.

Re-creating or Truncating a File at Runtime

For many years, Btrieve did not offer a truncate function, so programmers resorted to re-creating files on the fly using B_CREATE. Since the Mertech drivers do not have access to the DDFs at runtime, a call to B_CREATE does not create the precise column names and data types that are generated during migration.

The Solution - PERMANENT_INT Token

The Mertech driver solves this runtime schema issue by reading an existing INT file when B_CREATE is called. If the INT file appears to be compatible with the definition in the B_CREATE call, the driver uses the INT file and the SQL table looks like it did after migration. If the B_CREATE call does not provide all of the indexes (added later using B_BUILDINDEX), the driver thinks the INT file is incompatible and ignores it, so most of the record is undefined and column names are generic.

To handle this situation, Mertech has included a PERMANENT_INT token in the INT file. Setting the PERMANENT_INT token to YES (the default setting is NO) prevents the driver from overwriting the INT file during a B_CREATE call.

Alternative Solution – Truncate API

If the purpose of the B_CREATE call is to remove all of the records in the file without deleting the table structure, then B_TRUNCATE_FILE can be used  instead. B_TRUNCATE_FILE is faster than B_CREATE as it calls the native SQL functions (DELETE * or TRUNCATE). Additionally, with B_TRUNCATE_FILE there is no time period where a table is non-existent on the server, which can cause problems in multi-user environment. This Mertech API is documented in the SDK.

Summary

There are two options for removing all records in a BTRIEVE file without deleting the table structure:

• Set the INT file PERMANENT_INT token to YES then call B_CREATE.
Note: Powerflex ZEROFILE works by calling B_CREATE.  Setting PERMANENT_INT to YES ensures that a call to ZEROFILE preserves the table structure.

• Use Mertech’s B_TRUNCATE_FILE API.