This chapter discusses the following topics:

This section contains conceptual information about working with DDF Builder. The following sections are included:

Before you begin working with DDF Builder, you should review the terminology used throughout the manual. Some of the terms used are specific to the relational database model, whereas some of the information is from the transactional database methodology. Because DDF Builder is designed to create relational table definitions from transactional data, the utility brings together these two models in one place. This information is useful because terminology used in the transactional model may be referred to differently in the relational model. The following table lists the terms used throughout this manual.

When using DDF Builder with security-enabled databases, it is recommended that you turn off all security prior to opening the files in DDF Builder. Failure to turn off security results in DDF Builder sending secure login and password requests each time a call is executed. This could become excessive and counterproductive, depending on the number of calls needed to create or modify the table definition.

DDF Builder is supported on PSQL v9 and later versions. DDFs created with Scalable SQL v4.00 and later are supported along with Btrieve data files v6.x and later.

If you have DDFs that were created with Scalable SQL v3.xx or earlier, you must convert your DDFs before you can open them with DDF Builder.

For step-by-step instructions, see Lesson 1 – Working with v3.00 DDFs.

DDF Builder supports version 6.x files but does not support files created prior to version 6.x. If you have Btrieve data files that were created with Btrieve v5.x or earlier, you must rebuild the files before you can open them with DDF Builder.

For step-by-step instructions, see Lesson 2 – Working with Pre v6.x File Formats.

When you open an existing table definition associated with a Btrieve file, DDF Builder goes through a set of comparisons and checks. From these comparisons and checks, DDF Builder may make certain adjustments to the table definition, if errors are detected. Any changes made to the table definition are recorded in the Definition Errors window.

So that you can understand the assessments and potential changes DDF Builder makes, take a moment to look at the steps DDF Builder goes through.

First, DDF Builder reviews the Btrieve file and examines the pertinent details, such as record length, indexes, index segments, and so forth. Next, DDF Builder opens the existing DDFs and looks at the information contained in the DDFs in comparison to the Btrieve file. These two sets of details are then systematically compared to each other for matches and mismatches.

DDF Builder uses the details found in the Btrieve file as the basis for detecting and fixing any errors or problems. The following examples help clarify.

You have a Btrieve file that contains a key. The table definition does not contain a corresponding index. Since DDF Builder cannot alter your Btrieve file, the key defined in the Btrieve file is carried over to your table definition as an index.

For information on dealing with this type of situation, refer to Lesson 6 – Index Inconsistencies.

Your Btrieve file contains records defined with a total length of 120 bytes, yet your table definition only defines a total of 100 bytes. Since DDF Builder cannot alter the Btrieve record, a column is added to the table definition for the 20 bytes unaccounted for in the definition.

For information on dealing with this type of situation, refer to Lesson 8 – Record Length Mismatch.

The Btrieve file contains a flag on a key that does not match the flags set on the corresponding SQL index in the table definition. Again, because DDF Builder cannot change the Btrieve file, the flag setting on the SQL index in the table definition is changed to reflect the setting in the Btrieve file.

For information on dealing with this type of situation, refer to Lesson 5 – File/Field Flag Inconsistencies.

DDF Builder cannot repair every problem. For a complete list of the issues that DDF Builder detects and repairs, see Table Definition Errors.

Although DDF Builder makes recommended changes to your existing table definitions, you are not required to keep the changes, nor are you required to discard your original table definition if you accept the changes.

You can save both your modified and original table definitions by saving one of the definitions with a different name. If you accept the changes made by DDF Builder, you may save either the modified or the original table definition with a different name.

If you reject the changes made by DDF Builder, your original table definition is retained with the original name.

The Table Definition Editor is made up of several pages of information that you use for viewing, creating, and modifying table definitions. The Table Definition Editor contains the following pages:

The Table page is where you do most of the work when creating or modifying table definitions. The Table page includes the raw data view and the grid data view.

The raw data view displays data in your Btrieve file in a combined ASCII and hexadecimal view. Record length, offsets, and field sizes are also shown in this view.

The raw data view provides you with the visual indicators you may need to determine columns and null indicators, as well as identify unknown fields and bytes.

The grid data view is similar in feature and functionality to the grid window view in the SQL Editor in Zen Control Center.

Fields used in indexes cannot have their size or type modified; only the field name can be modified in the grid data view. To add indexes, drop indexes, or change other index information, you would need to modify the Btrieve file directly using the Btrieve Maintenance Utility.

The Indexes page allows you to see the indexes and index segments that DDF Builder detected in the Btrieve file. You may not add or modify any indexes or index segments from this page. Only index name changes are allowed on the Indexes page. If you change the name of an indexed field on the Table page, the name is updated in real-time on the Indexes page.

When working with Btrieve files that use alternate collating sequence (ACS) files, the ACS file must reside in the same directory as the Btrieve file and must have an .alt extension, as in upper.alt.

The Preview page provides a look at file data formatted using the current table definition. As you make changes to the table definition, the preview changes accordingly.

The Preview page is read-only. You may not edit any of the information displayed on this page. Although this page is read-only, you may move through the records by using the arrow buttons located at the bottom of the page.

The Statistics page displays the Btrieve file statistics for any file open in the Table Definition Editor. The information presented on this page would be the same information reported if a statistics report were generated on the file using the Btrieve Maintenance utility.

The SQL View page displays the underlying SQL statements that apply to the creation or modification of your table definitions. As you modify your table definition, the underlying SQL statements are updated immediately on the SQL View page.

The information on this page is read-only and cannot be altered on this page. You may select and copy the statements to use elsewhere, if needed.

Versions of Zen prior to Pervasive.SQL 2000 only provided support for legacy nulls. Files that use legacy nulls are recognized by DDF Builder, but no action is required to deal with this type of null when working with DDF Builder.

In Pervasive.SQL 2000, support was added for true nulls. With true nulls, a leading byte, known as a null indicator byte, is included to designate the corresponding column as having a null or non-null value

When defining record fields within DDF Builder, it is important to know the fields in the record that are 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 as such 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 that byte.

Creating nullable fields in DDF Builder requires that you consider the size of the field and the one byte needed for the null indicator. This means that a nullable field intended to be 25 bytes would actually be defined as 26, reserving one byte for the null indicator. Once you designate a field as nullable, the size is automatically reduced in DDF Builder by one.

DDF Builder provides a graphical user interface (GUI). The GUI includes various editors, views, and wizards with which you display and work with objects.

The object being edited is represented by a tab on top of the editor. The tab contains the name of the object. Data modified within an editor must be explicitly saved.

Views can be opened only one at a time. Actions performed within a view are applied immediately. No explicit save is required.

Wizards contain one or more dialogs and guide you through a task to obtain a specific result.

The following table lists the editors, views, and wizards that DDF Builder provides.

When you start DDF Builder, it displays the Welcome tab, where you can access a variety of information and perform common tasks, such as adding a new server and creating a new database. The information provided includes the following:

If Welcome tab is closed, you can open it in the DDF Builder menu by selecting Help > Welcome.

DDF Builder uses a file explorer, a tree of objects called the Data Sources Explorer. The objects include databases, data paths, Btrieve files, and SQL tables. The objects are referred to as nodes in the tree.

Nodes can be expanded or collapsed to reveal or conceal subordinate nodes. The expand/collapse icon appears to the left of the node if a lower node is available.

Btrieve File Editor creates the file and key specification for a new Btrieve file. DDFs are not automatically created for a new Btrieve file. You must add them separately, if you want them (see SQL Tables).

In Data Sources Explorer, right-click a data path under the Data Paths node and select Create Btrieve File.

A Btrieve file must be located on physical storage, which is why the editor is invoked from a data path.

Btrieve File Editor contains two tabs: one for specifying the characteristics of the file and one for specifying characteristics of the keys. The Apply button on each tab saves the specifications for that tab. Click File > Save to save the editing changes for both tabs.

At a minimum, you must supply a file name and a record length to create a Btrieve file. You specify these on the File Specifications tab.

As stated in What You Need to Know, this book assumes that you thoroughly understand the transactional access method and relational concepts. This section does not attempt to explain the controls on the Btrieve File Editor tabs. The following table provides links to related documentation if you need to further your understanding of Btrieve files.

Only one Btrieve file can be created at a time. On the Key Specifications tab, click Apply after adding or changing each segment of a segmented key. This saves the changes to each segment before you create or edit the next segment.

The Table Definition Editor creates new or changes existing schemas for SQL metadata. Note that Table Definition Editor is resembles Table Editor in Zen Control Center (ZenCC) and is used in similar ways. For more information, see Table Editor.

Do one of the following in Data Sources Explorer:

The Table Definition Editor, like the Table Editor in Zen Control Center (ZenCC), uses tabs or pages to display different views of table definition information. Some of the pages contain read-only information, while others provide the work area in which you will create and modify your table definitions.

You cannot change the size, offset, or data type for a column if the column is used in a key in the Btrieve file. DDF Builder does not change nor permit changing the layout structure of an existing Btrieve file.

The Table page in Table Definition Editor provides two metadata views – raw and grid. The raw view displays information in the Btrieve file, while the grid view displays it as a SQL table.

Select the Table page by clicking the Table tab at the bottom of the Table Definition Editor.

The raw data view shows both the hexadecimal and ASCII values of data records. This view also displays the record length and the offset and size for selected bytes. Offset and size are adjusted as bytes are selected in this view.

Field, column, null and unknown indicators appear at the top of the raw data view. Field indicators show where each field within the record begins. Null indicators show where the null indicator byte is located. Unknown fields and bytes are also displayed in the raw data view.

The following table lists the attributes displayed in the raw data view:

The grid data view shows the schema structure of a table as a grid of rows and columns. Each field is represented in a row on the grid. Each row consists of cells that show the attributes for each field. Most of the attributes are editable and can be saved as changes to the schema.

The following lists the attributes displayed in the grid data view:

Selecting a cell in the grid data view displays the corresponding bytes for that field in the raw data view, just as selecting individual bytes in the raw data view, selects the corresponding elements in the grid data view.

If you try to select past the field definition, an error message appears, and both rows in the grid data view are selected. Both error and warning messages display in this are of the raw data view.

The Indexes tab is read-only, with the exception of changing the SQL index name. You cannot change the structure of any of the indexes on this tab. Index additions or changes to a SQL table must be made with Zen Control Center. See Table Editor in Zen User’s Guide.

Select the Indexes page by clicking the Indexes tab at the bottom of the Table Definition Editor.

Name changes made on the Tables page to columns specified as indexes are updated and shown immediately on the Indexes page.

Only the SQL index name may be edited from the Indexes page.

When working with Btrieve files that use alternate collating sequence (ACS) files, the ACS file must reside in the same directory as the Btrieve file and must have an .alt extension (for example, upper.alt).

The Preview page shows file data in a readable layout of columns and rows.

Select the Preview page by clicking the Preview tab at the bottom of the Table Definition Editor.

You navigate among data records with the buttons at the bottom center of the page. The file position appears to the right of these buttons. The file position shows how many records are being displayed out of the total number of records. For example, “100-199/1314” indicates that the page displays records 100 through 199 out of a total of 1,314 records.

Changes made on the Tables page to any of the column definitions are reflected immediately on the Preview page.

Information on this page is read-only and cannot be modified.

The Statistics page displays the file and key specifications for a Btrieve file. The information is read-only; you cannot change it in the view.

Select the Statistics page by clicking the Statistics tab at the bottom of the Table Definition Editor.

Statistics provides a convenient way to look at the structural characteristics of a Btrieve file. This is particularly useful if you are considering exporting the file schema but are unfamiliar with the file and key specifications.

See also Export Btrieve Schema.

The SQL View page displays the SQL statement needed to create the current table definition.

Select the SQL View page by clicking the SQL View tab at the bottom of the Table Definition Editor.

Most SQL statements created here can be copied and reused in the Zen Control Center for creating new schemas based on the ones you created with DDF Builder. SQL statements that you use in the Zen Control Center can also be saved for future use.

Although the SQL statements created here can be copied and reused, they are not saved, nor is a means for automatically saving them currently offered in DDF Builder. To save the statement, you must copy from this window and save the statement in a text editor.

The Add Database Wizard creates a new database. This wizard is shared with Zen Control Center (ZenCC). See Databases and New Database GUI Reference in Zen User’s Guide.

DDF Builder provides the ability to check the consistency of a table. A consistency check uses a set of validation rules to compare a physical data file against the metadata in its data dictionary files.

Consistency check validates conditions such as the following:

You can check the consistency of all tables at once or of tables selected individually. The check reports a count of the validation messages, errors, and warnings by object. An object is the database, a table, or a data dictionary file (DDF).

Validation messages grouped under the heading Passed list the consistency checks that were validated. Errors are grouped under Error and warnings under Warning.

Error messages are always displayed. If an object has both errors and warnings, the warnings are also listed under Error.

You can display or hide the validation messages and the warnings.

An error indicates a problem in the table definition that will, in most cases, cause a failure or incorrect data to be returned when the data file is accessed. For example, an index defined in the DDFs but not in the data file results in an error. A SQL query that causes the engine to optimize on that particular index generates a failure because no such index actually exists in the data file.

Warnings are indicative of possible problems, but the problem may not cause any failures. For example, an index defined in a data file without a corresponding DDF entry results in a warning. SQL access does not know about the index and will not try to use it. The result may be a slow query, but the query eventually returns the correct results.

In Data Sources Explorer, right-click the name of a database and select Check Database, or right-click a SQL table name under either the Data Paths or SQL Tables node, then select Check Tables. You can select multiple tables by holding down the Shift or Control key then clicking the desired table names.

You can undock the DBCheck pane and move it to another place in the DDF Builder window. The pane can be minimized and maximized.

The Copy SQL Definition Wizard creates a new SQL table by using the schema of an existing SQL table as its source. The wizard also creates the Btrieve file associated with the new SQL table.

In Data Sources Explorer, right-click a SQL file name and select Copy SQL Definition.

The wizard requires that you specify the following:

The Export Btrieve Schema Wizard creates an XML file that specifies the schema of a source Btrieve file. You can use this XML file to create a new Btrieve file based on the structure of an existing Btrieve file. See Import Btrieve Schema.

Data in the source file is not exported. If you want to export data, see Exporting Data with Export Data Wizard.

In Data Sources Explorer, right-click a Btrieve file name under the Data Paths node, then click Export Btrieve Schema.

By default, the wizard uses the source file name for the exported XML file name. The wizard adds the file extension .xml and writes the output file to the same directory as the source file. You can change the default name and location of the output file.

The wizard also provides a preview of the XML content before you export it.

The Import Btrieve Schema Wizard creates a Btrieve file based on the structure of another Btrieve file. The structure must be a schema of the source file in XML format.

Data from the source file is not imported. If you want to import data, see Importing Data with Import Data Wizard.

In Data Sources Explorer, right-click a data path under the Data Paths node, then select Import Btrieve Schema.

A Btrieve file must be located on physical storage, which is why the wizard is invoked from a data path.

Since the wizard is invoked from the context of a known data path, you do not specify a path for the target file, only for a file name. The file name length must be less than or equal to 255 bytes. The name cannot contain spaces unless the Embedded Spaces client configuration option is enabled. The option is enabled by default. See Embedded Spaces under Application Characteristics properties for clients in Advanced Operations Guide.

Data paths are added to the Data Sources Explorer by using Add Data Path. A data path represents a location in physical storage where Btrieve files reside. Each database must have at least one data path identified for it.

DDFs that you create for Btrieve files are located in the original data path for the database because all SQL tables for an entire database are defined in the same set of DDFs.

In Data Sources Explorer, right-click the Data Paths node, then click Add Data Path.

The existing directory can be empty or contain files.

Use the Delete command to remove a data path from Data Sources Explorer (right-click Data Paths or a database name, then click Delete). The directory is not deleted from physical storage.

DDF Builder allows you to change the data file associated with a selected table definition (SQL Table).

In Data Sources Explorer, right-click the SQL Table which you want to change the associated data file, then click Change Associated Data File. You may enter or browse to select the full path name for the data file you want associated.

When changing the data file associated with a particular SQL table, you may enter the filename or use the browse button to select a file, based on the location.

DDF Builder provides a separate view for displaying the Btrieve data types and sizes, along with the corresponding SQL data types to which they map.

The Btrieve Types view can be used to analyze data. When you highlight bytes in the raw data view, the Btrieve Types view displays the data as it would appear for data types compatible with the size. When a particular column is selected in the Table Definition Editor, the Btrieve Types view also displays a preview of the formatted data.

The Btrieve Types tab is located in the left pane of the DDF Builder window, next to the Data Sources Explorer tab. Click the Btrieve Types tab to display the view.

The Btrieve Types pane is useful when you are trying to resolve incorrect data type and size conflicts by comparing and previewing data. To support this task, you can undock the Btrieve Types pane and move it to a more convenient place in the DDF Builder window.

A preview column is also provided, showing how the selected data would appear as that particular data type. Previewing the data in each column can also be used to determine the size of a data type.

Anytime that DDF Builder detects problems and alters your existing table definition, the Definition Errors window automatically opens. If you close this window, you can later review the changes by opening it.

In the DDF Builder menu, select Window > View Definition Errors. If no errors have occurred, the menu command is grayed out.

The issues listed in the Definition Errors window give specific information regarding the problems addressed by DDF Builder. The original table definition is also provided in a read-only mode in the Original Definitions pane. You can open these panes side-by-side to make it easier to compare them.

The following table lists the possible table definition errors that can be detected by DDF Builder in your existing definitions. A general description of what caused the error and how DDF Builder has modified the table definitions as a result are also provided.

Anytime that DDF Builder detects problems and alters an existing table definition, its original table definition is retained until the changes are saved. If you make changes to an existing table definition, the original definition is also retained until your changes are saved. The original table definition can be viewed by opening the Original Definition view.

In the DDF Builder menu, select Window > View Original Definition. If no changes have occurred, the menu command is grayed out.

The original table definition is provided in a read-only mode in this tab. This window may be moved so that comparisons to the updated table definition may be viewed.

You can always retain the original table definition by saving either the modified or the original definition with a different name. To do this, click File > Save As and enter a different name.

This section explains the tasks that you perform with DDF Builder. The tasks are divided into the following categories:

General tasks apply to the overall use of the tool.

See Starting DDF Builder

You can also select Help > Zen Documentation Library. Because DDF Builder and Zen Control Center (ZenCC) share common components, such as SQL Editor, online help opened within DDF Builder may also display information about other Zen features.

Most tasks in DDF Builder are initiated by selecting a command relevant to a node in Data Sources Explorer. The commands invoke an editor, a view, or a wizard. The following tables direct you to tasks based on what action you want to take. The tables are categorized by areas of interest: