While the table designer is good for creating tables and indexes in the IDE, it is not ideal for providing complete table information. The report available from the table designer is basically a “fields list” which is missing information on indexes (unless the index is a single field expression) and information on the table itself. The dbfDef utility displays most table, field, and index information provided by the TableDef class in one easy accessible notebook control and one convenient report.

Creating tables from code using the create table command can be be trying at first, and a bit tiring if you do it a lot. The dbfDef utility includes a create table command generator which outputs the create table command code from an existing table. This allows you to create tables using the table designer, then generate the create table code automatically.

Using the dbfDef Utility

The utility is contained in one file, dbfDef.prg, and contains various classes which make up the utility:

• dbfDefForm, the form class;
• dbfDefReport, the report class;
• TMPQuery, a custom query for creating and working with temporary tables;
• getTable, a custom control for selecting tables;
• and various custom textLabel and text controls.

While you can place the dbfDef.prg file in any directory, it is recommended that the file be placed in a directory included as a Search Path so that it will be accessible from anywhere in the dB2K IDE. This will allow you to navigate to your table directories and call the utility from the command window by typing: do dbfDef

Calling the utility opens the dbfDefForm class which contains a four page notebook control containing Table, Field, Index and Create Table tabs.

The Table tab includes the getTable control, textLabels displaying table information, and report related controls. Use the getTable control to select a table, either by clicking the tool button to open the standard getFile() dialog, or by dragging and dropping a table into the getTable container. Once a table is selected, information from the TableDef class is loaded and displayed. From this tab, information relating to the table itself is available:

• Primary Key
• Language
• Type/Version
• Record Count
• Whether the table is converted

The Field tab contains a standard grid datalinked to the field rowset. The grid columns can be moved or resized as needed, and contain information related to the table fields:

• Field Name
• Field Type
• Length (maximum number of bytes)
• DecimalLength
• SQL Data Type.

The Index tab contains a standard grid datalinked to the index rowset. The grid columns can be moved or resized as needed, and contain information related to the table indexes:

• Index Name
• Expression
• ForExpression
• Descending
• Unique

The Create Table tab contains controls related to the create table command generator. Clicking the pushbutton outputs the create table code for the selected table to the editor control. Text in the editor control can be copied to the windows clipboard using the standard windows shortcut Ctrl + C. By default, the name used for the create table is the name of the selected table. You can change the default name by typing in a different table name in the entryfield provided. The Fully Delimited checkbox can be used to generate delimited code which may be required when field names are reserved SQL keywords or contain spaces. Note that there is a 1,096 character limitation for the create table command. After this limitation is reached, you can use one or more alter table commands to add additional fields to the table created with create table. This utility does not currently use the alter table command.

The Table Definition Report

The Table Definition Report is accessed from the Table tab and can be previewed or printed. When Preview is selected, the report is displayed using the default report viewer where you can preview, then print. When Print is selected, the report is printed directly to the default printer.

The report displays basically the same information available from the form in three parts, table, field and index. This is fairly straightforward, but there are a couple of things worth noting. The report looks its best when the length of the field names, index names and index expressions are reasonable. When these exceed certain lengths, they will take up more than one printable line. Because the length of the field and index names can vary (some like to keep them short, others like them more descriptive,) the report columns are adjusted at print time to try to accommodate for the lengths. There is, however, a limit to the adjustment that can be made. In addition, the index expression and the index forExpression are reported under one column heading. Indexes will always have an expression, and occasionally may have a forExpression. If an index has a forExpression, it will be displayed in curly braces {} either on the same line as the expression, or on the following printable line. If any of these expressions are excessively long, they will take up more than one printable line.

Under the Hood

While fairly straightforward, there are a couple of techniques used in the underlying code which may be of interest to some. The first being the use of temporary tables, and the second being the use of ‘flat files’ for reporting purposes.

This utility is greatly dependent on the use of temporary tables created and managed by a custom query, TMPQuery. The field and index grids are datalinked to temporary tables which are created one time before the form opens and are updated (emptied and loaded) for each table selection. This process of loading the TableDef arrays to the temporary field and index tables should be instantaneous for most tables. Obviously, the more fields or indexes a table has, the slower this process is.

Looking at the report, you may think that it uses two or three different tables (if you are familiar with the Report Designer.) The report actually uses a combination of parameters and one temporary table. The temporary table (sometimes referred to as a flat file) uses generic character fields and is built from table, field and index information before rendering the report. Generic character fields are basically fields which contain different information depending on the type of row. For example, one field stores the field type for a field row, and the index expression for an index row. The report is designed on the principle that it is sometimes easier to feed in formatted information rather than force in normalized data.

Conclusion

The dbfDef Utility was created to provide simple table information in one convenient form and report. If you’re one who likes to have printed table documentation while working with dB2K, I think you will find this to be a useful reporting tool. For those interested, there is additional documentation included in the code itself. Feel free to use as you wish.

Special thanks to Gary White for taking a look at the utility and providing suggestions which have been implemented into the code.