Tutorial 1 – Creating Table Definitions with DDF Builder

In this tutorial you have a Btrieve data file for which you need to provide ODBC access to create reports.

To do this, a table definition needs to be created for the file. Table definitions are stored in DDF files of the database. To your knowledge, there are no table definitions for this file, so you are starting from scratch.

Goals

The goal of this tutorial is to walk you through the steps necessary to provide relational access to a Btrieve file without any table definitions. To achieve the goal for this tutorial, you perform the following tasks using DDF Builder:

Create a PSQL database

Open an Existing Btrieve file

Review DDF Builder’s Findings

Define the Record Fields

Save Your Table Definitions

Review the Index Information

Preview the Defined Data

Tip: As you step through creating table definitions, you learn fundamental tasks for working with DDF Builder. This tutorial is designed to call out those tasks within these instructions so that you can become familiar with the common tasks for using DDF Builder.

What You Need to Know

Any time you work with DDF Builder to create or modify table definitions, you need some knowledge as to the structure of the data. Some of this information is stored in the Btrieve file itself, such as record length and index information. The column information, however, is not stored in the Btrieve file. The column information may have been provided by the software vendor or the application developer. You can still attempt to create or modify table definitions by careful inspection of the data, but you should not use this utility without significant knowledge about the structure of your data.

For this tutorial, you know the data structure. Take a look at the structure for this file.

There is a Btrieve data file named CREATE_NEW.MKD. This file has a record length of 110 bytes and contains six fields. The fields are defined as follows:

Table 8

Data Structure for Creating DDFs

The file contains a nonduplicatable key (unique index) set on the ID field; the index is named indx_id.

Now that you understand the data structure, you can get started. Begin by creating an empty database using DDF Builder, which provides a set of empty DDFs to define for the Btrieve file.

Create a PSQL Database

Before you can create DDFs for an existing Btrieve file, you must first create a database. Begin by starting DDF Builder if it is not already running. See Starting DDF Builder.

►

To create a database

In the Data Sources Explorer, expand the tree for the machine where you are creating the database. This should be the same machine where DDF Builder is installed.

Right-click the Databases icon and click Add Database.

The New Database Wizard displays for you to create a new database.

Complete the New Database Wizard using the following parameters:

•

Database Name: Tutorial1

•

Location:

<Application Data>\DDFBUILDER\TUTORIALS\TUTORIAL1\

•

Bound: not checked

•

Create dictionary files: checked

•

Relational integrity enforced: checked

•

Long Metadata (V2 Metadata): checked

•

Database code page: Server Default

•

Create 32-bit Engine DSN: checked

The New Database dialog should look similar to the following:

Tip: Creating the database in these steps also creates a Data Source Name (DSN). When you have finished working with the tutorials, you may want to delete the DSN and database created here.

Click Finish to create the database and the empty DDF files in the location you specified.

Once the database is created, it displays as a new node on the databases tree in the Data Sources Explorer.

The database node includes the Data Paths, SQL Tables, and System Objects folders. The Data Paths folder contains the locations of your Btrieve files. The SQL Tables folder contains any relational tables for the database, and the System Objects folder is the holder for your system dictionary tables, and in this case, empty data dictionary files.

Creating a PSQL database using these parameters was the first step in creating table definitions for the Btrieve file. The database you just created contains dictionary files (DDFs), but these files currently contain only definitions for the structure of the DDFs themselves. Once you open the Btrieve file in DDF Builder, you can add a table definition to the DDFs created for this database so that the data in the Btrieve file can be accessed.

Notice that the database contains no SQL Tables—only the CREATE_NEW.MKD Btrieve file used for this tutorial. That is because there are no user tables defined. The empty DDFs are located in the System Objects folder, separate from the data files. As you create table definitions, you also will create corresponding SQL tables that will access the table definitions. Continue with opening the Btrieve file in DDF Builder.

Open an Existing Btrieve File

Now that you have created DDF files in the same location as the Btrieve file, open the file in DDF Builder and see how the utility deciphers the data structure.

►

To open the Btrieve file

In DDF Builder, select the local machine and the database you just created.

The Data Sources Explorer lists the Data Path and shows the directory location where the mkd file was installed. This is the same location where you created the database. Your Tutorial1 database directory structure in the Data Sources Explorer should look similar to the following:

Right-click the CREATE_NEW.MKD folder containing the file, and select Create Table Definition.

When you select to create the table definition, DDF Builder displays the New Table Definition dialog for you to associate a new table name with this set of table definitions. Think of this table as your corresponding relational table to your transactional file. When you have finished creating your table definitions, this SQL table should mirror the same structure as your original Btrieve file.

Enter CREATE_NEW as your Table name and click OK.

Once you provide a name for the table and click OK, DDF Builder begins to analyze the Btrieve file so that it can be opened. DDF Builder determines any known keys or indexes in the file and displays them as such inside the Table Definition Editor. Your display of the Table Definition Editor should look similar to the following:

Now that you have successfully opened the file, look at what DDF Builder found in more detail.

Review DDF Builder’s Findings

DDF Builder analyzes the data based on the file’s general statistics and the known keys or indexes in the file. In the file, DDF Builder detected a key in the first four bytes of the record and determined that the key is made up of an integer Btrieve data type. The key is illustrated in the Table Definition Editor with the key icon in the first column of the display.

The remaining parts of the record cannot be deciphered by DDF Builder, so they are grouped together and illustrated in the Table Definition Editor with the question mark icon and assigned a Btrieve type of Unknown.

Creating the table definitions for the Btrieve file involves splitting the unknown fields found by DDF Builder into the various fields you need—defining the particulars of each field as you go along.

Before you begin defining the record fields, you should make sure you understand how nulls are treated in the Table Definition Editor.

Note: This tutorial uses true nulls, but it is important to note that much older versions of PSQL did not provide support for true nulls—only legacy nulls. True nulls were first introduced in Pervasive.SQL 2000. If you are working with files created prior to Pervasive.SQL 2000, you may want to skip the following section on nulls.

A Note About Nulls

When you define record fields in this tutorial, it is important to first make note if the field is nullable, since marking a section of the record as nullable adds an extra byte to the field. This is because in a Btrieve file, nullable portions of the record are designated by the use of the null indicator byte. In the raw data view of the Table Definition Editor, the null indicator byte is indicated in the byte immediately preceding the field or column. When the Null check box is selected, the null indicator byte becomes active and the size of the field is automatically reduced by one byte to accommodate for the null indicator byte.

Say you wanted to create a nullable field 50 bytes in size. You would select the field size as 51 bytes to accommodate for the null indicator byte. Once you select that the field is nullable, the size is automatically reduced to 50.

This information is helpful as you define the record fields.

Note: For more information on working with nulls in DDF Builder, see Legacy Nulls in DDF Builder and True Nulls in DDF Builder.

Define the Record Fields

As you saw when you opened the Btrieve file, DDF Builder determined the index of four bytes and left an unknown field of 106 bytes. You define the unknown field into record fields.

►

To define the record fields

Although DDF Builder determined most of the attributes for the first field, a field name could not be determined, so go ahead and name the first field.

Name a Field

In the grid data view, select the unnamed_0 row, and enter the values needed to reflect the following information:

•

•

•

•

•

Btrieve Type: Integer

•

•

•

•

DDF Builder determined a lot of the attributes for this row, so entering the field is the only change you will need to make. Be certain to verify all the values of the row.

Your screen should now reflect two rows in the grid data view – the key row you just named ID and the unknown _1 row found by DDF Builder.

Now you are ready to parse out and define the unknown data.

The next field to define is nullable. The following steps outline how to select data from the raw data view and create a nullable column from that data.

Create a Nullable Column in the raw data view

In the raw data view, beginning at offset 4, select 21 bytes of data.

Tip: To find offset 4, locate the question mark just above the first record in the raw data view, or position the cursor until the Offset indicator shows 4.

Right-click and select Create Nullable Column.

Tip: Creating a column, whether in the raw data view or the grid data view, automatically creates a new column out of the remaining bytes in the record.

You may notice that although you selected 21 bytes, the grid data view shows the size as 20. This is because of the null indicator byte mentioned earlier. The null indicator byte is shown in the raw data view as the § symbol and accounts for the extra byte.

Now the editor shows three fields—the four byte integer (ID), the nullable column you just created (unnamed_1), and the new unknown field comprised of the remaining bytes (unnamed_2).

Before you go on, be sure and complete the definition for the nullable column you just created.

Select the unnamed_1 row in the grid data view.

Enter the values needed to reflect the following information:

•

•

•

•

•

•

•

•

•

Tip: The number of bytes selected in the raw data view, along with creating the column as nullable, predetermined the size and null selections. The size also limits which data types you can select for the column.

Notice that the unknown field indicator is no longer displayed in the grid data view, once the Btrieve Type is set and the field is defined.

Continue defining the unknown fields in the file by splitting the unknown row into multiple columns within the grid data view.

Split a Column in the grid data view

Select the unnamed_2 row in the grid data view.

Right-click and select Split Column.

When you split the column, the result is two columns of equal, or near equal, size. This means that the 85 byte column is now two columns, one with 42 and the other with 43 bytes.

Select the unnamed_2 row.

Enter the values needed to reflect the following information:

•

•

•

•

•

•

•

•

•

When you change the size from 42 to 20, DDF Builder adds the extra 22 bytes to the unnamed_3 row.

You may have already noticed that the SQL Preview column displays the data as it is defined. This provides you a preview of how the data is interpreted by DDF Builder based on your table definitions.

Right-click and select Split Column to split the unnamed_3 row.

Splitting the column changed unnamed_3 to 32 bytes and created unnamed_4 with 33 bytes.

Now define the unnamed _3; this field is nullable.

Select the Null checkbox so that the null indicator byte is reserved at the first of the record.

Notice how selecting the Null checkbox automatically decremented the size to 31.

Select the unnamed_3 row.

Enter the values needed to reflect the following information:

•

•

•

•

•

•

•

•

•

Your table definition should now look similar to the following:

When you changed the size to 4, DDF Builder combined the remaining bytes with the next field.

Now, there is one unknown field left with a size of 60 bytes. The data structure indicates that two more fields need defining. Use the raw data view to create the column.

Create a Column from the raw data view

Starting at offset 50, select 51 bytes in the raw data view.

Tip: To find offset 50, locate the question mark just above the first record in the raw data view, or position the cursor until the Offset indicator shows 50.

Right-click and select Create Column.

You created a not null column, but really you need a null column instead. Change the non-nullable column to nullable now using the grid data view.

Change a Non-Nullable Column to Nullable in the grid data view

Select the unnamed_4 row, and select the Null checkbox.

Checking the Null checkbox reserves the null byte indicator and reduces the size to 50 bytes.

Enter the rest of the values needed to reflect the following field information:

•

•

•

•

•

•

•

Your table definitions should now look similar to the following:

Lastly, define the last field in the file.

Select unnamed_5 row in the grid data view, then make the following changes in this order:

•

Null: checked

•

Field: Income

•

Btrieve Type: Currency

By selecting the Null checkbox first, you automatically set the size correctly. If you had first changed the size to 8, DDF Builder would have created a new unknown column from the last remaining byte.

You have now finished defining the fields for your file. Your table definition should look like the following:

Before you proceed, be sure and save your table definition.

Select FileSave, or click the Save icon on the toolbar.

You have now successfully created your table definition in your DDFs. When you saved your work, SQL tables were created under the SQL Tables node of the Data Sources Explorer.

Now that you have created the table definitions, take a look at the index.

Review the Index Information

Now take a look at the indexes DDF Builder found in the file.

Click the Indexes tab in the Table Definition Editor.

DDF Builder can detect any index created in the file, but because Btrieve does not use index names, the index has no associated name in DDF Builder.

Although the Indexes page is primarily read-only, you can enter or alter the index name.

Name an Index

Select the index_0 entry in the Indexes column by double-clicking the field.

Enter indx_id as the name of the index from the data structure.

Before you proceed, be sure and save your index changes. Click FileSave to do so.

That is the extent of what DDF Builder allows you to do to Indexes.

You should always preview the data once you have completed your table definitions.

Preview the Defined Data

Next, take a moment to double-check the data in the file for which you have just created table definitions.

With DDF Builder still open, click the Preview tab in the Table Definition Editor.

Your Preview page should look like the following:

The Preview page offers you a look at how the data in the file is formatted using the table definitions you just created. As you can see, all of the data appears reasonable for the column names and the type of data that should display.

The buttons at the bottom of this page allow you to move through the data in the file so that you can verify all of the records, or perhaps find data in an unexpected format that you may need to deal with to complete your table definitions.

If you find that some of your data does not display as you had expected, see next section, Tutorial 2 – Modifying Table Definitions with DDF Builder.

If you make any changes to your already saved table definitions, be sure and save your work before closing DDF Builder.

Conclusion

Congratulations! You have completed Tutorial 1 and now have a set of DDFs that allow you to access the data in your Btrieve file using relational access.

The next tutorial deals with making modifications to a set of already created DDFs for a Btrieve file. The next tutorial also discusses some of the problems with DDFs that DDF Builder automatically detects.

Tip: Remember, this tutorial created a new database and a DSN. If you need to remove these from your system, you may want to do that now.