| Prev | Advanced Operations Guide | Next |
Building a Table Definition
The table definition you have created is "empty"-it does not contain any information about the structure of the data file. You must now populate the table definition with specific information about the record structure of the data file. The Create Table Wizard guides you through this task by detecting certain structures within the data file, and helping you define the remaining field characteristics.
This section uses the Btrieve file PERSON.MKD as an example. This file is provided in the product as part of the "demodata" sample database.
Determining background information
Records in a Btrieve file can consist of any mixture of fixed-length and variable-length fields. In the physical file on disk, all the fixed-length fields and the pointer values for the variable-length fields are stored together. This is called the fixed-length portion of the record. The data contained in the variable-length fields is stored at the end of the fixed-length portion of the record, as a single large block of data.
You can determine whether the data file has at least one variable-length field by obtaining the file statistics on the Btrieve file using the Btrieve Maintenance Utility. Pervasive Software offers both a command-line and graphical version of the Btrieve Maintenance Utility. For the examples in this section, we chose to use the command-line Btrieve Maintenance Utility (BUTIL).
The following is an excerpt of the output produced by running the command BUTIL -stat on the PERSON.MKD file.
Total Number of Records = 1500 Record Length = 333 Data Compression = No Variable Records = Yes Variable-Tail Allocation Tables = No Blank Truncation = No Free Space Threshold = 5% ...From this output, we can determine that at least one variable-length field is defined. From the Record Length specified, we can also determine the size of the fixed length portion of the record. You will use this information later.
Naming Known Fields
Btrieve files contain some information about field lengths and data types, indexes, and overall record length that must match the corresponding table definition. The Create Table Wizard analyzes this information to come up with definitions of the indexed fields in the file, as shown in Figure 13-3.
- Continuing the Create Table Wizard steps from the previous section, the next window displays the columns and indexes that the wizard was able to discover in the file. See Figure 13-3 Create Table Wizard-Columns and Indexes Detected.
Note
In most cases, not all columns are displayed. The wizard can only detect columns that have indexes defined on them. This is the only method the wizard can use to gain information about the internal record structure of the file. As explained in the following steps, you must supply most of the information about the record structure.
Figure 13-3 Create Table Wizard-Columns and Indexes Detected
Name the columns appropriately according to your knowledge of the record structure. The maximum length for a column name is 20 characters. Click Next when you are finished.
If you are inspecting a Btrieve file that was created with Pervasive.SQL 2000 or later, each column that is allowed to have NULL values is preceded by a one-byte space that contains a NULL indicator value. You should not explicitly define these spaces as columns, however, you must allow for this extra byte when specifying the column offset. You should count this space as part of the column.
If you use Btrieve Maintenance Utility to display field data about a Btrieve file, you must mentally subtract one (1) from the position values shown in Btrieve Maintenance Utility output to generate comparable offset values, as shown in the Create Table Wizard. This is because byte position one (1) in the record, as shown in Btrieve Maintenance Utility, corresponds to an offset of zero (0), as shown in the Create Table Wizard.
Caution If there are any field overlaps in your indexed columns, you cannot create a valid table definition for your Btrieve file without modifying the file. For example, in the sample definition, you would have a field overlap if there were an indexed field at offset 5 with length of 1 or more. This would overlap the bytes in the ID field, and possibly the First_Name field as well.
If you have overlapping field definitions and you want to have relational access to the data, then you must:
(1) remove any indexes from your Btrieve file that refer to overlapping fields, and
(2) modify your Btrieve application program logic to reflect the newly changed index definitions.Typically you must separate the overlapping fields into multiple fields that do not overlap, and define an index (or segmented index) on these fields.
Defining Remaining Columns
To define the non-indexed columns
- The next screen is shown in Figure 13-4 Create Table Wizard-Specify Columns. This window shows you both the indexed columns as well as the gaps in the record structure that exist between the known columns. These gaps are named "unnamed_X" and their data type is currently "unknown."
Figure 13-4 Create Table Wizard-Specify Columns
The task now is to define the structure of the data in these undefined areas. Each area may contain one or more fields.
- In order to determine how to define these areas, you can inspect the actual contents of an undefined area by selecting the area and clicking the Split button:
Figure 13-5 Create Table Wizard-Split Columns
- The next window to appear is the Split Column window. This window allows you to view the data in an undefined area and determine where it should be split into separate fields.
By default, the window shows hex codes for each byte of data in several rows of the data file. Click Ascii view to switch to a view that shows the hex codes as ASCII characters.
- Visually inspect the data in the Split Column window to determine where fields begin and end.
- Click in zero or more Split At checkboxes to specify where the additional columns should begin. Keep in mind it is possible that the space is all one field, so you may not click any checkboxes. See Figure 13-5 for an example. The first field appears to be zip code information, while the next field appears to be country information.
If you know that you are defining columns that are nullable, split the columns by checking the box immediately prior to where the data starts. In the example shown, box 13 is checked because it is the null indicator position for the nullable "Country" information that begins in the next byte.
Later, when you go back to the main window, remember to check the Null box for this field. Doing so creates the null indicator byte corresponding to box 13, and specifies that the actual data starts at the next position, as is correct.
Click OK when you are ready to execute the split and go back to the main window.
- You may need to visit the Split Column window several times. You can also use the Merge button
to undo a split, if you split a column in the wrong place. You merge two or more adjacent columns into a single field by selecting the ones you want to merge and clicking the Merge button.
- Return to the main window and specify different data types if necessary, to achieve the desired display of column data in the main window.
Remember, nullable fields have a one-byte null indicator at the beginning of the field. When you click Null to make a field nullable, the field's start position increases by one (allowing for the null indicator byte) and the field's length decreases by one byte (because the next field must still begin at the appropriate location). If you find that you have a number of fields that are off by one byte, check that you have the Null attribute set correctly for all the columns in question.
Non-character columns may be difficult to interpret accurately. Columns of type BIT and LOGICAL may require a detailed knowledge of the record structure to define them properly.
If you create a column of type BIT, eight columns are created automatically, one for each bit in the byte selected. If you do not have eight bit columns to be defined, simply select the extra columns and click the Delete button:
If you are working through the fields sequentially from front to back and you reach a point where the Wizard won't allow you to define the columns correctly, you can try a couple other approaches:
1) Work through the remaining fields from back to front.
2) Define all the non-indexed fields in a gap area with a single use of the Split Column window. Do not define a few of the columns, return to the main window, then split again.
When you are satisfied that your entire column structure is correct, click Next in the main window.
Variable length fields
Variable-length fields require special mention. These must be specified as a
blobdata type, with a size of 8 bytes. The 8 bytes are actually a pointer value into an offset location in the variable-length portion of the record that comes after the fixed-length columns.Summary of Sample
The table below summarizes the full contents of the PERSON.MKD example file, as deduced by the Create Table Wizard:
Generating a Table Definition
This section explains how to complete the task of creating a table definition, once you have finished creating definitions for all the columns in your Btrieve file.
- After you complete all the field definitions and click Next, a new screen appears listing the SQL statement that creates the table structure you have defined. See Figure 13-6. Click Save... to save the SQL statement to a text file.
Figure 13-6 Create Table Wizard-Save SQL Script
It is recommended that you save the statement in case you need to reconstruct the table definition later, or in case you want to make minor changes to the table definition and then re-create it.
Figure 13-7 Create Table Wizard-Finish
- In the next screen, you must confirm whether you want the table definition to be created, as shown in Figure 13-7. Click Finish.
Verifying a Table Definition
You should verify the table definition after generating it. The verification process performs two tasks:
- Verifies that the index definitions in the table match the index definitions in the Btrieve file.
- Verifies that the field definitions match what you expect for your data.
In PCC, right-click the database to which you have just added the table definition, then choose Tasks4Check Database. The Check Database Wizard guides you through the steps to check your database definitions.
Conclusion
As you can see, creating a table definition for an existing Btrieve file can be a time-consuming, highly detailed task. However, when the task is complete, the result is a Btrieve file that can be accessed concurrently by both ODBC-based applications and Btrieve applications.
| Prev Associating a Data File with a Database |
Contents Up Check for Revisions | Next Manipulating Btrieve Data Files with Maintenance |