Lesson 3 – Invalid Data Types and Sizes
Scenario
In this lesson, you have a Btrieve file with incorrect data types and sizes. As a result of this, the data does not format in a way that is understandable.
Goals
The goal is to open the file’s existing table definitions with DDF Builder. You will inspect all the data types and sizes using the Btrieve Types view and make the modifications needed so that the data is formatted using the correct data type for the field size.
What You Need to Know
For this lesson, use the file named Type_Size.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.
1
In the DDF Builder expand the Data Sources Explorer nodes, and locate the Tutorial2 database in the list.
2
Double-click the Tutorial2 database icon to expand the nodes.
3
Expand the nodes until the Type_Size.MKD file and the associated SQL table display.
4
Double-click the type_size 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.
5
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 review the inconsistencies with which you are dealing. Specifically, look at the grid data view and the Definition Errors window.
The grid data view of the Table Definition Editor shows your existing table definition with the modifications that DDF Builder made.
*Note: DDF Builder changes are not automatically saved. Any modifications made by DDF Builder must be saved.
In this example, DDF Builder gives visual indications as to the columns that need attention by adding the unknown column indicator to the fields that are new or that DDF Builder changed to an unknown type.
*Tip: For more information on the attributes in the grid data view, refer to Field Attributes in Grid Data View.
A list of the issues DDF Builder detected and changed display in the Definition Errors view.
The original table definition, before DDF Builder made any changes, is available from the Original Definition view.
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 three problems:.
Where the problem is located...
The problem DDF Builder found...
What needs to be done now...
Column: unnamed_6
DDFBLDR-6: Undefined bytes in table definition. Unknown column added.
You must define the Unknown column that DDF Builder added to account for the undefined bytes.
Column: Age
DDFBLDR-11: SQL column type is not compatible with the column size. Column type changed to Unknown.
You must define the column type that DDF Builder changed to Unknown.
Column: dob
DDFBLDR-11: SQL column type is not compatible with the column size. Column type changed to Unknown.
You must define the column type that DDF Builder changed to Unknown.
The first error shows that the file contains a group of bytes that were undefined in the original table definition. DDF Builder created a new column from these undefined bytes and named the column unnamed_6.
The file also contains two fields (Age and dob) where the column data type was invalid for the column size. In both cases, the type for these fields has been changed to Unknown. Before you look at the Age column in depth, first review what DDF Builder can and cannot determine.
Invalid Type and Size
DDF Builder cannot confirm the size of a field based on the specified data type, but can only verify that the types available are valid for the defined size. DDF Builder can detect the problems found, make generic changes, and record the changes made, but it cannot determine if the type selected is correct.
DDF Builder limits the data types from which you can select, based on the field size. So when looking at the first invalid type and size in the Age column, for example, the size is four bytes. The Btrieve Type drop-down list in the grid data view is only populated with data types that can have a four byte size. Instead of trying each of the data types DDF Builder lets you select from, look at the data in the Btrieve Types view and the Preview page to determine an appropriate data type.
Next review the data types that can allow four bytes and preview the data in the file to see which data type is the most suitable.
Review Data Types and Sizes
To get a close look at the data with the possible data types for the field size, use the Btrieve Types view on the left side of the Table Definition Editor.
1
With the Table Definition Editor open, select the Age column so that the row is highlighted.
*Tip: Selecting the Age column in the table definition allows you to see specific data in the Btrieve Types tab, as discussed next.
2
Now, click the Btrieve Types tab on the left side of the DDF Builder interface. The Btrieve Types tab view should look similar to the following:
*Tip: For more information about the Btrieve Types view, see Btrieve Types.
3
Carefully review the data as it displays in the Preview column of the Btrieve Types view.
The Btrieve Types view only shows data types that can be four bytes in size. This is extremely helpful because it allows you to filter out all other data types that are invalid.
Knowing that the column represents an age and is four bytes in size, you can see that only three possible data types can work with the data.
Next, you can eliminate Auto Increment as a possible data type, since you know that using the Auto Increment data type automatically increases each new record by a value of one. After all, nobody likes adding a year to their age!
By a process of elimination you are now down to two possible data types from the Btrieve Types list—Integer and Unsigned Binary.
The Integer and Unsigned Binary data types are very similar in nature. Try each of these data types and see how the data gets interpreted. First, select Integer as the Btrieve data type for the Age column.
4
Select Integer from the Btrieve Type drop-down list.
You can see in the grid data view’s Preview column, that the data looks acceptable. Take a moment though, and look at all the data in the file via the Preview page.
5
Click the Preview page in the Table Definition Editor. You can see all of the data in the file in a readable layout.
Looking at all the data in the file helps to reconcile the invalid data type with more confidence, since you can quickly look at all the records in the file.
You can see that using the Integer data type, the data is formatted in what appears to be a suitable manner. Be sure though, and run the same review using the Unsigned Binary data type.
6
Select Unsigned Binary from the Btrieve Type drop-down list.
Again, the grid data view’s Preview column shows the data in an acceptable format. Be certain though, and look at all the data in the file via the Preview page, as you did when you used the Integer data type.
7
Click the Preview page in the Table Definition Editor to see all of the data in the file in a readable layout.
Both the Integer and the Unsigned Binary data types format the data in a manner that is acceptable and understandable, and both these data types support 1, 2, 4, and 8 bytes. So it seems there is no compelling reason to select one data type over another. This example clearly shows that you must know the basic and underlying structure of your data if you are creating or modifying table definitions.
Make the Final Changes
Now that you have reviewed the possible data types and the formatted data, select the Integer data type for the Age column.
This resolves the first table definition error. You cannot save the table definition though, until you resolve the other errors.
*Tip: You cannot save your table definitions until you resolve all Unknown types and account for every byte in the record.
The next two fields are side by side, and you should look at them collectively. To resolve the two remaining table definition errors, you need to consider them as a single error.
First, you know that DDF Builder noted that the dob field had a defined data type (Date) that is invalid for the column’s size of two bytes. As a result, DDF Builder changed the data type to Unknown.
Secondly, there were two bytes that were undefined in the original table definition. DDF Builder created a new column (unnamed_6) from these two bytes and gave them an Unknown type.
The intended data type for the dob field is Date, and the Date data type requires a field of four bytes. By merging both the dob and the unnamed_6 columns, you can create a four-byte field that is appropriate for the Date data type. You need to verify though, as you did with the previous error, that the data is understandable and acceptable using the Date data type and with the two columns merged.
1
In the Table Definition Editor, select both the dob column and the unnamed_6 column (use the Shift key).
2
Right-click and select Merge Columns.
The two columns are merged into one, making a four-byte column.
3
Select Date from the Btrieve Type drop-down list.
Once you enter this information and tab through the rest of the column fields, notice that adding the Date data type formatted the data appropriately in the visible record.
It is a good idea to check the Preview page to see how all the data is handled with the Date data type.
4
Click the Preview tab at the bottom of the grid data view.
Looking at the dob column in the Preview page, you can see that all of the data is formatted in an acceptable and understandable format.
Save the Table Definition
Now that you have completed the table definition, you must save your work for the changes to take effect. Before you save the work, take one final look at the table definitions. Your data grid view should look similar to the following:
You can take a quick glance and see that all of the Btrieve Types have been defined, you have no undefined fields remaining and every byte is accounted for. You can now save your table definition.
1
From the menu bar, click FileSave.
Conclusion
This lesson introduced you to how DDF Builder handles data types and sizes that are not compatible with each other. It showed you how invalid data types and sizes might be displayed within an existing table definition and provided you with some sample solutions for modifying your table definitions so that the sizes and data types are compatible and information displays properly and accurately.