Copyright: Pervasive Software 1997


ACS
You can specify an alternate collating sequence (ACS) for columns that are string data types. You cannot specify an ASC for a column that is case-sensitive, because case-sensitivity itself designates a special collating sequence.


About command (Help menu)

DDF Ease release 1. Copyright 1998. Pervasive Software Inc.


Adding Index Definitions
To add a column index:

1	Click Insert Index to add an index. To delete a column, make sure to click in the desired row and then select Delete Index.

2	If you want to create a segmented index (for example, Address might include columns StreetName, StreetNumber, City, State, and Zip), click on the specific column in the Index Definition field and then click Add Segment. Conversely, you can remove any segmented index with the Remove Segment button.

You must supply a unique name for each index; do not use the same name as a column.


Addting Data to the Order Entry Database
Adding Data to the Customer Order Database

At this time, DDF Ease does not support adding data to the database.  However, you can execute statements in Pervasive's SQLScope utility to add new data to the database.

For example, suppose you want to add data for the sales offices.  Here are the INSERT statements to add the sales office to the database.

INSERT INTO SalesOffices (City, State, Country, Target)

               VALUES ('Austin', 'TX', 'USA', 800000)

INSERT INTO SalesOffices (City, State, Country, Target)

               VALUES ('Boston', 'MA', 'USA', 510000)

INSERT INTO SalesOffices (City, State, Country, Target)

               VALUES ('San Francisco', 'CA', 'USA', 500000)

Here’s an INSERT statement to add the sales representative Andy Woodrif to the Austin sales office.

INSERT INTO SalesReps (FirstName, LastName, Office_ID, Quota, Sales)

VALUES ('Andy', 'Woodrif', 1, 250000, 0)

In SQLScope, insert this information (make sure to include separator (;) after each statement) and then select Run All from the Run menu. You should also save these statements into a .sql file and write it to the orders database directory.

After these statements finish running, return to DDF Ease and click on the SalesOffices table and then select the Data tab as discussed in the next step.


Advanced - Physical Data File Settings dialog
Advanced - Physical Data File Settings

Size Allocations	Page Size/Number of Pages to Preallocate Btrieve file limits the maximum number of key segments (rather than the maximum number of keys). The maximum number of key segments allowed depends on the page size: Page Size Maximum  Key Segments  512 8 1,024 23 1,536 24 2,048 54 2,560 54 3,072 54 3,584 54 4,096 119 Free Space Threshold The Free Space Threshold is a value expressed as a percentage and tells Btrieve how much free space must remain on a variable page in order for that page to appear on the Free Space List. When Btrieve adds new variable-length records to the file, it uses pages in the Free Space List before using new variable pages. For information regarding this setting, see your Btrieve Programmer's Guide. Data Compression If you select this option, Btrieve compresses the file's records before inserting or updating them and compresses the records when it retrieves them. The length of the record after compression depends on its contents. Even records whose lengths are equal before compression are likely to have different lengths after compression. For this reason, Btrieve stores compressed records on variable pages. For information regarding this setting, see your Btrieve Programmer's Guide.
Security	If you specify an owner name, you must provide this exact name any time you want to access this data file. DDF Ease does not store this information in a user accessible file. If an owner name is specified, select the type of owner access desired. No owner access restrictions apply unless there is an owner name.
Related Topics

How to Create a Table


Can't open Scalable SQL 4.x database because using Scalable SQL 3.x engine


Check Database Wizard - Check Database Definitions dialog
Check Database Wizard - Check Database Definitions

As the check is progressing, the numbers are continuously updated in the # Tables Passed and # Tables Failed fields. When the check is completed, the information is displayed under the related tab.

Pause	This button is active only when the check is occurring. During the check, click this button to interrupt the check. At this time, you can also print the contents of the current tab or save a report.
Resume	This button is active only when a check is occurring. Click this button to resume checking the tables that have not been already checked.
Failed	This tab displays each table that failed and why the check failed for each element in the table definition.
Passed	This tab shows only the names of the tables that passed.
Statistics	This tab displays a breakdown of the results of the database check.
Cancel	Click this button if you want to totally stop the checking and exit the dialog box.
Help	Click this button to receive help about this dialog box.
Print	Click this button to print the information displayed in the currently active tab to your default printer. If you paused the check, you can print the information displayed in the current tab.
Save Report	Click this button to save all or portions of the results of the database check to a .txt file.
Checking a database with Scalable SQL 4 mode

When you check a database with Scalable SQL 4 mode, you will get the following two failure cases:

Failure 1

Table: X$Field

Index Data Type Inconsistency.

Dictionary index 4 seg 2 indicates unsigned (Column = 'Xe$Dec')

Comment:  This failure is expected because Btrieve represents the unsigned(1) data type as a char(1) in the index on the Btrieve file.

Failure 2

Table: X$File

System Index Mod Flag Inconsistent with Scalable SQL version 4.

Index 1 seg 0 indicates mod = No

Expected mod = Yes

Comment:  A known problem with Scalable SQL 4 is that it generates the wrong Mod flag for Index 1 seg 0 of X$File. Run Convert Dictionary from the File menu with the option to convert to Scalable SQL 4. This will correct the system index flag inconsistency.

Related Topics

How to check a database


Check Database Wizard - Select Tables to Check dialog
Check Database Wizard - Select Tables to Check

Tables	The database tables and data files are displayed in this field. To check a table or data file, you need to include it in the Tables to Check field with the Add button. To move all of the items, click Add All. Similarly, if you do not want to include a table in the checking process, click Remove. Click Remove All if you want to move all of the tables back to the Tables field.
Tables to Check	Includes the list of tables and data files you want to check.
Next	Performs the check on the tables and data files you included.
Cancel	Allows you to cancel the check.
Related Topics

How to check a database


Checking a database with Scalable SQL 4 mode


Connect to Pervasive Software on the Web
Pervasive Web Site

http:\\www.pervasive.com


Create Table Wizard, Step 2

Column Definitions	Define each column by tabbing to each field and inserting text or selecting from a drop-down list. Click Insert Column to add a column. To delete a column, make sure to click in the desired row and then select Delete Column.
Index Definitions	Define each index by tabbing to each field and inserting text or selecting from a drop-down list. Attribute Keyword Description DUP MOD NULL SORT ACS
	To Add an Index
Advanced	Click this button to allocate size for data files and specify an owner name for security in the Advanced - Physical Data File Settings dialog box.
Back	Click this button to return to the Create Table Wizard, Step 1 dialog box if you want to change the table name.
Cancel	Click this button to cancel the create table operation.
Create Table	Click this button when you have defined the new table.
Related Topics

How to create a table

How to create a table for an existing Btrieve file

How to create DDFs for an existing Btrieve database

How to create a relational database


Create Table Wizard, Step 2 dialog
Create Table Wizard, Step 2

Indexes Found	Displays the index definitions found for the existing Btrieve data file.
Columns Found	Displays the column definitions found for the existing Btrieve data file.
Column Data (up to first ten rows)	Displays the data for each column in the data file.
Next	Click this button after you have named the columns.
Back	Click this button if you want to view the previous dialog box and choose another data file.
Cancel	Click this button to cancel the create table procedure.
Related Topics

How to create a table for an existing Btrieve Database

Data Types


Create Table Wizard, Step 3 dialog
Create Table Wizard, Step 3

This dialog box allows you to define non-indexed columns. A non-indexed column will allow you to change the data type. Initially the data type is set to unknown because this could be one or more columns.

To determine whether this column is just one column or if it should be split into multiple columns:

1  Click in the row and click Split Column. You will be able to see the bytes of data in hex and ASCII and determine how to split the column, if needed.

2  After you split the column (if it needs to be split), click on the data type pull-down list to see the available data types that match the first several rows of data found for the column.

You can refer to the Data Types reference to learn more about the internal storage representation of data types and view the raw data in the Split Column dialog box if you are unsure what data type to select.

3  You can also select a data type and view the data using that data type filter.  The data is displayed in the Column Data grid.

4  You must assign a data type and column name to each column.  If you have a column that is of type LVAR or NOTE, it is a variable-length column.  DDF Ease provides a default size of 100 for a variable-length column.  You should set this to an appropriate value for the data in your records because DDF Ease does not read all records to determine the maximum variable-length size.

5  After you specify a data type and column name for all columns, click  Create Table to create the table definition for your Btrieve file.  It should be displayed with the other tables in your database in tree view of the Main window.

Related Topics

How to create a table for an existing Btrieve database

Data Types

Helpful Hints for Splitting Columns


Creating Tables for the Order Entry Database
Creating Tables

Customers Table

1  	To create the Customers table, select Create from the Table menu.  Enter the table name "Customers" and then click Next. (See How to create a table for specific instructions.)

2  	Enter the columns and indexes as shown in the following table:

Column Definitions

Column Name	Data Type	Size	Dec	Case Sens.
Customer_ID	autoinc	4		N/A
Customer_Name	char	30		No
SalesRep_ID	integer	4		N/A
Credit_Limit	money	10		N/A
Index Definitions

Index Name	Column Type	Dup	Mod	Null	Sort ASC
Cust_ID	Customer_ID	No	Yes	No	Ascen
Cust_Sales	Customer_Name	Yes	Yes	No	Ascen
	SalesRep_ID	Yes	Yes	No	Ascen

3	To enter a column name, double-click the default "Unnamed" so that it is highlighted and then type the new name. To insert another column below the first column press Tab or the down-arrow key. Repeat these steps for inserting indexes. To insert a segmented index, first insert a new index and then click Add Segment. Scalable SQL 4.0 allows you to create segmented indexes with autoinc. However, this is not supported in Scalable SQL 3.01. Also, remember that you cannot have the same index name as the column name; these names must be unique. For specific information about completing column and index information, refer to the section on the Create Table Wizard, Step 2 dialog box. Click Create Table to create the table.

4	Suppose that later you want to add an index on Company_Name. In the main tree view, select the Customers table and then the Indexes tab.  Click in the last row of the Indexes view and press the Down Arrow.  Notice the row is highlighted in blue until you save the changes. Enter the index name "Comp_Name" and select the column "Company_Name."  Click Save from the toolbar.

Orders Table

1  	To create the Orders table, select Create from the Table menu.  Enter "Orders" as the table name and click Next.  Then enter the following column and index information, and click Create Table. (For help, press F1 or the Help button on this dialog box.)

Column Definitions

Column Name	Data Type	Size	Dec	Case Sens.
Order_ID	autoinc	4		N/A
Customer_ID	integer	4		N/A
Product	char	10		No
Quantity	integer	4		N/A
Amount	money	10		N/A
Order_Date	date	4		N/A
Index Definitions

Index Name	Column Type	Dup	Mod	Null	Sort ASC
Order_Num	Order_Number	No	Yes	No	Ascen
Order_Cust	Customer_ID	Yes	Yes	No	Ascen
SalesReps Table

1  	To create the SalesReps table, select Create from the Table menu.  Enter the following:

Column Definitions

Column Name	Data Type	Size	Dec	Case Sens.
SalesRep_ID	autoinc	4		N/A
FirstName	char	15		No
LastName	char	15		No
Office_ID	integer	4		N/A
Qutoa	money	10		N/A
Sales	money	10		N/A

Index Definitions

Index Name	Column Type	Dup	Mod	Null	Sort ASC
Rep_ID	SalesRep_ID	No	Yes	No	Ascen
Rep_Office	LastName	Yes	Yes	No	Ascen
	Office_ID	Yes	Yes	No	Ascen
SalesOffices Table

1  	To create the SalesOffices table, select Create from the Table menu.  Enter the following:

Column Definitions

Column Name	Data Type	Size	Dec	Case Sens.
Office_ID	autoinc	4		N/A
City	zstring	20		No
State	zstring	15		No
Country	zstring	20		No
Target	money	10		N/A

Index Definitions

Index Name	Column Type	Dup	Mod	Null	Sort ASC
Off_ID	Office_ID	No	Yes	No	Ascen

2  	After you add the column and index information for the tables, the Main window displays the tree view.  Some of the subfolders have been expanded to display columns and indexes.

Notice how some of the information is not visible in this window. Use the horizontal scroll bar to the right of the tabs to view more information.


Creating the Order Entry Database
Create the Order Entry Database

1	To create the database, select New Database from the File menu, or you click [image] on the toolbar.

The New Database dialog box is displayed. For more information on creating databases, see How to create a database.

2  	You now have an empty data base shown in the main tree view.  Next define the Customers, Orders, SalesReps, and SalesOffices tables.


DDF Ease Help

Key Information

What is DDF Ease

What are DDFs

Who uses DDF Ease

Why use DDF Ease

Software and System Requirements

Getting Help

Connect to Pervasive Software on the Web

Getting information on Scalable SQL, Btrieve, and ODBC

Send product suggestions

Technical support

How To . . .

How to create a database

How to open a database

How to save a database

How to close a database

How to delete a database

How to check a database

How to convert a database dictionary

How to create a table

How to create a table for an existing Btrieve file

How to add table columns

How to drop table columns

How to alter table columns

How to add table indexes

How to drop table indexes

How to restore table indexes

How to change a table location

How to display table data, definitions, and statistics

How to print a table definition

How to display system tables

How to set database security

Reference

Data Types

Scalable SQL v4.0 Standard DDFs

Rules for Defining Columns

Helpful Hints for Splitting Columns

Tutorial

How to create DDFs for existing Btrieve files

How to create a relational database

Troubleshooting DDF Ease

If you get Status Code 94 ...

If you get Status Code 96 ...

If you get Status Code 221 ...

If you get Status Code 234 or 207 ...

If you get Status Code 285 ...

If you get Status Code 288 ...

If you get Status Code 850 ...

If you get Status Code 2104 ...

Can't open Scalable SQL 4.x database because using Scalable SQL 3.x engine


DDFs
Data Dictionary Files (DDFs) describe data in your Btrieve database in terms of tables, columns, and indexes (in Btrieve terminology, this is Files, Fields, and Indexes).

A Btrieve database does not explicitly contain information that describes the format and meaning of the data in the database (this information is defined within a Btrieve application). DDFs provide a way of defining the fields in the database so that ODBC, Scalable SQL, and a variety of other commercial tools and applications can access the data in your Btrieve database. The format used to describe the structure and meaning of the data changed from Scalable SQL 3.01 to Scalable SQL 4.0.

DDF Ease creates and maintains DDFs that are standard to Scalable SQL and ODBC. These DDFs are FILE.DDF, FIELD.DDF, and INDEX.DDF.


DUP
Determines whether Scalable SQL allows multiple rows to have the same index column value. By default, Scalable SQL creates non-unique indexes.


Edit menu commands

The Edit menu offers the following commands:

Undo	Reverse previous editing operation.
Cut	Deletes data from the document and moves it to the clipboard.
Copy	Copies data from the document to the clipboard.
Paste	Pastes data from the clipboard into the document.


Example Data: Customer Database
Customers Table

Customer_ID	Company_Name	SalesRep_ID	Credit_Limit
1	Western Union	10	$ 50,000.00
2	Home Depot	33	$ 50,000.00
3	Sony	18	$ 25,000.00
4	Whole Foods	44	$ 20,000.00
...	...	...	...

Orders Table

Order_Number	Customer_ID	Product	Quantity	Amount	Date
1	2	CMD-101	4	$3,000	3-19-97
2	3	REB-200	2	$5,000	3-19-97
3	4	JHD-551	1	$1,000	3-20-97
4	1	ACC-008	3	$3,000	3-20-97
5	2	ENH-423	6	$9,000	3-20-97
...	...	...	...	...	...

SalesReps Table

SalesRep_ID	FirstName	LastName	Office_ID	Quota	Sales
1	Andy	Woodrif	1	$250,000	$62,000
2	Marie	Tantara	1	$300,000	$50,000
3	Harold	Chase	1	$300,000	$37,000
4	Joe	Gibbs	2	$210,000	$22,000
5	Bill	Costello	3	$500,000	$255,000
6	Adam	Smith	3	$300,000	$192,000
7	Ron	Abrams	2	$200,000	$87,000
...	...	...	...	...	...

-SalesOffices Table

Office_ID	City	State	Country	Target
1	Austin	TX	USA	$800,000
2	Boston	MA	USA	$510,000
3	San Francisco	CA	USA	$500,000


File Exit command
Exit command

Use this command to end your DDF Ease session.  You can also use the Close command on the application Control menu.  DDF Ease prompts you to save databases with unsaved changes.

Shortcuts

Mouse:	Double-click the application's Control menu button.

Keys:	ALT+F4


File Print Setup command

Use this command to select a printer and a printer connection.  This command presents a Print Setup dialog box, where you specify the printer and its connection.


File menu commands

The File menu offers the following commands:

New Database	Creates a new database.
Open Database	Opens an existing database.
Close Database	Closes the database.
Save Database	Save changes you have made to column or index definitions.
Delete Database	Deletes the DDFs only.
Check Database	DDF Ease allows you to verify whether your DDFs are compliant with Scalable SQL and ODBC.
Convert Dictionary	DDF Ease enables you to convert a database dictionary from Scalable SQL 3.x to Scalable SQL 4.x mode and vice versa.
Set Database Security	DDF Ease allows you to define and maintain security on your Btrieve database. This security protects how Scalable SQL, ODBC, JDBC, and other tools can access your database based on the DDFs you have defined.
Print	DDF Ease formats the table information you are currently viewing into a printable report. The document prints to your default printer.
Print Preview	Allows you to view the table information you are currently viewing before you print it.
Print Setup	Allows you to selects a printer and printer connection.
Exit	Exits DDF Ease.


Getting information on Scalable SQL, Btrieve, and ODBC

You can obtain information about Pervasive products at www.pervasive.com or call 1-800-BTRIEVE (1-800-287-4383).

You can also download information from ftp.pervasive.com.


Help Using Help Command

Context Help command

Use the Context Help command to obtain help on some portion of DDF Ease.  When you choose the Toolbar's Context Help button, the mouse pointer will change to an arrow and question mark.  Then click somewhere in the DDF Ease window, such as another Toolbar button.  The Help topic will be shown for the item you clicked.

Shortcut

Keys:	SHIFT+F1


Help menu commands

The Help menu offers the following commands:

Help Topics	Offers you an index to topics on which you can get help.
About	Displays the version number of DDF Ease.


How to add table columns

NOTE: You cannot add or drop table columns if you are running a Scalable SQL 3.x engine.

To add a table column:

1  	From the Main window, select a table to show the table information.

2  	Select the Columns tab.

3  	Use the Down Arrow key to add a new column definition, or click in the last column row and then press Tab to insert a new column. The new column is highlighted in blue.

4  	Name the new column. New column definitions remain highlighted in blue until they are saved. You are prompted to save changes when you try to click on another tab, select a different table, or try to exit DDF Ease.

NOTE:  When you add a column to an existing table, Scalable SQL always places the column at the end of the column list; the new column becomes the last column of your table. This is especially important if the table you are altering contains a variable-length column. Because a variable-length column must be the last column in a table, DDF Ease will not allow you to add more columns to a table that has a variable length column.

Related Topics

How to drop table columns

How to alter table columns

How to add table indexes

How to drop table indexes

How to restore table indexes

How to drop a table

How to create a table


How to add table indexes

NOTE:  You cannot add or drop table indexes if you are running a Scalable SQL 3.x engine.

To add a table index:

1  	From the Main window, select a table to show the table information.

2  	Select the Indexes tab.

3  	Use the Down Arrow key to add a new index definition, or click in the last index row and then press Tab to insert a new index. The new index is highlighted in blue. DDF Ease autogenerates a default index name, however, it is strongly recommended that you change this name to something that is unique and makes sense.

NOTE:  When you add an index, DDF Ease sets the case value on the index to the same case specified for the column indexed. However, it is your responsibility to ensure the ACS value is consistent with any other ACS values set for that column if the column is indexed.

If the index you are creating is a segmented index (an index on more than one field), enter the same index name for each index segment.  Each segment must be adjacent to another segment of the same index; in other words, enter segmented index entries consecutively. For example, you cannot have three rows with names index4, index5, then index4 because the two index4s are not adjacent.

4  	You are prompted to save changes (additions and deletions) when you try to click on another tab, select a different table, or try to exit DDF Ease. If you choose Apply to DDF and Data, the changes you make are applied to the actual data file. If you choose Save DDF, your changes are only made to the DDF not the data file, which means your data file and DDFs are out of sync.

Related Topics

How to add table columns

How to drop table columns

How to alter table columns

How to drop table indexes

How to restore table indexex

How to drop a table

How to create a table


How to alter table columns

NOTE: Before you make any changes to table columns, it is recommended that you backup your database.

You can change the name, data type, and size of any column in an existing Btrieve database.

1	Select the table name and then select the Column tab.

2	To change a column name, click in the specific text box of the column name you want to change.

3	To change a data type, click the specific list box of the data type you want to change.

4	To change a data type size, use the up or down arrows in the list box to change the size. When you change a data type size, the offset also adjusts according to the new size.

5	Make more changes to the column definitions or save your changes. Depending on your Btrieve data file and the changes you make, the Save dialog box could have any of the following settings:

If the Apply changes to Btrieve files is . . .	Then
Checked and enabled	your Btrieve file and DDFs are consistent (you can uncheck the check box)
Checked and disabled	it indicates that your database is bound and you have to make changes to the Btrieve file
Unchecked and disabled	your Btrieve file and DDFs are inconsistent
When making changes to column definitions, keep the following in mind:

When you change certain data types, you may receive a Status Code 850 when you try to save your changes. This is due to the Scalable SQL engine not allowing the data type conversion because it is logically impossible. You will not be able to save your change if you get this status code. You need to choose a different data type or change it back to the original.

If you are changing a column name, the Save dialog box warns you that it may affect stored procedures, triggers, or views.

Related Topics

How to add a table column

How to drop a table column

How to restore table indexes

How to add a table index

How to drop a table index

How to change a table location


How to change a table location

You can change the location of a table, however, this can have harmful effect. Use this feature with caution. It is recommended that you backup your database first.

To change a table location:

1	Select the table in the tree view.

2	Select the Statistics tab.

3	Click in the table location on the data grid and enter the new location.

Related Topics

How to add a table column

How to drop a table column

How to alter a table column

How to restore table indexes

How to add a table index

How to drop a table index


How to check a database

DDF Ease allows you to check if your database definitions are consistent with Scalable SQL 3.x  or Scalable SQL 4.x.

For many years Btrieve did not include a tool for creating DDFs and table definitions. Developers either created their own using Btrieve APIs or third-party tools. Thus, many existing DDFs contain definitions that are invalid or inconsistent with the expected dictionary definitions. However, DDFs need to be compliant with Pervasive's Scalable SQL and ODBC Interface products.

DDF Ease makes the following database checks:

Whether the table, column, and index names are ODBC compliant.

Whether the record length defined for a table matches the record length defined in the data file.

Whether the variable length column definition of a table matches that in the data file.

Whether there are indexes defined for the table that are not defined in the data file, and vice versa.

Whether the table index attributes match the index attributes defined in the data file.  Index attributes checked are: unique, modifiable,  nulls not indexed,  alternate collating sequence, case,  and sort order.

Whether the table index data type, offset, and length match the index data type, offset, and length defined in the data file

Whether there are any column data types that are not supported by the version of Scalable SQL you are checking against.

Whether there are illegal variable length field definitions.  Only one variable length field can be defined, and it must be defined last  and after a fixed length field.

Whether there are any overlapping column definitions.  For example, a column defined at offset 0 length 4 and a column defined at offset 2 length 10 overlap 2 bytes from offset 2 to offset 4.

Whether the system table definitions for X$File (file.ddf), X$Field (field.ddf), and X$Index (index.ddf) are as expected for the version of Scalable SQL you are checking against.

To check your database:


How to close a database

To close a database, select the database or one of its subfolders and then choose Close Database from the File menu.

Related Topics

How to Create a Database

How to Open a Database

How to Save a Database

How to Delete a Database


How to convert a database dictionary

DDF Ease enables you to convert a database dictionary from Scalable SQL 3.01 to Scalable SQL 4.0 mode and vice versa. Only the dictionary files FILE.DDF, FIELD.DDF, and INDEX.DDF are converted. If you are converting from Scalable SQL 3.x to 4.0 and your database has views, you need to run the View Conversion utility to convert your database views.

All users need to be logged out of the database before converting the dictionary for that database. Scalable SQL 3.01 dictionary system tables and Scalable SQL 4.0 dictionary tables have slight differences of data types for representing system columns and with index definitions. Refer to your Scalable SQL Reference for a complete description of the system tables for your version of Scalable SQL.

Scalable SQL 4.0 introduces some new data types (timestamp, unsigned, and currency) and features (triggers and stored procedures). If your database contains the new data types or features, you will not be able to convert your dictionary to Scalable SQL 3.01.

Your dictionary files must be in a Btrieve file format of version 6.x or greater. If your dictionary files are not in this format, set the Create File Version setting of the MicroKernel Database Engine to 6.x or higher and run the Rebuild utility on your dictionary files: file.ddf, field.ddf, and index.ddf. Keep in mind that the Create File Format setting affects all Btrieve and Scalable SQL applications that run on the server; all new files that are created will be of the file version you specify.

NOTE:  The Convert Database Dictionary function does not modify any non-system table Btrieve files.

To convert a dictionary:

1  	From the File menu, select Convert Dictionary. The Convert Dictionary dialog box is displayed.

2  	Enter the path of the database in the DDF Path field. Click the browse button to search for the specific database path.

3  	If security is enabled for the database, enter the valid password in the Master Password field.

4  	Specify which version of Scalable SQL to which you want to convert the database in the Dictionary Conversion pull-down list. Your choices are Compatibility with Scalable SQL 4.0 (default) or Compatibility with Scalable SQL 3.01.

5  	The Convert Dictionary actually modifies the files: FILE.DDF, FIELD.DDF, and INDEX.DDF. Before allowing DDF Ease to make these changes, it is recommended that you back up the original files to another directory so that you can restore your original DDFs if needed.

6  	Click the Backup to directory check box and provide a full directory path in the space provided.

7  	Click Next to begin the conversion. The progress of the operation is displayed in the Convert Dictionary Progress dialog box.

This dialog box displays status information that occurred during the conversion.

If your DDFs are not in a Btrieve 6.x format or higher, the conversion is aborted and no changes are made to the DDFs.


How to create DDFs for existing Btrieve files

Creating the Patients Database

This tutorial takes you through the process of creating data dictionary files for existing Btrieve files. In this tutorial, you are going to create a database, copy Btrieve files into the database directory, and then create table definitions for each Btrieve file. The Patients database includes the following tables:

Patients	The patients who go to the same doctor's office. The Btrieve data file is patients.dta.
Appointments	Appointments for each patient.  The Btrieve data file is patapp.dta.
Procedures	Procedures performed on each patient. The Btrieve data file is patproc.dta.
1  	To create the Patients database, click [image] on the toolbar or select New Database from the File menu. The New Database dialog box is displayed.

2  	If you are running DDF Ease from a Windows 95/NT client, enter the name of the remote directory where you want the database to reside in the DDF Path field and name it "Patients". Although optional, you can specify the directory where you want the data files to reside in the Data File Path field. Click OK to create the database. If the directory does not exist, you are prompted to create it. Click OK in the message pop-up dialog box.

The DDF Path is the directory where you want your data dictionary files (e.g., file.ddf, index.ddf, field.ddf) to reside. The Data File Path is the directory where your Btrieve data files (e.g., *.dta, *.mkd, etc.)  reside.

3  	Copy the following files from your c:\Pvsw\demodata\odbc directory (or the local directory that contains your Pervasive database products) to your Patients directory:

patients.dta

patapp.dta

patproc.dta

Create table definitions for each of Btrieve file.

1  	In the DDF Ease main window, click on the Patients database and then select Create from the Table menu. In the Create Table Wizard, Step 1 dialog box, enter "Patients" in the Table Name field and then use the Browse button to select the Patients.dta from the newly created Patients database directory. Click Next.

2  	A message is displayed indicating that your Btrieve file is in 6.0 format and your database dictionary 7.0 format. Click Yes to confirm this message. The Create Table Wizard, Step 2 - Patients dialog box is displayed.

Because you have created a table with an existing Btrieve file, DDF Ease can deduce some information about the data in the file. The dialog box displays index and column information. Specify the column names in the Columns Found fields using the information in the following figure. Notice that the newly typed in column names are displayed in the Indexes Found and the Column Data fields. Click Next when you have specified each column name. Notice the new names appear in the Column Data field after you enter them. For more information about this dialog box, click Help or press F1.

3	The Create Table Wizard, Step 3 - Patients dialog box is displayed. This dialog box directs you to specify column definitions for columns that have an "Unknown" data type.

Because Btrieve data files are strings of bytes and some contain indexes, you may want to view the data to decide whether to split the column. You can view the data by clicking in a column name that has a data type that you can change and then clicking Split Column to actually see the data in hex and ASCII formats. When you view the data in the Split Column dialog box, you may notice the data is hard to recognize. For this example, the only column you will split is the second Unnamed column (which you will later name Address). In the Split Column dialog box, you will notice that the data looks like address information: street address, city and state. So, you will want to split the column into three sections by marking the appropriate breaks. For this example, you are going to mark the "A" in Austin (position 26) and the "T" in TX (position 41). Click Split when you are finished. For more information about splitting columns, see Helpful Hints for Splitting Columns.

4  	Use the information in the table below to complete the column definitions.

Column Definitions

Column Name	Data Type	Offset	Size	Dec	Case Sens.
MiddleInitial	char	18	2		No
LastName	char	20	20		Yes
Address	char	40	25		No
City	char	65	15		No
State	char	80	2		No
Zip	char	82	10		Yes
Phone	char	92	12		No

5  	When you have finished naming the columns and specifying data types, click Create Table. The new table is displayed in the directory tree of the main window. The default view displays the Statistics tab. Click the Columns tab to view the table structure.

Repeat the procedures in the previous step to create tables for Appointments using the patapp.dta file. In the Create Table Wizard, Step 2 dialog box, name the columns as illustrated in the following table.

Column Definitions

Column Name	Data Type	Offset	Size	Dec	Case Sens.
ID	char	0	6		Yes
ApptDate	date	6	4		N/A
ApptTime	time	10	4		N/A
AMPM	char	14	4		Yes
Code	char	30	3		Yes

6  	Click Next. The Create Table Wizard, Step 3 dialog box is displayed.

7  	Notice that there are only two columns that are labeled "Unnamed" (columns 5 and 7). Before you can specify a data type, you may need to look at the data in the column. For column 5, click on its row and then select Split Column. You will see that the data contains last names, which means this is a char data type. Click Cancel on the Split Column dialog box, name the column "Doctor" and select char from the drop-down list. For column 7, look at the data in the Split Column dialog box. There is an obvious pattern of HEX 0F for all data at position 8. For the Appointments table, you will split the seventh column at the position 9. For more information splitting columns, see Helpful Hints for Splitting Columns.

8  	Name column 7 AmtPaid and specify it as money data type. For column 8, click Split Column to view the data. Because it's a four byte data type and the Hex values show a variation of numbers, it's a good candidate for a Date data type. Click Cancel, name this column "DatePaid," and specify it as a Date data type. Click Create Table to create the Appointments table.

Repeat the previous steps for the Procedures table, using the patproc.dta file. In the Create Table Wizard, Step 2 dialog box, name the column "Code" and then click Next.

9  	In the Create Table Wizard, Step 3 dialog box, click in the Unnamed_1 column and then click Split Column to view the data so that you can decide whether to split the column. When you view the data in the Split Column dialog box, you will notice the first 14 bytes of data are easy to recognize words (in this case, names of procedures). Split the column at position 16 as this is the start of a new character set and then click Split column. Notice there are now two columns, Unnamed_1 and Unnamed_2. Name Unnamed_1 "ProcedureName" and define it as a char data type. Because Unnamed_2 contains characters that are not printable as ASCII, it is not numeric, numericsa, or numericsts; however, in this example, it is money. Ensure the data in dialog box reflects the information in the following table.

Column Definitions

Column Name	Data Type	Offset	Size	Dec	Case Sens.
Code	char	0	3		Yes
ProcedureName	char	3	16		No
Price	money	19	7		N/A
10	Click Create Table.

Once you have created these three tables, you can display different views of the data. If you select Tables in the database directory tree, you see a summary of the three tables you created. Use the horizontal scroll bar to view more information. Click on a specific table name and then select a Tab (Statistics, Columns, Indexes, Data) to display more information.

Related Topics

How to create a database

How to create a table

How to create table for an existing Btrieve file

How to alter table columns

How to alter table indexes

Data Types


How to create a database

1 	From the File menu, select New Database or click [image]. The New Database dialog box is displayed.

2  	Enter the path of the DDFs (e.g., file.ddf, field.ddf or index.ddf) in the DDF Path field. Click the browse button to search for the specific path.

The current drive setting is the default directory location. If a database exists in the specified location, you are prompted to replace the database with an existing one (only the dictionary files will be replaced). If the path does not exist, you are prompted to create the new directory.

NOTE:  You can only create a database where the Pervasive Server engines reside. If your server engine is on M:\, you must create your database on M:. The example illustrated in the previous figure assumes the Server engine resides locally.

3  	Optional. Enter the path of the data file. If the file is not specified, the data path is the DDF path.

4  	Click OK to create a database. The new database is displayed in the DDF Ease database tree on the Main window.

DDF Ease creates FILE.DDF, FIELD.DDF and INDEX.DDF in the DDF path or overwrites these three existing DDFs if you replace an existing database.

Related Topics

How to create a table

How to create a table with an existing Btrieve file

How to open a database


How to create a relational database

This tutorial contains a quick tour of DDF Ease functions to create a simple relational database (using Scalable SQL 4.0) that keeps track of customer orders for a small company.  You are going to create the Customer Order database, which has four tables described as follows:

Customers	The customers that purchase the company’s products.
Orders	Orders placed by customers.
SalesReps	The sales representatives that take customer orders
SalesOffices	The sales office that the sales representative work
Example data to be stored in the Customer Order database

Creating the Order Entry Database

Creating Tables for the Order Entry Database

Adding Index Definitions

Addting Data to the Order Entry Database

Viewing Table Data

Retrieving Data Using Queries

Related Topics

How to create a database

How to create a table


How to create a table

There are two paths for creating tables in DDF Ease: 1) Creating a new table and table data file, and 2) Creating a table and specifying a table location for an existing Btrieve data file.

If you are creating a new table and data file, you must provide a table name. Although optional, you can  provide a file name which is the name of the data file for the table data. If you are adding relational access to a Btrieve data file, you must create a table definition for that file. Btrieve data files contain some information about indexed fields (i.e., position, length, and data type), index attributes (unique, modifiable, sort order, and so on), and record length.

DDF Ease uses this information to help you specify the column and indexes for the table.

Creating a new table

1  	Open or create a database.

2  	Ensure you have a database selected in the directory tree.

3  	From the Table menu, click Create. This menu item is not enabled unless you have a database selected.

The Create Table Wizard, Step 1 dialog box is displayed.

4  	Enter the name of the new table in the Table Name field and click Next.

NOTE:  If you are creating a table for a Btrieve 6.x or 5.x database, you may receive a message indicating that the configuration setting for Create File Version is set to Btrieve 7.0 file format. For example, Btrieve version 6.15 workstation engines cannot access files of a Btrieve 7.0 file format. If you are creating DDFs for a pre-7.0  Btrieve  and plan to access the database files with a pre-7.0 workstation engine, you must change this configuration setting to the appropriate Btrieve file version. Consult the User's Guide for information on setting this option in the Configuration utility.

The Create Table Wizard, Step 2 dialog box is displayed. This screen allows you to create columns and index definitions for your table.

5  	Define the columns for the new table. By default, the first column is created for you. Use Tab to navigate to the various fields in the Column Definition. When you reach the Case Sens. field and press Tab, a new column is created. You can also click Insert Column to create a new column. You need to be aware of the various rules for defining columns and working with data types.

6  	To create an index, click Insert Index. You navigate and define indexes in the same manner you did for defining columns.

NOTE:  The data type of a column must be a string data type such as CHAR, LSTRING, or ZSTRING to specify a collation sequence on the column. The collation sequence, such as case sensitivity or Alternate Collating Sequence, on an index must match the collation on a column. For example, if you have two index segments on column First_Name, the ACS value should be the same for both index segments. In addition, DDF Ease requires that the case value set on the column is Yes and will set the column case to Yes if you specify ACS.

7  	To segment the index, click Add Segment.

8  	Optional. If you want to specify physical file settings, click Advanced. The Advanced - Physical Data File Settings dialog box is displayed.

Specify the size allocations for the data file. If you specify an owner name, you must provide this exact name any time you want to access this data file. DDF Ease does not store this information in a user accessible file.

9	 When you are finished specify the table definitions, click Create Table.

The new table with associated columns and indexes is displayed in the table directory tree on the Main window. You can click a specific tab to view the table information.

Related Topics

How to create DDFs for an existing Btrieve file (Tutorial)

How to display system tables

How to display table data, definitions, and statistics


How to create a table for an existing Btrieve file

If your database is not a named, bound database, you can create table definitions for existing Btrieve files that do not have overlapping columns. Overlapping columns, exist if you have column indexes that overlap the same bytes in the record (for example, index on position 1 length 10 overlaps index on position 5 length 10).

Creating a table with an existing data file

1  	Open or create a database.

2  	Ensure you have a database selected in the directory tree.

3  	From the Table menu, click Create. This menu item is not enabled unless you have a database selected.

The Create Table Wizard, Step 1 dialog box is displayed.

4  	Enter the name of the table in the Table Name field.

5  	Enter its name in the Table Location field. Click Browse to search for the table location.

When you enter the Table Location, it is recommended that you provide a relative path and the file name. The Create Table Wizard, Step 2 dialog box is displayed.

DDF Ease helps you deduce the definitions of columns and indexes from the Btrieve index statistics of the file (run BUTIL -stat filename to view the statistics) and from the data within the file itself. The first 10 rows of data are displayed to assist you in providing a logical column name. The display filter of the data depends on the data type.

6  	When the dialog box is displayed, the default column names are labeled "Unnamed." Provide names for known columns by double-clicking in the specific Column Name field and entering the new name.

When you name the columns, you must click outside of the Column Name cell to view the new names in the Column Data field.

NOTE:	It is recommended that you do not define columns as Case Sensitive.

7  	C	lick Next. The Create Table Wizard, Step 3 dialog box is displayed.

This dialog box allows you assign the data type for non-indexed columns by allowing you to view the first several rows of data and "split" the column according to the data you see.

8  	You must provide the data type for columns where "Unknown" is displayed in the Data Type field. In most cases, you need to press Split Column to view data in Hex and ASCII format and then determine whether to split the column. You must click in the Column name cell of a column that can be split. Columns that can be split have pull-down menus for the data type and are the color of your system window (usually white). The Split Column dialog box is displayed.

You may want to view the Data Types section for a better understanding of the internal storage representation of data types.

9  	Split the column as needed and then click Split. See Helpful Hints for Splitting Columns when you do not know the internal record structure.

10 	Continue the process of splitting columns as needed. You can undo the effect of splitting a column by merging two or more columns. To merge column, click in the column field and then hold down Shift as you click in the other columns you want to merge.  Click Merge.

11 	When you have complete defining columns, click Create Table. The new table appears in the database directory tree in the main window.

Related Topics

How to create a relational database


How to delete a database

To delete a database, select the database and then choose Delete Database from the File menu. This will first close the database and then delete it. You cannot delete a database that has files in use. Files are in use if another application or user has the database open.

NOTE: Only your DDFs are deleted (INDEX.DDF, FILE.DDF, FIELD.DDF, etc.); your data files will not be deleted.

Related Topics

How to Create a Database

How to Open a Database

How to Close a Database


How to display system tables

You can view system tables after you have opened or created a database. The minimum set of standard tables are: X$Field, X$Index, and X$File. Other system tables are created automatically when you define users, views, and so on.

To display the system tables, select Show System Tables from the Table menu.

Related Topics

How to Create a Table

How to Open a Database


How to display table data, definitions, and statistics

Display Table Data

1	Select a table in the main database tree.

2	In the view screen on the right, select the Data tab.

Only the first 100 rows of data are displayed with this release of DDF Ease.

Display Table Definitions

1  	Select a table in the main database tree.

2  	Click the Columns or Indexes tab to view the full column or index definition, respectively.

3  	To view just list of columns or indexes, expand those sub folders in the main tree view.

Display Table Statistics

1  	Select a table in the main database tree.

2  	Select the Statistics tab. This information is similar to what you use the command-line Btrieve Maintenance utility (BUTIL) and perform a BUTIL -stat.

Related Topics

How to create a table


How to drop a table

CAUTION:  Dropping a table removes the table definition from the database and deletes the data file. If you want to drop a table definition and retain your data file, first copy your data file to another location.

To drop a table:

1  	Open the database from which you want to drop the table (unless it is already open).

2  	In the database tree, select the specific table name.

3  	From the Table menu, select Drop. A dialog box is displayed, warning you that your table definition and corresponding data file will be deleted.

4  	Click Yes to drop the table.

Related Topics

How to create a table

How to create a table for an existing Btrieve file

How to alter table columns

How to add table columns

How to drop table columns

How to restore table indexes

How to add table indexes

How to drop table indexes


How to drop table columns
To drop a table column:

1  	Select a row by pointing the cursor on the box to the left of the column row so that it turns into a right facing arrow. Click the mouse to select the row.

2  	Press Delete. The row is deleted. However, it is not permanently deleted until you save your changes.

3  	You are prompted to save changes (additions and deletions) when you try to click on another tab, select a different table, or try to exit DDF Ease. If you do not want to delete a column that has been selected for deletion, select Abort Changes. If you choose Apply to DDF and Data, the changes you make are applied to the actual data file. If you choose Save DDF, your changes are only made to the DDF not the data file, which means your data file and DDFs are out of sync.

Related Topics

How to add table columns

How to alter table columns

How to add table indexes

How to drop table indexes

How to restore table indexes

How to drop a table

How to create a table


How to drop table indexes
To drop a table index:

1  	Select a row by pointing the cursor on the box to the left of the index row so that it turns into a right facing arrow. Click the mouse to select the row.

2  	Press Delete. However, it is not permanently deleted until you save your changes.

3  	You are prompted to save changes (additions and deletions) when you try to click on another tab, select a different table, or try to exit DDF Ease. If you do not want to delete a column that has been selected for deletion, select Abort Changes. If you choose Apply to DDF and Data, the changes you make are applied to the actual data file. If you choose Save DDF, your changes are only made to the DDF not the data file, which means your data file and DDFs are out of sync.

Related Topics

How to add table columns

How to drop table columns

How to alter table columns

How to add table indexes

How to restore table indexes

How to drop a table

How to create a table


How to open a database

1  	From the File menu, select Open Database, or click [image] from the toolbar. The Open Database dialog box is displayed.

2  	Enter the path of the DDF in the DDF Path field. Click the browse button to search for the specific path.

The current drive setting is the default directory location. You can enter multiple paths separated by a semicolon.

3  	Optional. Enter the path of the data file(s). If you do not specify a path, DDF Ease uses the DDF path as the data path. You can enter multiple paths separated by semicolons if your data files are in more than one directory. DDF Ease will only look in these directories for data files.

4  	Optional. Enter the user name and password in the Security field if the security is enabled on the database.

5  	If you have Btrieve files with owner names set, specify the owner name and password. You can also enter every owner name separated by a comma in the Btrieve File Owner Names field.

If you do not provide an owner name at this time for a Btrieve file that has an owner name set, you will be prompted for the table owner name when you try to view table definitions. You will not be able to view statistics and data without the owner name.

6  	Click Open Database. The database and associated tables are displayed in the Main window.

Related Topics

How to close a database

How to save a database


How to print a table definition

You can print the following table definitions:

table statistics

column definition

index definition

To print the information, select the tab and then choose Print from the File menu. This command displays a Print dialog box, where you may specify the range of pages to be printed, the number of copies, the destination printer, and other printer setup options.

Shortcuts

Toolbar:

Keys:	CTRL+P

Related Topics

Print Preview


How to restore table indexes

If your dictionary columns match the data file's fields but your dictionary indexes are out of synch with your data file's keys, you may need to restore the indexes for a specific table or tables.

NOTE: Btrieve files have keys, whereas data dictionaries have indexes.

To restore a table's indexes:

1	Select the the specific table in the tree view.

2	Select Restore Dictionary Indexes from the Table menu.

3	Select Yes to convert the indexes.

Related Topics

How to add a table column

How to drop a table column

How to alter table columns

How to add a table index

How to drop a table index

How to check a database


How to save a database

To save any changes you made to your database, select Save Database from the File menu. If you make changes to a database, such as add or remove an index, and then you select another Tab on the main window, you are prompted to save your changes first.

Related Topics

How to Create a Database

How to Open a Database

How to Close a Database

How to Delete a Database


How to set database security

To enable database security:

1  	Open a database.

2  	From the File menu, select Set Database Security. The Set Database Security dialog box is displayed.

3  	If the database security is currently disabled, the default option is to enable it as indicated in the figure. To enable security, you must provide a master password and confirmation password before you click OK.

If the database security is currently enabled, the default setting is to disable it. Click OK to disable. If you disable database security, all permission to the tables is removed.

Related Topics

How to open a database


MOD
Determines whether you can modify index column values after Scalable SQL stores the corresponding row. By default, scalable SQL does not allow changes to index column values once Scalable SQL stores the row.


Main Window

File Menu

Edit Menu

Table Menu

View Menu

Help Menu

Related Topics

How to Create a Database

How to Check a Database

How to Convert a Database Dictionary

How to Delete a Database

How to Open a Database

How to Create a Table


NULL
Determines whether Scalable SQL includes columns that contain the predefined null value in the index structure. By default, Scalable SQL includes null values in the index structure.


Print Preview

This command allows you to view the table information you are currently viewing before you print it. You can also zoom in or out. Click Print to send the document to your default printer.

Print Preview toolbar

The print preview toolbar offers you the following options:

Print

Bring up the print dialog box, to start a print job.

Next Page

Preview the next printed page.

Prev Page

Preview the previous printed page.

One Page / Two Page

Preview one or two printed pages at a time.

Zoom In

Take a closer look at the printed page.

Zoom Out

Take a larger look at the printed page.

Close

Return from print preview to the editing window.


Print Setup dialog box
Print Setup

The following options allow you to select the destination printer and its connection.

Printer

Select the printer you want to use.  Choose the Default Printer; or choose the Specific Printer option and select one of the current installed printers shown in the box.  You install printers and configure ports using the Windows Control Panel.

Orientation

Choose Portrait or Landscape.

Paper Size

Select the size of paper that the document is to be printed on.

Paper Source

Some printers offer multiple trays for different paper sources.  Specify the tray here.

Options

Displays a dialog box where you can make additional choices about printing, specific to the type of printer you have selected.

Network...

Choose this button to connect to a network location, assigning it a new drive letter.


Print dialog box
Print

The following options allow you to specify how the document should be printed:

Printer

This is the active printer and printer connection.  Choose the Setup option to change the printer and printer connection.

Setup

Displays a Print Setup dialog box, so  you can select a printer and printer connection.

Print Range

Specify the pages you want to print:

All	Prints the entire document.
Selection	Prints the currently selected text.
Pages	Prints the range of pages you specify in the From and To boxes.
Copies

Specify the number of copies you want to print for the above page range.

Collate Copies

Prints copies in page number order, instead of separated multiple copies of each page.

Print Quality

Select the quality of the printing.  Generally, lower quality printing takes less time to produce.


Retrieving Data Using Queries

After you populate your tables with data, you can execute statements in SQLScope to select data.  For example, if you want to view the list of all sales for the product "CMD-101" the select statement is:

SELECT * FROM Orders WHERE Product='CMD-101'

Please refer to your Scalable SQL manuals for information on how to create more complex queries. For more information about SQLScope, refer to the Pervasive User's Guide (Server Edition).


Rules for Defining Columns

Some of the fields in the column definition are drop-down lists and others are text fields. DDF Ease follows rules for both ODBC and Scalable SQL. You must use standard naming conventions when you name a column; do not use a reserved word like Currency, Date, etc. Use valid characters such as:

  a through z

  A through Z

  0 through 9

  ^ (caret)

  _ (underscore)

  ~ (tilde)

  $ (dollar sign)

NOTE:  The name of a database component must begin with a letter. If you specify the name of a database component or a parameter name that does not follow these conventions, specify the name in double quotes (such as "name").

For more information concerning name standards, refer to the Scalable SQL Database Design Guide and the "Scalable SQL Keywords" section in the Scalable SQL Reference Guide.

Also, depending on the version of Scalable SQL you are using, the supported data types for the particular version appear in the Data Type drop down list. Refer to Scalable SQL Data Types for a complete list of supported data types.

In terms of data types, the total number of fixed-length and variable-length data types cannot exceed 32,765 bytes. You cannot add a table column with a fixed-length data type after a column that has a variable-length data type. DDF Ease will move the column with the variable-length data type to the the end of the column list.


SORT
Determines how Scalable SQL sorts index column values. By default, Scalable SQL sorts index column values in ascending order (from smallest to largest).


Scalable SQL Data Types
Data Types

Scalable SQL uses several data types that are divided into two categories: fixed-length and variable-length. All the Scalable SQL data types are fixed-length except NOTE and LVAR, which are variable length. Scalable SQL 4.0 uses three data types that are not supported in earlier versions of the product: CURRENCY, TIMESTAMP, and UNSIGNED. Also, INTEGER has a length of 8, which is an increase from Scalable SQL 3.x.

Fixed-length data types have a designated stored length that does not vary from row to row within a table. Each table must contain at least one column that has a fixed-length data type and the fixed-length column sizes must combine to at least 4 bytes.

Variable-length data types have a stored length that can vary from row to row in a table, and is limited only by the amount of data the MicroKernel can store. A table or view cannot contain more than one column that has a variable-length data type.

You must specify a data type for a column anytime you create or alter a column definition. Following are the types of statements with which you can create or alter column definitions:

CREATE TABLE

ALTER TABLE

Table 1 lists the code, default display mask, and default input format for each data type. For more information about masks, refer to the Scalable SQL Database Design Guide. Table 2 lists the default length, valid length, and valid value range for each data type.

Scalable SQL uses the data type codes (rather than the keywords) internally when you create a table. These codes also appear in the X$Column system table, and you can use them to determine the data type of a particular column.

Fixed-Length Data Types

The stored length of a fixed-length data type does not vary from row to row within a table. The Scalable SQL fixed-length data types correspond to the most useful data types that many programming languages recognize. The following sections discuss each Scalable SQL fixed-length data type.

AUTOINC

The AUTOINC data type represents a special form of signed 2 or 4-byte integers. If you specify an AUTOINC column value of binary zeros when inserting a row, Scalable SQL replaces the specified value by automatically incrementing the highest existing value in the column and using the result value in the row you are inserting. Specify a non-zero value only when replacing or updating a row within the existing order.

To automatically increment values in an AUTOINC column, define the column as a unique index. An auto-increment index can only be part of a segmented index if the index number of the auto-increment index is less than the index number of the segmented index. If you do not define the column as an index, Scalable SQL treats it as an INTEGER column.

BFLOAT

The BFLOAT data type represents either a single-precision (4-byte) or a double-precision (8-byte) real number stored in a format that the Microsoft BASIC programming language defines. A single-precision real number is stored with a 23-bit mantissa, an 8-bit exponent biased by 128, and a sign bit.

The internal layout for a 4-byte BFLOAT column is as follows:

The representation of an 8-byte BFLOAT column is the same as that for a 4-byte BFLOAT column except the mantissa is 55 bits instead of 23 bits. The least significant 32 bits are stored in the first 4 bytes.

NOTE:  In the same way that some values (such as 1/3) cannot be expressed accurately in decimal terms, some values (such as 1/10) cannot be stored accurately in FLOAT or BFLOAT format. Therefore, when storing values that represent precise numbers (such as monetary values), use the CURRENCY, DECIMAL, MONEY, NUMERIC, NUMERICSA, NUMERICSTS, or UNSIGNED data types instead. Restrictions using float values may be more effective using a range (such as column >= 1.501 and column <= 1.502) rather than an equality (such as column = 1.501).

BIT

The BIT data type is a 1-byte value that allows Scalable SQL to compress logical values into bit flags. The column values of adjacent BIT columns are packed into as few bytes as possible; up to eight values can be stored in a single byte. However, if two BIT columns are separated by one or more non-bit columns, the BIT values cannot be stored in the same byte.

For example, if a table contains two adjacent BIT columns, their values are packed into a single byte. If a third BIT column in the table is preceded by a non-bit column, the value of the third BIT column cannot be stored in the byte containing the other two BIT column values.

NOTE:  A column of the BIT data type may not be defined as an index or index segment.

CHARACTER

The CHARACTER data type in Scalable SQL represents a sequence of characters ordered from left to right, of fixed length, and does not have a terminator character. Each character is represented in ASCII format in a single byte. Scalable SQL pads CHARACTER values with blanks to the defined length of the column.

In comparisons between CHARACTER values and constants, Scalable SQL pads constants with blanks to the length of the value. Trailing blanks in a CHARACTER value are considered significant for comparisons in restriction clauses and expressions and when the LENGTH function returns the length of a string column. Use the LTRIM, RTRIM, or SUBSTRING function to ensure that the lengths of strings you compare are the same.

CURRENCY

The CURRENCY data type represents an 8-byte signed quantity, sorted and stored in Intel binary integer format; therefore, its internal representation is the same as an 8 byte INTEGER data type. The CURRENCY data type has an implied four digit scale of decimal places, which represents the fractional component of the currency data value.

DATE

Column values of data type DATE are stored internally as 4-byte values in Intel integer format. Day and month values are each stored in 1-byte binary format. Year values are stored as 2-byte binary numbers. The day is stored in the first byte, the month in the second byte, and the year in the two bytes following the month.

The internal layout for a DATE column is as follows:

DECIMAL

Column values of data type DECIMAL are signed or unsigned real numbers stored internally as packed decimal numbers, with two decimal digits per byte. This format is consistent with the COMP3 data type in ANSI74 standard COBOL programming language.

For DECIMAL values, each digit takes one half byte of storage and the sign takes one half byte. The internal size is M/2 + 1, where M  is the number of significant displayable digits. Therefore, any DECIMAL value that has an even number of displayable digits has an extra one half byte of internal storage.

The internal representation for an n-byte DECIMAL column is as follows. (The high-order bit is on the left side.)

The sign digit is either C for positive numbers and D for negative numbers. The decimal point is not stored with the data; its location in the column value is fixed according to the column definition in the dictionary.

Example

The DECIMAL column value 12345678.1234 requires a minimum of 7 bytes (12/2 + 1). The internal storage format for the value is as follows:

The DECIMAL column value 123456789.1234 also requires a minimum of 7 bytes (13/2 + 1). The internal storage format for the value is as follows:

FLOAT

The FLOAT data type is consistent with the IEEE standard for single and double-precision real numbers. The internal format for a 4-byte FLOAT consists of a sign bit, an 8-bit exponent biased by 127, and a 23-bit mantissa. The internal layout for a 4-byte FLOAT value is as follows:

A FLOAT data type column with 8 bytes has a sign bit, an 11-bit exponent biased by 1023, and a 52-bit mantissa. The internal layout for an 8-byte FLOAT value is as follows:

NOTE:  In the same way that some values (such as 1/3) cannot be expressed accurately in decimal terms, some values (such as 1/10) cannot be stored accurately in FLOAT or BFLOAT format. Therefore, when storing values that represent precise numbers (such as monetary values), use the CURRENCY, DECIMAL, MONEY, NUMERIC, NUMERICSA, NUMERICSTS, or UNSIGNED data types instead. Restrictions using float values may be more effective using a range (such as column >= 1.501 and column <= 1.502) rather than an equality (such as column = 1.501).

INTEGER

The INTEGER data type represents a signed or unsigned whole number. Internally, INTEGER values are stored in Intel binary integer format. The 1 byte INTEGER uses the character extended data type and the 2, 4, and 8 byte INTEGER use the unsigned extended data type when indexed.

INTEGER columns store values in the following ranges:

Length in Bytes	Value Ranges
1	0 - 255
2	-32768 - 32767
4	-2147483648 - 2147483647
8	-9223372036854775808 - 9223372036854775807
LOGICAL

The LOGICAL data type is stored as a 1 or 2 byte binary value representing either the logical value true or the logical value false. In an INSERT statement, a value of 1 or TRUE are acceptable input values for a true value. A value of 0 or FALSE are acceptable input values for a false value. These input values are valid when the default masks are set for this LOGICAL column. When a user-defined mask is set on a LOGICAL column, the insert values must match the mask values.

LSTRING

The LSTRING data type in Scalable SQL corresponds to a Pascal string. It has the same characteristics as the CHARACTER data type except the first byte of the string contains the binary representation of the string’s length. The length of each value must be 0 for column size -1. Bytes beyond the specified end of the string are undefined.

An LSTRING value may contain significant blanks at the end of the string. The length byte indicates the true length.

Example

For an 8-byte LSTRING column, the value Hello is stored as follows:

MONEY

The data type MONEY has the same characteristics as the DECIMAL data type except the number of decimal digits is fixed at two. The internal size requirements are the same as for the DECIMAL data type. By default, Scalable SQL displays MONEY column values with a dollar sign, which increases the display width by one.

NUMERIC

The total display size required for a NUMERIC column value is equal to the maximum number of significant digits, plus 1 for a decimal point and 1 for a possible sign. Column values of the NUMERIC data type are signed or unsigned real numbers stored internally as ASCII strings that are right justified and padded with leading zeros. Each digit occupies 1 byte internally. The rightmost byte of the number can include an embedded sign, which does not require any additional space. Thus, the internal size of a NUMERIC value is equal to the number of significant digits.

Table A-3 indicates how the rightmost digit is represented when it contains an embedded sign for positive and negative numbers.

Table 3	Numeric Embedded Sign Characters

Number	Positive	Negative
1	A	J
2	B	K
3	C	L
4	D	M
5	E	N
6	F	O
7	G	P
8	H	Q
9	I	R
0	{	}
For positive numbers, you can represent the rightmost digit with either 1 through 0 or A through { . When Scalable SQL returns positive numbers, the rightmost digit is always represented by 1 through 0. Scalable SQL accepts input from either range.

Example 1

The NUMERIC column value 12345678.4567 requires a minimum of 12 bytes. The storage format is as follows:

The value 47 in the last byte represents the embedded sign character G, a display value of 7 and a positive sign for the column value.

Example 2

The NUMERIC column value -12345678.4567 also has an internal size of 12 bytes. The storage format is as follows:

The value 50 in the last byte represents the embedded sign character P,  a display value of 7 and a negative sign for the column value.

NUMERICSA

The total display size required for a NUMERICSA column value is equal to the maximum number of significant digits, plus 1 for a decimal point and 1 for a possible sign. Column values of the NUMERICSA data type are signed or unsigned real numbers stored internally as ASCII strings that are right justified and padded with leading zeros. Each digit occupies 1 byte internally. The rightmost byte of the number can include an embedded sign, which does not require any additional space. Thus, the internal size of a NUMERICSA value is equal to the number of significant digits.

A NUMERICSA column is consistent with a numeric signed ASCII column in COBOL. The NUMERICSA data type is identical to the NUMERIC data type except it uses a different embedded sign for negative values.

Table 4 indicates how the rightmost digit is represented when it contains an embedded sign for negative numbers.

Table 4	Numericsa Embedded Sign Characters

Number	Positive	Negative
0	p	p
1	Q	q
2	R	r
3	S	s
4	T	t
5	U	u
6	V	v
7	W	w
8	X	x
9	Y	y
For positive numbers, you can represent the rightmost digit with either 0 through 9 or P through Y. When Scalable SQL returns positive numbers, the rightmost digit is always represented by 1 through 0. Scalable SQL accepts input from either range.

NUMERICSTS

The total display size required for a NUMERICSTS column value is equal to the maximum number of significant digits, plus 1 for a decimal point and 1 for a possible sign. The NUMERICSTS data type is consistent with a numeric sign trailing separate column in COBOL. NUMERICSTS values are signed or unsigned real numbers stored internally as ASCII strings that are right justified and padded with leading zeros. Each digit occupies 1 byte internally.

Unlike the NUMERIC data type, the NUMERICSTS data type does not embed the sign. Instead, the sign is stored in its own byte. Since the sign uses one byte, the number of digits your application can place in a NUMERICSTS column is one less than the length you specify when you create the table.

The sign is stored in the rightmost byte. Thus, the internal size for a NUMERICSTS column value is equal to the number of significant digits, plus one byte to store the sign.

Example

The NUMERICSTS column value 12345678.4567 requires a minimum of 13 bytes. The storage format is as follows:

The value 2B in the last byte indicates a positive sign for the column value.

TIME

Column values of data type TIME are stored internally as 4-byte values. Hundredths of a second, seconds, minutes, and hours are each stored in 1-byte binary format. The hundredths of a second value is stored in the first byte, followed by the second, minute, and hour values.

The internal layout for a TIME column is as follows:

TIMESTAMP

The TIMESTAMP data type represents a time and date value. You use this data type to stamp a record with the time and date of the last update to the record. The column value is stored in 8-byte unsigned values representing septa seconds (10-7 second) since January 1, 0001 in a Gregorian calendar.

You specify the TIMESTAMP data type in the following format using a CREATE TABLE or ALTER TABLE statement:

TIMESTAMP [ (timestamp precision) ]

The timestamp precision is the number of fractional digits in seconds. Valid values of precision are between 0 and 7 inclusive. The following table indicates the valid values of each component of TIMESTAMP:

YEAR	0001 to 9999
MONTH	01 to 12
DAY	01 to 31, constrained by the value of MONTH and YEAR in the Gregorian calendar.
HOUR	00 to 23
MINUTE	00 to 59
SECOND	00 to 59
The value is stored internally as a TIMESTAMP MicroKernel key, an 8-byte long field that contains the complete date and time value converted to the fractions of seconds that timestamp precision specifies. For example, it is converted to microseconds when timestamp precision is 6, and it is converted to milliseconds when timestamp precision is 3. You provide the value of a TIMESTAMP in local time and the Scalable SQL engine converts it to Coordinated Universal Time (UTC), formerly Greenwich Mean Time (GMT), before storing it in a MicroKernel record. When you request the value of a TIMESTAMP, Scalable SQL converts it from UTC to local time before returning the data.

NOTE:	It is critical that you set time zone information correctly. If you move across time zones or change time zone information, the returned data will change when it is converted from UTC to local time. The local time/UTC conversions occur in Scalable SQL using the time zone information where the Scalable SQL engine is running. The time zone information for sessions that are in different time zones than the Scalable SQL engine are not used in the local time/UTC conversions.

UNSIGNED

The UNSIGNED data type represents an unsigned quantity of 1, 2, 4, or 8 bytes. Internally, INTEGER values are stored in Intel binary integer format. The 1 byte UNSIGNED uses the character extended data type and the 2, 4 and 8 byte UNSIGNED use the unsigned extended data type when indexed.

Following are the values each column accepts:

Length in Bytes	Value Range
1	0 - 255
2	0 - 65535
4	0 - 4294967295
8	0 - 18446744073709551615
ZSTRING

The ZSTRING data type in Scalable SQL corresponds to a C string. It has the same characteristics as the CHARACTER data type except it is terminated by a byte containing a binary 0. The length of each value must be 0 for column size -1. Bytes beyond the specified end of the string are undefined. A ZSTRING value may contain significant blanks immediately preceding the termination character.

Example

For an 8-byte ZSTRING column, the value Hello is stored as follows:

Variable-Length Data Types

Scalable SQL allows you to define columns of variable length using the NOTE and LVAR data types. Columns of data types NOTE and LVAR can contain any type of data, and the column values can vary in length from row to row within a table; they are limited only by the maximum amount of data that the MicroKernel is able to store in a data file record.

Five restrictions apply when you use a variable-length column in a table:

You cannot insert data in a variable-length column if the data is longer than the length you defined for the column in the dictionary definition. The size of a column is stored in a 2-byte integer value. You can specify sizes up to 32767, or use the default size, which is unlimited.

Developers only: If you specify an unlimited size, you must use the chunk operations to fetch or update existing rows. To interact with data in an unlimited sized column, use the buffer format described in the Scalable SQL Programmer’s Guide.

You cannot include more than one variable-length column when you create a table or a view.

If you include a variable-length column in a table or a view, you must define the column as the last column in the table or view.

You cannot define a variable-length column as an index or sort column.

A table or a view must contain at least one fixed-length data type.

Data from a variable-length column is always specified and returned in internal format.

LVAR

The maximum record length in Scalable SQL is 32,765 bytes. The data you insert into an LVAR column must not exceed the column length you specify. A record can contain a variable length field defined as the last column; the maximum column length is 32,765 bytes minus the length of any fixed-length data.

The LVAR data type uses length words in conjunction with an end-of-column delimiter. Each variable-length data segment in the column is preceded by a 2-byte length word that indicates the length of that segment. The end-of-column delimiter is always 2 bytes of binary zeros.

An LVAR column with multiple data segments is structured as follows:

NOTE

The maximum record length in Scalable SQL is 32,765 bytes. The data you insert into a NOTE column must not exceed the column length you specify. A record can contain a variable length field defined as the last column; the maximum column length is 32,765 bytes minus the length of any fixed-length data.

The NOTE data type uses a binary zero as a delimiter to denote the end of the column value. To determine the length of a NOTE column value, Scalable SQL scans from the end of the value until it reaches the delimiter. It then scans until it reaches a non-delimiter. The last delimiter immediately after the non-delimiter is interpreted as the end of the data.


Scalable SQL v4.0 Standard DDFs

There are three standard dictionary files that are created when you create a new dictionary:

Dictionary File	Table Name	Description
FILE.DDF	X$File	Contains the names and locations of the tables in your database.
FIELD.DDF	X$Field	Contains the field and named index definitions. (A field is the Btrieve term for a table column.)
INDEX.DDF	X$Index	Contains the index definitions.
Please refer to the Scalable SQL Reference for additional details about data dictionary files.


Send product suggestions

We encourage and welcome any feedback you have about DDF Ease. Please send your comments to techsupport@pervasive.com or fax them to Pervasive Customer Support at (512) 794-1778. Make sure to put "DDF Ease Product Comments" in the Subject field of your e-mail or fax.


Software and System Requirements

DDF Ease is installed as part of the default Pervasive Win32 Client installation. This utility requires that you have ODBC Interface 2.5 installed, which is also a part of the default client installation. However, if you choose to uninstall ODBC, you will not be able to run DDF Ease.

Server Settings

We recommend that you use the default Pervasive components on both the client and server with DDF Ease.  However, you can use the Scalable SQL 3.01 engine, with the limitation that DDF Ease will not support adding and removing table columns and indexes.  Other configurations, such as using a remote Btrieve 6.15 server engine with a Pervasive client, should work but have not been tested.  The Btrieve file format of the DDF files must be 6.x or higher, but the table data files can be of pre-6.x Btrieve file formats.

Btrieve 7.0 engines are backward compatible with previous file format versions. To modify the Btrieve file version, use the Win32 Setup utility that is included with your Pervasive product and configure the following setting:

MicroKernel Database Engine : File Settings : Create File Version : 7.x | 6.x

Choose 7.x as the setting if your database is of Btrieve 7.x file format, and choose 6.x if your Btrieve database is of 6.x format.

WARNING!   Be aware that the "Create File Version" setting affects all applications running on the server. If your production applications require that your server is configured for 6.x and you need to create DDFs for 7.x, you will need to either (1) schedule a time to change the setting and restart your server for the DDF Ease work and then reset it when you've finished, or (2) install the appropriate software on a non-production server and do the work there so that there are no side affects from production applications and the Create File Version setting.


Split Column dialog
Split Column

This dialog box displays each byte of data in both a hex and ASCII format. If the byte of the data is a non-ASCII character, the ASCII character displays as a dot (.).  If you know the internal record structure, use this knowledge to split the column with the appropriate length. After splitting the column, change the data types to those defined in your record structure.

Helpful Hints for Splitting Columns

When You Don't Know the Internal Record Structure

1  	Look for data that is in an easy-to-identify storage representation.

Some data types, such as string and numeric, store data in such a way that it is easily recognizable when viewed in an ASCII format.

Consider the following column data in the person.mkd file. This column data is stored in some sort of "string" format as indicated by the data. The data type zstring requires a null (0) terminator at the end of the string, whereas character data does not and is typically padded with blanks (HEX 20) to fill out the full length of the column. The column below ends with a 0, which indicates that it is a zstring and should be split at position 12.

Consider the following column to be split in person.mkd. Here we can recognize the data as phone numbers that should be split into columns at position 109 and at position 119.

Consider the following column data in the student.mkd file. This column data is stored in a numeric format as indicated by an ASCII representation of each digit, and the last byte contains some sort of "sign" indicator. Depending on whether this is a numeric, numericsa, or numericsts, the sign indicator will change but the basic "pattern" remains the same.

2  Look for beginnings and ending of similar patterns.

Sometimes you can choose where to split a column by looking at the patterns of data within the column when the data type is not easy to recognize. Consider the following example. In this example, positions 136-138 are ASCII blanks and HEX 20, probably part of a character text field that has a beginning position before position 136. The data in position 139 is either a 0 or a 1, which makes it a good candidate for a logical or bit data type. Position 144 is clearly the beginning of some sort of string field. Because there are 4 bytes left "unknown" in positions 140-143 that have unprintable ASCII characters in it, a good initial guess is that this is some sort of integer or unsigned data type field of size 4.

The previous example is easy to understand compared to the following example. The next screen requires additional thought in terms of how the data is stored and how to split the column correctly. There is an obvious pattern of HEX 0F for all data at positions 6 and 12. Because the data is mostly non-printable ASCII characters, positions 4 and 12 are probably a "sign" byte for a "number" data type. Because the characters are not printable as ASCII, it is not numeric, numericsa, or numericsts; however, decimal and money have a sign byte at the right half of the right most byte that is a HEX F or HEX C if the value is positive, and a HEX D if the value is negative. So, we split the column at position 7 for two decimal or money fields, each of length 6.

Related Topics

Data types

How to create a table for an existing Btrieve file


Table 1: Data Types

Table 1	Data Types, Codes, Default Masks, and Default Input Formats

Data Type	Code	Default Display Mask	Default Input Formats
AUTOINC	15	- ZZZZZ	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
BFLOAT	9	- Z.ZZZZZZE+ZZ	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
BIT	16	true - false	true -false 1 -0
CHARACTER	0	none	none
CURRENCY	19	- $ZZZZZZZZZZZZZZZ.ZZZZ	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
DATE	3	mm/dd/yy	mm/dd/yy mm/dd/yyyy mm/d/yy mm/d/yyyy m/dd/yy m/dd/yyyy m/d/yy m/d/yyyy yyyy-mm-dd
DECIMAL	5	- ZZZZZZZZZZZ.ZZZ	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
FLOAT	2	- Z.ZZZZZZE+ZZ	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
INTEGER	1	-ZZZZZ1	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
LOGICAL	7	true -false	true -false 1 -0
LSTRING	10	none	none
LVAR	13	none	none
MONEY	6	- $ZZZZZZZZZ.ZZ1	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
NOTE	12	none	none
NUMERIC	8	- ZZZZZZ.ZZZ2	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
NUMERICSA	18	- ZZZZZZ.ZZZ2	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
NUMERICSTS	17	-ZZZZZ.ZZZ2	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
TIME	4	hh:mm:ss	hh:mm:ss hh:mm:ss:uu
TIMESTAMP	20	yyyy-mm-ddBhh:tt:ss[.fffffff]	yyyy-mm-ddBhh:tt:ss[.fffffff]
UNSIGNED	14	ZZZZZ1	[+-]$<number>[.<digit><digit>] [+-]<number>[.<number>] [+-]<number>[.[<number>]]e[+-]<number>
ZSTRING	11	none	none


Table 2: Data Type Lengths and Ranges

Table 2	 Data Type Lengths and Ranges

Data Type Keyword	Default Length (in bytes)	Valid Length (in bytes)	Valid Value Range
AUTOINC	2	2	-32768 - +32767
		4	-2147483648 - 2147483647
BFLOAT	4	4	±(5.8774718E-39 - 1.70141173E+38)
		8	±(5.8774718E-39 - 1.70141183E+38)
BIT	1	1	0 or 1
CHARACTER	1	1- 255	N/A
CURRENCY	8	8	-922337203685477.5808 - 922337203685477.5807
DATE	4	4	01-01-0001 - 12-31-9999
DECIMAL	6, 0	1-10	Depends on the length and number of decimal places.
FLOAT	4	4	±(1.17549E-38 - 3.402823E+38)
		8	±(2.2250738585072E-380 - 1.79769313486232E+308)
INTEGER	2	1	0 - 255
		2	-32768 - 32767
		4	-2147483648 - 2147483647
		8	-9223372036854775808 - 9223372036854775807
LOGICAL	2	1, 2	0 or non-zero
LSTRING	2	2- 255	N/A
LVAR	5	5-32767	N/A
MONEY	6, 2	1 - 10	Depends on the length and number of decimal places.
NOTE	2	2-32767	N/A
NUMERIC	6, 0	1 -15	Depends on the length and number of decimal places.
NUMERICSA	6, 0	1 -15	Depends on the length and number of decimal places.
NUMERICSTS	6,0	2-15	Depends on the length and number of decimal places.
TIME	4	4	00:00:00:00 - 23:59:59:99
TIMESTAMP	8	8	0001-01-01 00:00:00.0000000 -  9999-12-31 23:59:59.9999999 UTC
UNSIGNED	2	1	0 - 255
		2	0 - 65535
		4	0 - 4294967295
		8	0 - 18446744073709551615
ZSTRING	2	2-255	N/A


Table menu
Table menu commands

Create	Creates a table.
Drop	Deletes a table.
Restore Dictionary Indexes	Converts Btrieve file's indexes and keys and inserts them to Index.ddf.
Show System Tables	Displays X$Field, X$File, X$Index.


Technical Support

To reach Pervasive's Technical Support department:

E-mail:	techsupport@pervasive.com

Phone:	1-800-BTRIEVE or 512-794-1719

Fax:	Attn: Technical Support

(512) 794-1778


To check your database:

1	From the File menu, select Check Database. The Check Database dialog box is displayed.

2 	Enter the path of the database in the DDF Path field. Click browse icon for the specific database path.

3	Enter the path of the data files in the Data File Path field.

If your data files are in multiple directories, separate the paths with semicolons.

4	If security is enabled for the database, enter the valid password in the Master Password field.

5	Specify which version of Scalable SQL you want to run a consistency check against in the Database Check pull-down list. Your choices are Compatibility with Scalable SQL 4.0 (default) or Compatibility with Scalable SQL 3.01.

6	Specify any owner names associated with the Btrieve files. You can enter multiple owner names separated by a comma.

7	Click Next. The Check Database Wizard - Select Tables to Check dialog box is displayed.

8  	Include tables and data files you want to check. Click Add All to move them all to the Tables to Check field.

9  	Click Next. The Check Database Wizard - Check Database Definitions dialog box is displayed and database check begins.

10 	Click Pause in case you need to interrupt a long processing job. While the checking is in its paused state, you can print the contents of the information in the currently active tab or save a report in a .txt file. Press Resume to continue the checking.

These buttons are only active when there are tables to be checked. Click Cancel if you want to totally stop the checking and exit the dialog box. While the tables and data files are being checked, the screen displays the number of tables that have passed and failed. The detailed information is displayed for each table and file checked. When you click the Passed tab, you will only see the specific tables that passed. The Statistics tab shows a breakdown of the elements that passed and failed.

NOTE:	You will get the following warning in DDF Ease if you check a database that was generated with Pervasive's Scalable SQL 3.x engine:

X$File

Fixed Record Length Mismatch.

Dictionary indicates length 87.

Data file indicates length 97.

11	At this point, you can print or save the report. Click Cancel to return to the main window. Click each tab to print its respective information or click Save Report to create a consolidated report in a .txt file.

NOTE:  If a table(s) fails because it has bad DDFs, you need to do one of the following:

  If you did not create the DDFs, send the report to your application vendor so that they can correct the bad DDFs.

  If you created the Btrieve data files, you can use SQL Scope or Btrieve calls to change the values.

  You can also create a new database in a different location and then create a table for each of the table names in the original data files.


Troubleshooting DDF Ease

Status Code 94
Status Message: The application encountered a permission error.

Action:	You may encounter Status Code 94 while trying to open a database if you are running from an NT client to a remote server. If you have operating system level permission to the files, your may need to check the Scalable SQL Requester or MicroKernel Router settings in the Win32 Setup utility:

Scalable SQL Requester

Local:	On
Remote:	Yes
Target Engine:	Workstation
MicroKernel Router

Local:	On
Requester:	On
Target Engine:	Server Only or Try Server, then Workstation
If after making these changes you still experience the error, read the error help for Status Code 94 in the Status Codes and Messages help file.

Status Code 96
Status Message: A communications environment occurred.

Action: 	The Pervasive engines may be in an inconsistent state. Try stopping and restarting the services. Start the Btrieve 7 service before starting the Scalable SQL 4 service if you are running Scalable SQL 4. Start DDF Ease and retry the operation.

Status Code 221
Status Message: The syntax for an expression or restriction clause is invalid.

Action:	The Pervasive engines may be in an inconsistent state. Try stopping and restarting the services. Start the Btrieve 7 service before starting the Scalable SQL 4 service if you are running Scalable SQL 4. Start DDF Ease and retry the operation.

Status Codes 234 and 207

If you get either Status Codes 234 or 207, DDF Ease is trying to do a sort query which requires the external sort path to be to a directory which is accessible to the Btrieve engine being used. If a Btrieve client/server configuration is being used, the external sort path must point to a directory on the server where Btrieve is running.

For Win32 ODBC applications, the external sort path is configured in the registry under:

HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Scalable SQL Engine\Version 3.01\Workstation Engine\Settings\external sort path

For Win16 ODBC or Scalable SQL applications, the external sort path is configured in the BTI.INI file in the local Windows directory under:

[Scalable SQL Engine]

external sort path=C:\WINDOWS

Status Code 285

If you receive Status Code 285, you are probably trying to create or open DDFs on a file system on a server that does not have a Btrieve engine running. Check that Btrieve is installed and running on the server with the file system in question.

Status Code 288
Status Message: The dictionary is in use.

Action:	You are probably trying to perform a data dictionary update operation. Make sure that nobody else is logged into the database. If nobody is logged into the database, then try stopping and starting the services. Start the Btrieve 7 service before starting the Scalable SQL 4 service if you are running Scalable SQL 4. Start DDF Ease and retry the operation.

Status Code 850

Status Message: Scalable SQL cannot convert the data values to the new data type.

Status Code 2104
Status Message: The maximum number of logins has been exceeded. Scalable SQL requester error 2104.

Action:	You may get this message if you are attempting to open a database. Exit DDF Ease. Use the Win32 Setup utility to increase the value following settings, such as:

[Scalable SQL Requester]

Number of concurrent sessions: 100

Number of concurrent tasks: 100

Can't open Scalable SQL 4.x engine because a Scalable SQL 3.x engine is in use

Action:	If you know that you are using a Scalable SQL 4.x engine and you receive this message when trying to open a Scalable SQL 4.x database, another application may have overwritten your settings. Check the settings in the Registry or use the Win32 Setup utility to ensure the settings are set correctly.


View menu commands

The View menu offers the following commands:

Toolbar	Shows or hides the toolbar.
Status Bar	Shows or hides the status bar.


Viewing Table Data

DDF Ease is currently limited to displaying the first 100 rows of data for each table.  To view the table data, select the table from the tree view and then select the Data tab.

For example, to view the data for the SalesOffices table that you added with SQLScope, select the SalesOffices table and then select the Data tab.  If you make changes to table data and want to refresh the view, simply select another table, then reselect the table to force rereading of the data and table definitions.


What is DDF Ease
What is DDF Ease?

DDF Ease is a graphical application that allows Pervasive database developers to create and maintain Data Dictionary Files (DDFs) and database files. With DDF Ease, you can add relational capabilities to an existing Btrieve navigational database, create new databases, design new tables, check your database for inconsistencies with table definitions, and convert your dictionary from Scalable SQL 3.x to Scalable SQL 4.x dictionary format. DDF Ease performs Btrieve calls to obtain table statistics and other information. This means that you should have at least read permission on the server.

Because DDF Ease uses ODBC and Scalable SQL to create and maintain dictionary and table definitions, your database is able to work with Scalable SQL, ODBC, and ODBC-based third-party tools.

DDF Ease provides the following capabilities:

Support for Scalable SQL v3.x and Scalable SQL v4.0 DDF file formats.

100% compatible with ODBC and Scalable SQL.

Create, open, and delete DDFs.

Create new table definitions.

Create table definitions for existing Btrieve data files.

Drop table definitions.

Add/Drop table columns.

Add/Drop named indexes.

Display/print table data, table definitions, and statistics.

Convert database dictionary formats.

Check the database for inconsistencies.

Enable/disable database security.


Who uses DDF Ease
Who uses DDF Ease?

DDF Ease is designed for application developers, maintenance engineers, technical support engineers, and database administrators to:

Add relational access to an existing Btrieve database,

Design new databases and tables,

Migrate from Scalable SQL 3.x to Scalable SQL 4.x, and

Check existing databases for definitions inconsistent with Scalable SQL 3.x or Scalable SQL 4.x.

DDF Ease performs Btrieve calls to obtain table statistics and other information. This means that you should have at least read permission on the server.


Why use DDF Ease
Why use DDF Ease?

DDF Ease is a low cost solution provided right out of the box to assist you in making your Btrieve database accessible from JDBC, ODBC, Scalable SQL, Microsoft Access, and other commercially available tools. In addition to helping you add relational access to an existing Btrieve database, DDF Ease assists you with migrating from Scalable SQL 3.x to Scalable SQL 4.x. It also gives you a head start in creating a new database and table definitions. DDF Ease helps you view, check, modify, and maintain your database.

DDF Ease only creates DDFs that are standard to Scalable SQL, ODBC, and JDBC, and supports both Scalable SQL v3.01 and Scalable SQL v4.0 DDF file formats. In addition to creating standard DDFs, DDF Ease uses standard Scalable SQL data types. Other tools may define non-standard DDFs and allow non-standard field or column data types which may cause ODBC, Microsoft Access, and Scalable SQL to have problems accessing your Btrieve database.