# CHAPTER 3: Example Programs

## 3.1 Introduction

This chapter describes in greater detail how to use the mx routines. A number of example programs are given. Each example is a complete program that can be compiled and run. Though the early examples do not accomplish anything useful, later examples build upon earlier ones.

In the example programs of this manual, we will be using `repairs` as the logical database name. The physical location of the database could be anywhere on the system.

## 3.2 Opening and Closing Tables

The routine `mxopen` opens a table and sets up locking for it at the level specified in the data dictionary. (Locking is discussed in the Empress SQL: Reference manual under the LOCK LEVEL command. The data dictionary and its tables are discussed in the Empress: Database Administrator's Guide under The Data Dictionary.) `mxopen` takes the name of the database, the name of the table, and a code for opening the table in read-only, update or dirty read mode. The routine returns true if the table is opened successfully, and false if not. (These are boolean values defined in the header file `mscc.h`.) Its form is:

       flag = mxopen (database, table, mode);

Tables may be opened in read-only mode (`r`) to protect their contents, or in deferred (`d`) or update mode (`u`) to allow 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. Applications for general use that will examine but not alter the contents of a table should therefore open tables in read-only mode to preclude this kind of failure.

A table that is opened in dirty read mode will retrieve records without placing any read locks when the retrieval is performed through `mxget` or other corresponding routines. An update operation will fail in this situation.

Once all desired operations have been performed on a table, it should be closed using `mxclose`. This routine takes the table name as its argument and has no returned value. Its form is:

       mxclose (table);

Closing a table removes any locks made by `mxopen`.

Example

The following program, `open.c` opens, then closes, a table.

       #include <mscc.h>

       #define  DATABASE  "repairs"

       msmain ()
       {
            mxopen (DATABASE, "loans", "r");
            mxclose ("loans");
       )

## 3.3 Checking for an Attribute

The routine `mxchkattr` determines whether an attribute exists in a table. It returns true if the specified attribute exists and false if it does not. The routine has the form:

       flag = mxchkattr (attribute);

The `attribute` is the name of the attribute to be checked for. If the name is used in more than one table currently open, it must be qualified with a table name (and database name, if necessary) to specify the attribute unambiguously.

This routine is useful for verifying attribute names entered interactively.

Example

The following program, `check.c`, opens a table and checks for the presence of an attribute in it.

       #include     <mscc.h>

       #define     DATABASE      "repairs"

       msmain()
       {
            mxopen (DATABASE, "personnel", "r");
            if (mxchkattr ("name"))
                 printf ("'name' is in personnel\n");
            else
                 printf ("'name' is not in personnel\n");
            mxclose ("personnel");
       }

## 3.4 Entering Attribute Values into Records

The routine `mxputvs` assigns a value to an attribute. If there is a current record, the value of the attribute in the current record is altered. The routine takes an attribute name and a character string value. The form is:

       flag = mxputvs (attribute, value);

The routine returns true if the value is successfully added to the attribute and false if not. For example, an attempt to place alphanumeric data into an integer attribute will fail.

To set an attribute to `NULL`, the second argument should be the `NULL` string (" ").

To set every attribute of a record to `NULL`, use the routine `mxsetnull` instead. The form is:

       mxsetnull (table);

The use of `mxputvs` is illustrated in the example program `add.c` in the next section.

## 3.5 Adding Records to a Table

The routine `mxadd` adds a record to a table, using the values currently assigned to its attributes. Its form is:

       flag = mxadd (table);

`mxadd` returns true if the record is successfully inserted and false if not.

Example

The following program, `add.c`, assigns attribute values for a new record for the `loans` table, then adds the record to the table. It also illustrates using `mscall` with the mx routines.

       #include     <mscc.h>

       #define     DATABASE      "repairs"

       msmain ()
       {
            mxopen (DATABASE, "loans", "u");
            mxerret = 1;

            if (! mxputvs ("name", "Jones"))
                 error_print ("Name");

            else if (! mxputvs ("date", "2 July 1992"))
                 error_print ("Date");

            else if (! mxputvs ("amount", "$75.00"))
                 error_print ("Amount");

            else printf ("Conversion successful\n");

            if (mxadd ("loans"))
                 printf ("Record successfully added to\
                      database\n");
            else
                 printf ("Could not add record to\
                      database\n");

            mxclose ("loans");
            mscall (DATABASE, "select from loans");
       }
       error_print (char* name)
       {
            printf ("%s conversion unsuccessful\n", name);
            mxclose ("loans");
            msexit (1);
       }

## 3.6 Retrieving Records

Records are retrieved from a table with a set of routines: `mxgetbegin`, `mxsrtbegin`, `mxget`, `mxreget`, `mxprev`, `mxreprev` and `mxgetend`.

When a record is made current, attribute values are retrieved with the routines `mxgetvs` and `mxgetvi`.

### 3.6.1 Record Retrieval Routines

`mxgetbegin` initializes a group of records for retrieval, using any qualifications that have been set up. (Setting up qualifications is described later.) Its form is:

       flag = mxgetbegin (table_1, table_2, ..., CHARNIL);

`CHARNIL` is `(char*)0` as defined in the header file `mscc.h`.

If only one table is specified, all subsequent retrieval statements refer to that table. Specifying more than one table implies a join. `mxgetbegin` returns true if it succeeds in accessing all the necessary records and false if it does not (usually because a record is locked).

`WARNINGS:` | You should not nest calls to `mxgetbegin` specifying the same table name unpredictable results will ensue.

`mxsrtbegin` is like `mxgetbegin`, except that records are initialized for retrieval in sorted order according to supplied specifications. Its form is:

       flag = mxsrtbegin (indicator, table_1, table_2,
            ... , CHARNIL, attr_1, type, attr_2, type,
            ... , CHARNIL);

`indicator` is the string "`u`" for a unique sort or "`s`" for a standard (no-unique) sort. `attr_n` are the attributes on which retrieved records are to be sorted, in the listed order. `type` is either "`a`" for an ascending, or "`d`" for a descending sort.

If an unique sort is requested, only those attributes that are listed are guaranteed to be unique. Records may contain duplicate values for unlisted attributes, and the order of such records is undetermined.

`mxget`, `mxreget`, `mxprev` and `mxreprev` retrieve single records from the table (or tables in the case of a join). `mxget` has no arguments. It makes the next record of the table current. In a join it makes the records forming the next line resulting from the join current. Successive calls to `mxget` step through the table(s) until all records have been retrieved. The routine returns 1 if the record is retrieved successfully, 0 if there are no more records, and -1 if the record is locked.

If a record is locked, `mxreget` may be used to make repeated attempts to retrieve the same record. It returns the same codes as `mxget`.

The routines `mxprev` and `mxreprev` are used to retrieve previous records. They return the same codes as `mxget`.

They have the forms:

       flag = mxget ();
       flag = mxreget ();
       flag = mxprev ();
       flag = mxreprev ();

Indices are used if they are present. If `mxgetbegin` has not been called, these routines will fail and the calling program will stop.

Once all records have been retrieved, `mxgetend` should be called to free the space allocated by the retrieval routines. It takes no arguments and does not return any value.

### 3.6.2 Retrieving Attribute Values

The routine `mxgetvs` is used to retrieve attribute values. It returns a character pointer to an attribute value. Its form is:

       value = mxgetvs (attribute)>

Each time `mxgetvs` is called, it retrieves the value of the specified attribute in the current record. Note that since `mxget` makes the next record current, calls to `mxgetvs` separated by calls to `mxget` will retrieve data from different records (unless the end of the table has been reached, in which case `mxgetvs` retrieves an indefinite value).

If the value retrieved is to be converted to an integer, the retrieval and conversion may be done simultaneously with the routine `mxgetvi`, which returns an integer rather than a pointer to a character string. Its form is:

       value = mxgetvi (attribute);

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

Example

The following program, `employees.c`, does a simple retrieval, printing the name and phone number of all records in the personnel table. The program contains a call to `mxgetbegin`, then a loop that calls `mxget` to process each record. Within the loop `mxgetvs` is called for each attribute, and the retrieved values printed. `mxgetend` is called when the record retrieval loop is finished.

       #include     <mscc.h>

       #define     DATABASE      "repairs"

       msmain ()
       {
            int     employees;
            char*   name_value;
            char*   phone_value;

            mxopen (DATABASE, "personnel", "r");
            printf ("Current Employees\n\n");
            printf ("Name         Phone\n");

            employees = 0;
            mxgetbegin ("personnel", CHARNIL);
            while (mxget ())
            {
                 name_value = mxgetvs ("name");
                 phone_value = mxgetvs ("phone");
                 printf ("%-12s%-12s\n", name_value,
                      phone_value);
                 employees++;
            }
            mxgetend ();

            printf ("\nTotal Number of Employees = %d\n",
                 employees);
            mxclose ("personnel");
       }

The following program, `sortloans.c`, illustrates the use of `mxsrtbegin`. It finds all employees with loans currently outstanding and prints their names in alphabetical order, listing the loans for each with the largest amount first.

       #include     <mscc.h>

       #define     DATABASE      "repairs"

       msmain ()
       {
            char*  name;
            char*  amount;

            mxopen (DATABASE, "loans", "r");
            printf ("Current Loans Outstanding\n\n");
            printf ("Name         Amount\n");
            mxsrtbegin ("s", "loans", CHARNIL, "name",
                 "a", "amount", "d", CHARNIL);
            while (mxget ())
            {
                 name = mxgetvs ("name");
                 amount = mxgetvs ("amount");
                 printf ("%-12s%-12s\n", name, amount);
            }
            mxgetend ();
            mxclose ("loans");
       }

### 3.6.3 Nesting Retrieval Loops

In application programs it is often necessary to nest a loop retrieving data from a table within a loop retrieving data from another table. In these situations, a number of points should be remembered:

1. A nested retrieval may not refer to a table from which records are already being retrieved. A program structured as follows will give no problems:

          mxgetbegin ("loans", CHARNIL);
          while ...
          {
             mxgetbegin ("personnel", CHARNIL);
             ...
          }

   However, be sure to avoid programs structured as:

          mxgetbegin ("loans", CHARNIL);
          while ...
          {
             mxgetbegin ("loans", "personnel", CHARNIL);
             ...
          }

2. Each nested retrieval loop must be terminated with `mxgetend`, or the outer loops will either fail or retrieve records from the wrong table.

   Since `mxget` takes no arguments, the table(s) to which it refers is determined by the last call to `mxgetbegin` not annulled with a call to `mxgetend`. If `mxgetend` is not called after an inner loop has ended, subsequent calls to `mxget` will continue to attempt retrievals from the table(s) named in the `mxgetbegin` call appropriate to the inner loop. Furthermore, since the next call to `mxgetend` will clean up for the most recent still active call to `mxgetbegin`, the `mxgetbegin` associated with the outermost retrieval loop will not be annulled and its allocated space not cleaned up.

   When an `mxgetend` is missing, an error message is printed when the program ends:

          *** User Error *** leftover retrieval expressions

   Other symptoms are wrong retrieved values, and a loop which suddenly stops cycling. Typically, such problems will occur when an exit is made from the middle of a retrieval loop, bypassing the `mxgetend` at the end of the loop.

3. The above points apply to both `mxgetbegin` and `mxsrtbegin`.

An example of nested retrieval loops is given in the program `interest.c` under the "Updating Records" section.

## 3.7 Qualification Building

Qualifications are analogous to `WHERE` clauses in the Query Language. There are twelve routines involved in constructing qualifications:

Table 3-1: Qualification Building Routines

Routine | Compares attribute value with | Format | Returns
`mxqatr` | an attribute value | external | true/false
`mxqcon` | a constant | external | true/false
`mxqmch` | a pattern | external | true/false
`mxqnul` | null | external | true/false
`mxqrng` | a range | external | true/false
`mxqconi` | a constant | internal | true/false
`mxqrngi` | a range | internal | true/false
`mxqieq` | an integer (equality) | external |
`mxqseq` | a string (equality) | external |

Table 3-2: Qualification Building Operators

Routine | Performs Boolean
`mxqand` | `AND` operation
`mxqor` | `OR` operation
`mxqnot` | `NOT` operation

Most of these routines require some parameters. `mxqatr`, `mxqcon`, `mxqmch`, `mxqnul`, and `mxqrng` all return true or false, depending on whether the values passed to them can be converted to a legal value for the attribute. The others have no return value, and terminate the calling program if they fail.

Their general forms are:

       flag = mxqatr (operator, attr_1, attr_2);
       flag = mxqcon (operator, attribute, string);
       flag = mxqmch (operator, attribute, pattern);
       flag = mxqnul (operator, attribute);
       flag = mxqrng (attribute, lower_limit, limit_type_1, upper_limit, limit_type_2);
       flag = mxqconi (operator, attribute, internal_value_ptr);
       flag = mxqrngi (attribute, lower_ptr, limit_type_1, upper_ptr, limit_type_2);

       mxqieq (attribute, integer);
       mxqseq (attribute, string);

       mxqand ();
       mxqor ();
       mxqnot ();

All these routines push a qualification onto an internal stack, the top of which is popped when `mxgetbegin` is called, so qualifications should be created just before the `mxgetbegin` is called. The stack grows indefinitely, so previous qualifications are still available when the top of the stack has been popped. Remember that the `mxqand` and `mxqor` routines remove the top two qualifications from the stack. If you intend to build the stack up and then pop it with successive calls to `mxgetbegin`, keep careful track of it.

If two tables with attribute names in common are open, common attribute names must be prefixed with the appropriate table name to ensure that the correct table is searched when a qualification is made.

### 3.7.1 Conjunctions of Qualifications

The routines `mxqand`, `mxqor`, and `mxqnot` create complex qualifications. These are analogous to the `AND`, `OR` and `NOT` operators of the Query Language. Each call to `mxqand` or `mxqor` pops the top two qualifications from the internal stack and combines them, leaving the new, combined qualification on the top of the stack. Qualifications of arbitrary complexity can be created by repeated calls to these two functions. `mxqnot` pops the top qualification from the stack and replaces it with one of opposite sense (e.g., calling `mxqieq` and then `mxqnot` qualifies all records NOT equal to the integer). `mxqand` is used to find the set of records that satisfy two conditions simultaneously. It takes no arguments; it combines the last two qualifications constructed, replacing them with a single qualification. If there are fewer than two qualifications left on the stack, `mxqand` will fail when the program is executed.

`mxqor` is used to find the set of records that satisfy either of two conditions. (Records that satisfy both conditions are included in the set.) It also takes no arguments, combining the last two qualifications constructed and replacing them with a single qualification.

Qualifications of unlimited complexity may be constructed using `mxqand` and `mxqor`. However, to achieve the desired result, care is required concerning the order in which the component qualifications are constructed.

Example

Suppose you wish to find all loans that are either older than 1 June 1992 or over $400, that do not belong to Mosca; i.e., the equivalent of the `WHERE` clause:

       WHERE (date < "June 1 1992" OR amount > 400) AND name != "Mosca"

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

       mxqcon ("<", "date", "June 1 1992");

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

       mxqcon ("<", "date", "June 1 1992");
       mxqcon (">", "amount", "$400.00");

The amount qualification is now on top of the stack, and the date qualification the second item down.

To combine the two using a boolean `OR` operator, i.e., to form the boolean expression "older than June 1st 1992 or larger than $400", use a call to `mxqor`:

       mxqcon ("<", "date", "June 1 1992");
       mxqcon (">", "amount", "$400.00");
       mxqor ();

The call to `mxqor` 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 `mxqor` or `mxqand` call were made at this point, it would fail because there are not enough qualifications on the stack.

The qualification "name != 'Mosca'" may now be constructed:

       mxqcon ("<", "date", "June 1 1992");
       mxqcon (">", "amount", "$400.00");
       mxqor ();
       mxqcon ("!=", "name", "Mosca");

The name qualification is now on the top of the stack, moving the qualification formed by the `mxqor` call to the position in the stack.

The complete qualification may now be made by combining the two current ones with a call to `mxqand`:

       mxqcon ("<", "date", "June 1 1992");
       mxqcon (">", "amount", "$400.00");
       mxqor ();
       mxqcon ("!=", "name", "Mosca");
       mxqand ();

This replaces the two qualifications on the stack with a single qualification, equivalent to the `WHERE` clause given earlier.

The following program, `oldloans.c`, retrieves loans that are older than a given date or larger than a given amount, for a given employee. It constructs an equivalent of the `WHERE` clause:

       WHERE (name = name_value AND date <= date_value) OR
             (name = name_value AND amount > amount_value)

This program also illustrates how to use arguments to set parameter values.

       #include    <mscc.h>

       #define     DATABASE      "repairs"
       #define     ARGNUMBER     4

       msmain (int argc, char* argv[])
       {
            char*   name;
            char*   date;
            char*   amount;
            char*   date_value;
            char*   amount_value;

            if (argc != ARGNUMBER)
            {
                 printf ("Usage: %s name date amount\n",
                      argv[0]);
                 msexit (1);
            }
            name = argv[1];
            date = argv[2];
            amount = argv[3];

            mxopen (DATABASE, "loans", "r");

            printf ("Loans to %s Greater Than %s or Older\
                 Than %s\n\n", name, amount, date);
            printf ("Date            Amount\n\n");

            mxqcon ("=", "name", name);
            mxqcon ("<=","date",date);
            mxqand ();
            mxqcon (">", "amount", amount);
            mxqcon ("=", "name", name);
            mxqand ();
            mxqor ();

            mxgetbegin ("loans", CHARNIL);
            while (mxget ())
            {
                 amount_value = mxgetvs ("amount");
                 date_value = mxgetvs ("date");
                 printf ("%17s     %-12s\n", date_value,
                     amount_value);
            }
            mxgetend ();
            mxclose ("loans");
       }

### 3.7.2 Comparing with a Constant

The routine `mxqcon` compares the value of an attribute with a constant. It takes three arguments. Its form is:

       flag = mxqcon (operator, attribute, "constant");

`operator` specifies the relationship to be used, and must be one of "`=`", "`!=`", "`<`", "`>`", or "`>=`".

`attribute` is the name of the attribute to be compared. If there is more than one open table with an attribute of that name, the attribute must be qualified with its table name.

`constant` is the string containing the constant for the comparison. If this value is not a string, it must first be converted to one. For example, to find data on employees with a credit limit greater than `n`, where `n` is declared as an integer, the first step is to convert `n` into a string. This can be done by calling a standard C routine, such as `sprintf.n` can be converted to a string, `limit`, with:

       sprintf (limit, "%d", n);

where `limit` is declared `char *`. `mxqcon` may then be called:

       mxcon ("=", "credit_limit", limit);

Example

The following example, `constant.c`, prints the name and credit limit of all employees with loans over $100.00.

       #include     <mscc.h>

       #define     DATABASE      "repairs"

       msmain ()
       {
            char*   name_value;
            char*   amount_value;

            mxopen (DATABASE, "loans", "r");
            printf ("Employees with Loans Greater \
                 Than $100.00\n\n");
            printf ("Name        Loans\n\n");
            mxqcon (">", "amount", "100.00");
            mxgetbegin ("loans", CHARNIL);
            while (mxget ())
            {
                 name_value = mxgetvs ("name");
                 amount_value = mxgetvs ("amount");
                 printf ("%-12s%-12s\n", name_value,
                      amount_value);
            }
            mxgetend ();
            mxclose ("loans");
       }

### 3.7.3 Selecting a Range of Values

Although it is possible to qualify records with attribute values between two constants by using two calls to `mxqcon` and a call to `mxqand`, the routine `mxqrng` is considerably more efficient. `mxqrng` is analogous to the `RANGE` keyword of the Query Language, and its syntax is similar.

It has the form:

       flag = mxqrng (attribute, lower_limit, limit_type,
                      upper_limit, limit_type);

`attribute` is the name of the attribute. `lower_limit` is a string containing the lower bound of the range. `upper_limit` is a string containing the upper bound of the range. `limit_type` is either "`e`" or "`i`", specifying exclusive or inclusive boundaries respectively.

Example

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

       #include     <mscc.h>

       #define     DATABASE      "repairs"
       msmain ()
       {
            char*   name_value;
            char*   date_value;
            char*   amount_value;

            mxopen (DATABASE, "loans", "r");

            printf ("Employees Owing Between $100 and\
                    $200\n\n");
            printf ("Name     Date           Amount\n");

            mxqrng ("amount", "100", "e", "200", "i");

            mxgetbegin ("loans", CHARNIL);
            while (mxget ())
            {
                 name_value = mxgetvs ("name");
                 date_value = mxgetvs ("date");
                 amount_value = mxgetvs ("amount");
                 printf ("%-12s%17s     %-12s\n",
                         name_value, date_value,
                         amount_value);
            }
            mxgetend ();
            mxclose ("loans");
       }

### 3.7.4 Comparing Attributes

The routine `mxqatr` compares the values of two attributes. It takes one of the six operators "`<`" "`>`" "`<=`" "`>`" "`=`" and "`!=`" and the names of the attributes to be compared as its arguments. Its general form is:

       flag = mxqatr (operator, attr_1, attr_2);

The two attributes must be of compatible types, or `mxqatr` will fail.

Example

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

       #include     <mscc.h>

       #define     DATABASE      "repairs"

       msmain ()
       {
            char*    name;
            char*    credit;
            char*    amount;

            mxopen (DATABASE, "loans", "r");
            mxopen (DATABASE, "personnel", "r");

            printf ("Employees Exceeding Credit\
                 Limit\n\n");
            printf (" Name    Credit Limit  Amount\n\n");
            mxqatr ("=", "loans.name", "personnel.name");
            mxqatr (">", "amount", "credit_limit");
            mxqand ();

            mxgetbegin ("loans", "personnel", CHARNIL);
            while (mxget ())
            {
                 name = mxgetvs ("personnel.name");
                 credit = mxgetvs ("credit_limit");
                 amount = mxgetvs ("amount");
                 printf ("%-12s%12s%12s\n", name,
                      credit, amount);
            }
            mxgetend ();
            mxclose ("loans");
            mxclose "personnel");
       }

### 3.7.5 Match Operators

The routine `mxqmch` compares an attribute value with a pattern. It has the form:

       flag = mxqmch (operator, attribute, pattern);

`operator` is one of the five operators `MATCH`, `SMATCH`, `!MATCH`, `!SMATCH`, `LIKE`. Refer to the Empress SQL Reference manual for a description on operators.

`pattern` is an external representation of a pattern. A pattern is any string. The following characters have a special Meaning:

Table 3-3: 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 lower case 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 "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 letter except "x".
`\` | Placed before any of the special characters "?", "*", "|", "&", "{", "}", "[", "]", and "\", 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.

Example

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

      #include     <mscc.h>

      #define     DATABASE      "repairs"

      msmain ()
      {
           char*   name;
           char*   phone;

           mxopen (DATABASE, "personnel", "r");

           printf ("Phone Numbers of Employees in\
                Local Area\n\n");
           printf (" Name        Phone\n\n");
           mxqmch ("match", "phone", "544*");

           mxgetbegin ("personnel", CHARNIL);
           while (mxget ())
           {
                name = mxgetvs ("name");
                phone = mxgetvs ("phone");
                printf ("%-12s%-12s\n", name, phone);
           }
           mxgetend ();
           mxclose ("personnel");
      }

### 3.7.6 Comparing with NULL

Any Empress attribute can contain a `NULL` value. A special routine is required to test for `NULL` values, since `NULL` is an indeterminate value that cannot logically be compared with anything. `mxqnul` tests whether an attribute value is or is not `NULL`. `mxqnul` takes an operator and an attribute name as arguments. Its general form is:

       flag = mxqnul (operator, attribute);

`operator` is either of the strings "`=`" or "`!=`".

Example

The following example, `nullcmp.c`, finds all employees whose phone numbers are not known (i.e., phone numbers are `NULL`).

       #include     <mscc.h>

       #define     DATABASE      "repairs"

       msmain ()
       {
            char*   name;

            mxopen (DATABASE, "personnel", "r");

            printf("Employees with Unknown Phone\
                  Numbers:\n\n");

            mxqnul ("=", "phone");

            mxgetbegin ("personnel", CHARNIL);
            while (mxget ())
            {
                 name = mxgetvs ("name");
                 printf ("%-12s\n", name);
            }
            mxgetend ();
            mxclose ("personnel");
       }

## 3.8 Deleting Records

The routine `mxdel` deletes the current record of a specified table. It has the form:

       flag = mxdel (table);

Normally, records to be deleted are specified using qualification routines and initialized for retrieval using `mxgetbegin`. They are then retrieved with `mxget` and deleted.

Example

The following example, `delete.c`, deletes all records from the loans table that were made before May 1992.

       #include    <mscc.h>

       #define     DATABASE      "repairs"

       msmain ()
       {
            mxopen (DATABASE, "loans", "u");

            mxqcon ("<", "date", "1 May 1992");

            mxgetbegin ("loans", CHARNIL);
            while (mxget ())
                 mxdel ("loans");

            mxgetend ();

            mxclose ("loans");
       }

## 3.9 Updating Records

Updating a record involves three steps:

1. Retrieving the record from the database, using qualification routines `mxgetbegin` and `mxget`.

2. Changing the attribute values of the record, using `mxputvs`.

3. Placing the updated record back into the database using the routine `mxput`.

The routine `mxput` has the form:

       flag = mxput (table);

It returns true if the update is successful and false if it is not.

Example

The following program, `interest.c`, calculates interest at 2% on each loan in the loans table, adds the interest to the existing total, prints out a monthly statement, and updates the database. The routine `dollcvt` changes 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 `mxputvs`.

       #include    <mscc.h>

       #define     DATABASE      "repairs"
       #define     DOLLSIZE     25

       extern     double      dollcvt ();

       msmain ()
       {
            double   sum, dollars, interest, newamount;
            char*    name;
            char*    date;
            char*    amount;

            mxopen (DATABASE, "personnel", "r");
            mxopen (DATABASE, "loans", "u");
            mxerret = 1;

            mxgetbegin ("personnel", CHARNIL);
            while (mxget ())
            {
                 name = mxgetvs ("personnel.name");
                 printf ("\n\nMonthly Statement for\
                      %s:\n\n", name);
                 printf (" Loan   Date Made   Interest\
                      Total\n");

                 mxqcon ("=", "loans.name", name);
                 sum = 0;
                 mxgetbegin ("loans", CHARNIL);
                 while (mxget())
                 {
                      amount = mxgetvs ("amount");
                      date = mxgetvs ("date");
                      dollars = dollcvt (amount);
                      interest = dollars * 0.02;
                      newamount = dollars + interest;
                      sum = sum + newamount;

                      printf ("$%-6.2f   %s\
                           $%-6.2f    $%-6.2f\n",
                           dollars, date, interest,
                                newamount);

                      sprintf (amount, "%6.2f", newamount);
                      if (! mxputvs ("amount", amount))
                           fprintf (stderr, "Conversion\
                                error: \
                                cannot convert '%' for\
                                'amount'\n", amount);
                      else
                           mxput ("loans");
                 }
                 mxgetend();

                 if (sum != 0)
                 printf ("\nTotal now owing: $%-6.2f\n",
                      sum);
                 else
                       printf ("\nNo loans outstanding.\n");
            }
            mxgetend();

            mxclose ("loans");
            mxclose ("personnel");
       }

       double dollcvt (char* string)
       {
            extern      double     atof ();
            char    c, buffer [DOLLSIZE], *buf_ptr;

            for (buf_ptr = buffer ; (c =  *string++) != '\0';)
                 if (c != '$' && c != '*' &&
                     c != ' ' && c != ',')
                        *buf_ptr++ = c;
                 *buf_ptr = '\0';
                 return (atof (buffer));
       }

## 3.10 Using Internal Data Representations

In some fixed applications, faster execution times may be obtained by using the internal representations of data values rather than character strings when comparing or retrieving attribute values. For example, if numeric data is retrieved in its internal representation rather than as a string containing the data, conversion of the string to an integer or float value can be avoided and arithmetic operations performed directly.

To take advantage of internal representations, however, you must know the forms in which the data values are stored on the machine for which the application is intended, since the declaration of the variables used to contain the values in internal representation must be appropriate for the data they will store. For the same reason, applications using internal representations are usually not portable among machines, and considerable care is required when altering them. In general, we do not recommend the use of the routines described below unless you are certain that the application really is fixed, or that speed constraints are vital.

The routines `mxqcon` and `mxqrng` have variants that use internal representation. The routines are called `mxqconi` and `mxqrngi`, respectively. They use pointers to the internal representation of constants rather than character strings. `mxqconi` is called in a manner very similar to `mxqcon`:

       flag = mxqconi (operator, attribute, var_ptr);

`operator` is one of the standard operators for `mxqcon`. `var_ptr` is a pointer to the variable (containing a value in internal representation) to be compared with the attribute. `mxqrngi` is called in a manner very similar to `mxqrng`:

       flag = mxqrngi (attribute, lower_ptr, limit_type_1,
                       upper_ptr, limit_type_2);

`lower_ptr` is a pointer to the lower limit of the range and `upper_ptr` is a pointer to the upper limit of the range.

The routines `mxcopyi` and `mxputi` are used to retrieve and update attribute values in internal format. They are functionally equivalent to `mxgetvs` and `mxputvs`, respectively. The formats are:

       flag = mxcopyi (attribute, var_ptr);
       flag = mxputi (attribute, var_ptr);

`var_ptr` points to an appropriately declared variable.

Example

For an example using these routines, the `repairs` database no longer suffices. It is too simple and does not have appropriate attributes. Imagine instead 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, `inventory.c`, prompts for an integer part code, prints the quantity currently in stock, prompts for the number of items just sold, decreases the quantity in stock by this amount, and prints the new quantity. It illustrates the use of `mxqconi`, `mxcopyi`, and `mxputi`.

Create the table `stock` with the following:

       CREATE TABLE stock (part_code LONGINTEGER,
            quantity LONGINTEGER);

Then:

       #include    <mscc.h>

       #define     DATABASE      "repairs"
       #define     false  0
       #define     true   1

       msmain ()
       {
            int   quantity, sold, newquantity, prodcode, found;

            mxopen (DATABASE, "stock", "u");
            for ( ; ; )
            {
                 printf ("\n\nEnter part code: ");
                 scanf ("%d", &prodcode);
                 if (prodcode == 0)
                 {
                      mxclose ("stock");
                      msexit (0);
                 }
                 found = false;
                 mxqconi ("=", "part_code", &prodcode);
                 mxgetbegin ("stock", CHARNIL);
                 while (mxget ())
                 {
                      found = true;
                      mxcopyi ("quantity", &quantity);
                      printf ("Number in stock = %d\n",
                           quantity);
                      printf ("\nEnter number sold: ");
                      scanf ("%d", &sold);
                      newquantity = quantity - sold;
                      printf ("Number now in stock =\
                           %d\n", newquantity)
                      mxputi ("quantity", &newquantity);
                      mxput ("stock");
                 }
                 mxgetend ();

                 if (! found)
                 printf ("\nNo entries for that\
                      part code.\n");
            }
       }

## 3.11 Transactions

A transaction is a group of operations on a database that 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.

A transaction may be started by a call to `mxtrstart`. The routine takes no arguments and returns true if it succeeds in starting the transaction, and false if it does not. Failure normally means a transaction is already in progress.

A transaction is committed by a call to `mxtrcommit` and cancelled by a call to `mxtrcancel`. These both take no arguments and return a flag to indicate whether the operation was successful.

The general forms of these are:

       flag = mxtrstart ()
       flag = mxtrcommit ();
       flag = mxtrcancel ();;

A save point can be set in a transaction. If a save point is set, it can be rolled back to. The forms of the routines are:

       flag = mxtrsave (savepoint);
       flag = mxtrrollback (savepoint);

Where `savepoint` is a character string.