# CHAPTER 4: Database Manipulation Language Interface

The Database Manipulation Language Interface provides a set of procedures for a direct interface to the database structures in the Fortran programming language. These are called the `mf` routines. These routines have the advantage of being faster than the Command Language Interface, since the parser is not invoked for each call. In addition, the user has much greater control over what is done with the retrieved data, such as performing complex arithmetic functions, generating customized report formats, generating high-level user interfaces, and so on. It should be noted, however, that using the Database Manipulation Language Interface does require more care than using the Command Language Interface, and hence represents a greater programming effort.

## 4.1 The mf Routines

The `mf` routines are classified into:

1. Routines that open and close tables.

   The first group includes routines that open and close table files. A table must be opened before it can be read or updated. Opening a table will also lock it at the level indicated in the data dictionary for the database. Any table which has been opened must be closed before the program ends. The lock will be released when the table is closed.

2. Qualification building routines.

   The qualification-building routines are used to specify a subset of a table. This is analogous to the construction of a `WHERE` clause in the Empress SQL.

3. Record retrieval routines based on qualifications.

   The third group of routines is used to retrieve records from one or more tables. Any qualifications which may have been constructed are used by these routines. The retrieval routines will automatically use indices if they are present to improve retrieval speed.

4. Record manipulation routines.

   The fourth group of routines is used to add to, delete from, and update tables.

## 4.2 Accessing the mf Routines

### 4.2.1 The Header File

The header file `msfer.h`, which has the correct definitions for all the `mf` routines, must be included at the beginning of each program using the `mf` routines.

You should use the full pathname for this file, which resides in the `include` directory under the directory where `Empress` is installed, unless your system provides other conventions for including files.

`msfer.h` contains:

       logical  mfcpi, mfchka, mftrst
       logical  mfadd, mfdel, mfgetb, mfopen
       logical  mfptvs, mfptvi, mfputi, mfsrtb, mfput
       logical  mfqatr, mfqc, mfqci, mfqmch
       logical  mfqnul, mfqrg, mfqrgi

       integer  mfcomp, mfgtvi, mfgetv, mfigta
       integer  mfget, mfprev, mfrget, mfrprv
       integer  mfoper

       external  mfcall, mfstop, mfinit, mfexit, mfcln
       external  mfadd, mfdel, mfgetb, mfcpi, mfchka, mfopen
       external  mfptvs, mfptvi, mfputi, mfsrtb, mfput
       external  mfcomp, mfgtvi, mfgetv, mfigta
       external  mfget, mfprev, mfrget, mfrprv
       external  mfgete, mfclos, mfacls, mfsetn, mfqand, mfqor
       external  mfqnot, mfqatr, mfqc, mfqci, mfqmch, mfqnul
       external  mfqrg, mfqrgi, mfqieq, mfqseq
       external  mftrst, mftrcn, mftrcm
       external  mferrt, mfoper, mfprte

### 4.2.2 Initialization and Cleanup

There are two ways to organize a program using the `mf` routines.

- You can have the main program unit defined for you by naming the entry point of your program subroutine mfmain. Initialization and cleanup will then be taken care of. If you do this, you should also replace the `stop` call, if any, with a call to `mfstop`.

- You can write your own main program unit, calling `mfinit` before using the interface routines and `mfcln` before `stop`. These calls are necessary to carry out various initialization and cleanup tasks.

### 4.2.3 Compiling and Linking

Compile and link programs using `mf` routines with the utility program `empf77` or `empf90` (Refer to the Manual Pages for deatil information). This compiles and links your program including the Empress libraries.

## 4.3 Handling Normal Failures

There are several situations in which routines can be unsuccessful in performing what an application intends. These arise when record-level locking is in force for a table and a record which the routine needs to access is locked, or when data which a routine attempts to insert into a table, or update it with, is disallowed because of range checking or limited-duplicate indices.

The routines return `.TRUE.` when successful and `.FALSE.` when they fail. To prevent failures from aborting the program, the `mferrt` procedure must be called with a non-zero argument. If a failure occurs, the routine `mfoper` is called to return an error code indicating why the routine failed, and the application program may then do something sensible about it.

The error codes returned by `mfoper` are given in the header file `EMPRESSPATH/include/errorid/mr_error_id.h`. This header file shows the error codes and their related error messages.

Alternatively, you may elect to have the calling program terminated, with a suitable error message printed automatically, when one of these routines fails. This eliminates the need to check error codes and write routines to handle failures properly, but makes the application less robust in regard to user errors.

To have the calling program terminated on failure, use the header file `msf.h` instead of the normal header file, `msfer.h`. Note that usage of several of the routines is different in this case, since they have no returned values.

## 4.4 Internal, External, and File Attribute Formats

Throughout this manual, references will be made to internal, external, and file formats of attribute values.

The internal format is the format in which the value is manipulated by Empress, and does not necessarily correspond to the external format. For example, `DATE (1)` values are not manipulated as `1 May 1992`, nor are dollar values manipulated complete with dollar signs, asterisks, and commas.

The external format is what is printed by a Query Language `SELECT` statement. External formats are character strings.

Finally, there is the file format, which is the way the value is actually stored by Empress. Application programs do not retrieve file formats values.

Most `mf` output routines produce external formats, and most `mf` input routines expect external formats. There are a few input routines which accept integers or internal formats as input and convert them to file formats, and a few output routines which produce integers or internal formats rather than external formats (`mfcpyi`, `mfputi`, `mfgtvi`, `mfptvi`).

To do arithmetic on external format (character string) retrieved values, they must first be converted to numeric values using `sscanf`, `atof`, etc. Note that dollar signs, asterisks, and so on must be removed from dollar values before converting them. A simple procedure for doing this, `dolcvt`, is presented in the example program `interest.f`.

Bulk data values are handled differently from other types. To output bulk values in external format (as in a Query Language `SELECT`) would require converting the bytes to hexadecimal characters, which is not what is generally needed. Instead, bulk data output is normally in internal format using the routine `mfcpyi` this routine is intended specifically for retrieving bulk data, though it can be used to get the internal format of any other type of data as well. The routine `mfputi` is intended for placing bulk data into a record.

The internal format of the variable length data types `BULK` and `TEXT` are given below.

  Internal Format - Bulk Data

  In internal format, the bulk data is stored as a structure of the form

  Number of Bytes
  All the Bytes

  The number of bytes is stored as a `integer*4`.

  Internal Format - Text Data

  In internal format, the text data is stored like this:

  All the bytes terminated with a null byte

  The number of bytes is stored as a `integer*4`.

## 4.5 Naming Conventions

The Empress `mf` routines have been named in a logical manner to indicate their functions. Since external names in Fortran are limited, the Database Manipulation Language Interface names are by necessity abbreviated. Each routine name begins with `mf`. Most then have a third generic letter, such as `q` for the routines dealing with qualifications, or a full word, such as `get` for the routines which find attribute values. A list of all the `mf` routines is shown on below. When writing programs, refer to the chapter "Manual Pages" for the detailed specifications of each routine.

The following table lists the routines used to open and close tables:

Table 4-1: Open and Close Routines

Name | Purpose
`mfopen` | Open a table.
`mfclos` | Close a table.
`mfacls` | Close all tables and de-allocate storage.

The following table lists the routines used to check attributes:

Table 4-2: Check Attribute Routines

Name | Purpose
`mfchka` | Check whether an attribute exists.
`mfigta` | Find an attribute name given its number.

The following table lists the routines used to retrieve values and records:

Table 4-3: Retrieval Routines

Name | Purpose
`mfgetb` | Initialize retrieval routines.
`mfsrtb` | Initialize retrieval routines for sorted output.
`mfget` | Retrieve the next record.
`mfrget` | Try again to retrieve the next record.
`mfprev` | Retrieve the previous record.
`mfrprv` | Try again to retrieve the previous record.
`mfgete` | Terminate retrieval routines.
`mfgetv` | Get an external format attribute value.
`mfgtvi` | Get an integer attribute value.
`mfcpi` | Get an internal format attribute value.

The following table lists the routines used to update records:

Table 4-4: Update Routines

Name | Purpose
`mfadd` | Insert a record into a table.
`mfdel` | Delete a record from a table.
`mfput` | Update a record in a table.
`mfptvs` | Put an external format value into an attribute.
`mfptvi` | Put an integer value into an attribute.
`mfputi` | Put an internal format value into an attribute.
`mfsetn` | Set all attributes in a record to null.

The following table lists the routines used to perform qualifications:

Table 4-5: Qualification Routines

Name | Purpose
`mfqc` | Compare an attribute with an external format constant.
`mfqci` | Compare an attribute with an internal format constant.
`mfqrg` | Compare an attribute with a range (external format).
`mfqrgi` | Compare an attribute with a range (internal format).
`mfqatr` | Compare two attribute values.
`mfqmch` | Match an attribute value with a pattern.
`mfqnul` | Compare an attribute value with `NULL`.
`mfqand` | Perform an `AND` on two qualifications.
`mfqor` | Perform an `OR` on two qualifications.
`mfqnot` | Negate a qualification.
`mfqieq` | Qualify attributes equal to an integer value.
`mfqseq` | Qualify attributes equal to a string value.
`mfcomp` | Test if a constant is "=", ">", or "<" an attribute value.

The following table lists the routines used to control transactions:

Table 4-6: Transaction Routines

Name | Purpose
`mftrst` | Start a transaction.
`mftrcn` | Cancel a transaction.
`mftrcm` | Commit a transaction.
`mftrsv` | Set a save point.
`mftrbk` | Rollback to save point.

## 4.6 General Overview of the mf Routines

Given a database directory, a table within it, and a code for the mode of opening, `mfopen` opens the table and locks it at the level indicated in the data dictionary for the database.

The presence or absence of a particular attribute within a table may be checked by the routine `mfchka`.

Record retrievals must be initialized by calling `mfgetb`, which associates any qualification (an internal representation of a `WHERE` clause) with the record. (`mfgetb` must be called even if there is no qualification.) If sorting is desired, `mfsrtb` should be used instead of `mfgetb`.

Retrievals are accomplished by repeated calls to `mfget` and `mfprev`. Indices will automatically be used to improve retrieval speed if they are present. `mfgetv` is used to transfer an attribute value to user working storage. This produces a character string representation of the data, which may be examined and manipulated by the user's program. `mfgete` must be called to clean up after retrievals.

To create a new record, attribute values are added to a record using `mfptvs`, `mfptvi` or `mfputi` and an entire record may be explicitly set to `NULL` using `mfsetn`. The record is then added to the table by a call to `mfadd`.

Records are deleted using `mfdel`.

Updates are done using `mfptvs`, `mfptvi` or `mfputi` to change attribute values in a previously-retrieved record, and `mfput` to replace the old record with the new one.

Qualifications are created by calls to `mfqc`, `mfqci`, `mfqrg`, `mfqrgi`, `mfqatr`, `mfqmch`, `mfqnul`, `mfqieq`, or `mfqseq`, which compare an attribute value with a constant, a range of values, or another attribute value, perform a match with a constant, a comparison with `NULL`, an "equals" comparison with an integer, or an "equals" comparison with a string, respectively. Complex qualifications are produced using `mfqand`, `mfqor`, or `mfqnot`, which perform `AND` and `OR` functions and negate another qualification, respectively. If you simply wish to see whether a value in external format is equal to, less than, or greater than an attribute value, the routine `mfcomp`may be used.

When all operations on a table have been finished, it should be closed using `mfclos`. Note that it is good practice to close tables once they are no longer needed; otherwise, the application may run out of space.

If an application program terminates abnormally due to some error condition, such as receiving an interrupt, the routine `mfacls` may be called to close all opened tables and de-allocate all `mf`-used storage. However, this routine should not be used indiscriminately in place of `mfclos` and `mfgete` as using it defeats all the normal error-checking done by the `mf` routines. (If an application program has failed to close the tables or de-allocate storage, an error message warning that space is still allocated is normally printed when the program is run.)

Finally, a transaction may be started by `mftrst`, committed by `mftrcm`, or cancelled by `mftrcn`.

## 4.7 Database, Table, and Attribute Names

Most arguments to the `mf` routines are used to specify database, table or attribute names or data values. Generally, these arguments are character strings.

When two or more opened databases have tables with the same name, the database name and a colon are prefixed to the table name:

       database:table

Attribute names can be similarly qualified by prefixing a table name and a period to the attribute name. This may be further qualified by a database name as before. Hence, acceptable forms of attribute names are:

       attribute
       table.attribute
       database:table.attribute

It is good practice to qualify attribute names with their table since the effects of manipulating an attribute from the wrong table may be quite difficult to spot when debugging code. This also guards against errors resulting from later attribute name changes in the tables used by the application.

## 4.8 Error Messages

Several Empress-related errors are liable to occur when using the `mf` routines. There are five major types of errors: program bugs, space problems, user errors, database problems, and file problems. All have the following format:

       *** Error Type *** explanation_of_error

### 4.8.1 Program Bug

A program bug error message indicates that there is a programming mistake in the application program and this usually arises from passing a routine with the wrong parameters. Another example is attempting to insert data in a table which was opened in read-only mode.

### 4.8.2 Space Problem

This error message simply indicates that the program has run out of space. If you get this error, check to see that all tables which are no longer needed are closed.

### 4.8.3 User Error

This error message is not strictly related to the use of the Database Manipulation Language Interface itself, but is more likely to result from data input by users running the applications. It is generally caused by passing incorrect table or attribute names to a routine. Incorrect names which are written into an application program are usually easy to find and fix. The routine `mfopen` can be used to check the validity of table names and `mfchka` can be used to check attribute names.

### 4.8.4 Database Problem

This error is very serious and occurs when the database is found to be corrupted.

### 4.8.5 File Problem

This error occurs when the `mf` routines cannot access a file which they should be able to access. It is generally a serious problem, and may indicate a corrupted database.

## 4.9 Examples Programs

In the following sections, example programs are provided to illustrate in greater detail the use of the `mf` routines. Each example is written as a complete program which can be compiled and run, even though the early examples do not accomplish anything practical. Succeeding examples build logically upon the earlier ones, eventually producing working programs which actually manipulate the sample database by selecting, adding, deleting, or updating records.

The database name in the following examples is a logical database named `repairs`. The physical location of the database could be anywhere on the system, such as:

       /usr/joe/repairs

In the following examples, the header file is indicated as `HEADER_FILE` and you should substitute this with the full pathname of the header file. For example:

       include '/usr/empress/include/msfer.h'

### 4.9.1 Opening and Closing Tables

`mfopen` opens an entire table and locks it at the level indicated in the database's data dictionary (see the Empress SQL: Reference manual for a further discussion on locking). `mfopen` takes the name of the database, the name of the table, and a code for the opening mode. Its usage is:

       flag = mfopen (database, table, mode)

`mfopen` returns `.TRUE.` if the table is opened successfully, and `.FALSE.` if not.

Tables may be opened in read-only mode (`r`), for protection of their contents, or in deferred (`d`) or update `u`) mode, which allows alteration or deletion of records. Since Empress tables are usually created write-protected from all but their owners, an attempt to open a table in update mode by anyone but the owner of the table may fail. Hence, applications which are intended to be run by others and which only examine the contents of a table without altering it should open tables in read-only mode to guard against this kind of failure.

Once all desired operations have been performed on a table, it is necessary to close it using `mfclos`, which takes the table name as its argument and has no returned value. Its usage is:

       call mfclos (table);

Closing a table also removes any locks made by `mfopen`.

The following short program, `open.f`, illustrates opening a table and closing it again.

       subroutine mfmain
       include 'HEADER_FILE'

       print *, 'open and close'
       junk = mfopen ('repairs', 'loans', 'r')
       call mfclos ('loans')
       call mfstop
       end

### 4.9.2 Checking for an Attribute

`mfchka` is a function which returns either `.TRUE.` or `.FALSE.`, depending on whether a particular attribute exists in a table. `.TRUE.` will be returned if it exists; `.FALSE.` if it does not. `mfchka` has the form:

       flag = mfchka (attribute)

where:

`attribute` | is the name of the attribute to be checked for.

The attribute should be qualified with its table name (and the database name, if necessary) to make sure that the correct attribute is checked for.

This routine is particularly useful for verifying data entered by a user.

The following program, `check.for`, shows how to open a table and check for the presence of an attribute.

       subroutine mfmain
       include 'HEADER_FILE'

       junk = mfopen ('repairs', 'loans', 'r')
       if (mfchka ('name')) then
          print *, ' ''name'' is in loans'
       else
          print *, ' ''name'' is not in loans'
       end if
       call mfclos ('loans')
       call mfstop
       end

### 4.9.3 Entering Attribute Values into Records

Attribute values are assigned to a record one at a time, using the routine `mfptvs`, which takes an attribute and a character string as parameters. The usage for `mfptvs` is:

       flag = mfptvs (attribute_name, 'value of attribute')

and it returns `.TRUE.` depending on whether or not the value has been successfully added to the attribute. For instance, attempts to place alphanumeric data into an `INTEGER` attribute, or to access a locked record, will fail.

To set an attribute to `NULL`, the second argument should be "`\0`".

The use of `mfptvs` is illustrated in the example program `add.f` in the next section.

An entire record may also be set to `NULL` using `mfsetn`, with the table name as its argument, instead of `mfptvs`:

       call mfsetn (table_name)

`mfptvi` may be used to put an integer value directly into an attribute, without converting it to a character string first. If the integer and the attribute are incompatible, `mfptvi` will fail. Its format is:

       flag = mfptvi (attribute, integer)

### 4.9.4 Adding Records to a Table

Once the attributes of a record have been assigned values, the record may be added to the appropriate table. This is done by `mfadd`.

`mfadd` has the form:

       flag = mfadd (table)

and returns `.TRUE.` or `.FALSE.` depending on whether or not it is successful.

The example below, `add.f`, shows entering the attribute values into a new record for the `loans` table and actually adding the record to the table.

          subroutine  mfmain
          include     HEADER_FILE'
          junk = mfopen ('repairs', 'loans', 'u')

          if (.not. mfptvs ('name', 'Jones'))
       & call errprt ('Name')
          if (.not. mfptvs ('date', '2 July 1992'))
       &  call errprt ('Date')
          if (.not. mfptvs ('amount', '$750.00'))
       & call errprt ('Amount')
          print *,'Conversion successful'

          if (.not. mfadd ('loans')) then
             print *,'Could not add to 'repairs''
          else
             print *,'Record successfully added to
                   'repairs'
          end if

          call mfclos ('loans')
          call mfstop
          end

          subroutine errprt (name)
          character*(*) name

          print *, name, 'conversion unsuccessful'
          call mfclos('loans')
          call mfstop
          end

### 4.9.5 Retrieving Records

There are seven routines involved in simple retrievals of records from a table: `mfgetb`, `mfsrtb`, `mfget`, `mfrget`, `msprev`, `mfrprv`, `mfgete`.

`mfgetb` associates any qualifications which may have been made (we will see later how to set up qualifications) with one or more records, and performs the initializations necessary to retrieving records. It has the form:

       flag = mfgetb (rel1, rel2, ..., '\0')

It returns `.TRUE.` if all necessary records could be accessed, and `.FALSE.` if not.

If only one table is specified, all subsequent retrieval statements will refer to that table. (Use of more than one table implies a cross-product or join.)

Note that it is not possible to nest calls to `mfgetb` which specify the same table name.

`mfsrtb` behaves like `mfgetb`, except that the records retrieved will be sorted according to the specifications given in the call to `mfsrtb`. Its form is:

       flag = mfsrtb (indicator, rel1, rel2, ..., '\0',
                      attr1, type, attr2, type, ..., '\0')

where:

`indicator` | is the string '`u`' for a unique sort or '`s`' for a non-unique one.
`attr1, attr2` | are the attributes the output is to be sorted on, in the order the sorts are to be made.
`type` | is either '`ascending`' or '`descending`', as desired. `ascending` and `descending` may be abbreviated to '`a`' and '`d`', respectively.

Like `mfgetb`, `mfsrtb` returns `.TRUE.` if all necessary records could be accessed, and `.FALSE.` if not.

`mfget`, `mfrget`, `mfprev` and `mfrprv` do the actual work of retrieving records. `mfget` has no arguments. It simply finds the next record(s) in the table(s), and has the form:

       flag = mfget

It returns `1` until all qualified records have been retrieved, and then returns `0`; if a record is inaccessible (locked), it returns `-1`. Indices will automatically be used to improve retrieval speed if they are present. If `mfgetb` has not been called, `mfget` will fail and the program calling it will stop.

`mfrget` is used to make second and subsequent attempts to retrieve the same record after `mfget` has failed because the record was locked. It has no arguments, and returns the same values as `mfget`.

`mfprev` is similar to `mfget` and is used to find the previous record in the table(s). `mfrprv` is used to make second and subsequent attempts to retrieve the same record after `mfprev` has failed because the record was locked.

Once all records have been retrieved, `mfgete` must be called. It has no parameters and does not return any value; its purpose is to free the space allocated in the retrieval routine.

`mfgetv` is used to actually put the value of an attribute into user working storage. It returns the length of the value found. Each time `mfgetv` is called, it looks for the value of the argument `attribute` in the current record and assigns it to the string variable given as its first argument. If the length of the value is greater than the length of the variable, only part of the value will have been retrieved. It will then be necessary to call `mfgetv` again with a variable of greater length to retrieve the entire value. The format of `mfgetv` is:

       vlen = mfgetv (variable, length, attribute)

where:

`variable` | holds the retrieved value.
`length` | is the length of variable.
`vlen` | is the actual length of the value found.

If the value retrieved is to be converted to an integer, the retrieval and conversion may be done in a single step via the routine `mfgtvi`, which returns an integer rather than a character string. Its usage is:

       value = mfgtvi (attribute)

If the attribute value cannot be converted to an integer, `mfgtvi` will fail and terminate the calling program.

Since `mfget` finds the next record, calls to `mfgetv` separated by `mfget` will get data from different records (unless the end of the table has been reached, in which case `mfgetv` retrieves an undefined value).

Performing a simple retrieval, such as printing the name and phone number of all employees in the `personnel` table, requires a call to `mfgetb`, then a loop within which `mfget` is called for each record and `mfgetv` for each attribute, and the values found printed. `mfgete` is called once the loop is finished. This is shown in the example below, `employees.f`, which is equivalent to the Shell and Standard Interface examples `employees` and `employees.c` (see the Empress Host Language Interface: Command Language Interface manual for the Shell example and C example).

          subroutine   mfmain
          include      'HEADER_FILE'

          integer      empls
          character    namev*12, phonev*12

          junk = mfopen ('repairs', 'personnel', 'r')
          print 10
       10 format ('Current Employees', //
       &         'Name          Phone')

          empls = 0
          junk = mfgetb ('personnel', '\0')

       20 if ( mfget () .ne. 1) goto 40
             junk = mfgetv (namev, 12, 'name')
             junk = mfgetv (phonev, 12, 'phone')
             print 30, namev, phonev
       30    format (1x, a12, a12)
             empls = empls + 1
             goto 20
       40   call mfgete

          print 50, empls
       50 format (1x, 'Total Number of Employees = ', i4)

          call mfclos('personnel')
          call mfstop
          end

To illustrate the use of `mfsrtb`, the following simple example, `sortloans.f`, prints all the employees with loans currently outstanding in alphabetical order, and lists the loans for each with the largest amounts first.

          subroutine   mfmain<
          include      'HEADER_FILE'

          character    name*12, amount*12
           junk = mfopen (`repairs', 'loans', 'r')
          print 10
       10 format ('urrent Loans Outstanding' //
       &         'Name          Amount')

          junk = mfsrtb ('s', 'loans', '\0', 'name', 'a',
       &                                  `amount', 'd', '\0')

       20 if ( mfget () .ne. 1) goto 40
                      junk = mfgetv (name, 12, 'name')
                      junk = mfgetv (amount, 12, 'amount'
                      print 30, name, amount
       30             format (1x, a12, a12)
                      goto 20
       40 call mfgete
          call mfclos ('loans')
          call mfstop
          end

### 4.9.6 Nesting Retrieval Loops

In many application programs, it will be necessary to nest a loop retrieving data from a second table inside a loop retrieving from a first table. In these cases, the following guidelines must be followed.

First, any nested retrievals may not refer to a table from which retrievals are already in progress. For example, an outer loop preceded by:

       junk = mfgetb ('loans', '\0')

and an inner one preceded by:

       junk = mfgetb ('personnel', '\0')

will not give problems, but if the second `mfgetb` call is changed to:

       junk = mfgetb ('loans', 'personnel', '\0')

the program will give erroneous results.

Second, each nested retrieval loop must be terminated with `mfgete`, or the outer loops will either fail or retrieve records from the wrong table. Since `mfget` takes no arguments, the table with which it is associated is determined by the call to `mfgetb` immediately preceding it. Thus, if `mfgete` is not called after an inner retrieval loop has ended, the next call to `mfget` will continue retrievals from the table(s) named in the `mfgetb` call for the inner loop. Furthermore, the next call to `mfgete` will also clean up for the last call to `mfgetb`, since `mfgete` is also associated with the immediately preceding `mfgetb`.

A typical situation in which such problems will occur is in a retrieval loop which exits in the middle, so that the `mfgete` at the loop's end is not reached.

The above guidelines also apply to nested retrieval loops preceded by calls to `mfsrtb`.

### 4.9.7 Qualification Building

Qualifications are analogous to the `WHERE` clause in the Query Language. There are twelve routines involved in constructing qualifications: `mfqatr`, `mfqc`, `mfqmch`, `mfqnul`, `mfqrg`, to compare an attribute value with another attribute value, a constant, a pattern, null, or a range of values, all in external format; `mfqci` and `mfqrgi` to compare a constant or a range of values in internal format; `mfqieq` and `mfqseq` to find attributes equal to an integer or a string; and `mfqand`, `mfqor` and `mfqnot` to perform `AND`s, `OR`s, and `NOT`s.

Most of these routines require some parameters. `mfqatr`, `mfqc`, `mfqmch`, `mfqnul`, and `mfqrg` all return `.TRUE.` or `.FALSE.`, depending on whether the values passed to them can be converted to a legal value for the attribute. The other routines have no return value, and terminate the calling program if they fail.

Their general forms are:

       flag = mfqatr (attribute, attribute)
       flag = mfqc (operator, attribute, string)
       flag = mfqmch (operator, attribute, pattern)
       flag = mfqnul (operator, attribute)
       flag = mfqrg (attribute, lower_limit, indicator,
                    upper_limit, indicator)
       flag = mfqci (operator, attribute, internal_value)
       flag = mfqrgi (attribute, internal_lower, indicator,
                     internal_upper, indicator)
       call mfqieq (attribute, integer)
       call mfqseq (attribute, string)
       call mfqand
       call mfqor
       call mfqnot

All of these routines push a qualification onto an internal stack, the top of which is popped when `mfgetb` is called, so qualifications should be created just before the `mfgetb` is applied.

Note that if two opened tables have attribute names in common, a qualification made on the common attribute must be prefixed with its table name to ensure that the attribute is searched in the correct table. It is good practice to qualify all attribute names with their table names.

### 4.9.8 Conjunctions of Qualifications

In order to create complex qualifications, `mfqand`, `mfqor`, and `mfqnot` are used. These are analogous to the `AND`, `OR`, and `NOT` of the Query Language, respectively. Each call to `mfqand` or `mfqor` pops the top two qualifications from the internal stack and combines them, leaving the new, combined qualification on the top of the stack. Each call to `mfqnot` reverses the sense of (negates) the top qualification on the stack (e.g., if the top qualification was created by a call to `mfqrg`, calling `mfqnot` qualifies all records not in the range). Qualifications of arbitrary complexity can be created by repeated calls to these functions.

`mfqand` is used to find the set of data that satisfies two conditions simultaneously. `mfqand` performs a conjunction of the last two qualifications that were constructed, resulting in a single qualification that must satisfy both previous qualifications. These two previous qualifications are erased and replaced with the new qualification in the process. `mfqand` has no arguments. If there are fewer than two qualifications left in on the stack, `mfqand` will fail when the program is executed by the user.

`mfqor` operates similarly to `mfqand` except that data will be selected that satisfies either the first condition or the second or both. Again, the top two existing qualifications are popped from the stack and replaced by the `OR` combination.

The complexity of qualifications using `mfqand` and `mfqor` is unlimited. However, when constructing a complex qualification, it is necessary to be careful about the order in which the individual qualifications are constructed, so that the desired result is finally achieved.

As an example, suppose you wish to find all loans which are either very large or older than a given date (for example June 1, 1992) but do not belong to a personnel named Mosca.

To begin, the qualification "older than June 1, 1992" must be constructed. This is done using:

       call mfqc ('<', 'date', 'June 1 1992')

and the qualification formed by this is now on the top of the stack. Next, the qualification "larger than $400 is formed:

       call mfqc ('>', 'amount', '$400.00')

This qualification is now on top of the stack, with the previous one as the second item down.

To construct the combination of the two, i.e., "older than June 1, 1992 or larger than $400", a call to `mfqor` is used as follows:

       call mfqor

`mfqor` pops the top two qualifications from the stack and replaces them with the single qualification it forms. The stack now has only one qualification on it. Hence, if another `mfqor` or `mfqand` call were made at this point, it would fail because there are not enough qualifications on the stack.

At this point the qualification "name is not Mosca" is constructed:

       call mfqc ('!=', 'name', 'Mosca')

This qualification is now on the top of the stack, moving the qualification created by the previous `mfqor` call to second place.

At this point, the final qualification can be made, linking the two current ones together in a call to `mfqand`. This is done simply by:

       call mfqand

This replaces the two qualifications on the stack with a single one, equivalent to the following Query Language command:

       SELECT FROM loans WHERE (date < 'June 1 1992' OR amount > 400)
          AND name != 'Mosca';

### 4.9.9 Comparing With a Constant

`mfqc` is used to compare the value of an attribute to a constant and takes three arguments. Its general form is:

       call mfqc (operator, attribute_name,
                  constant)

The first argument specifies the relationship to be used, and must be one of "=", "!=", "<", "<=", ">", or ">=".

The second argument is the name of the attribute to be compared. Note that if there is more than one table containing the named attribute, the attribute must be qualified with its table name.

The third argument is the string containing the constant for the comparison. Note that if this value is not a string, it must first be converted to one.

The following example, `constant.f`, prints the name and amount of each loan for all employees with loans over $100.00.

          subroutine   mfmain
          include      'HEADER_FILE'

          character    namev*12, amtv*12

          junk = mfopen ('repairs', 'loans', 'r')
          print 10
       10 format (1x, 'Employees with Loans Greater',
       &               'Than $100.00', //1x,
       &               'Name  Loans', /)

          junk = mfqc ('>>', 'amount', '100.00')

          junk = mfgetb ('loans', '\0')

       20 if ( mfget () .ne. 1) goto 40
             junk = mfgetv (namev, 12, 'name')
             junk = mfgetv (amtv, 12, 'amount')
             print 30, namev, amtv
       30    format (1x, a12, a12)
             goto 20
       40 call mfgete

          call mfclos ('loans')
          call mfstop
          end

### 4.9.10 Selecting a Range of Values

Although it is possible to select records with attribute values between two constants by using two calls to `mfqc` and a call to `mfqand`, Empress provides a single command to do so, `mfqrg`, which is considerably more efficient than setting up two separate qualifications and combining them.

`mfqrg` is analogous to the `RANGE` keyword of the Query Language, and its syntax is also similar.

`mfqrg` has the form:

       call mfqrg (attr, lower_value, indicator, upper_value,
                   indicator)

where:

`attr` | is the name of the attribute.
`lower_value` | is a string containing the lower bound of the range.
`upper_value` | is a string containing the upper bound of the range.
`indicator` | is either '`e`' or '`i`', specifying exclusive or inclusive boundaries, respectively.

The following example, `range.f`, illustrates the use of `mfqrg` and finds all records from the `loans` table where the amount borrowed is greater than $100 but less than or equal to $200.

          subroutine   mfmain
          include      'HEADER_FILE'

          character    namev*12, datev*17, amtv*12
          junk = mfopen ('repairs', 'loans', 'r')

          print 10
       10 format (1x, 'Employees Owing Between $100 and
       &              $200', //1x, 'Name', 8x 'Date' 13x,
                     'Amount')
          call mfqrg ('amount', '100', 'e', '200', 'i')
          call mfgetb ('loans', '\0')
       20 if ( mfget () .ne. 1) goto 40
             junk = mfgetv (namev, 12, 'name')
             junk = mfgetv (datev, 17, 'date')
             junk = mfgetv (amtv, 12, 'amount')
             print 30, namev, datev, amtv
       30        format (1x, a12, a12, 5x, a12)
             goto 20
       40 call mfgete
          call mfclos ('loans')
          call mfstop
          end

### 4.9.11 Comparing Attributes

`mfqatr` compares the value of two attributes. It takes as its arguments one of six operators: "<", ">", "<=", ">=", "=", and "!=", and the names of the two attributes, and has the form:

       call mfqatr (operator, attribute,
                    attribute)

Note that the two attributes must be compatible, otherwise `mfqatr` will fail.

The following example, `attrcmp.f`, finds all instances in the database where employees have single loans greater than their credit limits. (It also illustrates the use of `mfqand`.)

          subroutine   mfmain
          include      'HEADER_FILE'

          character    name*12, credit*12, amount*12
          call mfopen ('repairs', 'loans', 'r')
          call mfopen ('repairs', 'personnel','r')
          print 10
       10 format (1x, 'Employees Exceeding Credit Limit', //, 1x,
       &              'Name', 8x, 'Credit Limit', 3x, 'Amount', /)

          call mfqatr ('=', 'loans.name', 'personnel.name')
          call mfqatr ('>', 'amount','credit_limit')
          call mfqand

          call mfgetb ('loans', 'personnel', '\0')
       20 if (mfget () .ne. 1) goto 40
               junk = mfgetv (name, 12, 'personnel.name')
               junk = mfgetv (credit, 12, `credit_limit')
               junk = mfgetv (amount, 12, `amount')
               print 30, name, credit, amount
       30      format (1x, a12, a12, a12)
               goto 20
       40 call mfgete
          call mfclos ('loans')
          call mfclos ('personnel')
          call mfstop
          end

### 4.9.12 Match Operators

`mfqmch` performs a match between an attribute value and a pattern, and has the form

       call mfqmch (operator, attribute,
                    pattern)

`mfqmch` takes as its arguments one of four operators: `match`, `smatch`, `!match`, `!smatch`; an attribute name; and the external representation of a pattern.

A pattern is any string. The following characters have special meaning:

Table 4-7: Pattern Matching Characters

Character | Use | Example
`?` | Matches any character in the position. |
`*` | Matches zero or more occurrences of any character. |
`[...]` | Matches any of a set of characters in the position. | `[abc]` matches `a` or `b` or `c`.
`{...}` | Matches zero or more occurrences of a fixed length pattern. | `{[a-z]}` matches any string of lowercase letters.
`[.-.]` | Matches a range of characters in the position. | `[a-cf-i]` matches `a`, `b`, `c`, `f`, `g`, `h`, `i`.
`[^...]` | Matches anything but a set or range of characters in the position. | `[^123]` matches anything except `1`, `2`, or `3` ; `[^a-d]` matches anything except `a<`, `b`, `c`<, or `d`.
`...|...` | Requires a match on either side of the bar. | `ab|cd` requires a match on `ab` or `cd`.
`...&...` | Requires a match on both sides of the ampersand. | `[a-z]&[^x]` matches any lowercase letter except `x`.
`\` | placed before any of the special characters "?", "*", "|", "&", "{", "}", "[". "]", and "\" causes that character to be interpreted as an ordinary character rather than a special one. |

For a more detailed description of patterns, see the Empress SQL: Reference manual.

The following example, `match.for`, selects all employee names and phone numbers from a given local telephone exchange:

          subroutine   mfmain
          include      'HEADER_FILE'

          character    name*12, phone*12

          call mfopen ('repairs', 'personnel','r')
          print 10
       10 format (1x, 'Phone Numbers of Employees in
       &             Local Area',//, 1x, 'Name', 7x, 'Phone' /)

          call mfqmch ('match', 'phone', '544*')

          call mfgetb ('personnel', '\0')
       20 if ( mfget () .ne. 1) goto 40
             junk = mfgetv (name, 12, 'name')
             junk = mfgetv (phone, 12, 'phone')
             print 30, name, phone
       30    format (1x, a12, a12)
             goto 20
       40 call mfgete
          call mfclos ('personnel')
          call mfstop
          end

### 4.9.13 Comparing with Null

Any attribute in Empress can contain a null value. A special routine is required to deal with null, since it does not match anything, nor is it greater or less than anything. `mfqnul` tests whether an attribute value is or is not null. `mfqnul` takes two arguments: an operator and an attribute name. Its format is as follows:

       call mfqnul (operator, attribute)

where:

`operator` | is a string containing "="> or "!=".

The following example, `nullcmp.for`, finds all of the employees whose phone number is not known:

          subroutine   mfmain
          include      'HEADER_FILE'

          character    name*12

          call mfopen ('repairs', 'personnel', 'r')

         print 10
       10 format (1x, 'Employees with Unknown Phone Numbers:',/)
          call mfqnul ('=', 'phone')
          call mfgetb ('personnel', '\0')
       20 if ( mfget () .ne. 1) goto 40
               junk = mfgetv (name, 12, 'name')
               print 30, name
       30      format (1x, a12)
               goto 20
       40 call mfgete
          call mfclos ('personnel')
          call mfstop
          end

### 4.9.14 Complex Qualifications

The use of `mfqand` and `mfqor` is further illustrated in this section. The following example, `oldloans.f`, finds any large or overdue loans outstanding to a particular employee; it retrieves all amounts from the `loans` table for a named employee which are older than a given date or larger than a given amount.

         subroutine   mfmain
         include      'HEADER_FILE'

          character    name*13, date*18, amt*13,
       &             datev*17, amtv*12
          print 2
       2 format (1x, 'Enter customer name: ')
          read *, name
          print 4
       4 format (1x, 'Enter date: ')
          read *, date
          print 6
       6 format (1x, 'Enter loan amount: ')
          read *, amt

          print 20, name, amt, date
       20 format (1x, 'Loans to', a12, 'Greater Than ',
       &              a12, 'or Older Than ',a18//1x,
       &              'Date', 8x, 'Amount', /)
          call mfopen ('repairs', 'loans', 'r')
          call mfqc ('<', 'date', date)
          call mfqc ('>', 'amount', amt)
          call mfqor
          call mfqc ('=', 'name', name)
          call mfqand
          call mfgetb ('loans', '\0')
       30 if ( mfget () .ne. 1)goto 50
              junk = mfgetv (amtv, 12, 'amount')
              junk = mfgetv (datev, 17, 'date')
              print 40, datev, amtv
       40     format (1x, a17, 5x, a12)
              goto 30
       50 call mfgete
          call mfclos ('loans')
          call mfstop
          end

### 4.9.15 Deleting Records

To delete a record, `mfdel` is used. It has the form:

       flag = mfdel (table)

and returns `.TRUE.` or .`FALSE.`, depending on whether or not it could delete the record. Normally, only qualified records are to be deleted, so the appropriate records are found by using `mfgetb` and any qualification routines, and then calling `mfget`, as in the examples in the earlier sections.

The following example, `del.f`, deletes all records from the `loans` table which were made before May 1992. It also illustrates using `mfcall` with the `mf` routines.

          subroutine   mfmain
          include      'HEADER_FILE'

          call mfopen ('repairs', 'loans', 'u')

          call mfqc ('<', 'date', '1 may 1992')
          call mfgetb ('loans', '\0')
       10 if ( mfget () .ne. 1) goto 20
             call mfdel ('loans')
             goto 10
       20 call mfgete
          call mfclos ('loans')
          call mfcall ('repairs', 'select from loans')
          call mfstop
          end

### 4.9.16 Updating Records

Updating a record involves three steps:

- Retrieving the record from the database.
- Changing attribute values of the record.
- Placing the updated record back into the database.

The first step is done using `mfgetb`, `mfget`, and qualifications as before. The second step is functionally equivalent to entering attribute values into records, using `mfptvs`, `mfptvi` or `mfputi`. The last step requires calling the routine `mfput`, which has the form:

       flag = mfput (table)

and returns `.TRUE.` if the update is successful and `.FALSE.` if not.

The following example, `interest.f`, calculates interest at 2% on each loan in the `loan` table, prints out a monthly statement, adds the interest to the existing total, and updates the database. The routine `dolcvt` is used to change the value of a dollar attribute into a form suitable for conversion into `FLOAT` or `DOUBLE`. Note that the new amount of the loan plus interest must be re-converted to a string before it can be used by `mfptvs`.

#### 4.9.16.1 Using Character Strings

Example program, `interest.f` :

          subroutine   mfmain
          include      'HEADER_FILE'
          double precision dolcvt
          double precision dolsum, dolls, int, newamt

          character    name*12, date*17, amt*12, buf*30
          integer      vlen

          junk = mfopen ('repairs', 'personnel','r')
          junk = mfopen ('repairs', 'loans', 'u')

         junk = mfgetb ('personnel', '\0')
       10 if ( mfget () .ne. 1) goto 100
                vlen = mfgetv (name, 12, 'personnel.name')
                print 20, name
       20       format (//1x, 'Monthly Statement for ',
       &                     12a, ':', //,1x, 'Loan
       &               Date Made        Interest        Total')

                junk = mfqc ('=', 'loans.name', name)

                dolsum = 0.00
                junk = mfgetb ('loans', '\0')
       30       if ( mfget () .ne. 1) goto 70
                   vlen = mfgetv (amt, 12, 'amount')
                   junk = mfgetv (date, 17, 'date')
                   dolls = dolcvt (amt, min(vlen, 12))
                   int = dolls * 0.02
                   newamt = dolls + int
                   dolsum = dolsum + newamt
                   print 40, dolls, date, int, newamt
       40       format (1x, '$', f6.2, 3x, a17, 7x,
       &                '$', f6.2, 4x, '$', f6.2)
                write (buf, 50) newamt
       50       format (1x, f6.2)
                read (buf, 51) amt
       51       format (a12)
                if (.not. mfptvs ('amount', amt)) then
                        print 60, amt
       60                 format ('Conversion error: ',
       &                        'cannot convert ', a12,
       &                        'for ''amount''')
                else
                        junk = mfput ('loans')
                end if
                goto 30
       70       call mfgete
                if (dolsum .gt. 0.00) then
                        print 80, dolsum
       80                  format (/, 1x, 'Total now owing: $', f6.2)
                else
                        print 90
       90                  format (/, 1x, 'No loans outstanding.')
                end if
                goto 10
       100 call mfgete
          call mfclos ('loans')
          call mfclos ('personnel')
          call mfstop
          end

          double precision function dolcvt (string, length)
          character string (length)

          character    c, buffer(25), buf*25
          integer      i, j
          j = 1
          do 10 i = 1, length
                       c = string(i)
                       if (c .ne. '$' .and.
       &                  c .ne. '*' .and.
       &                  c .ne. ' ' .and.
       &                  c .ne. ',') then
                          buffer(j) = c
                          j = j + 1
                       end if
       10              continue
          do 15 i = j, 25
              buffer(i) = ' '
       15     continue
          write (buf, 16) buffer
       16 format (25a1)
          read (buf, 20) dolcvt
       20 format (f20.0)
          return
          end

#### 4.9.16.2 Using Internal Data Representations

In some fixed applications, it is convenient as well as faster to use the internal representations of data values rather than character strings when comparing or retrieving attribute values. For example, if numeric data is retrieved as its internal representation rather than a string containing the data, the step involving conversion of the string to `INTEGER` or `FLOAT` can be avoided and arithmetic operations performed more directly. To use internal representations, however, it is necessary to know the form in which Empress data types are stored internally on the machine the application is intended for, since the declaration of the variable to contain the internal representation must be appropriate for the data it will eventually store. For this reason, applications using internal representations are usually not portable among machines, and considerable care is required when altering them later. In general, we do not recommend the use of the routines described below unless you are certain the application really is fixed or speed constraints are vital.

Internal representation using variants of the routines `mfqc` and `mfqrg`, called `mfqci` and `mfqrgi`, respectively, use variables containing the internal representation of the constants used by these routines rather than character strings. `mfqci` is called in a manner very similar to `mfqc`:

       call mfqci (operator, attribute,
                   variable)

where:

`operator` | is one of the standard operators for `mfqc`.
`variable` | is a variable containing the internal representation to which the attribute is to be compared, passed by reference.

`mfqrgi` is also called in a manner very similar to `mfqrg`:

       call mfqrgi (attr, lower,
                    indicator, upper, indicator)

using variables containing the lower and upper limits of the range of values desired passed by reference.

For retrieving and updating attribute values, the routines `mfcpi` and `mfputi` may be used. They are functionally equivalent to `mfgetv` and `mfptvs`, respectively. `mfcpi` is used as follows:

       flag = mfcpi (attribute, variable)

where:

`variable` | is appropriately declared, and passed by reference.

`mfputi` is used as follows:

       flag = mfputi (attribute, variable)

Both return `.TRUE.` or `.FALSE.` to indicate whether they were successful.

For an example of the use of these routines,the `repairs` database no longer suffices. It is too simple and does not have appropriate attributes. Let us instead use an auto parts inventory, kept in a table named `stock` which has attributes `part_code` and `quantity`, both of which are `INTEGERS`. The following program, `invent.for`, prompts for an integer part code, prints the quantity currently in stock, prompts for the number of items sold, decreases the quantity in stock by this amount, and prints the new quantity. It illustrates the use of `mfqci`, `mfcpi`, and `mfputi`.

This program assumes that Empress `INTEGER` data types may be mapped into 16-bit variables. This is true for 16-bit machines, but the program cannot be guaranteed to work consistently on a non-16 bit machine.

          subroutine   mfmain
          include      'HEADER_FILE'

          integer*2    quant, sold, newq, prcode, exitcode
          logical      found, ok  call mfopen ('repairs', 'stock', 'u')
          exitcode = 9999
       10 print 20
       20 format ('enter product code:')
          read *, prcode

          if (prcode .ne. exitcode) then
                call mfclos ('stock')
                call mfstop
                end if
          call mfqci ('=', 'part_code', prcode)
          call mfgetb ('stock', '\0')
          found = .FALSE.
       30 if ( mfget () .ne. 1) goto 70
                found = .TRUE.
                if (.not. mfcpi ('quantity', quant))
       &          quant = 0
                print 40, quant
       40       format ('Number in stock = ', i6)
                print 50
       50       format ('Enter number sold: ')
                read *, sold
                newq = quant - sold
                print 60, newq
       60       format ('Number now in stock = ', i4)

                ok = mfputi ('quantity', newq)
               ok = mfput ('stock')
               goto 30
       70 call mfgete
          if (.not. found)
       &       print 80
       80            format (/, 'No entries for that part code')
          goto 10
          end

### 4.9.17 Transactions

A transaction is a group of operations on a database which have no permanent effect upon it until explicitly committed. If the transaction is cancelled, the database is left in the state it was in before the transaction started (see the Empress SQL: User's Guide for a further discussion on transactions).

A transaction may be started by a call to `mftrst`, which has no arguments and returns `.TRUE.` or `.FALSE.` depending on whether or not it could begin the transaction. Failure normally means a transaction is already in progress.

A transaction is committed by a call to `mftrcm` and cancelled by `mftrcn`. These both have no arguments and return `.TRUE.` or .`FALSE.` to indicate whether the operation was successful.

The general forms of these routines are:

       flag = mftrst
       flag = mftrcm
       flag = mftrcn