Using DDF Builder
 
Using DDF Builder
A Guide for Working with DDF Builder
This chapter discusses the following topics:
DDF Builder Concepts
GUI Reference
DDF Builder Tasks
DDF Builder Concepts
This section contains conceptual information about working with DDF Builder. The following sections are included:
Terminology Review
Security
Previous Database and DDF Versions
DDF Builder Error Detection and Correction
Saving Original and Modified Definitions
Table Definition Editor Pages
Legacy Nulls in DDF Builder
True Nulls in DDF Builder
Terminology Review
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.
Terms
Definition
Database
A collection of files that includes a set of DDFs with table definitions, a set of data files and a DSN.
File
Table
The Btrieve physical file or SQL table name.
Dictionary
Data Dictionary
System Catalog
System Tables
A set of DDF files that can contain table definitions of the data (metadata).
Table Definition
Entry in the DDFs that corresponds to a Btrieve file.
DDFs
System Tables
System Objects
Files with a .DDF extension. DDFs are system tables that provide the means for defining files within the constraints of a relational database (the metadata).
Key
Index
Specified data used to sort a table or file in a particular order and to optimize searches on particular values.
Security
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.
Previous Database and DDF Versions
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.
Scalable SQL v3.xx and Earlier
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.
Btrieve v5.x and Earlier
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.
DDF Builder Error Detection and Correction
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.
Note DDF Builder always retains the original table definition, so the changes made by DDF Builder are never automatically saved to your table definition. You may save either the modified or the original table definition with a different name to retain both definitions.
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.
Example 1 - Index Inconsistencies
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.
Example 2 - Record Length Mismatch
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.
Example 3 - Flag Inconsistencies
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.
Note The topic DDF Builder Tutorials provides step-by-step instructions on dealing with the various situations you may encounter when working with DDF Builder.
DDF Builder cannot repair every problem. For a complete list of the issues that DDF Builder detects and repairs, see Table Definition Errors.
Saving Original and Modified Definitions
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.
Table Definition Editor Pages
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:
Table Page
Indexes Page
Preview Page
Statistics Page
SQL View Page
Table Page
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.
Raw 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.
Tip For information on the visual indicators used in the raw data view, refer to Field Attributes in Raw Data View.
Grid Data View
The grid data view is similar in feature and functionality to the grid window view in the SQL Editor in PSQL 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.
Tip For information on the visual indicators used in the grid data view, refer to Field Attributes in Grid Data View.
Indexes Page
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.
Note For the steps needed to name an index, see Name an Index.
Alternate Collating Sequence (ACS) Files
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.
Preview Page
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.
Statistics 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.
SQL View Page
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.
Caution Do not reuse any statement that contains a reference to any of the PSQL dictionary system objects. These objects are easily identified by their X$<tablename> and include a proprietary comment prohibiting reuse.
Legacy Nulls in DDF Builder
Versions of PSQL 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.
True Nulls in 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
Working with True Nulls
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.
Note If you are working with files created prior to Pervasive.SQL 2000, nulls are most likely not an issue.
Creating Nullable Fields
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.
Note For examples of working with nullable and non-nullable columns, see the examples in Tutorial 1, Create a Nullable Column in the raw data view and Change a Non-Nullable Column to Nullable in the grid data view.
GUI Reference
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.
Table 3 Editors, Views, and Wizards Within DDF Builder  
GUI Component
Editor
View
Wizard
Description
Data Sources Explorer
 
* 
 
Btrieve File Editor
* 
 
 
Table Definition Editor
* 
 
 
SQL Editor
* 
 
 
SQL Editor in PSQL User's Guide1
Grid Window View
 
* 
 
Grid Window View in PSQL User's Guide1
Text Window View
 
* 
 
Text Window View in PSQL User's Guide1
Outline
 
* 
 
Outline Window View in PSQL User's Guide1
Add Database
 
 
* 
Check table consistency (Check Database and Check Tables)
 
*2
 
Copy SQL Definition
 
 
* 
Export Btrieve Schema
 
 
* 
Import Btrieve Schema
 
 
* 
Change Associated Data File
 
 
* 
Btrieve Types
 
* 
 
Definition Errors
 
* 
 
Original Definition
 
* 
 
1DDF Builder and PCC share common components. Because of this, some editors, views, and wizards are documented in other PSQL books and not in DDF Builder User’s Guide.
2The Check Database and Check Table action is similar to a wizard except that you make your selection in Data Sources Explorer rather than in a dialog. The results generated by checking consistency are similar to a view.
Welcome Page
Each time you start DDF Builder, the Welcome page opens. From this page you can quickly access a variety of information and perform common tasks.
Common tasks you can perform from this page are adding a new server and creating a new database.
A variety of information is also available from this page. The page contains several links which provide quick access to various pieces of information. The information provided in the Welcome page includes the following:
Getting Started with PSQL DDF Builder
DDF Builder Tutorials
PSQL Knowledge Base
Common Tasks
How to Access
If you have closed the Welcome page in DDF Builder, you can view it again at any time. From the DDF Builder menu bar, click Help > Welcome to display the Welcome page.
Figure 2 DDF Builder Welcome Page
Data Sources Explorer
DDF Builder uses a file explorer motif – a tree of objects – referred to as the Data Sources Explorer. Examples of objects include databases, data paths, Btrieve files, and SQL tables. The tree objects are referred to as nodes.
Figure 3 Data Sources Explorer
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 subordinate node is available.
Btrieve File Editor
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).
Tip To create both a Btrieve file and its DDFs, use SQL Editor in PSQL Control Center.
How to Access
In Data Sources Explorer, right-click a data path under the Data Paths node and select Create Btrieve File.
Figure 4 Btrieve File Editor
A Btrieve file must be located on physical storage, which is why the editor is invoked from a data path.
Features
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.
 
Table 4 Related Documentation for Creating Btrieve Files
Tab
Advanced Reference Documentation
Developer Reference Documentation
File Specifications
In Advanced Operations Guide:
“File Size”
“Owner Names and Security”
“System Data”
“File Information Editor”
In PSQL Programmer's Guide:
“Btrieve Fundamentals” chapter
“Designing the Database” chapter
Key Specifications
In Advanced Operations Guide:
“Methods for Handling Duplicate Keys”
In PSQL Programmer's Guide:
“Btrieve Fundamentals” chapter
“Designing the Database” chapter
“Working with Records” chapter
“Creating a Database” chapter
Restrictions
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.
Table Definition Editor
The Table Definition Editor creates new or changes existing schemas for SQL metadata. Note that Table Definition Editor is resembles Table Editor in PSQL Control Center (PCC) and is used in similar ways. For more information, see Table Editor.
How to Access
Do one of the following in Data Sources Explorer:
Right-click a Btrieve file name and select Create Table Definition.
Double-click a SQL table name to edit an existing table definition.
Right-click a SQL table name and select Edit Table Definition.
Figure 5 Table Definition Editor
Features
The Table Definition Editor, like the Table Editor in PSQL Control Center (PCC), 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.
Restrictions
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.
Table Page
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.
How to Access
Select the Table page by clicking the Table tab at the bottom of the Table Definition Editor.
Figure 6 Table Definition Editor Table Page Tab
Raw Data View
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.
Figure 7 Raw Data View in Table Definition Editor
The following table lists the attributes displayed in the raw data view:
Table 5 Field Attributes in Raw Data View
Attribute
Description
Error Message
Displays any error or warning condition
Record Length
Displays the entire length of the record, excluding the variable portion.
Offset
Displays the beginning and ending position of the selected bytes or field.
Size
Displays the size (in bytes) of the selected bytes or field.
Data Indicators
Displays the field/column indicator () to show where each field within the record begins.
Displays the null indicator () if the field is designated as nullable. This byte represents the null indicator byte.
Displays the unknown field indicator () if the field has not yet been defined.
Displays the unknown byte indicator () if the bytes have not yet been defined.
Grid 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.
Figure 8 Grid Data View in Table Definition Editor
The following lists the attributes displayed in the grid data view:
Table 6 Field Attributes in Grid Data View
Attribute
Description
Column Indicator
Displays the key icon () if the field is used in a key (index) definition.
Displays the unknown icon () if the field is unknown.
Displays the variable icon () if the field is an unknown variable-length portion.
Field
Displays the table field name.
Offset
Displays the offset, or cumulative, position of the field within the record, starting at 0.
View Only
Size
Displays the size (in bytes) of the field.
Btrieve Type
Displays the Btrieve data type for the field.
SQL Type
Displays the SQL data type for the field.
View Only
Precision
Displays the number of significant digits for floating point values.
Scale
Displays the number of significant digits that are to the right of the decimal point for floating point values.
Null
Selected if the field uses true Null values.
Case
Selected if the field is case insensitive.
Preview
Displays the field contents formatted with the applied data type.
View Only
How the Two Views Work Together
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.
Figure 9 Selecting Bytes in the Table Definition Editor
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.
Figure 10 Table Definition Editor Error Message
Figure 11 Table Definition Editor Caution/Warning Message
Indexes Page
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 PSQL Control Center. See Table Editor in PSQL User's Guide.
How to Access
Select the Indexes page by clicking the Indexes tab at the bottom of the Table Definition Editor.
Figure 12 Indexes Page in Table Definition Editor
Features
Name changes made on the Tables page to columns specified as indexes are updated and shown immediately on the Indexes page.
Restrictions
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).
Preview Page
The Preview page shows file data in a readable layout of columns and rows.
How to Access
Select the Preview page by clicking the Preview tab at the bottom of the Table Definition Editor.
Figure 13 Preview Page in Table Definition Editor
Features
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.
Statistics Page
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.
How to Access
Select the Statistics page by clicking the Statistics tab at the bottom of the Table Definition Editor.
Figure 14 Statistics Page in Table Definition Editor
Features
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.
Note The statistics information is based solely on information in the physical Btrieve file; it does not show any metadata information.
SQL View Page
The SQL View page displays the SQL statement needed to create the current table definition.
How to Access
Select the SQL View page by clicking the SQL View tab at the bottom of the Table Definition Editor.
Figure 15 SQL View Page in Table Definition Editor
Features
Most SQL statements created here can be copied and reused in the PSQL Control Center for creating new schemas based on the ones you created with DDF Builder. SQL statements that you use in the PSQL Control Center can also be saved for future use.
Restrictions
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.
Caution Do not reuse any statement that contains a reference to any of the PSQL dictionary system objects. These objects are easily identified by their X$<tablename> and include a proprietary comment prohibiting reuse.
Add Database
The Add Database Wizard creates a new database. This wizard is shared with PSQL Control Center (PCC). See Databases and New Database GUI Reference in PSQL User's Guide.
Check Table Consistency
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:
Dictionary files have compatible version information.
Tables have a valid name, ID, and an existing, accessible physical file.
Columns have valid names, correct total record length, and no overlapping definitions.
Indexes are correct in name, number, size, data type, offset, and attributes.
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.
Icons on the results view identify the different types of messages:
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.
How to Access
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.
Figure 16 DBCheck (consistency check results) View
Features
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 pane also provides icons for saving the consistency check results to a text file and for hiding validation check messages and warnings by filtering them.
Error Message Tips
A tip appears if you click on an error message. The tip provides comments about the message and may include a suggested corrective action. The tips are also written to the text file if you save the DBCheck results to a file.
Copy SQL Definition
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.
How to Access
In Data Sources Explorer, right-click a SQL file name and select Copy SQL Definition.
Features
The wizard requires that you specify the following:
Server on which the database engine is running.
Database in which you want to include the new table.
Name of the new table. By default, this name becomes the name of the associated Btrieve file. You cannot use an existing table name for the copy.
Note The new table does not include data, but you can export it from the source table and then import it into the new table. See Importing Data with Import Data Wizard and Exporting Data with Export Data Wizard.
Export Btrieve Schema
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.
How to Access
In Data Sources Explorer, right-click a Btrieve file name under the Data Paths node, then click Export Btrieve Schema.
Features
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.
Import Btrieve Schema
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.
How to Access
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.
Features
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.
Add Data Path
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.
How to Access
In Data Sources Explorer, right-click the Data Paths node, then click Add Data Path.
Features
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.
Change Associated Data File
DDF Builder allows you to change the data file associated with a selected table definition (SQL Table).
How to Access
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.
Features
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.
Btrieve Types
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.
How to Access
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.
Figure 17 Btrieve Types View
Features
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.
Definition Errors
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.
How to Access
In the DDF Builder menu, select Window > View Definition Errors. If no errors have occurred, the menu command is grayed out.
Figure 18 Definition Errors in Table Definition Editor
Features
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.
Definition Errors List
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.
Table 7 Table Definition Errors
The Message Displays...
What it means...
DDFBLDR-1: No corresponding Btrieve key for this SQL index. Index ignored.
The Btrieve file does not contain a key to correspond with a SQL index in the existing table definition.
The index in the SQL table is ignored.
DDFBLDR-2: No corresponding SQL index found for this Btrieve key. Index added.
The Btrieve file contains a key that is not defined as a SQL index in the existing table definition.
An index is added to the table definition that corresponds with the key in the Btrieve file.
DDFBLDR-3: SQL segment null flag does not match corresponding Btrieve segment null flag.
The Btrieve file contains a segment with a null flag that is not present in your SQL file segment.
OR
The SQL file contains a segment with a null flag that is not present in your Btrieve file.
DDFBLDR-4: SQL column overlaps another column. The column’s size has been truncated.
The existing table definition contains a SQL column that overlaps another column in the table.
DDFBLDR-5: No corresponding SQL column found for Btrieve segment. New column added.
The Btrieve file contains a segment that has no corresponding SQL column in the existing table definition.
DDFBLDR-6: Undefined bytes in table definition. Unknown column added.
The existing table definition does not account for the same number of bytes found in your Btrieve file. The table definition contains some undefined bytes.
An new unknown column is added to the table definition to account for the undefined bytes in your Btrieve file.
DDFBLDR-7: SQL column type does not match Btrieve segment type. Column type changed.
The Btrieve file segment uses a different type than what was found in your SQL table column.
The data type in your SQL table column is changed to the same data type as the Btrieve file segment.
DDFBLDR-8: SQL column null flag does not match Btrieve segment null flag. Column null flag changed.
The existing table definition contains a SQL column with a different null flag setting than what DDF Builder found in the corresponding Btrieve segment.
DDFBLDR-9: SQL column case flag does not match Btrieve segment case flag. Column case flag changed.
The existing table definition contains a SQL column with a different case flag setting than what DDF Builder found in the corresponding Btrieve segment.
DDFBLDR-10: SQL column defined across a Btrieve segment boundary. Column ignored.
The existing table definition contains a SQL column defined across the boundary of a segment in your Btrieve file.
The SQL column defined across the segment boundary is ignored in the existing table definition.
DDFBLDR-11: SQL column type is not compatible with the column size. Column type changed to Unknown.
The existing table definition contains a SQL column with a data type that is not compatible with the column size.
The SQL column type is changed to unknown; size is not changed.
DDFBLDR-12: Variable length column can only be the last column in a table. Column type changed to Unknown.
The existing table definition defines a variable length record portion in a column that is not last in the table.
The SQL column type is changed to unknown.
DDFBLDR-13: Invalid bit mask detected. Bit mask value corrected.
The table definition contains incorrect bit masks that do not equal the sizes needed to complete the byte. The bit masks have been changed in the table definition so their sizes are correct and total the byte.
DDFBLDR-14: No compatible SQL type for Btrieve WSTRING or WZSTRING. Column type set to SQL CHAR. Column not usable.
The Btrieve file contains columns defined with the WSTRING or WZSTRING type. These types do not map to a SQL data type. These columns are not usable and have been changed to CHAR type.
Original Definition
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.
How to Access
In the DDF Builder menu, select Window > View Original Definition. If no changes have occurred, the menu command is grayed out.
Figure 19 Original Definition in Table Definition Editor
Features
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.
DDF Builder Tasks
This section explains the tasks that you perform with DDF Builder. The tasks are divided into the following categories:
Category
Description
Orient you to the overall use of DDF Builder
Allow you to access editors, views, and wizards
General Tasks
General tasks apply to the overall use of the tool.
To start DDF Builder
See Starting DDF Builder
Accessing User Documentation
To access DDF Builder online help
1 Start DDF Builder
2 Click the part of the user interface that interests you
3 Press F1.
4 If you click another area of the user interface, you may need to press F1 again to refresh the help view.
You can also select Help > PSQL Documentation Library. Because DDF Builder and PSQL Control Center (PCC) share common components, such as SQL Editor, online help opened within DDF Builder may also display information about other PSQL features.
DDF Builder Log File
To access DDF Builder Log file
1 Start DDF Builder and click Help > DDF Builder Log.
The log file opens in your system text editor.
To clear DDF Builder Log file
1 Start DDF Builder and click Help > Clear DDF Builder Log.
A dialog box opens for you to confirm the action.
2 Click Yes to confirm and the log file is deleted.
The next time you start DDF Builder, a new log file is automatically created.
To see added or deleted nodes in Data Sources Explorer
1 Right-click the node for which you want to see the addition or deletion.
2 Click Refresh.
The context of the Refresh command applies to the node from which you invoke it. If you want to refresh all nodes in Data Sources Explorer, execute the command from the Engines node (the top of the tree).
Tasks Initiated from Data Sources Explorer
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:
Btrieve Files
Data
Database
Data Sources Explorer
Online Help
SQL Queries
SQL Tables
Note When using DDF Builder with security-enabled databases, you should always take the database offline and turn off all security prior to working with the files in DDF Builder.
Btrieve Files
 
If You Want To…
Then…
Notes
Create a new Btrieve file with an editor.
Right-click a data path under the Data Paths node.
Click Create Btrieve File .
DDF Builder does not automatically create DDFs for the new file.
Export the Btrieve file structure to an XML file.
Right-click a Btrieve file name under the Data Paths node.
Click Export Btrieve Schema .
The data in the Btrieve file is not exported.
Create a new file based on the structure of another Btrieve file, as specified in a XML file.
Right-click a data path under the Data Paths node.
Click Import Btrieve Schema .
The data in the Btrieve file is not imported.
Specify a directory where you want a Btrieve file located.
Right-click the Data Paths folder .
Click Add Data Path .
Any DDFs that you create for the Btrieve file are located in the original data path for the database. This is because all SQL tables are defined in the same set of DDFs.
Remove a data path directory from Data Sources Explorer.
Right-click a data path under the Data Paths node.
Click Delete .
Neither the directory nor any files in it are deleted from physical storage.
View the Btrieve file statistics with which the SQL table is associated.
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click Edit Table Definition .
Click Statistics tab.
The table names appear under the SQL Tables node, or under Data Paths node.
Change the Btrieve (data) file associated with the selected SQL table.
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click Change Associated Data File .
Only one data file may be associated with a particular table.
Data
 
If You Want To…
Then…
Notes
Check the consistency for all tables at once
Right-click Databases node.
Click Check Database.
Check the consistency for one or more tables individually
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click Check Tables.
The names appear under the SQL Tables node, or under a Btrieve file name (Data Paths node) if the Btrieve file has DDFs.
You can select multiple tables by holding down the Shift or Control key then clicking the desired table names.
Specify in Data Sources Explorer a directory where you want data files to reside or where they already reside
Right-click the Data Paths folder .
Click Add Data Path .
Remove from Data Sources Explorer a directory where data files reside
Right-click a data path listed under the Data Paths node.
Click Delete .
Neither the directory nor any files in it are deleted from physical storage.
Database
 
If You Want To…
Then…
Notes
Create a new database
Right-click Databases node.
Click Add Database.
Remove a database from Data Sources Explorer
Right-click Databases node.
Click Delete .
The database is not deleted from physical storage.
Data Sources Explorer
 
If You Want To…
Then…
Notes
Update the tree to show additions to or deletions from nodes
Right-click any node except for the name of a SQL table.
Either click Refresh or press F5.
The context of the Refresh command applies to the node from which you invoke it. If you want to refresh all nodes in Data Sources Explorer, execute the command from the Engines node at the top of the tree.
Online Help
 
If You Want To…
Then…
Notes
Access the user documentation
Press F1 (or Shift F1 for Linux) within an editor, view, or wizard (or click Help > DDF Builder Help Contents).
SQL Queries
 
If You Want To…
Then…
Notes
Execute a SELECT statement for all records in a SQL table (SELECT * FROM)
or
Access an editor in which you can type SQL statements to execute against a SQL table
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click SQL Editor .
By default, SQL Editor executes a SELECT * FROM statement when it opens.
SQL table names display as subordinate nodes under SQL Tables and under the Btrieve file name (provided the Btrieve file has DDFs).
Execute SQL statements in SQL Editor or in Outline View
See Execution Tasks in PSQL User's Guide.
You can perform numerous tasks with SQL Editor. See SQL Editor Tasks in PSQL User's Guide for a complete list.
SQL Tables
 
If You Want To…
Then…
Notes
Create a new table definition with an editor
Right-click a Btrieve file name under the Data Paths node.
Click Create Table Definition .
You can create one or more SQL tables for a Btrieve file. Each SQL table appears as a subordinate node under the Btrieve file name.
Create a new table based on the schema of another SQL table
Right-click a SQL table name under either the Btrieve file or SQL Tables node.
Click Copy SQL Definition .
The table names appear under the SQL Tables node, or under a Btrieve file name (Data Paths node) if the Btrieve file has DDFs.
Modify a table definition
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click Edit Table Definition .
The names appear under the SQL Tables node, or under a Btrieve file name (Data Paths node) if the Btrieve file has DDFs.
Remove a table from Data Sources Explorer
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click Delete .
The table is not deleted from physical storage.
The table names appear under the SQL Tables node, or under a Btrieve file name (Data Paths node) if the Btrieve file has DDFs.
Change the Btrieve (data) file associated with the SQL table.
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click Change Associated Data File .
Only one data file may be associated with a particular table.
View the Btrieve file statistics with which the SQL table is associated
Right-click a SQL table name under either the Data Paths or SQL Tables node.
Click Edit Table Definition .
Click Statistics tab.
The table names appear under the SQL Tables node, or under Data Paths node.