Lesson 1 – Working with v3.00 DDFs

In this lesson, you have a database with an existing set of table definitions. The table definitions were created with a much older version of PSQL and are no longer compatible with the version of Pervasive.SQL 2000 supported in this release of DDF Builder.

Caution: DDF Builder supports version 4.xx DDFs but does not support DDFs created prior to Scalable SQL version 4.xx.

Goals

The goal is to try and open the DDFs with DDF Builder. This tutorial explains how DDF Builder handles these files and provides you with a solution so that you can convert your files to a version compatible with DDF Builder.

What You Need to Know

The files to use for this lesson reside in a folder named V3. Assuming you installed using the default installation locations, this folder is located at:

<Application Data>\DDFBUILDER\TUTORIALS\TUTORIAL2\V3

You must create a DSN for this database before continuing.

Note: Refer to Create Data Source Names (DSN) if you need information about creating the DSN.

Open the Btrieve File

Begin by starting DDF Builder, if it is not already running.

In the DDF Builder Data Sources Explorer, expand the Databases node to see all of the available databases.

Locate the V3 database in the list.

Double-click the V3 database icon.

The following message displays:

Click Ok to close the message window.

You are unable to open these files because they were originally created with a version of the product that is no longer supported.

Understanding the Warning Message

DDF Builder is unable to open Scalable SQL v3.00 data dictionary files because the format used at that time (Scalable SQL 3.01) is no longer supported.

Tip: 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.

How To Convert My Files

If you have older DDFs you need to convert them to the new format. Use the following steps.

Backup your existing, old database by copying the data files and DDFs to a different storage location.

Export the table schema for all tables in your old database using PSQL Control Center (PCC). See To export all table schemas at once in PSQL User's Guide. Ensure that you select the export option “add IN DICTIONARY clause to CREATE statements.”

Ensure that the CREATE INDEX statements in the export file put the index segments in the correct order for each table.

The indexes for the tables must be in the same order as in the original data files. The exported table schemas put the indexes in alphabetical order, so you may need to rearrange the statements.

To determine the sequential order of the indexes, you can use the butil -stat command against the original data file. See Viewing Data File Statistics in Advanced Operations Guide. Another method is to use the dbo.fSQLStatistics catalog function. See dbo.fSQLStatistics in SQL Engine Reference.

Typically, because you are converting old files, you need to add the following line to the export schema file as the first line in the file:

SET TRUENULLCREATE = OFF;

Add the following line as the last line of the file:

SET TRUENULLCREATE = ON;

Create a new database with PCC. See To create a new database in PSQL User's Guide.

In PCC’s SQL Editor, execute the CREATE TABLE SQL statements in the export schema file against the new database to create all of the tables. See To open an SQL script in PSQL User's Guide.

Copy the old PSQL data files—not the DDFs—from the old database to the storage location for the new database data files.

You should be able to access your existing data via SQL (for example, with PCC).

Use DDF Builder to build table definitions for any Btrieve files that did not have a definition and to correct problem definitions. See also Conversion Notes.

If required, rebuild your data files (it is likely they are a pre 6.x file format). For information on rebuilding your Btrieve data files, proceed to the next section, Lesson 2 – Working with Pre v6.x File Formats.

Conversion Notes

The steps above are the most direct for simple data files. Try the steps first to determine the results of the conversion.

You may encounter two situations that require additional effort:

•

Alternating Collating Sequences

•

Table Definitions That Require Using PCC and DDF Builder

Alternating Collating Sequences

If a table column uses an alternating collating sequence (ACS), you must manually modify the CREATE TABLE statement. PCC does not include an ACS when you export a table schema.

To determine if a column uses an ACS, check table consistency with DDF Builder (see Check Table Consistency). If a message informs you of an attribute mismatch with physical key file segment, then the column uses an ACS.

The following example is a modified CREATE TABLE statement in which collating was added manually:

SET TRUENULLCREATE = OFF;

CREATE TABLE "PATAPP" IN DICTIONARY USING 'PATAPP.DTA' (

"ID" CHAR(6) NOT NULL COLLATE 'UPPER.alt',

"Appointment Date" DATE NOT NULL,

"Appointment Time" TIME NOT NULL,

"AMPM" CHAR(4) NOT NULL COLLATE 'UPPER.alt',

"Doctor" CHAR(12) NOT NULL,

"Code" CHAR(3) NOT NULL COLLATE 'UPPER.alt',

"Amount Paid" MONEY(14,2) NOT NULL,

"Date Paid" DATE NOT NULL);

CREATE INDEX "index_0" IN DICTIONARY ON "PATAPP" (

"Appointment Date" ,

"AMPM" ,

"Appointment Time" );

CREATE INDEX "index_1" IN DICTIONARY ON "PATAPP" (

"ID" );

CREATE INDEX "index_2" IN DICTIONARY ON "PATAPP" (

"Code" );

SET TRUENULLCREATE = ON;

Also, if the indexes are not in the correct sequence for the table, you must rearrange the CREATE INDEX statements to put the index segments in the correct sequence.

Table Definitions That Require Using PCC and DDF Builder

Some table definitions may be so difficult to resolve that you have to use both PCC and DDF Builder. If columns are marked as “unknown” in DDF Builder, you may need to view the table in PCC’s Table Editor, then define the columns similarly in DDF Builder. Using both utilities and a back-and-forth method, you should be able to complete the table definitions.

Conclusion

This lesson introduced you to how DDF Builder handles Scalable SQL v3.01 DDFs and provided you with a solution for converting your files so that they may work with current versions of the software.

You also learned that databases created with Scalable SQL v3.01 DDFs are likely to contain Btrieve data files with a pre v6.x file format and must be rebuilt before using DDF Builder.