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.

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.

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

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.

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.

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.

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.

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

The following table lists the routines used to check attributes:

Table 4-2: Check Attribute Routines

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

Table 4-3: Retrieval Routines

The following table lists the routines used to update records:

Table 4-4: Update Routines

The following table lists the routines used to perform qualifications:

Table 4-5: Qualification Routines

The following table lists the routines used to control transactions:

Table 4-6: Transaction 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 mfcompmay 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.

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.

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

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.

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.

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.

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

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.

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'

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

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:

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

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)

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

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:

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:

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

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.

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 ANDs, ORs, and NOTs.

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.

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';

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

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:

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

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

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

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

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:

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

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

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

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.

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

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:

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:

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

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