Lesson 4 – Overlapping Column Definitions

In this lesson you have an existing table definition that contains two column definitions that share some of the same bytes in the file. This creates an overlapping column definition that must be corrected.

Goals

The goal is to open the file with DDF Builder and see if DDF Builder makes any updates to the definitions. You will inspect any changes that DDF Builder implements and discuss changes needed to fix the overlapping column definition.

Note: DDF Builder recommends solutions to fix the overlapping column definitions in your file and allows you to save those changes.

What You Need to Know

For this lesson, use the file named OVERLAP.MKD. This file resides in a folder named Tutorial2. Assuming you installed using the default installation locations, this folder is located at:

<Application Data>\DDFBUILDER\TUTORIALS\TUTORIAL2

This folder is part of the Tutorial2 database.

Note: You must have a Data Source Name (DSN) that points to this database in order to access the data in this tutorial. If you have not yet created this DSN, refer to Create Data Source Names (DSN).

Open the Btrieve File

You should have DDF Builder already running from the last lesson. If not, begin by starting DDF Builder.

In the DDF Builder expand the Data Sources Explorer nodes, and locate the Tutorial2 database in the list.

Double-click the Tutorial2 database icon to expand the nodes.

Expand the nodes until the OVERLAP.MKD file and the associated SQL table display.

Double-click the overlap SQL table, or right-click and select Edit Table Definition.

The Table Definition Editor opens and displays the following message:

This message tells you that DDF Builder has analyzed your existing table definitions and found problems with those definitions. As a result of this, DDF Builder had to make some modifications to open and display the existing table definitions.

Click OK to clear the message and display the table definition.

Tip: For a complete list of possible definition errors, refer to Table Definition Errors.

Look for Inconsistencies

Begin by looking at the DDF Builder interface to see the differences between the original definition and the modifications that DDF Builder made, as well as reviewing the errors reported.

The Table Definition Editor’s grid data view shows the existing table definition with the modifications made by DDF Builder.

No unknown icons or other visual indicators bring to your attention the fields that are altered or added.

The Definition Errors window displays the issues DDF Builder detected and changed.

The original table definition, before DDF Builder made any changes, is always available from the Original Definition view.

Note: DDF Builder changes are not automatically saved. Any modifications made by DDF Builder must be saved.

In a moment compare the original definition to the modified definition, but first take a look at the error DDF Builder reported.

Understanding the Errors

The Definition Errors view tells the following:

•

where the problems are located

•

what problems DDF Builder found

•

what changes, if any, DDF Builder made to the original table definition

The Definition Errors lists one problem; look at that problem a bit more in-depth.


Where the problem is located...	The problem DDF Builder found...	What needs to be done now...
Column: firstName	DDFBLDR-4: SQL column overlaps another column. The column’s size has been truncated.	The column that DDF Builder named (firstName) overlaps another column. To eliminate the overlapping columns and make certain that both the table definition and the Btrieve file have the same record length, DDF Builder shortened the column size.

The error DDF Builder reported is an overlapping column definition. This means the table definition contains definitions for two columns that share some of the same bytes.

DDF Builder automatically reduced the size of the firstName column so that it no longer overlaps with the next column. Now the column lengths match and no bytes are defined across multiple columns.

You can see the overlapping columns by comparing the grid data view with the original definition.

Notice in the original definition the firstName field has a size of 30, compared to a size of 20 in the modified definition. The original definition shows both a first name (Joe) and last name (Smith) in the Preview column. The modified definition, with the smaller size, only shows the first name in the Preview column, as you would expect.

Accept or Reject Changes

Making comparisons between the original definition and the definition that DDF Builder modifies, along with verifying how the data is formatted, helps you determine whether or not to accept the changes made by DDF Builder.

As a result of the changes that DDF Builder made, the data displays more in a manner that you would expect, and you can be assured that column lengths match.

Now that you have reviewed the differences in the files, understand the changes that DDF Builder made, and can verify the data displays properly, you are ready to accept the changes.

Save the Table Definition

Any time that changes are made to your table definitions, whether they are made by you or by DDF Builder, they must be saved in order for them to take effect. Before you save the table definition, it is a good idea to take one final look at the definition and the Preview tab to make certain that all fields are defined and every byte is accounted for.

Note: You cannot save a table definition with fields that contain a Btrieve Type of Unknown or that contain bytes that are unaccounted for.

You can see all of the Btrieve Types are defined and every byte in the record is accounted for. You can now save your table definition.

From the menu bar, click FileSave to save the table definition.

The table definition DDF Builder modified to fix the overlapping column definition is now saved as your current definition.

Conclusion

This lesson introduced you to how DDF Builder deals with overlapping column definitions and the kind of changes DDF Builder implements to resolve overlapping columns. It showed you ways to compare the definition modified by DDF Builder with the original definition to determine if changes should be accepted or rejected.