Developing Database Applications

  • Database Essentials

  • Data Dictionaries

  • The Embedded Database

  • Data Connectivity

  • Using Embedded SQL


Logical Structure of a Database File

Logical Structure of a Database

The purpose of a database is to record information on disk and make it accessible for use through programs. In order to facilitate use, the data is organized into tables, columns, and rows. A table is made up of a variable number of rows, all of which have the same column structure. Rows differ from one another only in terms of their content. Thus, the terms "structure of a table", "row structure," and "column structure" are all essentially synonymous.

Columns

We also use the term "database element" when referring to a class of data distinct from others (constant, variable, etc.). This term refers to a value, or a colum of a table, for a particular row. The particular row referred to is determined by program action at run time, but programs normally act on database elements one row at a time, rather than generally on the column value of all rows in a table. It is possible, of course, to change the value of a particular column in all records with a program that loops through all the rows of a table.

In programs, database elements are referred to in a standard format composed of the programming name of the table, followed by a period, followed by the column name, as TableName.ColumnName. The column lastName in the table person, for example, appears as person.lastName every time it is referred to in a program.

This binomial nomenclature of database elements is a characteristic of Visual DataFlex's application independence of data. Application independence of data refers to the fact that the data can exist independently of any given program. Databases are not generally defined, nor modified, in applications, although for specific situations, there are means of doing so.

Rather, database elements are referred to in programs by their full names in normal usage. The name of any given database element does not vary from one program to another, nor does its type or length.

When using the Embedded Database, column names can be any combination of characters, up to 15 characters long, that starts with a letter and contains no punctuation other than underscore (_). Columns are typed as String, Number, or Date, plus two types not found in other classes: Text and Binary. The first three types are fixed-length types, and the last two are variable-length. For more information, see "Variable-Length Columns".

Just as there is no way in Visual DataFlex to refer to the value of a column in more than one row at a time, there is no way to refer to more than one column value of a given row at one time except through a special type of column called an Overlap column. For more information, see "Overlap Columns".

Data Types in Database Elements

When data is moved to a database element, validity checking for the type of the column is applied to the data, triggering an error when the data violates the type format. Examples would be moving abc to a Number field, or 13/13/13 to a Date. String and text-type columns will reject any characters outside the printable range, such as control sequences. Binary columns reject nothing.

The storage formats of the data types on disk also vary somewhat. Numbers and Dates are stored as fixed-point values in Binary Coded Decimal (BCD) form. Dates are Julian integers and are three bytes in length for all values.

BCD format permits the storage of two digits in one byte of disk space (unlike String, which uses one byte per character). A three-byte Number field can store values up to ±999,999 or, if a decimal point is involved, ±99.9999, for example.

Variable-Length Columns

The Visual DataFlex Text and Binary data formats apply exclusively to database columns intended to hold ASCII and binary data whose length exceeds the 255-byte maximum that applies to String columns. For this reason, such columns cannot be manipulated with the commands and functions used for manipulating ordinary columns such as move, uppercase, trim, calc, and left. Such columns must be opened for input or output with source and destination commands, and their contents manipulated like those of any actual sequential file.

The facility for addressing Text and Binary (variable-length) columns as sequential files is the dbms: driver and the dbms option to the source and destination commands. When the dbms: driver (with the colon) is used, the table and column must be addressed by their numbers. When the command option (without the colon) is used, on the other hand, the table and column can be specified in the usual fileName.fieldName format.

The following program would append the entire contents of Sequential File WITNESS.EYE onto column "account" of the table  "newsEvent" in the row whose "name" column had the value of "Hindenburg":

string sTransvar

open newsevent

direct_input "width: 250: witness.eye"

append_output dbms newsEvent.account

move "Hindenburg" to newsEvent.name

find eq newsEvent.name

if not (seqeof) repeat

readln sTransvar

writeln sTransvar

if not (seqeof) loop

close_input

save newsevent

Since the append_output command was used, the imported material would be appended to any existing contents of Field account. The width: format option is set at 250 to provide end-of-line characters for the readln command that imports the sequential data.

Output into files, devices, or fields opened with append_output can be positioned within the existing content with the set_channel_position command. Where a width: greater than the default argument size is required, the set_argument_size command may be used to increase available capacity. Entire files and fields up to the size set can be moved directly from one to another in a single command without use of an intermediate variable.

For binary files, the read_block command is most suitable for moving data. The following program would export the value of Field 43 in the record whose name field had the value of Hyde of Database File employee (number 16) to binary Graphic File SUSPECT.CUT:

string sTransvar

direct_output "suspect.bmp"

open employee

move "Hyde" to employee.name

find eq employee.name

[found] direct_input "dbms: binary: 16, 43"

if not (seqeof) repeat

read_block sTransvar 250

write sTransvar

if not (seqeof) loop

abort

Since File SUSPECT.BMP is opened with a direct_output command, any existing file by that name is deleted before output begins. The numerical reference (16, 43) to the file and field in the direct_input command is the format required where a driver (binary in this case) is used. Dbms is also used in the form of a driver (with a colon, inside the quotation marks) for the same reason.

The Text and Binary formats are provided to support fields larger than the 255-byte limit that applies to String fields. These types are sometimes called "variable-length," referring to the length of what is returned from one of these fields when it is retrieved into, say, a separate disk file, or a device such as a printer. Unlike String type fields, they do not output trailing spaces to pad their contents out to their full length. They accomplish this through the maintenance, when compressed, of a two-byte header at the beginning of each such field that indicates the length of the data in bytes. Due to this header, the amount of data which a Binary or Text field can hold is two bytes less than its declared size. For example, a Binary field declared with a size of 1024 will be able to hold no more than 1022 bytes of data.

On the disk, however, Text and Binary fields do occupy their entire declared length, unless you use data compression for the file. For this reason, using compression is practically mandatory for files containing such fields. Furthermore, when a file containing a field of either of these types is opened, the record buffer for the file will actually consume memory for the full declared length of such fields, a serious factor under circumstances of limited memory and many large fields.

Overlap Fields

A last field "type" is Overlap. This is not only not an actual data type, it is further not even a field. It is a means of designating a range within each record that is to be handled as though it were a field. Like other fixed-length types, Overlap "fields" are defined in terms of the starting byte and the stopping byte of the field, but unlike other types, Overlap fields can, and should, span existing fields.

Overlap fields may encompass a single "real" field, several real fields, part of a real field, or even parts of two or more real fields. Since any single Overlap field must, like any other field, be made up of contiguous bytes, however, the fields encompassed by an Overlap field must themselves be contiguous in the file definition.

Since they merely provide an alternate means of addressing space that already exists within the record definition, Overlap fields consume no space in the data file themselves. Overlap fields have many uses.

When you put data to an Overlap field, it is moved to the database the same as though you had addressed the real underlying fields. Type checking is applied to the data according to the type(s) of the underlying real field(s). For this reason, it is generally recommended that Overlap fields not encompass fields of more than one type, or that at least such Overlap fields not have data put to them.

An important thing to remember when modifying files with Overlap fields in their definitions is that the byte positions of Overlap fields do not change automatically when changes in field lengths are made in the file, as the byte positions of real fields do. If you insert or delete a field, or change its length, any Overlap field addressing a position in that field or any field listed after it in the file definition will be changed. If you wish the Overlap field to continue addressing the same fields in the record, you will have to correct its definition to the new correct byte positions for the fields in question.

Supporting Relationships with Multiple Fields

Sometimes a file relationship must be based on two or more fields. But since a file relationship can, in fact, be based only on a single field in the child file, the relationship can be based on two or more fields only by encompassing them within a single Overlap field. The Overlap field, then, is designated as the one having the relationship to the parent file. The fields in the parent file should similarly be encompassed by an Overlap field, and the index supporting the relationship should be defined on the Overlap field, rather than on the underlying real fields.

Making a Secondary Field Primary without Moving It

When you have two fields in a child file relating to fields in a parent file, the field listed first in the definition of the child file is the one that will be used by a relate command to find the parent record. The later-listed field will not be used by relates. If you should discover that you wish to use the secondary field as primary, you can bring that about simply by defining an Overlap field ahead of the primary field in the list that addresses the byte position of the later-listed field. The relation should then be pointed to the Overlap field.

Relating to More than One Parent from One Field

If you should need to relate to more than one parent file from the value of a single field in the child file, you can do so by defining "clones" of the field in the child file. When you create an Overlap field that uses the same record space as a field that relates to one parent file, you acquire the means to relate to a different parent file with the same data. If you should execute an attach on the child file, however, the field will be updated first from the parent named in the first-listed field, then from the parent named in the later listed field(s). At best, this is redundant, so if you are doing attaches on the file, you should either have all but one of the parents closed, or arrange the sequence of the fields in keeping with your needs, or ensure that the data in the parent files never varies from one parent file to the other.

Supporting More than One Main Index per Field

Each field may have only one Main index. Where a field provides the first segment of more than one index, and you need two or more "clones" of the same data from which to use different Main indexes, you can create such clone fields with an Overlap field addressing the record position of the real field in question. In your programs, you must then choose which of the fields to address in commands according to which Main index you wish to use.

Addressing Partial Fields through Overlap Fields

Overlap fields addressed to a part of the range of a field or fields can provide useful functionality. For example, when a report should break on the first letter of a field's value, an Overlap field can be defined on the first character of the field, and that Overlap field named on the report command line as the field on whose value to execute subtotal breaks. The same field can be used in headers and footers for the subtotal break.

Where a portion of a field or fields is known to provide suitable values on which the base an index (unique or non-unique), that portion of the field can be addressed in an Overlap field, and the index based on the Overlap field rather than on the real field. Aside from the unique values that can be attained by straddling contiguous portions of two fields, this technique can yield smaller index keys than might be attained if full real fields were used. Smaller index keys not only produce smaller index files, but also yield better performance.


Database Essentials

The Database Essentials chapter concerns aspects of Visual DataFlex that are common to all database manipulation, regardless of the database management system used.

  • The Filelist

  • Database Builder

  • Designing and Maintaining Indexes

  • Table Relationships

  • Using Databases in Applications

  • The Embedded Database

  • Database Attributes

  • Transaction Processing

  • Creating and Modifying File Structures


The FileList

Visual DataFlex maintains a list of tables in a special file called (by default) FILELIST.CFG. This filelist file must be found to run or compile Visual DataFlex programs that access data. The file is found at the Filelist: location specified in the workspace configuration file. You can set the file that your program uses as the filelist as it runs by use of the set_filelist command, and query it with get_filelist. With this command, the name of the file may be set to any name you desire.

The file list links together four of a table's attributes: the DataFlex name, filelist number, user-display name and physical name, optionally including the path. A tables' user-display name is displayed in the Studio, Database Builder and other tools, where it may be modified. Since user-display names can be up to 40 characters long, they can, and should, contain a good description of the table’s purpose, contents, ownership, and other pertinent information.

Running programs normally access database files by their filelist numbers. When a program is being compiled, the compiler finds the table’s definition, including its number, in the table’s FD file, and replaces the table and column names in the source code with numbers in the compiled application. When the program is running, these numbers are used to find the physical names of the tables in the file list. Table and column numbers are sometimes explicitly used in source code.

If the filelist contains no directory path for a table, the runtime searches the directory(ies) identified in the Data: value specified in the workspace configuration file.

If your requirements exceed the supported range of a single filelist, then you may use multiple filelists, and you can have tables open concurrently from more than one filelist. A large application may even have its own filelist. If a program is run or compiled with different filelists having conflicting assignments of names or numbers, however, serious problems can arise.


Accessing Filelists

Each entry in FILELIST.CFG is associated with a file number (also called a file handle) and contains three names associated with the file; the display name, the logical name and the root name. These names are accessed as the DF_FILE_DISPLAY_NAME, DF_FILE_LOGICAL_NAME and the DF_FILE_ROOT_NAME attributes respectively. To access these names for a specific file number, the file handle passed to the get_ or set_attribute function is the file number. Following are some examples:

// Set the logical name for file number five to CUSTOMER

set_Attribute DF_FILE_LOGICAL_NAME of 5 to "CUSTOMER"

// Get the root name for file number 50

string lpszRootName

get_Attribute DF_FILE_ROOT_NAME of 50 to lpszRootName

There are also attributes, DF_FILE_NEXT_USED and DF_FILE_NEXT_EMPTY, for getting the next used or next empty file number. Both of these attributes return a file number relative to the file handle passed to the function. Below is an example of creating a new FILELIST.CFG entry for the first non-used file number after file number 100. If there are no empty slots greater than 100, then DF_FILE_NEXT_EMPTY will be 0.

handle hFile

// Get the next available file number after 100

get_Attribute DF_FILE_NEXT_EMPTY of 100 to hFile

if (hFile > 0) begin

// Create the entry in FILELIST.CFG

set_Attribute DF_FILE_LOGICAL_NAME of hFile to "VENDOR"

set_Attribute DF_FILE_ROOT_NAME of hFile to "VENDOR"

set_Attribute DF_FILE_DISPLAY_NAME of hFile to "Vendor File"

end

else if (hFile = 0) begin

// There were no unused file numbers

showln "No empty FILELIST slots."

end


Methods of Accessing Database Files

Internally, DataFlex uses three different names for each database file, each having a distinct purpose.

Directory Path/Rootname

The directory path/rootname of a database file is what the operating system uses to locate the physical fileset that makes up the database file. If a directory path/rootname contains a path from the root directory, an operating system directory command would list the files. If the directory path/rootname contains only the file rootname common to all the directory files of the fileset, DataFlex uses the value of the DataPath registry key to find the fileset. It searches each directory identified in the value of Data in the order in which they are listed until the fileset is found.

User Name

This name is the one displayed by the Database Builder and others that display data from the file list. With a capacity of 40 characters, it is intended for displaying a good description of the file, its use, its contents, originator, owner, or whatever information may help users who are looking for it to identify it. With the filelist command, you can display this name from your own programs.

If you begin the User name with an at sign (@), it will not display in tools, and will not be selectable.

DataFlex Name

This form of a database file’s name is for use in programs, for such commands as open, close, relate, attach, save, saverecord, clear, and zerofile. It is also used with a period and field names for addressing database elements in programs. It need not bear any resemblance whatsoever to its rootname, but the default action of Database Builder will make them the same unless you change it. With its independence from the rootname, it is independent of operating systems using different syntaxes for rootnames, preserving the transportability of DataFlex programs.


The Database Builder

Database Builder

The Visual DataFlex Database Builder utility is the preferred method of creating database files and data dictionaries, before using the Visual DataFlex Studio to create the user interface for the application(s) using the files.

Database Builder provides a means for viewing the field structure, index structure, number of records, and other attributes of existing database files. Database Builder maintains all the directory files that make up a database file, and it maintains them all automatically except for the *.DEF file. *.DEF files can be created with Database Builder, and when a new one is created, it overwrites any previous version of the same file that may have existed in the directory.

Database files can be modified with data in them, but it is faster and less likely to cause errors to modify empty files before putting data in them. When Database Builder is called upon to restructure a file with data in it, it must convert the data from the old structure to the new, a process that can consume significant amounts of time in large files.

With Database Builder, you can add or delete fields, or move them relative to each other. You can insert new fields at any point. The order of fields in the file is usually of no importance, but sometimes can be. You can change the lengths of fields, and you can change their types, as well as their names. Changing the name of a field, of course, will render program source code that uses the obsolete names unusable until the names are updated in the source code. You can change the database file's record length, either implicitly by changing the aggregate length of its fields, or explicitly by direct entry.

Database Builder is the facility for defining a database file's indexes, and any change desired may be made, although improper changes to interrelated files can upset file relationships. Indexes can be added, deleted, inserted, or changed as to whether they are on-line (updated on each change to the database) or batch (updated only by running Reindex). For existing indexes, segments (participating fields) can be added, deleted, inserted, or changed as to whether they are ascending or descending, or case-sensitive or case-insensitive. You can change the maximum number of records the database file may hold, a factor of importance only to the design of its index files. You can update one or all the indexes of a database file through Database Builder.

Database Builder provides a means for defining relationships between files. Defining relationships is a process of defining which field in a parent file is related to by which field in the child file. These definitions are always made in the child file, the one in which many records may relate to a single record in the parent file (the reverse is not permitted).

Finally, Database Builder affords the means of setting the type of data compression that should be applied to a file, and also the means for updating such compression in files whose contents have changed substantially since the last time they were recompressed.

See Also

Database Builder


Designing and Maintaining Indexes

Each embedded database file may have up to 15 indexes, each with up to 16 segments (fields) in them, and each having an aggregate key length up to 256 bytes. Indexes are created and modified in Database Builder. They can be updated interactively through the Database Builder, and can also be updated from running programs by use of the sort command.

As explained in Finding Records, indexes are vital not only for finding records that users explicitly seek, but also for finding (sometimes internally, without display) records in related-to files. Indexes are vital to the implementation of file relationships. The distinction between unique and non-unique indexes is also discussed there, as is the meaning of "Main index" with regard to a field.


Batch vs Online Indexes

Each time you create an index, you will decide whether the index should be "online" or "batch." The distinction between these two types of indexes has to do with when they are updated, and that factor, in turn, has to do with the way each index is used, and the performance of your applications when records are being added, edited, or deleted.

An online index is updated whenever a change is made to any field (in any record) of which it is composed. Changes occur to an index when new records are created, when existing records are deleted, or when one or more of the fields making up the index is changed in an existing record.

In complex applications using dozens of related files, each containing millions of records and each having many online indexes with many segments and long aggregate key lengths, changes to the database can impose waiting periods of perceptible length on users while records are saved or deleted.

This overhead can be reduced when certain indexes are used only at isolated and predictable times by changing them from online to batch. Batch indexes are updated only when the database is explicitly sorted through the sort command, or the Database Builder.

A situation where a batch index might be appropriate would be where once a month, a list of customers' names, balances, and phone numbers was output by the number of days by which payment was overdue. It might not be necessary to access customer records by that index on a daily basis, but for the report, the index is required once a month.

If the index of number of days overdue were defined in the database file as a batch index, the program that output the report each month could contain at or near its top a sort command addressing the file(s) whose batch indexes were to be updated. The program would continue after the command to output the report, probably also blocking changes to the database until the program was finished running.

If a batch index is used without needed updating, however, records will be output out of order, recent records will be omitted or unfindable, and attempts to find records deleted since the last update will generate errors. Suitable measures must be taken to prevent such occurrences where batch indexes are used.


Case-Insensitive and Descending Segments

You can designate individual segments of indexes case-insensitive and/or descending. Case-insensitive segments are of special value where material such as names may contain variations in capitalization unknown to users searching for records. In indexes containing case-insensitive segments, capitalization in the field in question does not affect the sequencing of records. Where this attribute is not used, the capital letter is sequenced immediately ahead of the lowercase of the same letter. The sequence is AaBbCcDd, and so on.

Index segments designated descending (the default is ascending) also can be very useful, but can impose certain requirements on programming. When a find ... gt statement, for example, is executed on an index containing descending segments, the gt operates as an lt on the descending segments. Where a starting value is moved to the record buffer to set a starting point for a loop containing such a command, the starting values for such segments should be at the bottom of the desired range, rather than at the top as for default (ascending) indexes.

If, for example, a report of all accounts with amounts over 30 days overdue were desired, and the index on number of days overdue was (properly) descending, the effective approach would be to start with a clear buffer and a find ... ge statement.

If the index were made up of, say, number of days overdue followed by customer name, the ge would have the effect of an le on the first segment (number of days), and the last record by the index (greatest number of days overdue) would be found. On the next segment, customer name, the ge would find the first customer alphabetically, and customers would be listed alphabetically for each number of days overdue. The report would continue with a loop containing find ... gt until the value 30 was encountered in days overdue, and at that point, the report would terminate.

Case-insensitive index segments can reduce the usability of find optimization, in which reports can output index values instead of actual record values. The values stored in such segments are all upper case, so if the field values must be output as actually capitalized in the database, the record buffer will have to be loaded to access the actual values. Find optimization works by making it unnecessary to load the record buffer. Where output in solid capital letters is acceptable, this may not pose any problem.


Supporting Selections

If your application must support selections of ranges of records sharing a value, or a range of values, you can speed up certain selections considerably by providing indexes to support the selections. The selections in question are those in which the data to be output is all within the data needed for the index, or include relatively little data not used in the index.

Consider, for example, a report of customers by country with customer numbers after the customer names. Further assume that each time the report was run, it was only for the customers in one particular country. The required index would be based on, first, country, then customer name. If that index is used, the record for every qualifying customer must be retrieved into the record buffer to get the customer number. But if the index contains country, customer name, and then customer number, the record data need not be loaded at all—the entire report can be output directly from the index data.

Although this kind of index greatly speeds up selections, the larger and more-complex indexes that sometimes result exact their own costs. Specifically, saves of new and edited records take longer if such an index is on-line (but not if it is batch). Individual finds from user input also may take longer, if the extended index is used for such finds. This disadvantage, if it is significant, can be avoided by using the extended index only for selection reports, and the shorter form of the index for individual finds. In this case, it is more important to set the extended index to batch mode, since it constitutes a whole additional index to be updated in every save if it is left online.


Table Relationships

Table relationships are indispensable to sound data management in applications. Table relationships are virtually the cornerstone of contemporary database practice, and database applications that do not make use of them are either rudimentary or of very specialized purpose.

DataFlex's relational capabilities begin with its ability to have multiple database files open for add, change, and delete concurrently. By itself, this would be sufficient to enable you to create and maintain file relationships. However, DataFlex further supports relationships through provisions in file definitions, and a number of relational commands such as relate and attach. See Finding Records for more information.

The best way to make full use of DataFlex's relational capabilities is to model thoughtfully the data flows you wish to automate. This requires insight and understanding when converting from manual systems, and perhaps even more when converting from automated systems of nonrelational design.

DataFlex's relational mechanism is inherently hierarchical—that is, it supports many-to-one relationships. This means that, in two related files, many records in one file may relate to each record in the other file. This also means that a record in the "many" files may relate to only one record in the "one" file. This is referred to as a "many-to-one" relationship.

In this manual, the "many" file and its records are called the child, and the "one" file and its records are called the parent. Although a record in a child file can relate to only one record in a given parent file, there is theoretically no limit on the number of parent files a child file might have (nor to the number of child files a parent might have). The same record in a child file could relate to one record in each of five parent files, if desired. Such arrangements are not unusual.

Parent files can have parents of their own, and child files can have children. While child and parent refer to the proximate generation, the terms descendent and ancestor are used to refer to files removed from the reference file by an indeterminate number of generations.


Mechanics of Relationships

Visual DataFlex relationships are based on the maintenance in each child record of one or more field values that match field values in the related-to parent record. It is not necessary for the fields to have the same names in both files, but it is often helpful in understanding how program code works to make the names similar. The fields must, however, be of the same data type and of the same length in both files.

If there were a file of invoices and a file of customers, each invoice would relate to one customer. A given customer might have many invoices, but each invoice would be to only one customer. Each record in this invoice file would contain a field to support the relationship. This field, whose data would match that in a field in the parent record, might be a customer number, the customer's name—anything that identified the record in the customer file. Record number can serve this purpose very well, but when records are deleted or a database file becomes corrupted, the original data structure can be difficult or impossible to restore when relationships are based on record number.

The record values that are related to in parent files must be inherently unique. Even in applications where it is not necessary to find all the child records that relate to a particular parent, it is often necessary to find the parent record from a child file. In the case of the invoices, it would be to acquire the customer's address, for example, and perhaps also credit limit.

For this reason, the field(s) that the relationship is based on must be indexed in the parent file. That index must be unique, rather than non-unique, so that child records each relate to one and only one parent record. In some cases where parent records contain no positively unique field, the relating index is based on combinations of two or more fields (overlaps) in both files that do yield unique values in combination. In other situations, a serial-number generator, such as the kind used for invoice numbers, is used to "manufacture" unique data for parent records. If the invoice file had a child file of line items (transactions), it would very likely use invoice number to identify the parent records.

Besides providing the fields in both files and the index in the parent file, one more step is required to create a relationship between two files. The fields in the file definition for the child file must be designated as relating to their companion fields in the parent file. Alternatively, the set_relate command may be executed on the child file. No equivalent designation is necessary, or even possible, in the parent file. Thus, it may be said that children "know" their parents, but parents do not "know" their children.


Using Table Relationships

Once the relational mechanism is in place, it can be used in programs both for database maintenance and for reporting purposes.

At this point, it should be emphasized that the pre-eminent facility for maintaining file relationships is the DataDictionary class. See The DataDictionary class for more information. DataDictionary classes are built and maintained mostly visually using Database Builder.

All the foregoing steps—creating the fields, creating the index, defining the relationship—are required in order to use file relationships with or without such data-dictionary objects (DDOs). Actually using them without DDOs, however, is a demanding process requiring considerable understanding not only of relational theory, but with the mechanics of maintaining data integrity in a multi-file, multi-user setting.

Any time a group of related database files is to be used together in a program, all the files to be used must first be opened with the open command. Typically, a program oriented to a particular database file opens that file plus some or all of the file's ancestors. If a child file to the "main file" is to be opened, the program is probably in reality oriented to that child file, rather than to its parent. For example, where an invoicing program addresses a file of invoices and its child file of transactions, the transactions file should be regarded as the "main file" of the program, and the invoice file and its siblings and ancestors, as the parent files.

When a new record has been opened, or an existing record found, in the main file, the pertinent parent record can be found with a command in the form relate child_file. If, for example, you were in an invoice file and you wished to check an existing invoice known to you only by its number, you could enter the invoice number into a field in the invoice record, and the relate into the customer file (relate invoice) would find the customer record. That record, for example, would display the customer's name, address, current balance, and other data that might not be in the invoice record.

If, on the other hand, you had entered a new invoice for an existing customer, and you kept the customer's name in each invoice record to facilitate reports of invoices by customer name alphabetically, an attach invoice would move the customer's name down into the field of the invoice record that related to the customer name field.

If no attach were executed and the invoice record were saved, the same thing would still happen, since the save command executes an attach before saving to disk. The reason for putting an attach into a saving command like this is not just a convenience and a reminder. It is also because attaches should be done as late before the save as possible. The greater the time between an attach and a save, the greater the possibility that a field in the parent record will be changed by another user, and the (attached) data in the child-file record made obsolete. This is guarded against by DataFlex, but if it occurs, it is an error condition, and can at a minimum produce some processing overhead.

Primary and Secondary Relationships

The example above of an invoice record containing both a customer number and a customer's name is an illustration of primary and secondary relationships between the same two files. The primary relationship (the customer number) is the one used by relates, and it is made primary by the placement of the field before the field on which the secondary relationship is based. Relate checks the child file's fields for relationships in the order in which the fields stand in the file definition, and once a relationship to a particular parent file has led to finding a record in the parent file, additional fields relating to the same file are not used for finding.

There are a number of advantages to "redundant" relationships of this kind. The most-obvious one is that two ways of relating into parent files are provided (both customer number and customer name would appear to be of value), and child files acquire an additional way of being indexed (again, both number and name would seem to be of use). But there are additional benefits.

One has to do with changes in the key values that the relationship is based on. If customer names might change from time to time, so long as customer numbers were not allowed to change, customer records could always be found from invoice records based on the customer number. The customer names would become simply auxiliary data, on which no actual file relationship was based. The purpose of the secondary relationships would be purely to support attaches, never to support relates. Used this way, the secondary-field data in the parent file would not have to be unique, and would not even have to be indexed, as is required of fields on which relationships are based.

A kind of opposite use would be as a backup to the primary field. If, for example, record number were used for the relationship, it would be primary, because record number is always the first "field" in every database file. Record number always gives the fastest relational performance, as well. But record numbers are "fragile"—subject to change from file corruption and other mishaps. If the child and parent files had a field on which a secondary relationship could be based, this field could be used to restore the record numbers in the event record numbers were corrupted. To do the restoration, it would be necessary temporarily to remove the definition of the primary (recnum-based) relationship, bring each record of the child file into the buffer, relate on the old secondary field, move (not attach—the relationship has been temporarily removed) the parent's recnum into the appropriate field of the child, and resave the child record. Then the definition of the primary relationship could be restored. For this kind of use, the secondary field in the parent must be kept unique, and maintaining an online unique index on the field is a good way to do it.


Using Databases in Applications

  • Database Command Group

  • Opening Database Tables

  • Finding Records

  • Creating and Changing Records

  • Multi-User Considerations

  • Multi-User Command Group

  • Always Perform Edits in a Locked State


Database Command Group

The Database commands provide the facility to find, save and delete records in the database files, and to maintain "relationships" between and among records. You can read more about these commands in the Visual DataFlex Language Reference.

attach | file_mode
clear | fill_field
close | find
constrain | is_file_included
constrained_clear | open
constrained_find | relate
constraint_set | save
constraint_validate | saverecord
copy_db | sort
copy_records | vconstrain
declare_datafile | vfind
delete | zerofile
delete_db |

Database Commands and the File List

The Database commands use information contained in the header of the data file and in the data file’s entry in the file list. The file list (a file default-named FILELIST.CFG) contains data on each file's number of fields, field length and type, decimal formatting on numeric fields, name including drive or subdirectory reference, multi user status, key index structure, etc. All Visual DataFlex operations can "look up" the values of these file parameters whenever needed. This precludes the necessity for each program to maintain file specific data and substantially reduces application-development time.

The open command is key among these commands. None of the other commands can be executed on any file that has not first been opened. The file_mode command affects the way in which the file is opened—whether for only one user, for read-only, or for reading and writing access by more than one user concurrently (the default). When a file has been made the argument of a close command, it is no longer open. The declare_datafile command permits program source code to address a file and its fields at a point above the point in source code where the file is actually opened. This permits optimization of the program's performance at run time.

Many of the commands can address database files by either the file's DataFlex name or by its number, both of which are defined in the file list. The chief reason for this facility is for purposes of addressing non-native files, which may lack some of the components making up the file.field format customary for native files.

Where the DataFlex name of the file is known, its number may be used by an easier means. The keyword file_number may be used appended to the DataFlex filename with a period (.). The number of the file accounts, for example, can be addressed as the system variable accounts.file_number. For this to work, the file must either have been declared with the declare_datafile command or opened at some point previous (in source code) to use of the system variable. It does not have to be open (it may be closed) at the time of use of the system variable in either case.

Many of the commands can act on up to nine database files explicitly named as arguments (separated by spaces) on the command line. But some commands (clear, delete, and saverecord) affect only the file(s) named on their command lines, while the others (attach, relate, and save) reach up to the parent file(s) of the file(s) named on their command lines.

These remaining commands are inherently multi-user in nature, subject to the requirement mentioned above that no file is affected that is not open. Attach and save (which contains an attach) bring the values of fields in the open parent file(s) to which field(s) in the file(s) named on the command line relate, while relate reaches all the way up the parentage chain to all open ancestors of each named file to find a record in each file whose field data matches the data in the fields of each child file on which the relationship is based.

Finding and constraining commands are of two basic types: fixed and variable. Those of the variable type have names beginning with "v." These variable commands can be directed toward any of various database files selected by users at run time. The fixed commands can be used only on the file for which each is programmed, using the mode (greater than, less than, equal to, etc.) programmed. The constraining commands provide a "filter" facility in which, while the constraints are in effect, only those records of a database file whose data satisfied the constraints are "seen" by the program. This can greatly enhance performance in finding-intensive applications.

The following is a discussion of how the commands in that group can be used to manipulate the database in various ways.

In most real-life situations, the best way to utilize the database is to use program modules called views that contain one or more data-server objects to control access to the database, and one or more data-entry objects to provide an interface for users.

These objects deploy in your programs a sophisticated, extensive system to link the database to entry forms with numerous internal procedures, including a set of predefined accelerator-key procedures. They interconnect the screen buffer with the record buffer in a manner designed to maintain data integrity throughout sets of related files.

The dynamics described in this section represent a subset of what must be done by any program that uses the database. Use this section to acquire an understanding of how programs use the database. Do not infer recommended coding techniques for real-life applications from the basic code examples shown.


Opening Database Tables

Opening Database Files

Nothing can be done with a database file until that file has been opened. Database files are opened with the open command. Like other commands that address database files, this command uses the DataFlex name of the file.

Record Buffers

When a database file is opened, a buffer is established in memory containing enough room for all the fixed-length fields in the file's definition. The size of this buffer is slightly larger than the size of the file's record length, and except for the special case of system files, the buffer is empty immediately after an open.

A DataFlex program may have literally hundreds of database files open at the same time (memory capacity of the computer permitting, and there are as many record buffers in memory as there are files open, but each buffer contains only one record from its file. Buffers may be empty, or may contain a record, but they may not contain more than one record at a time.

When a record buffer is empty, it is said to be inactive, but when it contains an existing record, it becomes active. Active record buffers must either be saved, cleared, deleted, or deactivated (disassociated from the existing record on disk) by moving a value to their record number. If another record is moved to a buffer containing an active record, an error is triggered.

When a file's record buffer is active, the file's status is true, and when the buffer is inactive, the file's status is false. Each open file has its own status at all times, and it may be queried, regardless of finds which have been done in other files. See the Visual DataFlex Language Reference for more information on the DF_FILE_STATUS attribute.

System Files

A file that is defined to have a maximum of one record—a so-called "system" file—differs from other (multi-record) files in that the contents of its one record are moved into the record buffer immediately when the file is opened with the open command. A "system" file may be viewed as a sort of array on disk and, contrary to the implication of its name, DataFlex is not limited to one of these files per computer, or even per directory. You may have as many system files as database files generally. And system files may have as many fields as any other file.

System files are useful for data that pertains to a site, or even an application. It is common, for example, for a using entity (typically a company) to maintain its name, address, and telephone number in a system file for use in applications generally. It is also common for an application to rely on a system file to hold the last invoice number for an application in which multiple users may be creating new invoices in a single transaction file concurrently.

In any case, since system files have only one record, there is no finding in such files, and no potential for confusion as to which record is desired when the file is opened. Therefore, when such a file is opened, the data from its only record is automatically moved into its buffer.

A system file's file definition limit of one record does not actually prevent addition of additional records to the file. If your program does not positively prevent addition of records to the file, users will eventually (unintentionally) add records to the file. As soon as the file has more than one record, it no longer behaves in the manner described above, and instead behaves as a normal file, in which a find is required to fill the record buffer.


Finding Records

Except for the first and last record of a database file (or index), records are found by moving a finding value(s) to the appropriate field(s) of the file's record buffer. The record buffer is established when an open command is executed on the database file.

The first record of a file can be found from an empty buffer by executing a find command on its record number with the ge (greater than or equal to) comparison operator. The last record of a file can be found by executing a find le by recnum on an empty (freshly opened or freshly cleared) buffer. The first record by an index can be found from an empty buffer by executing a find ge by the desired index, or on the field whose main index is the desired index. The last record by an index can be found with find le by the index or field. On clear buffers, ge gives the same result as gt, and le gives the same result as lt.

Records other than the first or last are found by moving the key value of the desired record to the appropriate field in the record buffer and then executing a find with the appropriate comparison operator (eq for an exact match, ge for the match if it exists, or the next record if it does not, gt for the next record after the key value, lt for the previous record, or le).

open villains

move "Jekyll" to villains.alias

find eq villains.alias

In this example, database File villains is searched for the record whose Field alias has the value Jekyll. If no record has a matching value in that field, predefined Indicator found is set false.

Finding by Record Number (recnum)

Any record in a database file can be found by its ordinal position in the file, an attribute of a record that can be dealt with as though it were a field (the first field, or Field 0) with a reserved name of recnum. In certain cases where recnum is used as an account or serial number, or where file relationships are based on record number (both not generally recommended), this may be the appropriate way of finding specific records. It is always faster than finding by an index.

But the most common occasion for finding by record number is when simply traversing the entire file in its physical record number order for a report or the like. If, for example, the total of all the records for a certain field were needed, the appropriate way to accumulate the total would be to perform a find gt ... by recnum on a clear buffer, and repeat that find in a loop until no more records were found.

Finding by an Index

Finding records by the values of indexes, or key, fields is similar to finding records by recnum, and is more often the needed function in real-life situations, especially for finding specific records (rather than touring ranges of records).

In real-life situations, indexes are made up of values from not just one, but more usually several, fields. Each field participating in an index represents a segment of that index. When finding by indexes, it is essential that at least the initial segment have a value to find by.

For example, if an index (Index 1) were made up of two fields, firstName and lastName, a find gt ... by index.1 after moving the value Park to Field lastName (and leaving firstName blank) would find the first record with a lastName, firstName value greater than Park—the first Park with a non-blank firstName, if there were one in the file. If, on the other hand, a find eq command were executed under these conditions, only a Park with a blank firstName would be found.

Executing the same searches (using the same index) with lastName blank and a value in firstName would probably be an error. In any case, the find gt would find the first record in the file having a non-blank lastName (Abernathy), and the find eq would find the first record with a blank lastName—probably an illegal condition that would not exist in the file, producing a find failure.

Fill_field moves the highest or lowest value (as specified in the command) possible for a field's size and type into the record in the buffer for the specified file. This is useful for finding the first or last record in the file by the index based on the field, particularly in files of a DBMS other than DataFlex. Since it is designed for use with files that may not be native DataFlex, this command addresses files and fields by their numbers, not by their names.

Unique vs non-Unique Indexes

All key values in an index must be unique. It is often desired, however, to index records on fields whose values may not be unique. The surnames of persons are a good example of this, and the last and first names, in combination, of persons remain a good example, in most cases. Where an index must be based on non-unique values, a unique element, whether displayed or not, must be added to the index, and should be its last segment. Use of recnum for this purpose increases the number of segments in the index by one, but consumes the least possible space in the index file, and imposes the least cost in terms of performance. The sequential output of like-key records in such cases will be by record number, that is, arbitrary in most cases.

When finding by non-unique indexes, the eq (exact match) comparison operator is virtually never appropriate, or effective. Continuing with the lastName, firstName example, Index 1 would be made up of lastName, firstName, and recnum. If a find eq were attempted with recnum clear and only lastName and firstName specified, no record would ever be found, because the recnum of every record satisfying the requirements of the first two segments would not be blank, or zero. Therefore, only finds ge, gt, le, and lt would normally be appropriate.

The Main Index of a Field

When defining an index, you will be called upon to indicate what field the index is Main for. This designation is required for the two situations where a find is specified not by the number of an index, but by the name of a field. The first situation in which this is done is when a find command is programmed with the syntax find gt| ge| eq| le| lt df_fileName.fieldName. In order for a find to occur in this situation, fieldName must have a Main index in the definition of df_fileName.

The second situation in which a find is specified by a field rather than an index is when a find is launched by use of a key procedure when the cursor is in an image window. When the window is linked to a database field by an entry_item command, DataFlex can identify the field by which the search is to be conducted. The Main index of that field is used. When the field does not have a Main index, an error is reported advising users that the field by which the find was attempted has no index. This does not mean that the field does not participate in any index (it may participate in several), but rather, that no Main index is designated for the field.

No field may have more than one Main index, but more than one field may designate the same Main index. The lastName, firstName index would probably be the Main index not only for Field lastName, but for Field firstName as well.

Buffering Indexes

The open command provides an option for naming an index of the database file on the command line. When an index is so named on the open command line, the index is "buffered"—that is, blocks of the index are moved into memory as records are found so that it is not necessary to access the index file each time another record is to be found.

open villains by index.1

In this example, database File villains is opened with its Index 1 buffered.

Index buffering is effective when all (or major portions of) the records of a file are to be accessed in order by an index, without gaps, as when traversing the entire file, or ranges thereof. If used for these situations, it improves performance of the program. If used at other times, such as during random access of records, it may slow performance somewhat, and in any case consumes memory needlessly. If used during data entry, it will frustrate the operation of online indexes, creating the appearance that they are corrupted.

Outputting Data from Indexes

When after a find in your program, exclusively record data that is contained in the index in use is used, the record buffer is not loaded after the find. The required data is drawn from the index itself, improving the speed of repetitive finds considerably. Such repetitive finds are involved, for example, in selections from the database, such as all agents in Sri Lanka, or all license plates issued in St. Gotthard canton.

By default, the DataFlex database driver does this only where no index segment is designated case-insensitive. Using the call_driver command, you can change the value of the FLEX_INDEX_OPT variable from its default FLEX_INDEX_OPT_ON. If you set it to FLEX_INDEX_OPT_OFF, index data will never be returned to the program; the record buffer will be loaded after every find. If you set it to FLEX_INDEX_OPT_PERMISSIVE, index data will be used even if it is from case-insensitive segments. The data in such segments is solid uppercase letters, which may differ from the data in the record.

If the program calls for any data not in the index in use, the record buffer will be loaded and all data will come from the record itself, rather than from the index.

Finding through Relationships

When finding or creating new records in a file that has one or more ancestor files, it is often desirable to find the records in the ancestor files to which the new or existing record relates. For example, a sales transaction file might relate to a parent parts file, where what is being sold is quantities of parts, each line item of which is a record in the parent file. The parts file, in turn, might relate to a parent supplier file. The original child, sales, might also relate to a customer file, containing records of customers to whom parts are being sold.

If all three of these ancestors (two parents and one grandparent) have been opened with the open command, the records for the part, the supplier, and the customer can all be found with a single relate command. The command relate sales would move the related-to records into their respective buffers provided the buffer of sales contained valid values to identify the part and the customer. After the find in parts, of course, it would be necessary for the buffer of parts to contain a valid identifier for a supplier to support the ensuing find at the grandparent level.

The relate command sets a series of finds in motion, first in open parent files, then in open grandparent files, and so on up the ancestry tree as far as file definitions reach. If any file in the chain is not open, the chain is broken, and the relate up the branch the non-open file was in halts. Since this is often an intended condition, no error is reported in such situations.

Since the finds done in ancestor files on a relate are based on relationships defined relative to fields, each related-to field in each ancestor file must have a Main index to find by. Furthermore, indexes used for this purpose must be unique indexes—use of a non-unique index for a relationship would cause ambiguity in which record of ancestor files was related to by records in the descendent files.

A companion command, attach, works in the opposite direction, and does not involve finding. It is normally used after a relate command. After the relate sales command, for example, an attach sales command would move data from parents to their children. The parts record, for example, having been found by parts.number, would move down to the sales record such data as parts.description and parts.sellPrice, provided sales contained fields for these items.

Finding under Constraints

A number of commands whose names begin with constrain are provided for regulating finds throughout a program or application to subsets of the records in a file or group of related files.

The first of the most-important commands is constrain. With this command, you can specify constraint of the records of one or more named database files to those having values in certain fields within a particular range, or to those that relate to whatever specific record from a parent file is in its buffer, or even to complex Boolean combinations of selection criteria.

Two steps besides the constrain command itself are required to make use of the constraint system. The first is to use the constrained_find command when finding subject to constraints, and the second is to control finding loops according to the state of the found predefined indicator, something that is often done in any case with the standard find command. Unlike the find command, constrained_find will fill the buffer with a new record even though found is set false. Constrained_find sets found false not only when no record can be found, but also when a record is found that violates the constraints specified.

There are two main benefits to using the constraint system. The first is a convenient way of systematically restricting access to records selectively throughout a program or application. The second is in reports where one or more files (or ranges within files) must be scanned record-by-record because the criteria fields are not indexed. Although this can be done without the constraint system, it is faster when done with the constraint system. This record-by-record scanning is done with the as expression phrase of constrain. In this form of the command, expression may not contain any local variables; all variables must be global.

Constrained finding and regular finding do not preclude each other in a program. Both can be used in the same program without interfering with each other.

During application development, you can use two predefined Integers to determine how many records are being tested in a constrained find, and how many meet the criteria. In that these Integers increment continuously while a program runs, you must initialize them to zero at the point where you wish to begin counting, or they will return incorrect values. Constrain_tests_count contains the number of records tested by the constraint system, while constrain_found_count contains the number of tested records that satisfy the constraints.

You can also inspect constrain_tests_count and constrain_found_count by using a more sophisticated debugging tool called DataDictionary Inspector in your application.


Creating and Changing Records

Creating New Records

Immediately after an open command, the record buffer is clear. Data moved to the record buffer will create a new record in the database file if it is saved. The record buffer is also clear immediately after execution of a clear command on an open file, and again a new record will be created on save. Where you wish to create a new record with some of its field data derived from an existing record, all that is necessary to do is to move a value (0 is recommended for clarity of intent) to the recnum of the database file before the save. The existing record must, of course, first be found and moved into the record buffer.

Editing Existing Records

Where you wish to change an existing record, the procedure is much simpler. Merely find the desired record, move the changed data into the buffer, and save the changed buffer. This description omits considerations that are vital to multi-user situations, where more than one user may be modifying the same record at the same time. In such cases, it is essential to reread the record data from the disk immediately before saving in order to avoid overwriting other users' saved changes with obsolete field data (that you did not change) drawn from the disk before their changes were saved.

It is important to remember that the multi-user features of Visual DataFlex are complete, well-designed, and so easy-to-use in most situations that they are virtually invisible. For additional information, see Multi-User Considerations.

Saving and Deleting Records

When there is data in the buffer, a saverecord command will save the buffer data either back to the existing record it was drawn from, or to a new record. A companion command, save, first fills all fields that relate to fields in parent files with the data in the companion parent fields, then performs a saverecord. This amounts to an attach followed by a saverecord.

The delete command only operates on the files named in the command line, never on ancestor or other files not named. For delete to work, the record buffer must contain an identifiable record—that is, an intact record number. A delete executed against a buffer of which this is not true will generate an error.


Multi-User Considerations

When multiple users are accessing (more particularly, writing to) the same database, it is essential that they not overwrite each others' work indiscriminately. It is equally essential that the work of each is not just ignored by the system, or allowed to "fall through the cracks" in some way. Very strong safeguards against this are available in Visual DataFlex, and if you use properly constructed views containing data-entry and data-set objects, they are automatic.

The following commands will only function under multi user runtime systems. They have no effect in single user runtimes, but it is recommended that full multi-user provisions be made in all programs to provide for the eventuality of a program's being run among multiple users. These commands are explained in detail in Multi-User Command Group.

lock | prevents other users from themselves locking or writing to data files. Remains in effect until the user issuing the lock issues an unlock
reread | first issues a lock, then reads data from any file with an Active record buffer. Used without an argument, reread affects all open data files. Alternatively, just the file to be affected can be named on the reread command line
unlock | restores the system to normal Read/Write status

Visual DataFlex handles the majority of multi-user issues automatically via data-dictionary objects (DDOs). However, there may be times where you want to perform "raw" database I/O with procedural commands. In these situations, Visual DataFlex assists you by enforcing sound multi-user coding techniques throughout every program.

The rule of writing proper multi-user code with Visual DataFlex is to never perform database edits in a non-locked situation. Moreover, to insure database integrity, a reread should be used prior to any updates to the database to ensure that database changes by all users are mutual rather than exclusive.

open vendor

find gt vendor

reread

move "TEST" to vendor.vendor

save vendor

unlock

This example illustrates proper multi-user coding. It finds a record, then rereads, which does a lock and then refinds the record. Then it does the edit (move command), saves only when locked, and unlocks when completed.

The following example is faulty:

open vendor

find gt vendor

move "TEST" to vendor.vendor

save vendor

In this example, no lock (or reread) is applied. Another user might edit the database at any point between the find and the save. Deletion and subsequent replacement of the record could cause the wrong record to receive the edit. This is a definite possibility in a busy multi-user site. If the field(s) being edited are included in indexes, then the indexes concerned may also be corrupted.

The following example is also faulty, for a subtler reason:

open vendor

find gt vendor

lock

move "TEST" to vendor.vendor

save vendor

unlock

In this example, a lock is done instead of a reread. Here, the danger described above is confined to the narrower range between the find and the lock, but it remains a possibility. Reread, which refinds the record, obviates this possibility.

No matter how fast your system is, there is always that small amount of time between the find and the lock. Both these situations produce a runtime error 4155 Edit requires reread or find during lock.

Error 4155 is triggered whenever an edit to an existing record happens in a non-locked state, or when the record was found in a non-locked state and no reread took place. (Note: the error is not triggered when a single-user runtime is in use, but good programming practice is to always code for multi-user for maximum portability.) The following code would not trigger the error:

open vendor

lock

find gt vendor

move "TEST" to vendor.vendor

save vendor

unlock

In this example, since the find takes place in a locked state, no reread is required and the edit still happens while locked. The following code will give the error:

open vendor

lock

find gt vendor

unlock

move "TEST" to vendor.vendor

save vendor

Even though the find took place in a locked state, the edit to the record buffer violates multi-user integrity, being performed in an unlocked state.

What do you do if you run a program and get this error message? You must ensure that, throughout your application, you always follow the rules of multi-user integrity.


Multi-User Command Group

Visual DataFlex provides true multi user operation. This means that more than one user may access and change the same database file concurrently.

lock

reread

unlock

In Visual DataFlex, the file is locked only for the time of the save. Updates are then done on the fresh, safe, data. And only those fields that have actually been changed are saved to the record, thus preserving any other changes. This type of multi user is virtually undetectable to the operator, greatly in contrast to a read time lock.

Multi user updates are of two basic types: transactional and replacement. Transactional updates are generally numeric adds or subtracts from totals in a master record. Since the update is based on the re read (safe) data, updates of totals will always be accurate. Replacement updates, (changing an address or credit limit) do not usually require multi user interaction, but do require that the application not arbitrarily rewrite fields that were not changed, as traditional methods provide. Visual DataFlex keeps track of which fields were changed in order not to rewrite an old value over changes made by another user subsequent to the first user's read.

Here is an example of how this works:

Three users have the inventory part ABC on their screens. The quantity in stock (10) and the description (WIDGET) are shown on each screen. User 1 is invoicing the sale of one widget, while User 2 is invoicing a sale of two. User 3 is changing the description to WIDGET, GREEN. All three users read their (identical) screens, key their input, and then press save within the same second. Let's say that the actual sequence of saves fell out as User 1 first, then User 2, and User 3 last.

User 1's save will reread the record and find it unchanged. His sale of one widget will then be subtracted from the quantity in stock, and the quantity in stock written back as 9.

User 2's save will then take control, reread the record (9 in stock), subtract two, and write back the proper number (7).

User 3's save will then get control, reread the record (with 7 in stock), only update the description, and write it back correctly. All this occurs with speed which typically is imperceptible to any user.

In such a system, replacement privilege to transactionally updated fields should be very carefully limited to responsible, thoroughly trained users. To make sure two users do not sell quantities in excess of the quantity in stock, the application program should be written to check for amount sold exceeding quantity in stock (and declare an error while rejecting the sale).

The foregoing provisions reduce the time a system or record is locked to the very brief interval actually required for the saving of a record instead of the reading and entry speed of the operators. Since one field does not interfere with another and transactional updates are optimally handled, the statement "Visual DataFlex provides transparent multi user protection to the field level" is amply justified.


Always Perform Edits in a Locked State

To make application logic multi-user safe, a reread of data from disk is required before data in an active record buffer is to be changed. This ensures that no other user or process has changed the data since it was retrieved from disk. In order to create a multi-user safe program the programmer that wants to edit data, should always reread after finding the record to edit or find records to edit in a locked state.

Programs that do not use multi-user safe code will generate error DFERR_EDIT_WITHOUT_REREAD (4155). If you have a program that generates this error, it should be adjusted so it becomes multi-user safe. While this adjustment is ongoing, there may be a need to use the program anyway. It is possible to suppress the error. Be aware that running a program that is not multi-user safe can result in data corruption. You should make your code multi-user safe and switch off the error suppression.

You can suppress the DFERR_EDIT_WITHOUT_REREAD error by setting the DF_REREAD_REQUIRED global attribute to false. You can do this in the program’s code or by setting an environment variable DFREREADOPT to a value (any value). The code below shows a procedure that adjusts all Vendor records in a multi-user unsafe way without generating the DFERR_EDIT_WITHOUT_REREAD error.

Procedure AdjustAllVendorsInAMultiUserUnsafeWay

Boolean bOrgRereadRequired

Open Vendor

Get_Attribute DF_REREAD_REQUIRED To bOrgRereadRequired

Set_Attribute DF_REREAD_REQUIRED To False

Clear Vendor

Find GT Vendor By 1

While (Found)

Move "TEST" To Vendor.Name

SaveRecord Vendor

Find GT Vendor By 1

End

Set_Attribute DF_REREAD_REQUIRED To bOrgRereadRequired

End_Procedure // AdjustAllVendorsInAMultiUserUnsafeWay

The code above can be adjusted in two ways to make it multi-user safe. You can make it one big transaction or alternatively make every edit a separate transaction. The best choice depends on the needs of the edit operation.

Procedure AdjustAllVendorsInOneTransaction

Open Vendor

Begin_Transaction

Clear Vendor

Find GT Vendor By 1

While (Found)

Move "TEST" To Vendor.Name

SaveRecord Vendor

Find GT Vendor By 1

End

End_Transaction

End_Procedure // AdjustAllVendorsInOneTransaction

Procedure AdjustEachVendorsInSeparateTransactions

Open Vendor

Clear Vendor

Find GT Vendor By 1

While (Found)

Begin_Transaction

Reread

Move "TEST" To Vendor.Name

SaveRecord Vendor

Unlock

End_Transaction

Find GT Vendor By 1

End

End_Procedure // AdjustEachVendorsInSeparateTransactions


The DataFlex Database Driver

The Embedded Database

Visual DataFlex is not limited to using only database files in the embedded (DataFlex) proprietary database format. Visual DataFlex can use files in any format for which you have a driver for the Visual DataFlex runtime. These are supplied separately from the Visual DataFlex runtime and development systems (please inquire with your Visual DataFlex dealer). The term "use" here refers to any and all database functions of which Visual DataFlex is capable, including creation, deletion, and editing of records. The Visual DataFlex driver, and some other drivers, also support creation, deletion, and modification of the structures (fields, indexes) of database files from running Visual DataFlex programs.

Everything in this chapter, then, is specific to the embedded (DataFlex) database driver. Equivalent functionality of other database drivers are described in the documentation supplied with those other database drivers.


Database Attributes

Visual DataFlex Attributes

Each of these attributes is documented in its own article in the Visual DataFlex Language Reference. They are specific to the embedded database driver, as may be seen in the first part of their names.

For detailed information on the Visual DataFlex API Attributes, see Database API Attributes in the Language Reference book.

Differences from the API

With the exceptions listed here, these attributes behave identically in the API and the Visual DataFlex runtime.

  • The default value for DF_STRICT_ATTRIBUTES is DFTRUE in the runtime, and DFFALSE in the API.

  • The default value for DF_REPORT_UNSUPPORTED_ATTRIBUTES is DFTRUE in the runtime, and DFFALSE in the API.

  • The attribute DF_RUNTIME_PROGRESS_FREQUENCY is not in the API, and can only be used in the Visual DataFlex runtime.


Transaction Processing

Visual DataFlex supports transaction processing. A transaction is a group of database operations that must all complete, or fail, together to maintain database integrity. If one of them can't be done for some reason, then none of the operations can be allowed to complete.

Let's use the process of creating an invoice as an example. First you would get the last invoice number from a system file, increment it, and write it back. Then you would write a record in the invoice header file. Next, for each line item, you would find the appropriate record in the part-number file, reduce the part's quantity on hand to reflect the quantity being sold on this line item and then write a record in the invoice line-item file. Finally, you would update the customer's balance in the customer file. All of the above operations make up one transaction.

Let's assume that while processing one of the invoice's line items, we discovered that the part specified for the line item was not in stock. The customer had said he needed all the items and could not wait for a back order to be filled. Therefore, we must cancel the invoice and tell the customer that he will have to find someone else to fill his order.

Without transaction processing, for each line-item record that had already been processed, we would have to look up the corresponding record in the part file, increase the part's quantity on hand to reflect the quantity being placed back in stock, and then delete the line-item record. We would then delete the invoice header record. We can't, however, in a multi-processing environment, give back the invoice number because someone else may already have used the next number (there are well-known ways to overcome this problem, but for the sake of our example we will assume that the problem does not exist). This process is called "backout." The code to do it might look like this:

Procedure DoTransaction

number nAmount

integer iItem iBackoutItem

boolean bVoid

// Assign invoice number for this new invoice

reread sysfile

move sysfile.next_invoice to invoice.invoice_number

saverecord invoice

unlock

// For each line item in the invoice ...

for iItemNum from 1 to iNumItems

// Create the item in the item file

clear item

: (move item fields to item database)

saverecord item

// Update our stock file to reflect quantity sold

move item.stock# to stock.stock#

if not (Err) begin

find eq stock.stock#

move (stock.available + item.amount) to stock.available

saverecord stock

end

// Update our invoice total to reflect line item amount

if not (Err) begin

move (nAmount + item.amount) to nAmount

end

// If something went wrong, backout this invoice

if (Err) get DoBackout iBackoutItems iItemNum to bVoid

if not (Err) loop

// If nothing went wrong, update the invoice total

if (iItemNum = iNumItems) begin

reread invoice

move (invoice.amount + nAmount) to invoice.amount

save invoice

unlock

end

End_Procedure

Function DoBackout integer iBackoutItems integer iItemNum returns Boolean

// For each item already saved for this invoice ...

for iBackoutItems from 1 to iItemNum

// Find the previously saved line item

clear item

move invoice.invoice_number to item.invoice_number

move backout_item to item.item#

lock

find eq item.item#

// Update stock to put back the quantity sold from this line item

clear stock

move item.stock# to stock.stock#

find eq stock.stock#

move (stock.available + item.amount) to stock.available

save stock

unlock

// Delete the save line item

delete item

loop

// Delete the saved invoice header

reread invoice

delete invoice

unlock

Function_Return True

End_Function

As you can see, you would have to go to some trouble to back out the invoice. However, using transaction processing, there is an easier way. Before we write the first record we mark the start of the transaction by executing the begin_transaction command. Now all database operations against files that allow transaction processing will be considered as one transaction. When we have successfully finished the last operation in the transaction, we commit the transaction by executing the end_transaction command. If we wish to abort the transaction before it is finished, we can execute the abort_transaction command. Following is the same example using transaction processing:

// Note that this example is not a complete DataFlex program.

procedure Create_Invoice

integer iInvoiceNum, iItem, iCustomerNum

number nAmount

move 0 to nAmount

:

// Mark start of transaction

Begin_Transaction

// Get the next invoice number

Move (Get_Invoice_Num(Self)) to iInvoiceNum

//write the invoice header record

Send Write_Invoice_Header iInvoiceNum

// Update each item's part record and write to line item record

For iItem From 0 To (Item_Count(Self))

If (Update_Part(iInvoiceNum, item)) = True) ;

move (nAmount + (Write_Line_Item(InvoiceNum, iItem))) to nAmount

Else ;

// Backout transaction

Abort_Transaction

Loop

// If we have all the parts requested, update customer balance

If (iItem = (Item_Count(Self)));

Send Update_Customer iCustomerNum nAmount

// Commit transaction

End_Transaction

:

End_Procedure

Note that, except for the abort_transaction command, we no longer need any logic to back out the transaction. When abort_transaction is executed, all database files including the system file that gave us the invoice number are restored to the state that existed before the begin_transaction function was executed.

After a transaction is aborted, the buffers of all the open files with a DF_FILE_TRANSACTION attribute setting of DF_FILE_TRANSACTION_NONE will be cleared. In addition, if any of these are system files, after being cleared, the first record will be read into the buffer.

Although transaction processing looks pretty good, there is a disadvantage to using it. When begin_transaction is executed, a lock is automatically executed. The lock is released after executing end_transaction or abort_transaction. This means a lock will be in effect for the full length of the transaction. This could cause response problems. For instance, in our example, no other invoice could be started until ours was done. However, since no other invoice could be started, the invoice number of an aborted invoice could be restored to the system file for reuse.

Keep in mind that each file can be marked to allow or not allow transaction processing. Therefore, you can control which files are part of a transaction. The only files that should have transaction processing turned off are files that will not be written to (e.g. lookup files).

If you use transaction-processing commands on files that have it turned off, a begin_transaction command is equivalent to a lock or reread, and an end_transaction command is equivalent to an unlock.


Types of Transaction Processing

There are three types of transaction processing: client-atomic, server-atomic, and server-logged. A file's transaction type is specified in its DF_FILE_TRANSACTION attribute. Not all database drivers offer all three types. Local DataFlex databases (as opposed to server-based ones) offer only client-atomic transactions. Individual drivers come with information on how they handle transaction processing and other driver-specific functionality.

DF_FILE_TRANSACTION_NONE

Files used only for lookup should have transaction processing turned off on them.

DF_FILE_TRANSACTION_CLIENT_ATOMIC

Client-atomic transactions can be aborted by the program and, under some circumstances (e.g., deadlock) they will be aborted automatically by Visual DataFlex. Client-atomic transactions do not provide protection from workstation crashes. If the workstation (whether from program failure or system failure) crashes while a client-atomic transaction is outstanding, part of the transaction may have already been written to the database and cannot be automatically backed out.

DF_FILE_TRANSACTION_SERVER_ATOMIC

Server-atomic transactions offer the same benefits as client-atomic transactions and, in addition, offer protection from workstation crashes. If a program (or the system) crashes while a server-atomic transaction is outstanding, the full transaction can be automatically backed out.

DF_FILE_TRANSACTION_SERVER_LOGGED

Server-logged transaction control offers the same benefits as server-atomic transaction control and, in addition, offer a rollback capability. System rollback restores the system to its status as of a specified point in time. For example, if your company payroll processing was started at 1 p.m. and then something went badly awry later in the afternoon, you could use this functionality to roll back the state of your system to the way it was at 1 p.m., so you could start again. Note that all transaction processing done after 1 p.m. would be affected, not just the payroll. This mode can only be used with server-based database files in a client/server architecture.


How Client-Atomic Transaction Processing Works

Once the begin_transaction function has been executed, all database changes are written to a virtual transaction file. There are actually only three operations that have to be saved in the transaction file. When you edit a record, the old contents of the changed fields, the record number, and the file number are saved. When you delete a record, the whole record, the record number and the file number are saved. And, when you write a new record, the record number of the new record and the file number are saved.

When the transaction is committed, the current contents of the transaction file are discarded. The transaction-abort process is more complicated, but basically the transaction file is read backwards sequentially. As each database change is encountered, the appropriate action is taken to back out the change. New records are deleted; deleted records are restored (with the original record numbers), and edited records are changed back to their original contents. Therefore, the databases are put back into the exact state they were in prior to the transaction. This works even for databases that do not allow reuse of deleted records. Once the transaction is backed out, the contents of the transaction file are discarded. If you terminate your program with a transaction in progress, an automatic transaction abort will occur.

Since we are writing to a "virtual transaction file", the file will use extended and/or expanded memory with only the overflow actually being written to disk. Since the transaction file will never contain more than one transaction's data, it is most likely that memory will not be overflowed and no actual disk I/O will ever occur.

What Client-Atomic Transaction Processing Won't Do

First, client-atomic transaction processing does not protect a database from a crash. This means that if a transaction was in progress and the system crashes (maybe because of a power failure) or your program terminates abnormally, the transaction will be partially completed. You would then have to either manually fix up the files or restore the files from backup.

There is also no protection from I/O errors. If you get an I/O error during a database operation that is part of a transaction, you can attempt to abort the transaction. However, there is no guarantee that the abort will work correctly. It is also possible to get an error during the abort process itself. In either case, abort_transaction would return an error and you would probably want to restore the files from backup.

As we discussed earlier, a lock is in effect during a transaction. While this prevents other processes from writing to the files, it does not prevent them from reading. This means that, just as when not using transaction processing, another process can read a partially complete transaction.


Server Command Group

Visual DataFlex is capable of working with client/server databases when they are run in conjunction with that type of database using a Connectivity Kit (driver) for this type of database. Connectivity Kits are database drivers that work with the Visual DataFlex runtime to create a true client/server architecture in multi-user environments. Connectivity Kits area avliable for ODBC-compliant databases, Microsoft SQL, Pervasive.SQL, IBM DB2 Universal Database and other databases.

The following commands invoke server functions from Visual DataFlex programs:

abort_transaction

begin_transaction

end_transaction

get_transaction_retry

login

logout

set_transaction_retry

These commands may behave differently depending upon the database with which they are used, as well as if they are used without a client/server database.


Commands Not to Be Called within a Transaction

Following is a list of commands that return an error if executed while a transaction is in progress:

open

close

login

logout

zerofile

All actions that change the structure of a file, such as adding or deleting fields, should not be done within a transaction. Following is a list of such commands that should not be executed within a transaction (but will not return an error):

create_field

create_index

delete_field

delete_index

erasefile

sort

structure_abort

structure_copy

structure_end

structure_start


Errors within Transactions

A lock/reread, unlock block, and a begin_transaction, end_transaction block both define a scope. One consequence of this is that they must match—for each begin_transaction there must be an end_transaction—otherwise, you will receive an error at compile time.

Another consequence is that you should not exit the scope by calling (or returning from) procedures or functions. The only recommended way to exit a transaction is through the terminating statement (unlock, or end_transaction) or through an abort_transaction command.

Here is a look at error handling within these blocks:

Lock/Reread

(Non-transaction) error occurs:

1. Report Error

2. Continue

Transaction error occurs:

1. Check transaction_retry setting

2. Send verify_retry (to the object containing the lock/reread)

if it is 0: retry (jump to lock/reread)

if it is non-zero: abort (jump to unlock)

Unlock

Begin_transaction

(Non-transaction) error occurs:

1. Jump to end_transaction

2. Abort transaction, backing out to the state at the beginning of the transaction

Transaction error occurs:

1. Check transaction_retry setting

2. Send verify_retry (to the object containing the begin_transaction)

if it is 0: retry (jump to begin_transaction)

if it is non-zero: abort (jump to end_transaction)

End_transaction

Examples of transaction errors are deadlock, can't lock, or lock timeout. These are errors which the driver "thinks" have a reasonable chance of working, if tried again. This set is also user-definable; that is, you can add specific errors to the set of Transaction Errors.


Conclusion

Transaction processing is very easy to implement. In fact it's easier to use it than to write the logic that may be required if you don't use it. Just remember that transaction processing is not suitable for all situations. Analyze the intended use to determine if transaction processing is reasonable for your particular situation.


Creating and Modifying File Structures

With the structure_start and structure_end functions and attribute functions, you can create a new, empty database file or modify an existing file structure.


Execute Structure_Start

Creating New Files

Set file attributes

Create fields and set field attributes

Create indexes and set index attributes

Execute Structure_EndThe following example shows how to create a database file.

integer iFileNumber iFieldNumber iHandle

string sFileName

// Fields used for displaying output

string sField1Value

integer iField2Value

date dField3Value

// The name of your database file

move "test3" to sFileName

// Call to Structure_Start puts the value of the file handle returned

// into the handle number

// In initializing this value to 0 shows that we are creating a new file

move 0 to iHandle

// Start of the structure operation. The DataFlex driver name is "DATAFLEX"

Structure_Start iHandle "DATAFLEX"

set_Attribute DF_FILE_PHYSICAL_NAME of iHandle to sFileName

// Create all the fields in the database.

// The create_field command always inserts a field in the file

// specified by file number.

for iFieldNumber from 1 to 3 begin

create_field iHandle at 0

loop

// Set Field Attributes

// Field Attributes for Field 1

set_Attribute DF_FIELD_NAME of iHandle 1 to "A1"

set_Attribute DF_FIELD_TYPE of iHandle 1 to DF_TEXT

set_Attribute DF_FIELD_LENGTH of iHandle 1 to 20

// Field Attributes for Field 2

set_Attribute DF_FIELD_NAME of iHandle 2 to "A2"

set_Attribute DF_FIELD_TYPE of iHandle 2 to DF_BCD

set_Attribute DF_FIELD_LENGTH of iHandle 2 to 12

// Field Attributes for Field 3

set_Attribute DF_FIELD_NAME of iHandle 3 to "A3"

set_Attribute DF_FIELD_TYPE of iHandle 3 to DF_DATE

// All file creation and modification starts with Structure_Start

// and ends with Structure_End. This example does not use a

// callback function, because the (new) file has no data in it.

Structure_End iHandle 0

// At this point, there is no entry in the filelist. File attributes have

// to be set to make an entry into the filelist. Selection of a filelist

// number to assign to your data file is random. You can assign any number

// not currently in use, or get the next higher available number by using

// the following:

get_Attribute DF_FILE_NEXT_EMPTY to iFileNumber

showln "The next available entry in filelist is" " " iFileNumber

// Set File attributes

//

set_Attribute DF_FILE_LOGICAL_NAME of iFileNumber to sFileName

set_Attribute DF_FILE_DISPLAY_NAME of iFileNumber to sFileName

set_Attribute DF_FILE_ROOT_NAME of iFileNumber to sFileName

// The Structure_End command closed the data file. To

// modify or set field values, you must reopen the file.

open "test3" as iFileNumber

showln "Opening file TEST3"

// Output the .DEF and .FD files

Output_Aux_File DF_AUX_FILE_DEF for iFileNumber

Output_Aux_File DF_AUX_FILE_FD for iFileNumber

set_Field_Value iFileNumber 1 to "HELLO"

set_Field_Value iFileNumber 2 to 200

set_Field_Value iFileNumber 3 to 12/12/94

saverecord iFileNumber

// Get the values of the fields, and display them.

get_Field_Value iFileNumber 1 to sField1Value

showln "The value of Field 1 is " sField1Value

get_Field_Value iFileNumber 2 to iField2Value

showln "The value of Field 2 is " iField2Value

get_Field_Value iFileNumber 3 to dField3Value

showln "The value of Field 3 is " dField3Value

inkey windowindex // hold display for a keystroke

abort

Defaults

When creating a file, there are many attributes which have default values. You may, of course, override any of these by setting their value manually. The following table lists these default values for the DataFlex database driver:

Attribute | Default value
Integrity Checking | TRUE
Lock Type | DF_LOCK_TYPE_FILE
Maximum Records | 10,000
Single/Multiuser | DF_FILE_USER_MULTI
Physical Name | "dfdeflt"
Reuse Deleted | TRUE
Transaction Processing | DF_FILE_TRANSACTION_CLIENT_ATOMIC
Field Name | "FIELD_N" (FIELD_1, FIELD_2, etc.)
Field Length | 0
Field Type | ASCII
Number of Segments in Index | 1
Index Segment Field number | 0
Index Segment Features | 0

Restructuring Existing Files

To modify the structure of a data file, you must begin a restructure operation. This is done with the command Structure_Start. This command takes the handle for an open file (or 0 if creating a new file) and returns a handle to the new structure. You can then set or get the attributes of this structure, changing field types, index segments, etc. Other commands, such as create_field and create_index, allow you to add new fields and indexes. Using the set_Attribute command to modify any database attribute of a file may only be done after you have executed a Structure_Start for the file; you may use set_Attribute to modify non-database attributes anytime.

When you have completed your changes, you execute the command Structure_End, which saves your new file definition, restructuring and/or reindexing the file if necessary. One feature of Structure_End is its ability to provide status information as the restructuring and reindexing operations are going on. This is done through the use of a callback function.

When you execute Structure_End, you may optionally pass it the ID of a UIMS object. During the restructure or reindex operation, this object will be sent a specific message, get_callback, which is a function that takes a String and an Integer parameter and returns an Integer. The String is text that conveys either status information or a warning message. The Integer is a classification of the message, e.g., if it is a warning, the Integer parameter is set to the constant value df_message_warning. The value returned from get_callback is a flag that tells the runtime whether to continue the restructure operation; if it is non-zero, the restructure is aborted.

What happens to a file when you abort a restructure or reindex operation after it has begun depends on the specific database driver being used to access the file, and can result in a damaged file. You may, of course, abort your restructure operation before it has started by executing the structure_abort command; this is guaranteed to leave your file untouched.

The make_file command also can be used on existing files containing data. There are advantages and disadvantages, however, to using make_file to restructure a file. To insert or delete fields, you must modify the .DEF file by hand using a text editor. Inserted fields must be given a field number of 0; deleted fields can simply be deleted from the .DEF. Using make_file in this way provides a quick and easy method to modify a file; however, modifying the .DEF file by hand must be done with great care to ensure that the data in the .DEF file is properly formatted and valid. Since this format has never been documented by Data Access Corporation, you are on your own when undertaking this operation. Nevertheless, if you need to produce a program quickly that will modify a file, the capability is there.

To recap:

  • Open the existing file using the DF_EXCLUSIVE open mode

  • Execute Structure_Start

  • Change file attributes as required

  • Create or delete fields and set field attributes as required

  • Create or delete indexes and set index attributes as required

  • Execute Structure_End

Note: If you change the structure of a file, any previous constraints on that file may no longer be valid; you should reset them after the restructure operation.

The following example shows how to modify an existing database file. It also shows how to create an index.

open test3

integer iFileNumber Field_Number IndexNumber file_handle

// Fields used for displaying output

string sField1Value

integer iField2Value

date dField3Value

move test3.File_Number to iFileNumber

// Let's say we want to create IndexNumber 2

move 2 to IndexNumber

// Start the structure operation

//

move iFileNumber to file_handle //pass Structure_Start its own variable

Structure_Start File_handle "DATAFLEX"

create_index File_handle at IndexNumber

// This shows that the index will be set based on two fields.

set_Attribute DF_INDEX_NUMBER_SEGMENTS of File_handle IndexNumber to 2

// Identify the fields

set_Attribute DF_INDEX_SEGMENT_FIELD of File_handle IndexNumber 1 to 0

set_Attribute DF_INDEX_SEGMENT_FIELD of File_handle IndexNumber 2 to 2

Structure_End File_handle 0

showln "Index Creation Completed"

open test3

move test3.file_number to iFileNumber

showln iFileNumber

//Output new .DEF and .FD files

Output_Aux_File DF_AUX_FILE_DEF for iFileNumber

Output_Aux_File DF_AUX_FILE_FD for iFileNumber

showln "DEF and FD output completed"

Querying File Attributes

File attributes can be queried with the get_Attributes command. The following example illustrates how file, index, and index-segment attributes can be queried to display a table from a running program that lists the numbers of the fields making up each index of a database file named calories:

handle hFile

integer iLastIndex iCounter iIndexNum iNumSegments iFieldNum

// Open CALORIES, and retrieve its filelist number to hFile

move 0 to hFile

open "Calories" as hFile

// Get the number of the highest numbered index,

// and use that as the upper limit for the loop.

get_Attribute DF_FILE_LAST_INDEX_NUMBER of hFile to iLastIndex

for iIndexNum from 1 to iLastIndex

// For each index, retrieve the number of segments.

get_Attribute DF_INDEX_NUMBER_SEGMENTS of hFile iIndexNum to iNumSegments

show "INDEX " (string(iIndexNum)) " is based on fields: "

for iCounter from 1 to iNumSegments

// For each index segment, retrieve the number

// of the field used for this segment.

get_Attribute DF_INDEX_SEGMENT_FIELD of hFile iIndexNum iCounter ;

to iFieldNum

show (string(iFieldNum)) " "

loop

showln

loop


Structure Command Group

create_field | output_aux_file
create_index | set_relate
delete_field | structure_abort
delete_index | structure_copy
field_map | structure_end
load_def | structure_start
make_file |

The Structure group provides the means for creating database files from running programs without the requirement to rely on any file definition defined outside the program. It is possible using these commands to specify virtually every aspect of a database file besides the actual data in the file. The same capabilities they offer for creation of files are also usable for modifying existing files, including both addition and deletion of fields, indexes, and index segments. Finally, to complement the group's capabilities in specifying the attributes of files and their components, there is a parallel capability in querying the existing state of the same attributes in existing files.

Most structuring and restructuring of files is done within a block begun by a structure_start command and terminated by a structure_end command. Within this block, attributes are set by the set_attribute command and queries by the get_attribute command. Neither of these last two commands is in the Structure group, however, because they are used for the setting and querying of attributes generally, not just those of files and their components.

Structure operations can be abandoned, or cancelled, by use of the structure_abort command, which undoes all operations in a structure block if the command is executed before structure_end is executed. Structure operations can be based on the structure attributes of an existing file by use of the structure_copy command. Fields and indexes can be added or deleted by the use of individual commands for deletion or creation.

All commands in this group other than structure_..., create_... and delete_... can be executed outside structure blocks, and those that can change attributes (file_size, make_file, set_relate) should not be executed inside structure blocks.


Opening Directory Files Not Named in the File List

With the as option of the open command, it is possible to open a directory fileset for a database file other than the one identified in the file list entry. This can be useful where a group of database files shares a common field structure but has different filenames or directory paths. With open ... as, the directory names and paths of physical filesets can, in fact, even be entered at run time.

This optional element of open must contain the directory rootname of the fileset to be opened (no extensions), plus the directory path where necessary, followed by the word as. Following as, the DataFlex name of an actual .FD file-definition file (created by an entry in the file list) must be given.

The physical fileset must have identical fields in terms of their names, their types, and the order in which they stand in the file, to those of the database file actually in the file list.


Callback Functions

A number of commands (such as call_driver, copy_db, sort, structure_end, and copy_records) use a callback function to report status back to the caller. The object receiving the get_callback should define a function whose first line looks like this:

function CallBack [for class] string Callback_msg integer Callback_type returns integer

Your callback function must return either DFTRUE or DFFALSE. If it returns DFFALSE, the operation will continue. If it returns DFTRUE the operation will be aborted. The for class parameter permits defining the function for inheritance by every instance of class.

On entry to your callback function, Callback_msg is a string. Callback_type indicates the type of message that Callback_msg contains. Callback_type will be one of the following:

DF_MESSAGE_TEXT - a generic message not falling into one of the following types.

DF_MESSAGE_HEADING_1 | the highest level heading.
DF_MESSAGE_HEADING_2 | a second level heading.
DF_MESSAGE_HEADING_3 | a third level heading.
DF_MESSAGE_HEADING_4 | a fourth level heading.
DF_MESSAGE_HEADING_5 | the lowest level heading.
DF_MESSAGE_WARNING | a warning message.
DF_MESSAGE_PROGRESS_TITLE | the title of the following progress values.
DF_MESSAGE_PROGRESS_VALUE | one or two values indicating how far the current operation has progressed.

By knowing the type of the message, an application can intelligently handle displaying the message. For example, if the message type is DF_MESSAGE_WARNING, the message can be displayed in a text box labeled Warning. In addition, the application may want to give users an opportunity to abort the operation if a warning message is received—although this can be dangerous. If they abort during a sort or restructure, indexes and even files can become corrupt.

All messages apply to the last message heading level. Since a heading typically identifies the current operation being performed, this gives the user an idea of what is happening. As an example, you might receive the following sequence of messages:

Message Text | Message Type
Sorting file 1 | DF_MESSAGE_HEADING_1
Building index 1. | DF_MESSAGE_HEADING_2
Building index 2. | DF_MESSAGE_HEADING_2
Sorting file 2. | DF_MESSAGE_HEADING_1
Building index 1. | DF_MESSAGE_HEADING_2

Since we know the types of the messages, they can be presented to users in a way that leaves the last header message for each level visible.

Progress messages (DF_MESSAGE_PROGRESS_VALUE) pass one or two integer values separated by a comma. The first (or only) integer represents the current completion level of the operation. The second integer represents the value for total completion of the current operation. For example, a DF_MESSAGE_PROGRESS_VALUE of 20,100 might mean that 20 out of 100 records have been read or that the operation is 20% complete. The actual meaning of the DF_MESSAGE_PROGRESS_VALUE is specified in the previous DF_MESSAGE_PROGRESS_TITLE message. The following is an example of a sequence that could be received:

Message Text | Message Type
Sorting file 1. | DF_MESSAGE_HEADING_1
Building index 1. | DF_MESSAGE_HEADING_2
Percent Complete: | DF_MESSAGE_PROGRESS_TITLE


The Embedded Database

  • Directory Files of a Database File

  • File Names & Extension Types

  • Records

  • Call_Driver Function Constants


Directory Files of a Database File

The term "database file" in DataFlex does not mean the same as "disk file" in computers. A DataFlex "database file" is actually a group of disk files sharing a common rootname in the directory. The files that make up a database file, then, are distinguished by their extensions, which are described below.

The Data File (*.DAT)

Every database file has a file whose name is of the form fileName.DAT. This file is typically the largest of the fileset, and it contains the actual data of the database file. It contains very little besides data. The files do not contain any characters separating one field from another, nor even one record from another. It does not even contain record numbers—records are found and identified by the number of bytes away they are from the beginning of the file's data. *.DAT files do contain a section (called the "header") at the beginning identifying the number of records in the file, the order, lengths and types of the fields, and so on. Some of this data may be considered Record 0 of the file, but it cannot be accessed or displayed in the manner of data in an actual record.

Compressed Data File (*.VLD)

If compression is elected for a database file, its data is kept in a file whose name ends in .VLD. When this is done, the .DAT file becomes a small fraction of its former size, and contains only pointers into the .VLD file. The .VLD file also is a great deal smaller than the .DAT file was, or would have been, even in combination with the new, shorter form of .DAT file that is used with compression.

Index Files (*.K##)

The great majority of database files have one or more indexes, up to a maximum of 15. A database file that has one index includes a file whose name is of the form fileName.K1. A database file with two indexes would have fileName.K1 and fileName.K2, and so on if there were more indexes. These files contain the key value for the index of every record, along with the record number for each. When a database file has many indexes with long keys, the aggregate size of the index files can exceed that of the *.DAT file.

For any given index to be usable at run time, the pertinent *.K## file must be present. If an index file is in a directory other than the directory of its *.DAT file, the other directories in the Data registry key will be searched for the file. When an index is being created (or reindexed), it will be created in the directory of the *.DAT file unless a predecessor version is found in a (different) Data directory.

Field-Name File (*.TAG)

The names of the fields of a database file are kept in a file whose name is of the form fileName.TAG. These field names are required for running the Database Builder and other tools on the file. This is an ASCII file, and it can be edited, but doing so is not generally recommended. If you wish to discourage browsing or modification of a file, you may remove its *.TAG file, as the database manager does not require it for using the database file.

File-Definition File (*.FD)

The file-definition file also contains a list of the database file's fields, and the file's DataFlex name (the name by which it is to be addressed in programs) as well. This file links each field name to the ordinal field number in the database file’s definition, and links the DataFlex name to the database file’s number in DataFlex’s file list. The file is required only for compilation of programs that address the database file and, although it is a plain ASCII file, it should not be modified by any means other than the tools. Where you wish to discourage compilation of all programs that use a particular database file, you may remove the *.FD file.

Definition-Display File (*.DEF)

All the foregoing files are created and maintained automatically by Database Builder. The *.DEF file, on the other hand, is neither created nor updated automatically, and the tools do not use it. The *.DEF file is a plain ASCII file that contains a tabular display of the database file’s names, number of records, maximum number of records, multi-user status, field names, types, and lengths, and definition of all indexes. In short, everything. When you print a file definition from a tool, the printout exactly matches this file. These files can be used to create on-disk displays of a file's structure, and they are used by the make_file command.

Header File (*.HDR)

The Database Builder contains an option called Header Integrity Checking. If this option is used for a database file, there will be a fileName.HDR file. This file is modified whenever a record is deleted or saved, or the database's definition is changed (through the Database Builder). If the header data (contained in the .DAT file) becomes corrupted, the .HDR file is required to restore it.

See Also

File Names & Extension Types


File Names & Extension Types

Each Visual DataFlex database file is actually a "family" of operating system files which function with each other for the Visual DataFlex user as a single unified whole. The data file rootname (specified by the developer in the Studio or Database Builder) is common to all the members of a family of data files, while differing extensions denote various standard functions for each of the different files.

The following are the standard extensions and file names used by Visual DataFlex. The "*" in the Type column indicates a file required for runtime. The "@" indicates a file required only at compile time. Files with .FD extensions must be on the same disk drive or subdirectory as the .DAT file which has the same rootname. SOLID CAPITAL letters denote explicit names and extensions, while italics represent a file's purpose or classification whose name or extension are user-defined.

File Name | Type | Description
rootname.DAT | *Random | Visual DataFlex data file. Contains file definition and data (one per database file)
rootname.HDR | *Random | Header integrity backup file
rootname.VLD | *Random | Compressed data file
rootname.K## | *Random | Visual DataFlex index file (one for each index)
rootname.FD | @Sequential | File definition info for compiler
rootname.DEF | Sequential | File definition listing
rootname.TAG | Sequential | Field tag names file
rootname.BAD | Sequential | Duplicate or corrupted records found in reindexing
program.EXE | *Flex | Executable Visual DataFlex program
program.SRC | @Sequential | Source code for sample programs
program.PRN | Sequential | Intermediate code output by compiler
program.FLP | @Flex | Pre-compiled program segment produced by Compiler
program.PKI | @Sequential | Symbol table for .FLP file
program.ERR | Sequential | Error file produced by compiler
FLEX.CFL | @Random | Packed macro command (FMAC) file
######.DFR | Encrypted | User-counting control file
FILELIST.CFG | *Random | Default filename of list of active file numbers
TERMLIST.CFG | *Random | Registration file
DF_COLLATE.CFG | Sequential | Collation sequence definition
CODEMAST.* | Random | Database of codes for application code lists
CODETYPE.* | Random | Database of code types for application code lists

See Also: Directory Files of a Database File


Records

While files and fields have both names and numbers, records have only numbers. These numbers start with 1 in every file, and proceed without gaps up to the last record in the file. Deletion of records in a file can create apparent gaps in record numbers. New records will always be created under the numbers of previously deleted records unless there are no deleted record numbers available or the Re-Use Deleted Space option has been rejected in the database file's definition. Thus, record numbers indicate the physical order of records in the data file on disk.

Every record of a database file has a value for every field, and every field has a value in every record. In DataFlex, there is no distinction between a null field and an empty one. DataFlex will not create a new record if all of its fields are blank. A blank record can be created from an existing one, however, by clearing all of its fields.

A given record is always ultimately found by its record number, which is a physical pointer based on the distance of the record from the beginning of the data. In a file of 20-byte records, the fifth record begins 100 bytes after the start of the first record. Fields are found the same way. DataFlex database files contain no delimiters.

Record Blocking

In order to facilitate finding records, DataFlex assigns record lengths that may exceed the aggregate length of the fields in the file. In a file whose fields total 27 bytes, for example, records will be created every 28 bytes, with one undefined byte "in" (or between) each record. Files whose fields total 28 bytes in length will be assigned the same size (28-byte) records. This blocking factor is applied automatically when a database file is created.

The following record sizes are available:

8 | 9 | 10 | 11 | 12 | 13 | 14 | 15
16 | 17 | 18 | 19 | 20 | 21 | 22 | 23
24 | 25 | 26 | 28 | 30 | 32 | 34 | 36
39 | 42 | 46 | 51 | 56 | 64 | 73 | 85
102 | 128 | 170 | 256 | 384 | 512 | 640 | 768

The blocking factor continues upward in 128-byte increments after 256.

Following is an illustration of what the first 60 bytes of data on disk would look like in a file containing records whose fields totalled 27 bytes. Since 27 bytes' total field length is assigned to records of 28 bytes' length, the last byte in each record block would be untyped (and not used). The hypothetical record is made up of the following fields:

a | String, 15 bytes
/ | Date, 3 bytes
1 | Number, 2 bytes
b | String, 4 bytes
2 | Number, 3 bytes
x | undefined

10 20 30 40 50 60

| | | | | |

aaaaaaaaaaaaaaa///11bbbb222xaaaaaaaaaaaaaaa///11bbbb222xaaaaaaaa

| | |

Record 1                    Record 2                    Record 3


Call_Driver Function Constants

There are several symbolic constants defined for the embedded (DataFlex) database driver. They are described in the following table. They can be used in the call_driver command to call the functions they represent.

Name | Meaning
FLEX_SET_MAX_SORT_BUFFER | Sets the maximum amount of memory to be used in sorting. The default is 8 Megabytes, or all available, if it is less.
FLEX_GET_MAX_SORT_BUFFER | Reports the maximum amount of memory to be used for sorting.
FLEX_SET_INDEX_OPT | Sets whether index data may be returned to the program on record finds
FLEX_GET_INDEX_OPT | Returns whether index data may be returned to the program on record finds
FLEX_REPAIR_FILE | Repairs a file that is damaged.
FLEX_GET_REPAIRS_NEEDED | Does an inspection of the file header, reporting errors found.
FLEX_GET_STRICT_TRANSACTIONS | Returns whether absence of TTS generates error when SERVER_ATOMIC files opened
FLEX_SET_STRICT_TRANSACTIONS | Sets whether absence of TTS generates error when SERVER_ATOMIC files opened

Some notes about the above functions:

  • When repairing a file, there are two phases: (1) the header is repaired, if necessary; and (2) the file is sorted, which repairs damaged indexes and a corrupt .VLD file. If you wish to skip the sort, there is a parameter of the call_driver command for doing so.

  • These constants are returned by FLEX_GET_REPAIRS_NEEDED to report what repairs are needed:

    FLEX_HEADER_OK
    FLEX_BLOCK0_BAD
    FLEX_HEADER_BAD
    FLEX_HEADER_INTEGRITY_OFF
    FLEX_HEADER_UNREADABLE

    If FLEX_GET_REPAIRS_NEEDED returns FLEX_HEADER_UNREADABLE, the file probably cannot be repaired; if it returns FLEX_HEADER_INTEGRITY_OFF, it definitely cannot be.

  • To keep a database file repairable, you must keep its header-integrity checking turned on (in Database Builder). This maintains a .HDR file for the database file that is examined by the FLEX_GET_REPAIRS_NEEDED function and used by the FLEX_REPAIR_FILE function.

  • If you attempt to set FLEX_SET_MAX_SORT_BUFFER to a value less than 32K, you will receive an error. Note that this function uses Kilobytes (1K =1024 bytes).

  • These constants are returned by FLEX_GET_INDEX_OPT to report under what conditions programs may use data as contained in indexes instead of filling the record buffer to access record data after a find:

    FLEX_INDEX_OPT_ON
    FLEX_INDEX_OPT_OFF
    FLEX_INDEX_OPT_PERMISSIVE

    ON is the default setting, allowing the use of index data for all segments that are not set case-insensitive. OFF prohibits all use of index data; the record buffer is always loaded. PERMISSIVE allows the use of all index data, including that in case-insensitive index segments.

  • FLEX_SET_STRICT_TRANSACTIONS is useful for utility and other programs that do not enter or edit transactions, and may be executed without the NetWare shell. If a file whose DF_FILE_TRANSACTION attribute is set SERVER_ATOMIC is opened by an application not running under the NetWare shell, an error will be returned. Setting this function DFFALSE will suppress the error and permit the open, without modifying the file's attribute.