# Chapter 3: Example Programs

## 3.1 Introduction

In the following sections, the mr routines are described in greater detail, and a number of example programs are given. Each example is written as a complete program which can be compiled and run. Though the early examples do not accomplish anything useful, later examples build upon them.

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 first step in accessing a table is to obtain a table_descriptor for it. The routine `mropen` opens a table and returns a table_descriptor. If it is unable to open the table, it will fail and terminate the calling program. `mropen` also sets up the table for locking at the level indicated in the data dictionary (the `tab_lock` attribute of `sys_tables`). It attempts to lock the table if table-level locking is set. If the lock is busy, it will try again as defined in the Empress variables `MSLOCKRETRY` and `MSLOCKSLEEP`; if it is unable to open and lock the table it prints a suitable message and terminates the calling program.

`mrtopen` also opens and sets up locking for a table, but does not terminate the calling program upon failure. If `mrtopen` fails, it returns a `NULL` pointer instead of a table_descriptor, and sets the value of the variable `mroperr` to one of the `MRER...` codes listed in the header file `mscc.h`. This variable may be examined to determine the cause of failure.

The general forms of these routines are:

       table_desc = mropen (database_name, table_name, mode);

       table_desc = mrtopen (database_name, table_name, mode);

Tables may be opened in read-only mode ('`r`') to protect their contents, in dirty read mode ('`n`') to bypass locking or in deferred ('`d`') or update mode ('`u`') to allow alteration or deletion of records. Since Empress tables are normally 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 will fail. To preclude problems of this sort, applications intended for general use that examine, but do not alter, the contents of a table, should open the table in read-only mode. Mode can be specified using either double or single quote.

Each call to `mropen` and `mrtopen` normally opens the data dictionary to read information on access privileges and lock levels, then closes the dictionary again. However, if the data dictionary has been explicitly opened (see next section), `CHARNIL` may be used instead of the database name for either of these routines. The routines will then not attempt to open and close the data dictionary, speeding up the process of opening the table.

Once all desired operations have been performed on a table, it should be closed with `mrclose`. This routine takes a table_descriptor as its argument; there is no returned value. `mrclose` will also unlock a table if it is locked. The general form of `mrclose` is:

       flag = mrclose (table_desc);

Example

The following program, `open.c`, opens the table `loans` in the database `repairs` in update mode, obtains a pointer to a table_descriptor for it, then closes the table again.

``

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr     loans_tabdesc;

            loans_tabdesc = mropen (DATABASE, "loans", 'u');
            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.3 Opening the Data Dictionary

The routines `mropdict` or `mrtopdict` open all the tables that make up the data dictionary. (Individual data dictionary tables may also be opened in the ordinary way with `mropen` or `mrtopen`.) The routine `mrcldict` closes the data dictionary.

The general forms of these routines are:

       mropdict (database_name, mode);

       flag = mrtopdict (database_name, mode);

       mrcldict ();

`mode` is one of '`r`' or '`u`', for read or update respectively.

The routine `mrtopdict` returns true if it succeeds in opening the data dictionary and false if it does not. If it fails, the variable `mroperr` will contain one of the `MRER...` codes described in the header file `mscc.h`.

When opening several tables in the same database, it is efficient to open the data dictionary, then call `mropen` or `mrtopen` with `CHARNIL` as the first argument instead of the database name (e.g., `mropen (CHARNIL, "loans", 'r')`). Opening the data dictionary, then opening several tables in the database, and closing the data dictionary again, is faster than simply opening the tables and referring to the database each time. With the data dictionary open, each call to `mropen` or `mrtopen` can check such things as access privileges and locking immediately.

Note that only one data dictionary may be opened for this to succeed, since there must be an unambiguous default for `mropen` and `mrtopen`.

Example

The following program, `opdict.c`, opens the data dictionary for the database, opens the `loans` table, closes the data dictionary, then closes the table.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr     loans_tabdesc;

            mropdict (DATABASE, 'r');
            loans_tabdesc = mropen (CHARNIL, "loans", 'u');
            mrcldict ();
            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.4 Checking Database Names

The routine `mrqdb` tests whether or not a directory is an Empress database. It takes a directory name as its argument, and returns true if the directory is a database and false if it is not. Its general form is:

       flag = mrqdb (directory);

The program `locktab.c` in the next section illustrates how to check for a valid database name.

## 3.5 Locks

Locking is supported at the table, group, and record level. When a table is opened, the locking level defined for it in the data dictionary is noted. A table level lock will be set by `mropen`, a group level lock will be set by the routine `mrgetbegin` which initializes a group of records for retrieval, and a record level lock will be set by `mrget` which retrieves individual records.

A table-level lock may also be obtained for a table by the routine `mrlktab`, which takes a table_descriptor as its argument. This lock is in addition to any lock made automatically by `mropen`. Note that if no locking is specified for the table in the data dictionary, no locks can be made by `mropen` or `mrlktab`.

`mrultab`, which also takes a table_descriptor, removes table-level locks, including any lock set by `mropen`. (`mrclose` will also remove all locks on a table.)

`mrlkrec` is used to hold a record-level lock on a retrieved record; `mrulrec` is used to remove this lock. It is important to unlock records once operations on them are finished, as leaving a record locked may cause operations on the table by other programs to fail unexpectedly.

`mrclose` will remove any locks remaining on a table or its records.

Both `mrlktab` and `mrlkrec` return true if successful and false if unsuccessful. The general forms of these routines are:

       flag = mrlktab (table_desc);

       flag = mrlkrec (record_desc);

       flag = mrultab (table_desc);

       flag = mrulrec (record_desc);

Note that if no locking is specified for the table in the data dictionary, `mrlktab` and `mrlkrec` will return true, but have no effect. The locking level for a table is specified in the tab_lock attribute of sys_tables, and may be examined to determine whether locking will be effective or not.

`mrlkrec` is illustrated under "Record Level Locks" of this chapter.

Example

The following program, `locktab.c`, tests whether a directory is an Empress database, opens a table in it if it is, and removes the table-level lock the open sets (assuming that table level locking is defined for the table in the data dictionary). It then attempts to place an explicit table level lock on the table. Finally, the table is unlocked before it is closed.

       #include   <mscc.h>

       #define    DATABASE   "repairs"

       msmain ()
       {
            addr   loans_tabdesc;

            if (! mrqdb (DATABASE))
            {
                 fprintf (stderr, "%s is not an Empress\
                      database", DATABASE);
    	     msend();
    	     return 1;
            }
            loans_tabdesc = mropen (DATABASE, "loans", 'u');

            if (!mrultab (loans_tabdesc))
    	{
    	     fprintf (stderr, "Unable to unlock the table\n"
    	     fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (! mrlktab (loans_tabdesc))
            {
                 fprintf (stderr, "Unable to lock table.\
                      Try again later.");
    	     msend();
    	     return 1;
            }
            if (!mrultab (loans_tabdesc))
    	{
    	     fprintf (stderr, "Unable to unlock the table\n"
    	     fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.6 Getting Attribute Descriptors

To use the value of an attribute in any way, or assign a new value to it, an attribute_descriptor must be obtained. There are two routines which return an attribute_descriptor: `mrigeta` and `mrngeta`. These take as arguments a table_descriptor and the number or name of an attribute, respectively. `mrigeta` returns a `NULL` pointer if the number of an attribute is invalid, and `mrngeta` returns a `NULL` pointer if the name is invalid. The general forms of these are:

       attr_desc = mrigeta (table_desc, attr_number);

       attr_desc = mrngeta (table_desc, attr_name);

Example

The following example, `ngeta.c`, uses `mrngeta` to obtain attribute_descriptors for each of the four attributes in the `personnel` table:

       #include   <mscc.h>

       #define    DATABASE   "repairs"

       msmain ()
       {
            addr   pers_tabdesc, number_attrdesc,
                   name_attrdesc, phone_attrdesc,
                   credit_attrdesc;

            pers_tabdesc = mropen (DATABASE, "personnel", 'r');

            number_attrdesc = mrngeta (pers_tabdesc, "number");
            name_attrdesc = mrngeta (pers_tabdesc, "name");
            phone_attrdesc = mrngeta (pers_tabdesc, "phone");
            credit_attrdesc = mrngeta (pers_tabdesc, "credit_limit");

            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

Example

The following program, `igeta.c`, uses `mrigeta` to obtain attribute_descriptors for all attributes of the `loans` table:

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr    loans_tabdesc, attr_desc[100];
            int     i;

            loans_tabdesc = mropen (DATABASE, "loans", 'r');

            /* attr_desc[0] is not used, so that each subscript of attr_desc
               will correspond to the number of the attribute it refers to, we
               assume there are fewer than 99 attributes                        */

            for (i = 1 ; (attr_desc[i] = mrigeta (loans_tabdesc, i)) != 0 ; i++);
                 /* loop has no body */

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.7 Finding the Name of an Attribute

If only the number and not the name of an attribute is known, `mrganame` can be used to find the name. It takes an attribute_descriptor as its argument, and returns a character pointer to the attribute name. Its form is:

       name = mrganame (attr_desc);

Example

The following example, `getname.c`, finds and prints the names of all the attributes in the `loans` table:

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr    loans_tabdesc, attr_desc;
            int     i;
            char*   name;

            loans_tabdesc = mropen (DATABASE, "loans", 'r');

            for (i = 1 ; (attr_desc = mrigeta (loans_tabdesc, i)) != 0 ; i++)
            {
                 name = mrganame (attr_desc);
                 printf("%s\n",name);
            }

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.8 Getting Record Descriptors

Before retrieving or adding records to a table, it is necessary to allocate space to store each record. The routine which does this is `mrmkrec`, which takes a table_descriptor as its argument and returns a record_descriptor.

Once a record_descriptor is no longer needed, the space which was allocated for it should be freed by calling `mrfrrec`. `mrfrrec` takes a record_descriptor as its argument and has no returned value. The general forms of these routines are:

       record_desc = mrmkrec (table_desc);

       flag = mrfrrec (record_desc);

Example

The following program, `record.c`, creates a record_descriptor, then frees the space it uses.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr   loans_tabdesc, loans_recdesc;

            loans_tabdesc = mropen (DATABASE, "loans", 'u');
            loans_recdesc = mrmkrec (loans_tabdesc);
            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record  buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.9 Entering Attribute Values into Records

Attribute values in external format (character strings) are assigned to a record one at a time using the routine `mrputvs`, which takes a record_descriptor, an attribute_descriptor, and a string as its arguments.

If the value to be assigned to the attribute is a C integer, the routine `mrputvi` can be used instead of `mrputvs`, without having to convert the integer to a string. Both of these return true if the value was assigned successfully, and false if it was not.

`mrmptvs` and `mrmptvi` perform the same functions as `mrputvs` and `mrputvi`, but return no value and terminate the calling program if they fail.

If it is desirable to use a pointer to an internal format attribute value rather than an external format attribute value, `mrputi` should be used. To use this routine, however, you must know exactly how each Empress data type is represented on the machine for which your application is written; also, the code you write will not be portable from machine to machine.

The general forms of all these are:

       flag = mrputvs (record_desc, attr_desc, value);

       flag = mrputvi (record_desc, attr_desc, integer);

       flag = mrputi (record_desc, attr_desc, var_ptr);

       mrmptvs (record_desc, attr_desc, value);

       mrmptvi (record_desc, attr_desc, integer);

`var_ptr` is a pointer to a variable containing an internal format value.

Note that the record is not actually inserted into the database until `mradd` or `mrtadd` is called.

Example

The following program, `putval.c`, enters attribute values into a new record for the `loans` table.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr   loans_tabdesc, loans_recdesc,
                   name_attrdesc, date_attrdesc,
                   amount_attrdesc;

            loans_tabdesc = mropen (DATABASE, "loans", 'u');

            name_attrdesc = mrngeta (loans_tabdesc, "name");
            date_attrdesc = mrngeta (loans_tabdesc, "date");
            amount_attrdesc = mrngeta (loans_tabdesc, "amount");
            loans_recdesc = mrmkrec (loans_tabdesc);

            if (! mrputvs (loans_recdesc, name_attrdesc, "Jones"))
            {
                 fprintf (stderr, "Name conversion unsuccessful\n");
                 msend();
    	     return 1;
            }
            else if (! mrputvs (loans_recdesc, date_attrdesc, "2 July 1992"))
            {
                 fprintf (stderr, "Date conversion unsuccessful\n");
                 msend();
    	     return 1;
            }
            else if (! mrputvs (loans_recdesc, amount_attrdesc, "$75.00"))
            {
                 fprintf (stderr, "Amount conversion unsuccessful\n");
                 msend();
    	     return 1;
            }
            else printf ("Conversion successful\n");

            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record  buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

Note that all attribute values in a record are set to `NULL` when the record is created, and must be individually assigned values. The routine `mrsetnv` is used to set an attribute value to `NULL`; it takes a record_descriptor and an attribute_descriptor as its arguments. The entire record may also be set to `NULL` by using `mrsetnr`, with the record_descriptor as its argument. These routines have the form:

       flag = mrsetnv (record_desc, attr_desc);

       flag = mrsetnr (record_desc);

Example

The example below, `null.c`, sets the date attribute of a record in the `loans` table to `NULL`, then sets an entire record to `NULL`.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr     loans_tabdesc, first_recdesc,
                     second_recdesc, name_attrdesc;
            addr     date_attrdesc, amount_attrdesc;

            loans_tabdesc = mropen (DATABASE, "loans", 'u');

            name_attrdesc = mrngeta (loans_tabdesc, "name");
            date_attrdesc = mrngeta (loans_tabdesc, "date");
            amount_attrdesc = mrngeta (loans_tabdesc, "amount");

            first_recdesc = mrmkrec (loans_tabdesc);

            if (! mrputvs (first_recdesc, name_attrdesc, "Mosca"))
            {
                 fprintf (stderr, "Name conversion unsuccessful\n");
                 msend();
    	     return 1;
            }
            else if (! mrputvs (first_recdesc, amount_attrdesc, "$75.00"))
            {
                 fprintf (stderr, "Amount conversion unsuccessful\n");
                 msend();
    	     return 1;
            }
            else printf ("Conversion successful\n");

            if (!mrsetnv (first_recdesc, date_attrdesc))
    	{
    		fprintf (stderr, "Unable to set attribute 'date' to null\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            second_recdesc = mrmkrec (loans_tabdesc);

            if (!mrsetnr (second_recdesc))
    	{
    		fprintf (stderr, "Unable to set record to null\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrfrrec (first_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers for first record descriptor\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrfrrec (second_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers for second record descriptor\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.10 Adding Records to a Table

When the attributes of a record have been assigned values, the record may be added to the appropriate table. This is done with `mradd` or `mrtadd`, which take a record_descriptor as their argument (the appropriate table is specified by the record_descriptor) and insert the record into the table.

`mradd` has no returned value, and terminates the calling program on failure. If there is a possibility that the new record cannot be inserted into the table (for example, it may have duplicate values for attributes with unique indices, or the table may have record level locking), you should use `mrtadd` instead of `mradd`. `mrtadd` returns true if successful and false if unsuccessful.

When no further additions are to be made to a table, `mraddend` should be called. This takes a record_descriptor as its argument. It writes the Empress internal buffer to the file.

The general forms of these routines are:

       mradd (record_desc);

       flag = mrtadd (record_desc);

       flag = mraddend (record_desc);

Example

The following program, `add.c`, inserts one of the records created in the examples above in the `loans` table.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr   loans_tabdesc, loans_recdesc,
                   name_attrdesc, date_attrdesc,
                   amount_attrdesc;

            loans_tabdesc = mropen (DATABASE, "loans", 'u');

            name_attrdesc = mrngeta (loans_tabdesc, "name");
            date_attrdesc = mrngeta (loans_tabdesc, "date");
            amount_attrdesc = mrngeta (loans_tabdesc, "amount");

            loans_recdesc = mrmkrec (loans_tabdesc);

            if (! mrputvs (loans_recdesc, name_attrdesc, "Jones"))
            {
                 fprintf (stderr, "Name conversion unsuccessful\n");
                 msend();
    	     return 1;
            }
            else if (! mrputvs (loans_recdesc, date_attrdesc,
                     "2 July 1992"))
            {
                 fprintf (stderr, "Date conversion unsuccessful\n");
                 msend();
    	     return 1;
            }
            else if (! mrputvs (loans_recdesc,
                 amount_attrdesc, "$75.00"))
            {
                 fprintf (stderr, "Amount conversion unsuccessful\n");
                 msend();
    	     return 1;
            }

            mradd (loans_recdesc);
            if (!mraddend (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in writing internal buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record  buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.11 Retrieving Records

There are six groups of routines used to retrieve records from a table:

1. `mrgetbegin`, `mrtgtbegin`, `mrsrtbegin`, and `mrtsrbegin` initialize the retrieval procedure and associate qualifications with the records of the table(s).

2. `mrget`, `mrprev`, `mrtget`, `mrtprev`, `mrreget`, `mrreprev`, and `mrgetrec` fetch records.

3. `mrspv` allocates space for attribute value storage.

4. `mrgetvs`, `mrgetvi`, `mrcopyv`, `mrcopyi` and `mrgeti` retrieve attribute values. In addition, `mrgfunc` and `mrtgfunc` return the result of functions on attribute values.

5. `mrgetptr` obtains a pointer to a record for later use.

6. `mrgetend` and `mrfree` clean up after retrievals.

### 3.11.1 Initializing Retrievals

The `mrgetbegin` routines associate a qualification with one or more records and perform the initializations involved in retrieving the records. Qualifications are analogous to `WHERE` clauses in the Query Language. If group level locking is set for the table, these routines will lock the group of records as well.

`mrgetbegin` takes as its arguments a qualification_descriptor and one or more record_descriptors (terminated by `ADDRNIL` as defined in the header file `mscc.h`). If there is more than one record_descriptor, each must have been derived from different table_descriptors. This provides a mechanism for performing joins. If you wish to join a table to itself, the table must be opened twice to obtain two distinct table_descriptors from which to derive the record_descriptors for the join. `mrgetbegin` returns a retrieval_descriptor which is then passed to `mrget`; it terminates the calling program if it cannot access all necessary records. Note that `mrgetbegin` must be called even if there is no qualification; in this case, `ADDRNIL` is given as the first argument.

`mrtgtbegin` is like `mrgetbegin` except that it returns `ADDRNIL` on failure instead of terminating the calling program.

If sorted output is desired, `mrsrtbegin` or `mrtsrbegin` is used. These take as arguments a qualification_descriptor, a character indicator "`s`" or "`u`" for standard or unique sorts, one or more record_descriptors, a terminating `ADDRNIL`, one or more attribute_descriptors each followed by the character "`a`" or "`d`" (for ascending or descending sorts) indicating which attributes the output is to be sorted on, and then a final terminating `ADDRNIL`. If a unique sort is requested, only those attributes that are specified are guaranteed to be unique. Records may contain duplicate values for unlisted attributes; their order when retrieved is not determined.

These routines also return a retrieval_descriptor. `mrsrtbegin` terminates the calling program if it cannot access all necessary records, whereas `mrtsrbegin` returns `ADDRNIL`.

The general forms for the retrieval initialization routines are:

       retrieval_desc = mrgetbegin (qual_desc,
            record_desc_1, record_desc_2, .... , ADDRNIL);

       retrieval_desc = mrtgtbegin (qual_desc,
            record_desc_1, record_desc_2, .... , ADDRNIL);

       retrieval_desc = mrsrtbegin (qual_desc, indicator,
            record_desc_1, record_desc_2, .... ,
            ADDRNIL, attr_desc_1, type, attr_desc_2,
            type, .... , ADDRNIL);

       retrieval_desc = mrtsrbegin (qual_desc, indicator,
            record_desc_1, record_desc_2, .... ,
            ADDRNIL, attr_desc_1, type, attr_desc_2,
            type, .... , ADDRNIL);

### 3.11.2 Fetching Records

The record fetching routines take as their argument the retrieval_descriptor returned by one of the retrieval initialization routines, or a pointer to a previously found record plus a record_descriptor in the case of `mrgetrec`. If record level locking is set for the table, these routines lock records as they are retrieved, otherwise, no record level locking will be performed.

`mrget` is the simplest of these routines. It retrieves the next qualified record. It returns true if it succeeds in retrieving a qualified record, and false if there are no more qualified records. If a record is inaccessible (i.e., locked), or some problems are encountered in evaluating an expression (e.g., division by zero), it prints a suitable error message and terminates the calling program.

`mrtget` is similar to `mrget`, but returns -1 if a record is locked or an expression cannot be evaluated, 0 if the end of the record has been reached, or 1 if the call succeeds. The variable `mrgtstat` offers additional information: it will distinguish between a locked record (-1) and an expression failure (-2), will identify the first record in a group (2), and recognizes the record containing the aggregate value for the group (3).

Successive calls to `mrget` or `mrtget` return different records, stepping through the table until all qualified records have been retrieved. `mrreget` does not fetch a new record, but attempts to retrieve the last record tried for by `mrtget`. It is normally used with `mrtget` to make several attempts to fetch a locked record, and returns the same values as `mrtget`.

`mrprev`, `mrtprev`, and `mrreprev` return previous records in the table.

`mrgetrec` takes as its argument a pointer to a record found by `mrgetptr`, and finds a single record. It returns true if successful, false if the record was not found, and -1 if the record is inaccessible because it is locked.

The general forms of these routines are:

       flag = mrget (retrieval_desc);

       flag = mrtget (retrieval_desc);

       flag = mrreget (retrieval_desc);

       flag = mrprev (retrieval_desc);

       flag = mrtprev (retrieval_desc);

       flag = mrreprev (retrieval_desc);

       flag = mrgetrec (record_desc, record_ptr);

### 3.11.3 Allocating Space for Attribute Value Storage

`mrspv` allocates space to store retrieved attribute values in external format. It takes an attribute_descriptor as its argument, and returns a pointer to storage space for the external format attribute value. This pointer is normally passed to `mrcopyv`. The space so allocated will remain allocated until specifically freed by `mrfree`. Note that `mrspv` cannot be used to allocate space for a `TEXT` or `BULK` data value, and will return a `NULL` pointer if passed a descriptor to attributes of those types. Retrieving text and bulk values is discussed in the next section.

The form for mrspv is:

       space = mrspv (attr_desc);

### 3.11.4 Retrieving Attribute Values

#### 3.11.4.1External Representations

`mrcopyv` is the simplest routine for retrieving an attribute value, and assigns an external format value to the space allocated by `mrspv`. It takes as its arguments a record_descriptor, an attribute_descriptor, and a pointer to the space allocated for storage (from `mrspv`). It returns true if successful and false if the value was `NULL`.

If the attribute to be retrieved is a `TEXT` data type, `mrgetvs` must be used instead of `mrcopyv`. (Note that `mrgetvs` may be used to retrieve any kind of attribute value, not just text.) This is due to the difficulties in handling allocation of space for variable length data. In this case, do not call `mrspv` first, since `mrgetvs` handles the space allocation. `mrgetvs` takes a record_descriptor and an attribute_descriptor as its arguments and returns a pointer to a string. The buffer in which the string is stored will be reused by the next call to `mrgetvs`; therefore the string should be assigned to permanent storage if it will be needed later.

If the value retrieved is to be converted to an integer, `mrgetvi` may be used to return an integer directly (it essentially calls `mrgetvs` and converts the result to an integer). `mrgetvi` will terminate the calling program if the conversion fails.

The general forms of these routines are:

       flag = mrcopyv (record_desc, attr_desc, space);

       result = mrgetvs (record_desc, attr_desc);

       result = mrgetvi (record_desc, attr_desc);

#### 3.11.4.2 Internal Representations

If you want to use a pointer to an internal format attribute value for faster execution time, use `mrcopyi` instead of `mrcopyv`. It also takes a record_descriptor, an attribute_descriptor, and a pointer to storage for the value, which must be the correct type and size for that value. To use this routine you must know exactly how Empress data types are represented on the machine for which your application is written; in addition, the code you write will not be portable from machine to machine.

`mrcopyi` returns true if the value was successfully retrieved, and false if it was `NULL`.

`mrgeti` also finds an internal format attribute value, taking a record_descriptor and an attribute_descriptor as arguments, and returning a pointer to the value (or a `NULL` pointer if the attribute is `NULL`). This routine is intended primarily for use with `BULK` data values. As with `mrgetvs`, the value returned is in a buffer which will be reused on the next call; it should be copied to permanent storage if it will be needed later.

The forms of these two routines are:

       flag = mrcopyi (record_desc, attr_desc, var_ptr);

       result = mrgeti (record_desc, attr_desc);

### 3.11.5 Cleaning Up after Retrievals

Once all records have been retrieved, `mrgetend` should be called. It takes a retrieval_descriptor as its argument, and frees the space allocated to the retrieval_descriptor. Its form is:

       mrgetend (retrieval_desc);

### 3.11.6 Functions - COUNT, MAX, MIN, SUM, AVG

The routines `mrgfunc` and `mrtgfunc` may be used to find the count of qualified records, the maximum or minimum value for a given attribute, its sum, or its average. These routines take a string containing the function name (one of `COUNT`, `SUM`, `AVG`, `MIN`, or `MAX`), a retrieval_descriptor (from one of the `mrgetbegin` routines), a record_descriptor, and an attribute_descriptor. In the case of `COUNT`, the attribute_descriptor is replaced by `ADDRNIL`.

Both routines return a pointer to the value of the function. `mrgfunc` terminates the calling program if it cannot access all necessary records; `mrtgfunc` returns `CHARNIL` in this case.

The general forms of these are:

       value = mrgfunc (function, retrieval_desc, record_desc, attr_desc);

       value = mrtgfunc (function, retrieval_desc, record_desc, attr_desc);

Note that `mrgetend` must be called with the proper retrieval_descriptor after `mrgfunc` or `mrtgfunc` have been called. The string is in an allocated buffer and should be freed with `mrfree` when no longer needed.

### 3.11.7 Examples

To perform a simple retrieval with no qualification, such as retrieving and printing the name and phone number of all employees in the `personnel` table, requires calls to `mrspv` to allocate space to store the name and phone number values, a call to `mrgetbegin`, then a loop in which `mrget` is called for each record. Within the loop, `mrcopyv` is called for each attribute, and the values printed. `mrgetend` is called once the loop is finished. This is shown in the example below, `employees.c`:

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr     pers_tabdesc, name_attrdesc,
                     phone_attrdesc, pers_recdesc;
            addr     retrieve_desc;
            int      employees;
            char*    name_value;
            char*    phone_value;

            pers_tabdesc = mropen (DATABASE, "personnel", 'r');

            pers_recdesc = mrmkrec (pers_tabdesc);
            name_attrdesc = mrngeta (pers_tabdesc, "name");
            phone_attrdesc = mrngeta (pers_tabdesc, "phone");

            name_value = mrspv (name_attrdesc);
            phone_value = mrspv (phone_attrdesc);

            printf ("Current Employees\n\n");
            printf (" Name Phone\n");

            employees = 0;
            retrieve_desc = mrgetbegin (ADDRNIL, pers_recdesc, ADDRNIL);
            while (mrget (retrieve_desc))
            {
                 mrcopyv (pers_recdesc, name_attrdesc, name_value);
                 mrcopyv (pers_recdesc, phone_attrdesc, phone_value);

                 printf ("%-12s%-12s\n", name_value, phone_value);
                 employees++;
            }
            mrgetend (retrieve_desc);

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

            mrfree (name_value);
            mrfree (phone_value);
            if (!mrfrrec (pers_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

The following program, `sort_emp.c`, uses `mrsrtbegin` to produce a list of employees sorted in descending order:

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            int     employees;
            addr    pers_tabdesc, name_attrdesc, phone_attrdesc,
                    pers_recdesc;
            addr    retrieve_desc;
            char*   name_value;
            char*   phone_value;

            pers_tabdesc = mropen (DATABASE, "personnel", 'r');
            pers_recdesc = mrmkrec (pers_tabdesc);
            name_attrdesc = mrngeta (pers_tabdesc, "name");
            phone_attrdesc = mrngeta (pers_tabdesc, "phone");

            name_value = mrspv (name_attrdesc);
            phone_value = mrspv (phone_attrdesc);

            printf ("Current Employees\n\n");
            printf (" Name Phone\n");

            employees = 0;
            retrieve_desc = mrsrtbegin (ADDRNIL, 's',
                 pers_recdesc, ADDRNIL, name_attrdesc, 'd', ADDRNIL);

            while (mrget (retrieve_desc))
            {
                 mrcopyv (pers_recdesc, name_attrdesc, name_value);
                 mrcopyv (pers_recdesc, phone_attrdesc,phone_value);
                 printf ("%-12s%-12s\n", name_value, phone_value);
                 employees++;
            }
            mrgetend (retrieve_desc);
            printf ("\nTotal Number of Employees = %d\n", employees);

            mrfree (name_value);
            mrfree (phone_value);
            if (!mrfrrec (pers_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.12 Comparisons

Comparisons may be made on retrieved values using the routines `mrcompare`, `mrnullr`, and `mrnullv`.

The routine `mrcompare` compares an attribute value with a constant in file format. It takes a record_descriptor, an attribute_descriptor, and a pointer to a variable containing a file format value as arguments. Its general form is:

       result = mrcompare (record_desc, attr_desc, var_ptr);

The pointer to a file format value may be obtained by one of the `mrcvt` routines (described in detail in the next section), as in:

       result = mrcompare (record_desc, attr_desc, mrcvt (attr_desc, string));

The routines `mrnullr` and `mrnullv` are used for comparisons with `NULL`. The routine `mrnullr` tests if an entire record consists of `NULL` values. It takes a record_descriptor as an argument, returning true if the record is `NULL` and false if it is not. The routine `mrnullv` tests whether an attribute value is `NULL`. It takes a record_descriptor and an attribute_descriptor as arguments, returning true if the attribute is `NULL` and false if it is not.

The general forms of these routines are:

       flag = mrnullr (record_desc);

       flag = mrnullv (record_desc, attr_desc);

### 3.12.1 Converting Values to File Format

The routines `mrcvt`, `mrcvtv`, and `mrcvtv2` convert external format attribute values to file format. The routines `mrcvtin`, `mrcvti`, and `mrcvti2` convert internal format values to file format. All of them return a pointer to the file format value, or `ADDRNIL` if the conversion is unsuccessful.

`mrcvt` and `mrcvtin` are usually called in the argument list to `mrqcon` or `mrtqcon`. They share a buffer for the converted value which is also used by a number of other mr routines (such as `mrgetvs`, `mrgetvi`), so a permanent copy of the converted value is not kept.

Since the buffer used by these routines is re-used with each call, they cannot be used with `mrqrng` or `mrtqrng`. If they were, the call for the upper limit of the range would destroy the value converted for the lower limit. The two routines `mrcvtv` and `mrcvtv2` (or `mrcvti` and `mrcvti2`) should be used instead. `mrcvtv` and `mrcvti` share a buffer with each other, and `mrcvtv2` and `mrcvti2` share another buffer.

The general forms of all these are:

       value = mrcvt (attr_desc, string);

       value = mrcvtv (attr_desc, string);

       value = mrcvtv2 (attr_desc, string);

       value = mrcvtin (attr_desc, var_ptr);

       value = mrcvti (attr_desc, var_ptr);

       value = mrcvti2 (attr_desc, var_ptr);

`string` is an external format attribute value. `var_ptr` is a pointer to a variable containing an internal format attribute value.

## 3.13 Qualifications

Qualifications are analogous to Query Language `WHERE` clauses, and are created by calls to the `mrq...` routines, all of which return qualification_descriptors which are passed to one of the retrieval initialization routines.

There are a group of 16 routines available for constructing qualifications: `mrqcon`, `mrtqcon`, `mrqrng`, `mrtqrng`, `mrqatr`, `mrtqatr`, `mrqmch`, `mrtqmch`, `mrqnul`, `mrtqnul`, `mrqseq`, `mrqieq`, `mrqand`, `mrqor`, `mrqnot`, and `mrqlst`.

### 3.13.1 Comparison with a Constant

`mrqcon` and `mrtqcon` compare the value of an attribute with a constant, and take as arguments a string containing one of the seven operators "`=`", "`>`", "`<`", "`>=`", "`<>`", "`<=`", or "`!=`" ("`~`" may be used as a synonym for "`!`"), an attribute_descriptor, and a constant value in file format. `mrqcon` terminates the calling program if there are problems with the arguments, while `mrtqcon` returns `ADDRNIL` on failure. Their general forms are:

       qual_desc = mrqcon (operator, attr_desc, var_ptr);

       qual_desc = mrtqcon (operator, attr_desc, var_ptr);

The conversion routines described in the previous section can be used to convert external or internal format attribute values to file format as required by `mrqcon` and `mrtqcon`.

Example

The program below, `constant.c`, uses `mrqcon` to select all records from the `loans` table where the amount of the loan is greater than $100.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr   loans_tabdesc, name_attrdesc,
                   date_attrdesc;
            addr   amount_attrdesc, loans_recdesc,
                   qual, retrieve_desc;
            char*  name_value;
            char*  date_value;
            char*  amount_value;

            loans_tabdesc = mropen (DATABASE, "loans", 'r');

            loans_recdesc = mrmkrec (loans_tabdesc);
            name_attrdesc = mrngeta (loans_tabdesc, "name");
            date_attrdesc = mrngeta (loans_tabdesc, "date");
            amount_attrdesc = mrngeta (loans_tabdesc, "amount");

            name_value = mrspv (name_attrdesc);
            date_value = mrspv (date_attrdesc);
            amount_value = mrspv (amount_attrdesc);

            printf ("Employees With Single Loans Of More Than $100\n\n");
            printf (" Name     Date     Amount\n");

            qual = mrqcon (">", amount_attrdesc, mrcvt (amount_attrdesc, "$100.00"));

            retrieve_desc = mrgetbegin (qual, loans_recdesc, ADDRNIL);
            while (mrget (retrieve_desc))
            {
                 mrcopyv (loans_recdesc, name_attrdesc, name_value);
                 mrcopyv (loans_recdesc, date_attrdesc, date_value);
                 mrcopyv (loans_recdesc, amount_attrdesc,amount_value);
                 printf ("%-12s%-22s%-12s\n", name_value, date_value, amount_value);
            }
            mrgetend (retrieve_desc);

            mrfree (name_value);
            mrfree (date_value);
            mrfree (amount_value);
            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

### 3.13.2 Comparison with a Range

`mrqrng` and `mrtqrng` compare an attribute value with a range of values. They take as arguments an attribute_descriptor, the lower limit of the range in file format, the character '`i`' or '`e`' to indicate inclusive or exclusive, the upper limit of the range in file format, and another '`i`' or '`e`'. `mrqrng` terminates the calling program if there are problems with the arguments, while `mrtqrng` returns `ADDRNIL` on failure. Their general forms are:

       qual_desc = mrqrng (attr_desc, lower_limit,
            limit_type_1, upper_limit, limit_type_2);

       qual_desc = mrtqrng (attr_desc, lower_limit,
            limit_type_1, upper_limit, limit_type_2);

The conversion routines can be used to convert external or internal format attribute values to file format as required by `mrqrng` and `mrtqrng`.

Example

The program below, `range.c`, uses `mrqrng` to select all records from the `loans` table where the loan amount is greater than $100.00 but less than or equal to $200.00.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr   loans_tabdesc, name_attrdesc,
                   date_attrdesc;
            addr   amount_attrdesc, loans_recdesc,
                   qual, retrieve_desc;
            char*  name_value;
            char*  date_value;
            char*  amount_value;

            loans_tabdesc = mropen (DATABASE, "loans", 'r');

            loans_recdesc = mrmkrec (loans_tabdesc);
            name_attrdesc = mrngeta (loans_tabdesc, "name");
            date_attrdesc = mrngeta (loans_tabdesc, "date");
            amount_attrdesc = mrngeta (loans_tabdesc, "amount");

            name_value = mrspv (name_attrdesc);
            date_value = mrspv (date_attrdesc);
            amount_value = mrspv (amount_attrdesc);

            printf ("Employees Owing Between $100 and $200\n\n");
            printf (" Name          Date           Amount\n");
            qual = mrqrng (amount_attrdesc, mrcvtv
                   (amount_attrdesc, "$100.00"), 'e', mrcvtv2
                   (amount_attrdesc, "$200.00"), 'i');

            retrieve_desc = mrgetbegin (qual, loans_recdesc, ADDRNIL);
            while (mrget (retrieve_desc))
            {
                 mrcopyv (loans_recdesc, name_attrdesc, name_value);
                 mrcopyv (loans_recdesc, date_attrdesc, date_value);
                 mrcopyv (loans_recdesc, amount_attrdesc, amount_value);
                 printf ("%-12s%-22s%-12s\n", name_value, date_value, amount_value);
            }
            mrgetend (retrieve_desc);

            mrfree (name_value);
            mrfree (date_value);
            mrfree (amount_value);
            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

### 3.13.3 Comparison with Another Attribute

`mrqatr` and `mrtqatr` compare the values of two attributes. They take as arguments one of the six operators described above, and the two attribute_descriptors. `mrqatr` terminates the calling program if there are problems with the arguments, while `mrtqatr` returns `ADDRNIL` on failure. Their general forms are:

       qual_desc = mrqatr (operator, attr_desc_1, attr_desc_2);

       qual_desc = mrtqatr (operator, attr_desc_1, attr_desc_2);

Example

The program below, `attrcmp.c`, uses `mrqatr`. It sums the loans for each employee and prints all instances where the sum exceeds the credit limit for that employee. (Note that this cannot be done without using temporary files if the Shell or Standard C Interfaces are used.) The routine `dollcvt` is used to change the value of a dollar attribute into a form suitable for conversion into float or double.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       extern      double     dollcvt ();

       msmain ()
       {
            addr      loans_tabdesc, pers_tabdesc,
                      loans_recdesc, pers_recdesc;
            addr      pname_attrdesc, credit_attrdesc,
                      lname_attrdesc, amount_attrdesc;
            addr      qual, p_retrieve_desc,
                      l_retrieve_desc;
            double    amount, sum, limit;
            char*     pname_value;
            char*     credit_value;
            char*     amount_value;

            loans_tabdesc = mropen (DATABASE, "loans",'r');
            pers_tabdesc = mropen (DATABASE, "personnel", 'r');

            loans_recdesc = mrmkrec (loans_tabdesc);
            pers_recdesc = mrmkrec (pers_tabdesc);

            amount_attrdesc = mrngeta (loans_tabdesc, "amount");
            pname_attrdesc = mrngeta (pers_tabdesc, "name");
            lname_attrdesc = mrngeta (loans_tabdesc, "name");
            credit_attrdesc = mrngeta (pers_tabdesc, "credit_limit");

            amount_value = mrspv (amount_attrdesc);
            pname_value = mrspv (pname_attrdesc);
            credit_value = mrspv (credit_attrdesc);

            printf ("Employees Exceeding Credit Limit\n\n");
            printf ("Name          Credit Limit Total\n\n");

            /* join loans and personnel */

            qual = mrqatr ("=", lname_attrdesc, pname_attrdesc);

            p_retrieve_desc = mrgetbegin (qual, pers_recdesc, loans_recdesc, ADDRNIL);

            while (mrget (p_retrieve_desc))
            {
                 /* get name from personnel */
                 mrcopyv (pers_recdesc, pname_attrdesc, pname_value);
                 mrcopyv (pers_recdesc, credit_attrdesc, credit_value);

                 /* go through loans summing all amounts
                 for that name */

                 qual = mrqcon ("=", lname_attrdesc,
                        mrcvt (lname_attrdesc, pname_value));
                 sum = 0;

                 l_retrieve_desc = mrgetbegin (qual, loans_recdesc, ADDRNIL);
                 while (mrget (l_retrieve_desc))
                 {
                      /* fetch amount from loans, turn
                      it into a float value, and sum it */

                      mrcopyv (loans_recdesc, amount_attrdesc,amount_value);
                      amount = dollcvt (amount_value);
                      sum = sum + amount;
                 }
                 mrgetend (l_retrieve_desc);

                 /* turn credit_limit into a float value
                 and compare it with the summed loans */

                 limit = dollcvt (credit_value);
                 if (sum > limit)
                    printf ("%-12s%-12s $%-6.2f\n\n", pname_value, credit_value, sum);
            }
            mrgetend (p_retrieve_desc);

            mrfree (pname_value);
            mrfree (credit_value);
            mrfree (amount_value);
            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrfrrec (pers_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

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

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

            *buf_ptr = '\0';
            return (atof (buffer));
       }

### 3.13.4 Comparison with a Pattern

`mrqmch` and `mrtqmch` test if an attribute value contains a string pattern. They take as arguments one of the four operators `MATCH`, `SMATCH`, `!MATCH` or `!SMATCH`, an attribute_descriptor, and the pattern in external format.

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

Table 3-1: 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. | `[1-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 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 discussion on patterns, see the Empress SQL Reference manual.

`mrqmch` terminates the calling program if there are problems with the arguments, while `mrtqmch` returns `ADDRNIL` on failure. Their general forms are:

       qual_desc = mrqmch (operator, attr_desc, pattern);

       qual_desc = mrtqmch (operator, attr_desc, pattern);

Example

The program below, `match.c`, uses `mrqmch` to select all employee names and phone numbers from a given telephone exchange.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr     pers_tabdesc, name_attrdesc,
                     phone_attrdesc, pers_recdesc;
            addr     qual, retrieve_desc;
            char*    name_value;
            char*    phone_value;

            pers_tabdesc = mropen (DATABASE, "personnel", 'r');

            pers_recdesc = mrmkrec (pers_tabdesc);
            name_attrdesc = mrngeta (pers_tabdesc, "name");
            phone_attrdesc = mrngeta (pers_tabdesc, "phone");

            name_value = mrspv (name_attrdesc);
            phone_value = mrspv (phone_attrdesc);

            printf ("Phone Numbers of Employees in Local Area\n\n");
            printf (" Name Phone\n\n");

            qual = mrqmch ("match", phone_attrdesc, "*961*");

            retrieve_desc = mrgetbegin (qual, pers_recdesc, ADDRNIL);
            while (mrget (retrieve_desc))
            {
                 mrcopyv (pers_recdesc, name_attrdesc, name_value);
                 mrcopyv (pers_recdesc, phone_attrdesc, phone_value);
                 printf ("%-12s%-12s\n", name_value, phone_value);
            }

            mrgetend (retrieve_desc);

            mrfree (name_value);
            mrfree (phone_value);
            if (!mrfrrec (pers_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

### 3.13.5 Comparison with NULL

`mrqnul` and `mrtqnul` test if an attribute value is `NULL`. They take as arguments one of the two operators "`=`" and "`!=`", and an attribute_descriptor. `mrqnul` terminates the calling program if there are problems with the arguments, while `mrtqnul` returns `ADDRNIL` on failure. Their general forms are:

       qual_desc = mrqnul (operator, attr_desc);

       qual_desc = mrtqnul (operator, attr_desc);

Example

The program below, `nullcmp.c`, uses `mrqnul`, the last of the simple qualification routines. It lists all employees whose phone number is not known.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr     pers_tabdesc, name_attrdesc,
                     phone_attrdesc, pers_recdesc;
            addr     qual, retrieve_desc;
            char*    name_value;

            pers_tabdesc = mropen (DATABASE, "personnel", 'r');

            pers_recdesc = mrmkrec (pers_tabdesc);
            name_attrdesc = mrngeta (pers_tabdesc, "name");
            phone_attrdesc = mrngeta (pers_tabdesc, "phone");

            name_value = mrspv (name_attrdesc);

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

            qual = mrqnul ("=", phone_attrdesc);
            retrieve_desc = mrgetbegin (qual, pers_recdesc, ADDRNIL);
            while (mrget (retrieve_desc))
            {
                 mrcopyv (pers_recdesc, name_attrdesc, name_value);
                 printf ("%-12s\n", name_value);
            }
            mrgetend (retrieve_desc);

            mrfree (name_value);
            if (!mrfrrec (pers_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

### 3.13.6 Combining Qualifications

`mrqseq` takes an attribute_descriptor and a string as arguments, and qualifies records where the attribute value is equal to the string. If the string cannot be converted to a legal value for that attribute, the routine will fail and terminate the calling program.

`mrqieq` takes an attribute_descriptor and an integer as arguments, and qualifies records where the attribute value is equal to the integer. If the integer cannot be converted to a legal value for that attribute, the routine will fail and terminate the calling program.

All these routines return a qualification_descriptor, which may then be passed to one of the `mrgetbegin` routines.

`mrqand` and `mrqor` are used to create complex qualifications. These are analogous to the `AND` and `OR` of the Query Language, and take as their arguments two of the qualification_descriptors returned by any of the `mrq...` functions, including `mrqand` and `mrqor`. Thus qualifications of arbitrary complexity can be created by repeated calls to these two functions. `mrqand` and `mrqor` also return qualification_descriptors which may be passed to `mrgetbegin` or themselves.

`mrqnot` takes a qualification_descriptor, and reverses the sense of the qualification. Thus, if `mrqieq` is used, passing the qualification_descriptor it returns to `mrqnot` qualifies records where the attribute is not equal to the integer. The qualification_descriptor returned by `mrqnot` may be passed to `mrqand`, `mrqor`, `mrqnot`, or one of the `mrgetbegin` (retrieval initialization) routines.

Note that passing a qualification_descriptor to `mrqand`, `mrqor`, or `mrqnot` destroys it, so that it cannot be re-used.

The general forms of `mrqand`, `mrqor`, and `mrqnot` are:

       qual_desc = mrqand (qual_desc_1, qual_desc_2);

       qual_desc = mrqor (qual_desc_1, qual_desc_2);

       qual_desc = mrqnot (qual_desc);

Finally, `mrqlst` takes a table_descriptor and an array of pointers to previously-found records (found by `mrgetptr`) and re-qualifies the records in the array, returning a qualification_descriptor.

Example

The following program, `oldloans.c`, uses `mrqand` and `mrqor` to find large or overdue loans outstanding to a particular employee. It retrieves all amounts from the `loans` table for a named employee which are larger than a given amount or older than a given date. This example also illustrates the use of arguments to set parameter values.

    #include    <mscc.h>

    #define     DATABASE   "repairs"

    #define     ARGNUMBER  4

    msmain   (int argc, char** argv)
    {
         addr     loans_tabdesc, loans_recdesc, name_attrdesc;
         addr     date_attrdesc, amount_attrdesc;
         char*    date_value;
         char*    amount_value;
         addr     qual, qual_2, retrieve_desc;
         char*    name;
         char*    date;
         char*    amount;

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

         loans_tabdesc = mropen (DATABASE, "loans", 'r');

         loans_recdesc = mrmkrec (loans_tabdesc);

         name_attrdesc = mrngeta (loans_tabdesc, "name");
         date_attrdesc = mrngeta (loans_tabdesc, "date");
         amount_attrdesc = mrngeta (loans_tabdesc, "amount");

         date_value = mrspv (date_attrdesc);
         amount_value = mrspv (amount_attrdesc);

         qual = mrqcon ("=", name_attrdesc, mrcvt (name_attrdesc, name));
         qual = mrqand (qual, mrqcon (">", date_attrdesc,
                        mrcvt (date_attrdesc, date)));

         qual_2 = mrqcon (">", amount_attrdesc, mrcvt (amount_attrdesc, amount));
         qual_2 = mrqand (qual_2, mrqcon ("=", name_attrdesc,
                          mrcvt (name_attrdesc, name)));

         qual = mrqor (qual, qual_2);

         printf ("Loans to %s Greater Than %s or Older Than %s\n\n", name, amount, date);
         printf ("Date     Amount\n");
         retrieve_desc = mrgetbegin (qual, loans_recdesc, ADDRNIL);
         while (mrget (retrieve_desc))
         {
            mrcopyv (loans_recdesc, amount_attrdesc, amount_value);
            mrcopyv (loans_recdesc, date_attrdesc, date_value);

            printf ("%-22s%-12s\n", date_value, amount_value);
         }
         mrgetend (retrieve_desc);

         mrfree (date_value);
         mrfree (amount_value);
         if (!mrfrrec (loans_recdesc))
         {
    	fprintf (stderr, "ERROR in freeing record buffers\n");
    	fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
         }

         if (!mrclose (loans_tabdesc))
         {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
         }
         return 0;
    }

## 3.14 Deleting Records

The routines `mrdel` or `mrtdel`, which take a record_descriptor as their argument, delete a record. `mrdel` terminates the calling program if the deletion fails, while `mrtdel` returns true if it succeeds and false if it fails. Normally, records are qualified before they are deleted, so the appropriate records are first initialized using `mrgetbegin` and the qualification routines, then made current with `mrget`. After all deletions have been made, `mrdelend`, which also takes a record_descriptor as its argument, should be called to write the Empress internal buffer to the file.

The general forms of the commands for deleting records are:

       mrdel (record_desc);

       flag = mrtdel (record_desc);

       flag = mrdelend (record_desc);

Example

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

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain ()
       {
            addr     loans_tabdesc, date_attrdesc,
                     loans_recdesc;
            addr     qual, retrieve_desc;
            char*    date_value;

            loans_tabdesc = mropen (DATABASE, "loans", 'u');

            loans_recdesc = mrmkrec (loans_tabdesc);
            date_attrdesc = mrngeta (loans_tabdesc, "date");
            qual = mrqcon ("<", date_attrdesc,
                           mrcvt (date_attrdesc, "1 May 1992"));

            retrieve_desc = mrgetbegin (qual, loans_recdesc, ADDRNIL);
            while (mrget (retrieve_desc))
                 mrdel (loans_recdesc);

            mrgetend (retrieve_desc);

            if (!mrdelend (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in writing internal buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.15 Updating Records

To update a record, a second record_descriptor for the table must first be obtained using `mrmkrec`. The first (old) record is then copied into the second (new) record, using `mrcopyr`, which takes as its arguments the new and old record_descriptors. Attribute values in the new record are altered using `mrputvs`, `mrputvi`, etc., in the same way as for a record to be added to the table. The record is updated by calling `mrput`, which takes the new and old record_descriptors as its arguments.

If there is a possibility that the update may be unsuccessful (for example, if it may cause a duplicate value for an attribute with a unique index, or if record level locking is in force), you should use `mrtput` instead of `mrput`. `mrtput` returns true if the update is successful and false if it is not.

The general forms of these routines are:

       flag = mrcopyr (new_rec_desc, old_rec_desc);

       mrput (new_rec_desc, old_rec_desc);

       flag = mrtput (new_rec_desc, old_rec_desc);

Example

The following program, `interest.c`, calculates interest at 2% on each loan in the `loans` table, prints out the current amount owing and the interest, adds the interest to the current amount, and prints out the new total. It then updates each record using the new totals.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       extern      double     dollcvt ();

       msmain ()
       {
            addr      loans_tabdesc, pers_tabdesc,
                      loans_recdesc, pers_recdesc,
                      new_recdesc;
            addr      pname_attrdesc, lname_attrdesc,
                      date_attrdesc, amount_attrdesc;
            addr      qual, p_retrieve_desc,
                      l_retrieve_desc;
            double    amount, sum, newamount, interest;
            char*     name;
            char*     pname_value;
            char*     date_value;
            char*     amount_value;
            char      value[20];

            loans_tabdesc = mropen (DATABASE, "loans", 'u');
            pers_tabdesc = mropen (DATABASE, "personnel", 'r');

            loans_recdesc = mrmkrec (loans_tabdesc);
            new_recdesc = mrmkrec (loans_tabdesc);
            pers_recdesc = mrmkrec (pers_tabdesc);

            pname_attrdesc = mrngeta (pers_tabdesc, "name");
            lname_attrdesc = mrngeta (loans_tabdesc, "name");
            amount_attrdesc = mrngeta (loans_tabdesc, "amount");
            date_attrdesc = mrngeta (loans_tabdesc, "date");

            amount_value = mrspv (amount_attrdesc);
            pname_value = mrspv (pname_attrdesc);
            date_value = mrspv (date_attrdesc);

            p_retrieve_desc = mrgetbegin (ADDRNIL, pers_recdesc, ADDRNIL);
            while (mrget (p_retrieve_desc))
            {
                 mrcopyv (pers_recdesc, pname_attrdesc, pname_value);

                 printf ("\n\nMonthly Statement for %s:\n\n", pname_value);
                 printf ("Loan     Date Made     Interest Total\n");

                 sum = 0;
                 qual = mrqcon ("=", lname_attrdesc,
                                mrcvt (lname_attrdesc, pname_value));
                 l_retrieve_desc = mrgetbegin (qual, loans_recdesc, ADDRNIL);
                 while (mrget (l_retrieve_desc))
                 {
                      mrcopyv (loans_recdesc, amount_attrdesc, amount_value);

                      mrcopyv (loans_recdesc, date_attrdesc, date_value);
                      amount = dollcvt (amount_value);
                      interest = amount * 0.02;
                      newamount = amount + interest;
                      sum = sum + newamount;
                      printf ("$%-6.2f %s      $%-6.2f $%-6.2f\n",
                              amount, date_value, interest, newamount);

            	  if (!mrcopyr (new_recdesc, loans_recdesc))
    		  {
    			fprintf (stderr, "Unable to copy the descriptors\n");
    			fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		  }

                      sprintf (value, "$%-6.2f", newamount);
                      if (mrputvs (new_recdesc, amount_attrdesc, value))
                           mrput (new_recdesc, loans_recdesc);
                      else
                           fprintf (stderr, "Cannot convert\
                                newamount '%-6.2f'\n\
                                Update not done for %s %s\
                                %-6.2f\n", newamount,
                                pname_value, date_value, amount);
                 }
                 mrgetend (l_retrieve_desc);

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

            mrfree (pname_value);
            mrfree (date_value);
            mrfree (amount_value);
            if (!mrfrrec (loans_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrfrrec (new_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrfrrec (pers_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (loans_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

       double dollcvt (char* string)     /* strip $ , * space from dollar amounts */
       {
            extern     double    atof ();
            char       c, buffer [20];
            char*      buf_ptr;

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

## 3.16 Saving a Record for Later Use

In application programs it is often necessary to carry out a number of different operations on the same record at different points in the application. Once a record has been found you may either:

1. Save it in memory so it is directly available.

2. Save a pointer to it so that subsequent retrievals need not go through the qualification process.

Saving a record or a pointer is especially useful if the record values have been changed, since the original set of qualifications would then not suffice to find the record again.

### 3.16.1 Saving a Record in Memory

Saving a record in memory involves copying the record_descriptor for that record. It should only be done for a few records, to conserve space. Saving a record in memory is also convenient if the record is to be kept locked.

To copy a record_descriptor, call `mrmkrec` to get a new record_descriptor and copy the current one to the new one with `mrcopyr`, as if for an update. This leaves a copy of the record in memory; it may be accessed at any time without having to be read from the table again. (This would normally occur in the middle of a retrieval loop, once you have decided that the current record will be needed later.) The saved record_descriptor may be passed to one of the value-retrieving routines, such as `mrcopyv`, at any time later in the program. When you are finished with the record, free up the space used with `mrfrrec`.

### 3.16.2 Saving a Pointer to a Record

To obtain a pointer to a record, use the routine `mrgetptr` (after making the record current with `mrget`) which takes a record_descriptor as its argument. To retrieve a single record again, pass this pointer and a record_descriptor to the routine `mrgetrec`, which reads the record from the table again. To retrieve several records again, collect an array of pointers to the records and pass it to the routine `mrqlst`, which returns a qualification_descriptor that may then be passed to one of the retrieval initialization (e.g. `mrgetbegin`) routines.

`mrgetrec` is actually a special case of using `mrqlst`. In effect, it assigns the pointer it is passed to the first entry of a pointer array, calls `mrqlst` with this array, calls `mrgetbegin` with the qualification_descriptor returned by `mrqlst`, calls `mrtget` to find the record, and `mrgetend` to clean up after the retrieval. If the routine is successful, the record_descriptor now refers to the desired record and may be passed to value-retrieving routines.

Because `mrgetrec` sets up what may be thought of as a one-iteration retrieval loop, more than a couple of successive calls to `mrgetrec` are less efficient than storing the pointers in an array and setting up a retrieval loop yourself.

If `mrgetrec` has been used, the record retrieved will have been locked if possible (see next section), in which case it must be explicitly unlocked again by calling `mrulrec` with the record_descriptor which was passed to `mrgetrec`.

## 3.17 Record-Level Locks

If record level locking has been set for a given table, then the mr routines will automatically lock each record as it is accessed via a retrieval loop. This automatic lock is removed as soon as the next record is retrieved, or when the loop ends (with `mrgetend`). If a record is to be saved for later use (see previous section), and you want to keep it locked to prevent others from accessing it, you must make an explicit lock on it.

To do this, obtain a new record_descriptor by a call to `mrmkrec` and ensure it refers to the right record by calling `mrcopyr`, then pass this new record_descriptor to `mrlkrec`. The record will then stay locked after the loop terminates. Be sure to use the new record_descriptor when referring to the record later.

After you have finished with the record, unlock it by passing its record_descriptor to `mrulrec`, and free up the space used by the record_descriptor (with `mrfrrec`).

This procedure also works if table or group level locking is set for the table. A lock made on a record within a retrieval loop with `mrlkrec` is in addition to the existing table or group level lock.

Example

The following program, `kilroy.c`, illustrates the general principle of saving a record and locking it. It is a variation of the employees program; it saves Kilroy's record from the `personnel` table and re-prints the name and phone after the main retrieval loop is finished.

       #include    <mscc.h>

       #define     DATABASE   "repairs"

       msmain()
       {
            addr     pers_tabdesc, name_attrdesc,
                     phone_attrdesc, pers_recdesc;
            addr     retrieve_desc, rec_desc_2;
            int      flag, employees;

            char*    name_value;
            char*    phone_value;

            pers_tabdesc = mropen (DATABASE, "personnel", 'r');

            pers_recdesc = mrmkrec (pers_tabdesc);
            name_attrdesc = mrngeta (pers_tabdesc, "name");
            phone_attrdesc = mrngeta (pers_tabdesc, "phone");

            name_value = mrspv (name_attrdesc);
            phone_value = mrspv (phone_attrdesc);

            printf ("Current Employees\n\n");
            printf ("Name           Phone\n\n");

            employees = flag = 0;
            retrieve_desc = mrgetbegin (ADDRNIL, pers_recdesc, ADDRNIL);
            while (mrget (retrieve_desc))
            {
                 mrcopyv (pers_recdesc, name_attrdesc, name_value);
                 mrcopyv (pers_recdesc, phone_attrdesc, phone_value);
                 printf ("%-12s%-12s\n", name_value, phone_value);
                 employees++;
                 if (strcmp (name_value, "Kilroy") == 0)
                 {
                      rec_desc_2 = mrmkrec (pers_tabdesc);
            	  if (!mrcopyr (rec_desc_2, pers_recdesc))
    		  {
    			fprintf (stderr, "Unable to copy the descriptors\n");
    			fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		  }
                      mrlkrec (rec_desc_2);
                      flag = 1;
                 }
            }
            mrgetend (retrieve_desc);
            printf ("\nTotal Number of Employees = %d\n\n",  employees);
            if (flag)
            {
                 mrcopyv (rec_desc_2, name_attrdesc, name_value);
                 mrcopyv (rec_desc_2, phone_attrdesc, phone_value);
                 printf ("%-12s%-12s\n", name_value, phone_value);

                 if (!mrulrec (rec_desc_2))
    	     {
    		fprintf (stderr, "Unable to remove lock\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	     }
                 if (!mrfrrec (rec_desc_2))
    	     {
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	     }
            }
            mrfree (name_value);
            mrfree (phone_value);
            if (!mrfrrec (pers_recdesc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (pers_tabdesc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
    	return 0;
       }

## 3.18 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 an introduction to transactions.

A transaction may be started with a call to `mrtrstart`. The routine has no arguments; it returns true if it succeeds in starting a transaction and false if it fails. Failure normally means a transaction is already in progress.

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

The general forms of these are:

       flag = mrtrstart ();

       flag = mrtrcommit ();

       flag = mrtrcancel ();

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

       flag = mrtrsave (savepoint);

       flag = mrtrrollback (savepoint);

If an mr error causes the calling program to be terminated while a transaction is in progress, the transaction is cancelled before the program is terminated.

## 3.19 Expressions

The mr routines allow you to define and execute queries directly on the data in Empress databases. Routines such as `mrqcon` (compare an attribute with a constant in file format), `mrqatr` (compare two attributes), and `mrqseq` (compare an attribute with a string constant), can be used to define simple boolean expressions that form the basis of qualifications. In addition, routines such as `mrqand`, `mrqor`, and `mrqnot` can be used to build complex qualifications from simpler ones. The use of these routines is discussed in the preceding "Qualifications" section.

Expressions are built from functions (or operators) and arguments (or operands). In this discussion we use the term "function" to include Empress operators (such as "+" and "-"), Empress built-in functions, and user defined functions and operators. In this discussion we use the term "argument" to include arguments to functions and operands of operators. The Empress operators and built-in functions are documented in the Empress SQL: Reference manual under the chapter Expressions. User defined functions and operators are described in the Empress: User Defined Functions. Operands can be constants, variables, or attribute values.

The expressions that you build using the mr routines can either be evaluated directly without reference to a database, or used in building qualifications for retrieving records from tables.

This section discusses the routines available for defining and executing complex expressions.

### 3.19.1 Using Expressions

All the expression-building routines are prefixed with `mre`. They are summarized in the next section.

Any boolean expression can be directly converted into a qualification using the routine `mrqexpr`. Its general form is:

       qual_desc = mrqexpr (expr_desc);

`expr_desc` is a pointer to a descriptor for the expression `N` (obtained from `mreend`). `qual_desc` is a pointer to the qualification_descriptor.

Expressions are evaluated directly using the routine `mrerun`. Its general form is:

       result = mrerun (expr_desc);

`result` is a pointer to the result of the expression.

### 3.19.2 Building Expressions

Expressions are built using a stack (in the same way that qualifications are built in the mx routines). Expression building begins with a call to `mrebegin` and ends with a call to `mreend` which returns a pointer to the expression descriptor. When an expression is no longer needed its descriptor should be freed using the routine `mrefree`; however, converting an expression to a qualification with the routine `mrqexpr` also frees the descriptor. If an error condition is detected while building an expression, the routine `mreabort` can be used to abort the expression building process.

The general forms of these are:

       mrebegin ();

       expr_desc = mreend ();

       flag = mrefree (expr_desc);

       mreabort ();

The routines `mrecons` and `mreivar` add constants and variables, respectively, as arguments to the expression. The routine `mrerecattr` adds an attribute argument to the expression. The routine `mrenull` is used to determine whether an argument value is `NULL`. Their general forms are:

       flag = mrecons (string, type_desc);

       mreivar (ivar, type_desc);

       flag = mrerecattr (record_desc, attr_desc);

       flag = mrenull (func);

`type_desc` is a pointer to a data type descriptor. `string` is a pointer to a constant in external format. `ivar` is an indirect pointer to a variable. `func` is a pointer to the function, in this case is "`=`" or "`!=`".

Functions are added to the expression using `mrefunc`. When a function is added the arguments it requires must be on the stack. The general form of the routine is:

       flag = mrefunc (func, nargs);

`func` is the name of the function and `nargs` is the number of arguments the function takes.

The routine `mrecvarg` converts the data type of the expression into a specified data type. Its general form is:

       flag = mrecvarg (type_desc);

### 3.19.3 Empress Values and C Variables

When retrieved values from a database, or values converted to Empress data types, are stored in C variables, the variables must be of the appropriate type. A table of correspondences between Empress and C data types is given in Data Type Correspondences of this manual. This applies to the expression related routines `mreivar` and `mrerun`.

### 3.19.4 Specifying Data Types to Routines

The routines that take a data type as an argument (that is, `mrecons`, `mreivar`, and `mrecvarg`) may use any of the Empress generic data types or user data types. For generic data types the following values can be used for the data type argument.

Table 3-2: Generic Data Type Values

Data Type | Parameter
`GENERIC BINARY` | `msdtpar_gen_bin_p`
`GENERIC BOOLEAN` | `msdtpar_gen_bol_p`
`GENERIC INTEGER` | `msdtpar_gen_int_p`
`GENERIC FLOAT` | `msdtpar_gen_flt_p`
`GENERIC CHAR` | `msdtpar_gen_chr_p`
`GENERIC DATE` | `msdtpar_gen_dat_p`
`GENERIC DECIMAL` | `msdtpar_gen_dec_p`
`GENERIC EXTERNAL` | `msdtpar_gen_ext_p`
`GENERIC INTERVAL` | `msdtpar_gen_inv_p`

For other data types the data type descriptor can be obtained by calling the routine `mrgdtpar` which returns a pointer to the data type descriptor for the attribute.

### 3.19.5 Examples

Eight examples illustrating building and evaluating expressions follow:

Example 1

The following program evaluates the simple expression (5-i), where i ranges from 1 to 5, and prints the results.

       /* Build and evaluate expression (5 -i), where i = 1 to 5 */

       #include     <mscc.h>

       msmain()
       {
            addr     result;
            addr     expr;
            long*    i;
            long     ival;

            i = &ival;

            mrebegin ();     /* begin building expression */

            mrecons ("5", dtpgint);
            mreivar ((addr)&i, dtpgint);
            mrefunc ("-", 2);

            expr = mreend();          /* expr is (5 - i) */

            for (*i = 1; *i <=5; ++(*i))
            {
                 result = mrerun (expr);     /* evaluate the expression */

                 printf ("%ld\n", *(long*) result);
            }
            if (!mrefree (expr)) /* free the expression */
    	{
    		fprintf (stderr, "ERROR in freeing the expression\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
       }

Example 2

The following program calculates the product of the attributes `a` (INTEGER data type) and `b` (FLOAT data type) in the table `tab` from the `repairs` database.

       /* expression (attr1 * attr2) */

       #include     <mscc.h>

       char*        Database = "repairs";

       msmain()
       {
            addr     rec_desc;
            addr     mr_desc;
            addr     ret_desc;
            addr     a_des, b_des;
            addr     result;
            addr     expr;

            mr_desc = mropen (Database, "tab", 'r');

            a_des = mrngeta (mr_desc, "a"); /*attribute descriptors for */

            b_des = mrngeta (mr_desc, "b");   /* a and b. */

            rec_desc = mrmkrec (mr_desc);

            mrebegin();

            if (!mrerecattr (rec_desc, a_des))     /* add attr a */
    	{
    		fprintf (stderr, "mrerecattr failed\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrerecattr (rec_desc, b_des))     /* add attr b */
    	{
    		fprintf (stderr, "mrerecattr failed\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            mrefunc ("*", 2);

            expr = mreend ();                 /* expr is (a*b)*/

            ret_desc = mrgetbegin (ADDRNIL, rec_desc, ADDRNIL);
            while (mrget (ret_desc))   /* for all the records in the table */
            {
                result = mrerun (expr);     /* evaluate expression */
                printf ("%f\n", *(double *) result );
            }
            mrgetend (ret_desc);
            if (!mrefree (expr)) /* free the expression */
    	{
    		fprintf (stderr, "ERROR in freeing the expression\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrfrrec (rec_desc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (mr_desc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
       }

Example 3

The following program prints the value of attribute `c` (CHAR data type) in table `tab` when it is not `NULL` from the `repairs` database.

       /* print the values of records for which a field is not null */

       #include     <mscc.h>

       char*        Database = "repairs";

       msmain()
       {
            addr     rec_desc;
            addr     mr_desc;
            addr     ret_desc;
            addr     c_des;
            char*    result;
            addr     expr;
            addr     q;

            mr_desc = mropen (Database, "tab", 'r');

            c_des = mrngeta (mr_desc, "c");
            rec_desc = mrmkrec (mr_desc);

            mrebegin ();

            if (!mrerecattr (rec_desc, c_des))     /* add attr c */
    	{
    		fprintf (stderr, "mrerecattr failed\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrenull ("!=" ))     /* c != null */
            {
                 printf ("Can not put null checking operator in the expression\n");
                 mreabort ();          /* abort building expression */

                 msend();
    	     return 1;
            }
            expr = mreend ();          /* expr is  c != null */

            q = mrqexpr (expr);        /* make qualification out of expr */

            ret_desc = mrgetbegin (q, rec_desc, ADDRNIL);

            while (mrget (ret_desc))
            {
                 printf ("%s\n", mrgetvs (rec_desc, c_des));
            }
            mrgetend (ret_desc);

            if (!mrfrrec (rec_desc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (mr_desc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
       }

Example 4

The following program prints the first three digits of the phone numbers of the `personnel` table in the `repairs` database.

       /* get the first 3 digits from the phone numbers for all employees */

       #include     <mscc.h>

       char*        Database = "repairs";

       msmain()
       {
            addr     rec_desc;
            addr     mr_desc;
            addr     ret_desc;
            addr     phone_des, name_des;
            char*    result;
            addr     expr;

            mr_desc = mropen (Database, "personnel", 'r');

            phone_des = mrngeta (mr_desc, "phone");
            name_des = mrngeta (mr_desc, "name");

            rec_desc = mrmkrec (mr_desc);

            mrebegin ();

            if (!mrerecattr (rec_desc, phone_des))     /* add attr phone */
    	{
    		fprintf (stderr, "mrerecattr failed\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            mrecons ("1", dtpgint); /* start position for string extraction */
            mrecons ("3", dtpgint); /* number of chars to be get */

            mrefunc ("substr", 3);

            expr = mreend ();   /* expression is substr(phone,1,3) */
            ret_desc = mrgetbegin (ADDRNIL, rec_desc, ADDRNIL);
            while (mrget (ret_desc)) /* for all the records in the table */
            {
                 result = mrerun (expr);     /* evaluate expression */
                 printf ("%s %s\n", mrgetvs (rec_desc, name_des), result);
            }
            mrgetend (ret_desc);

            if (!mrefree (expr)) /* free the expression */
    	{
    		fprintf (stderr, "ERROR in freeing the expression\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            if (!mrfrrec (rec_desc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (mr_desc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
       }

Example 5

The following program prints the names of employees whose credit limit will exceed $1000 given a 50% increase of credit.

       /* find the names of the employees who will exceed a credit
          limit of $1000, if given 50% increase in credit. */

       #include     <mscc.h>

       char*        Database = "repairs";

       msmain()
       {
            addr     rec_desc;
            addr     mr_desc;
            addr     ret_desc;
            addr     credit_des, name_des;
            addr     q;
            addr     expr;

            mr_desc = mropen (Database, "personnel", 'r');

            credit_des = mrngeta (mr_desc, "credit_limit");
            name_des = mrngeta (mr_desc, "name");
            rec_desc = mrmkrec (mr_desc);

            mrebegin ();

            if (!mrerecattr (rec_desc, credit_des))
    	{
    		fprintf (stderr, "mrerecattr failed\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
            mrecons ("1.5", dtpgdec);
            mrefunc ("*", 2);     /* (1.5 * credit_limit) */
            mrecons ("1000", dtpgdec);
            mrefunc (">", 2);

            expr = mreend ();     /* expression built is
                                     (1.5 * credit_limit) > 1000 */

            q = mrqexpr (expr);   /* make qualification */

            ret_desc = mrgetbegin (q, rec_desc, ADDRNIL);

            while (mrget (ret_desc))     /* for all the records in the table */
                 printf ("%s\n", mrgetvs (rec_desc, name_des));

            mrgetend (ret_desc);

            if (!mrfrrec (rec_desc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (mr_desc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
       }

Example 6

The following program prints the names of employees whose credit limit would exceed $1000 given an increase of $500.

          /* find the names of all the employees whose credit limit
             will   become $1000 or more if given an increase of $500. */

          #include     <mscc.h>

          char*        Database = "repairs";

          msmain()
          {
               addr     rec_desc;
               addr     mr_desc;
               addr     ret_desc;
               addr     credit_des, name_des;
               addr     q;
               addr     expr;

               mr_desc = mropen (Database, "personnel", 'r');

               credit_des = mrngeta (mr_desc, "credit_limit");
               name_des = mrngeta (mr_desc, "name");
               rec_desc = mrmkrec (mr_desc);

               mrebegin ();

               mrecons ("$500", mrgdtpar (credit_des));
               if (!mrerecattr (rec_desc, credit_des))
    	   {
    		fprintf (stderr, "mrerecattr failed\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }
               mrefunc ("+", 2);      /* (credit_limit + $500) */
               mrecons ("1000", dtpgdec);
               mrefunc (">", 2);

               expr = mreend ();      /* (credit_limit + $500) = 1000 */

               q = mrqexpr (expr);

               ret_desc = mrgetbegin (q, rec_desc, ADDRNIL);

               while (mrget (ret_desc))
                    printf ("%s\n", mrgetvs( rec_desc, name_des));

               mrgetend (ret_desc);

               if (!mrfrrec (rec_desc))
    	   {
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }

               if (!mrclose (mr_desc))
    	   {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }
          }

Example 7

The following program prints the names of employees other than Mosca whose credit limit would exceed $1000 given an increase of $500.

       /* find the names of the employees who will exceed a credit limit of$1000,
          if given $500 increase in credit and the name is not Mosca. */

       #include     <mscc.h>

       char*        Database = "repairs";

       msmain()
       {
            addr     rec_desc;
            addr     mr_desc;
            addr     ret_desc;
            addr     credit_des, name_des;
            addr     q, qexpr, qname;
            addr     expr;

            mr_desc = mropen (Database, "personnel", 'r');

            credit_des = mrngeta (mr_desc, "credit_limit");
            name_des = mrngeta (mr_desc, "name");

            rec_desc = mrmkrec (mr_desc);
            mrebegin ();

            if (!mrecons ("$500", mrgdtpar (credit_des)))
            {
                 printf ("Can not put '500' on expression stack\n");
                 mreabort ();
                 msend();
    	     return 1;
            }
            if (!mrerecattr (rec_desc, credit_des))
    	{
    		fprintf (stderr, "mrerecattr failed\n"
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (! mrefunc ("+", 2))     /*(credit_limit + 500) */
            {
                 printf ("Can not put '+' operator on expression stack\n");
                 mreabort ();
                 msend();
    	     return 1;
            }
            if (! mrecons ("1000", dtpgdec))
            {
                 printf ("Can not put '1000' on expression stack\n");
                 mreabort ();
                 msend();
    	     return 1;
            }
            if (! mrefunc (">", 2))
            {
                 printf ("Can not put '>' on expression stack\n");
                 mreabort ();
                 msend();
    	     return 1;
            }
            expr = mreend ();     /* expression built is
                                     (1.5 * credit_limit) = 1000*/

            qexpr = mrqexpr (expr);     /* make qualification */

            qname = mrqcon ("!=", name_des, "Mosca");
            q = mrqand (qexpr, qname);

            ret_desc = mrgetbegin (q, rec_desc, ADDRNIL);

            while (mrget(ret_desc))    /* for all the records in the table */
                 printf ("%s\n", mrgetvs (rec_desc, name_des));

            mrgetend (ret_desc);

            if (!mrfrrec (rec_desc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (mr_desc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
       }

Example 8

The following program (`swap.c`) evaluates the expression that uses a Persistent Stored Module to exchange the content of two non-null strings given as command line arguments to the program. The program demonstrates dynamic memory allocation routines:

|  |  | `mspsm_malloc` | Allocate space to store data in Persistant Stored Modules (PSM).
|  |  | `mrmalloc` | Allocate space to store data that will subsequently be used by Empress RDBMS.
|  |  | `mrfree` | Free the space allocated by `mrmalloc`.

Note that the space (i.e. region of memory) allocated inside the PSM by using `mspsm_malloc` routine will be freed by Empress RDBMS.

       /* PSM -  user defined function to exchange the content of two non-null strings */

       #include        <string.h>
       #include        <usrfns.h>

       void    swapptr (
                       char**         arg1p,
                       char**         arg2p)
       {
               char*   str1;
               char*   str2;

               str1 = (char*) mspsm_malloc (strlen (*arg2p) + 1);
               str2 = (char*) mspsm_malloc (strlen (*arg1p) + 1);
               strcpy (str1, *arg2p);
               strcpy (str2, *arg1p);
               *arg1p = str1;
               *arg2p = str2;
       }

Compile the source file and create a shared object file using `emppsmcc`. For example, compile `swap.c` and create shared object `swap.dll`:

       emppsmcc -o swap.dll swap.c

Within the Interactive SQL session, issue the following SQL commands:

       CREATE MODULE module_swap
          PROCEDURE swap (INOUT GENERIC CHAR, INOUT GENERIC CHAR)

          EXTERNAL NAME swapptr;
       END MODULE;

       UPDATE MODULE module_swap
          FROM "/usr/joe/swap.dll"

Now you can use the following program to call previously defined PSM.

       /* Program that uses expression building mechanism to call
          "swap" -  user defined function to exchange the content
          of two non-null strings  */

       #include        <stdlib.h>
       #include        <string.h>
       #include        <usrfns.h>

       static  void    abort_expr (void);

       #define         DATABASE        "db"

       msmain (int     argc,
               char**  argv)
       {
               char*           arg1;
               char*           arg2;
               addr*           arg1p;
               addr*           arg2p;
               addr            expr;

               if (argc < 3)
               {
                       printf ("Usage: %s str1 str2\n", argv[0]);
                       exit (0);
               }

               mropdict (DATABASE, 'r');

               arg1 = mrmalloc (strlen (argv[1]) + 1);
               arg2 = mrmalloc (strlen (argv[2]) + 1);

               strcpy (arg1, argv[1]);
               strcpy (arg2, argv[2]);

               arg1p = (addr*)& arg1;
               arg2p = (addr*)& arg2;

               printf ("Before swap arg1 = %s; arg2 = %s\n",
                                       *arg1p, *arg2p);

               mrebegin ();
               mreivar (arg1p, (addr)dtpgchr);
               mreivar (arg2p, (addr)dtpgchr);

               if (!mrefunc ("swap", 2))
                       abort_expr ();

               expr = mreend ();

               mrerun (expr);

               printf ("After swap arg1 = %s; arg2 = %s\n",
                                       *arg1p, *arg2p);

               mrfree (arg1);
               mrfree (arg2);
               if (!mrefree (expr)) /* free the expression */
    	   {
    		fprintf (stderr, "ERROR in freeing the expression\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }

               mrcldict ();
       }

       void    abort_expr (void)
       {
               mreabort ();
               mrcldict ();
               printf("Bad procedure name.\n");
       }

## 3.20 Printing Error Messages - mrprterr and mrerrmsg

All of the `mrt...` routines set the integer variable `mroperr` to one of the `MRER...` codes listed in the header file `mrerrno.h` if they fail. This variable may be examined to determine the cause of failure.

The routine `mrprterr` prints error messages when a failure occurs. This routine prints an appropriate message on standard error and terminates the calling program. Its form is:

       mrprterr ();

The routine `mrerrmsg` returns an appropriate message without terminating the calling program. Its form is:

       string = mrerrmsg ();

## 3.21 Manipulating Bulk Data

To illustrate how bulk data is stored and manipulated we will use a database containing digitized voice samples stored as bulk data, and present a program that compares input sounds with the stored samples. The sound samples are assumed to be derived from phoneme synthesis in which a 6-bit phoneme code and 2-bit pitch code are combined to represent each sound. Each sound is thus represented with a 1-byte hexadecimal code.

The sound samples are stored in a database called `sounds`. The table holding the samples was created as:

       CREATE voice_samples (id INTEGER, sample BULK (15,15,10,1));

The primary storage length for each sample is 15 bytes and the extent size is 10 bytes.

The table `voice_samples` currently holds the samples

       id    sample

       1     01 0c 25 2b 01 1f 03 03 27 12 23 0e 27 2a 10
       2     2b 24 1e 0d 3c 33 1f 03 03 27 14 19
       3     1b 02 18 26 2e 0d 1e 2d 12 18 0c 3b 2a

Each sound (represented by one hexadecimal number) is stored in one byte. The length of each sample in bytes can be retrieved as:

       SELECT id, length (sample) FROM voice_samples;

       id    EXPRESSION_1

       1     15
       2     12
       3     13

The bulk values are shown above in their external format - hexadecimal numbers. In external format each hexadecimal number is represented as a string of two bytes. Empress stores bulk values in binary and manipulates them in internal format - a long integer representing the number of bytes in the value followed by the actual bytes. The maximum size of a value is thus the size of the largest integer that can be stored in a C long on the given machine. Manipulating bulk values in internal representation requires only half the memory required to manipulate them than in external representation.

Example

The program `voice.c` illustrates how to access bulk data in internal format.

       #include    <mscc.h>

       #define     database   "sounds"

       msmain()
       {
            int     sound;

            printf ("Enter sound to be searched, CTRL D to quit : ");
            while ((scanf ("%x", &sound) != EOF))
            {
                 printf ("Sample Id\n");
                 getids (sound);
                 printf ("\n\nEnter sound to be searched, CTRL D To quit : ");
            }
            printf ("\n");
       }

       getids (int sound)
       {
            addr              qual;
            addr              ret_desc;
            addr              rec_desc;
            addr              tbl_desc;
            addr              attrid;
            addr              attrsample;
            char              *table_name;
            long              sample_id;
            unsigned char     *sample;
            long              sample_size;
            int               i;

            /* open the table containing voice samples */

            if ((tbl_desc = mrtopen (database, "voice_samples", 'r')) == ADDRNIL)
            {
                 printf ("Can not open table 'voice_samples '\n");
                 return;
            }

            rec_desc = mrmkrec (tbl_desc);

            attrid = mrngeta (tbl_desc, "id");

            attrsample = mrngeta (tbl_desc, "sample");

            /* set to get all the tuples from the table */

            ret_desc = mrgetbegin (ADDRNIL, rec_desc, ADDRNIL);

            while (mrget (ret_desc))
            {
                 /* get sample value in internal representation */
                 sample = mrgeti (rec_desc, attrsample);

                 /* get the size of the sample, in number of bytes */
                 sample_size = *(long *)sample;

                 /* check if the given sound exists in the entire sample */
                 for (i = 0; i < sample_size; i++)
                      if (* (sample + sizeof (long) + i) == sound)
                      {
                           /* get sample id */
                           sample_id = mrgetvi (rec_desc, attrid);
                           printf ("%d\n", sample_id);
                           break;
                      }
            }

            mrgetend (ret_desc);

            if (!mrfrrec (rec_desc))
    	{
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}

            if (!mrclose (tbl_desc))
    	{
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	}
       }

Routine `msdtfsta` can be used to improve performance of inserts and updates into `TEXT` and `BULK` attributes. For all variable length data types (`TEXT`, `BULK`, and `NLSTEXT`) the data is provided to Empress in a buffer via `mrputi` which assigns the value to an attribute in internal format. The part of the data which will go into the `.rel` file (governed by the second parameter to the `TEXT` and `BULK` data type) is maintained in a buffer within the record descriptor. If the data overflows, then the overflow data is written to a temporary file on disk. The actual record gets transferred into the database when the `mradd` (add the record) or `mrput` (update the record) call is made. At that time both the primary record and the overflow data are written to disk; the overflow data comes from the temporary file. The temporary file is removed when the process ends.

It is expensive in terms of I/O overhead to use a temporary file to store the overflow data. To make the data transfer faster, use `msdtfsta (true)`. If set, a temporary disk file will not be used for storing the overflow data. That is, the insert or update will be done directly from the internal Empress buffers to the database files, without the intermediate use of a disk file. This behavior continues until `msdtfsta (false)` is called.

Please note that if you are using `msdtfsta` you must guarantee that the buffer used to convey the bulk or text data is static or unaltered until `mradd` or `mrput` is called by the program.

This routine is intended to be used only with programming languages (mx, mr, mf, Precompiler) in Empress. It should not be used with any Data Definition Language (DDL) command such as `CREATE TABLE`, `ALTER TABLE`, etc. As a result, these programs must not use any `mscall` routine which executes a DDL statement.

## 3.22 Data Streaming

Data Streaming (manipulating bulk or text data in chunks) is a very useful feature when there is a need to insert/update or retrieve huge bulk (or BLOB, Binary Large Object) or text data. If the data itself is huge, without data streaming capability, it would have to allocate huge amount of memory space to hold the data. If the allocation of large memory space is not affordable or not possible, then the solution is to manipulate data in chunks. In that way it only needs to allocate the memory for one chunk of the bulk or text data.

There are six mr routines for data streaming:

- mrbuktxtcrt
- mrsubbegin
- mrsubend
- mrsubputi
- mrsubgeti
- mrbuktxtlen

To illustrate how data streaming with bulk data works, we will use the same database, `sounds`, from the previous example. This illustration was created with the following criteria:

1. In order to show the usefulness of data streaming, a large amount of bulk data should be used.

2. The bulk data which is used for the example programs should be easily generated by the user.

In this case, Empress Recovery Log is used for illustration purpose (not much of practical meaning). Since it can be easily turned on and generates reasonable amount of bulk data. Users can use it to try out the example programs. However, These example programs can be easily modified to be used for any other data (images, voices, ... etc) stored in files or read from the standard input (i.e. stdin).

The following steps will set up the database for the data streaming example programs:

1. To start the continuous recovery logging on the database `sounds`:

   - First, set the `tabzero` variable `MSDBRECOVERYLOG1` with the name of the recovery log file.
   - Next, issue `emprlini` to create the log file.

   For example if `MSDBRECOVERYLOG1` is set to:

          /usr/sounds/log1

   then after issuing `emprlini`,

          emprlini sounds

   you will see the following physical file created:

          /usr/sounds/log1.000

2. Create a table called `logs` which has to be defined with the primary storage length set to 0. For example:

          CREATE logs (log_key INTEGER, log_data BULK (20,0,1024,1));

3. For data streaming, there must be no check sums for the data. (i.e. `MSVALIDSIZE` must be set to zero). If the table `logs` is created with checksums, the easy way to convert it and change the checksum settings is:

          empadm sounds vsize logs -000

### 3.22.1 Example for Data Insertion

The program `insert_in_chunks.c` illustrates how to insert bulk data in chunks. It inserts Empress recovery log data into a `BULK` attribute `log_data` of the table `logs`. Since each bulk data insertion gets registered in the recovery log file, this file becomes very large after several iterations and becomes very illustrative for a case where a file bigger than machine memory resources gets inserted into the database.

The following is a general outline of the program that performs the insertion of the bulk data in chunks:

       Open and allocate space for the resources
               table_desc = mrtopen (database_name, table_name, mode);
               record_desc = mrmkrec (table_desc);
               attr_desc = mrngeta (table_desc, attr_name);
               ...
               bulk_attr_desc = mrngeta (table_desc, attr_name);

       Initiate the start of an update operation.
               mrsubbegin (record_desc);

       Assign an internal format value to a given attribute in a record (for attributes
       other than BULK).
               mrputi (record_desc, attr_desc, var_ptr);
               ...

       Define a size of a space for insertion in an existing overflow (.dtf) file.
               mrbuktxtcrt (record_desc, bulk_attr_desc, size);

       Insert a record into a given table.
               mrtadd (record_desc);

               LOOP
                       Perform a data segment update for BULK attribute.
                       mrsubputi (record_desc, bulk_attr_desc, bulk_attr_structure, offset);
               END LOOP

       Indicate the end of an update operation.
               mrsubend (record_desc);

       Clean up the resources
               mrfrrec (record_desc);
               mrclose(table_desc);

The following is the listing of `insert_in_chunks.c`:

       /*
          This program will read from a stream and insert into the database
       */

       #include <mscc.h>
       #include <string.h>
       #include <stdlib.h>
       #include <sys/errno.h>

       #define USAGE   "USAGE:  %s  size_of_file_to_be_inserted  log_key_value\n"

       static  int     read_and_insert_data (
                                       long            size_of_file,
                                       long            key_value);

       /* program will write in 1K chunks */
       #define SIZE_OF_DATA_CHUNK      1024

       /* attribute names */
       static  char*   attr_name[]= {"log_key","log_data"};

       /* Definition of Empress' bulk attribute */
       struct  bulk    {
                       long            size_of_bulk;
                       unsigned char   data[1];
       };

       int     msmain(
                       int     argc,
                       char*   argv[])
       {
               long    key_value = 0L;
               long    size_value = 0;
               int     res;

               if (argc == 3 )
               {
                       size_value = (long) atoi (argv[1]);
                       key_value = (long) atoi (argv[2]);
                       res = read_and_insert_data (size_value, key_value);
               }
               else
               {
                       fprintf (stderr, USAGE, argv[0], argv[0]);
                       res = 1;
               }
               return res;
       }

       /*
        This routine will read data from an input stream and store it in the
        database
       */
       static  int     read_and_insert_data (
                                       long    size_of_file,
                                       long    key_value)
       {
               addr    tab_des;
               addr    record_des;
               addr    attr_des[2];
               long    flag;
               unsigned char*  input_data;
               struct  bulk*   bulk_attr_structure;
               long    offset;
               long    total_bytes;
               int     bytes_read;

               /* open database table */
               if ((tab_des = mrtopen ("sounds", "logs", 'u')) == ADDRNIL)
               {
                       fprintf(stderr,"Can't open table logs\n");
                       return 1;
               }

               /* Allocating only enough space for 1 bulk chunk */
               if ((input_data = (unsigned char *) malloc (
                               sizeof (long) + SIZE_OF_DATA_CHUNK)) == 0)
               {

                       fprintf(stderr,"Unable to allocate data space\n");
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr,
    				"Unable to close the table\n");
    			fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       return 1;
               }
               bulk_attr_structure = (struct bulk *) input_data;

               /* Allocate a record descriptor */
               if ((record_des = mrmkrec (tab_des)) == ADDRNIL)
               {
                       fprintf(stderr,"Unknown error, check mr error\n");
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr,
    				"Unable to close the table\n");
    			fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       free (input_data);
                       return 1;
               }

               /* Get the attribute descriptors */
               if ((attr_des[0] = mrngeta (tab_des, attr_name[0])) == ADDRNIL)
               {
                       fprintf (stderr,
                               "Problem getting the attribute descriptor for %s\n",
                               attr_name[0]);
            	   if (!mrfrrec (record_des))
    		   {
    			   fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			   fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr,
    				"Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n" mroperr,
    				mrerrmsg ());
    		   }
                       free (input_data);
                                                   return 1;
               }
               if ((attr_des[1] = mrngeta (tab_des, attr_name[1])) == ADDRNIL)
               {
                       fprintf (stderr,
                               "Problem getting the attribute descriptor for %s\n",
                               attr_name[1]);
            	   if (!mrfrrec (record_des))
    		   {
    			   fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			   fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr,
    				"Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n" mroperr,
    				mrerrmsg ());
    		   }
                       free (input_data);
                       return 1;
               }
               /* Initiate a start of an update operation */
               if (!mrsubbegin (record_des))
    	   {
    		fprintf (stderr, "mrsubbegin failed\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }

               /* Insert the key into the database */
               if (!mrputi (record_des, attr_des[0], (addr) &key_value))
               {
                       fprintf (stderr,
                               "Problem: Can't insert key %d\n", key_value);
            	   if (!mrfrrec (record_des))
    		   {
    			   fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			   fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr,
    				"Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n" mroperr,
    				mrerrmsg ());
    		   }
                       free (input_data);
                       return 1;
               }

               /* Define a size of a space for insert in an overflow (.dtf) file.*/
               flag = mrbuktxtcrt (record_des, attr_des[1], size_of_file);
               if (flag != 0)
               {
                       fprintf (stderr,
                               "ERROR could not get %d bytes for buffer flag='%ld'\n",
                               size_of_file, flag);
            	   if (!mrfrrec (record_des))
    		   {
    			   fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			   fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr,
    				"Unable to close the table\n");
    			fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       free (input_data);
                       return 1;
               }
               else
                       fprintf (stderr,
                                       "Creates %d bytes in the overflow file\n",
                                       size_of_file);

               /* Insert a record into a given table */
               if (!mrtadd (record_des))
               {
                       fprintf (stderr, "Insert a record failed\n");
            	   if (!mrfrrec (record_des))
    		   {
    			   fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			   fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr,
    				"Unable to close the table\n");
    			fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       free (input_data);
                       return 1;
               }

               /* Update a given record with a partial bulk chunk */
               bytes_read = read (fileno(stdin), bulk_attr_structure->data,
                               SIZE_OF_DATA_CHUNK);
               bulk_attr_structure->size_of_bulk = (long) bytes_read;
               total_bytes = offset = 0L;
               while (bytes_read != 0)
               {
                       total_bytes = total_bytes + (long) bytes_read;
                       if ((flag = mrsubputi (record_des, attr_des[1],
                                               bulk_attr_structure,
                                               offset)) < 0L)
                       {
                               fprintf (stderr, "Update for bulk attribute at");
                               fprintf (stderr," offset %ld was not successful",
                                               offset);
                               fprintf (stderr," flag='%ld'\n",
                                               flag);
                               break;
                       }
                       else
                               fprintf (stderr,"Total bytes read in %d\n",
                                               total_bytes);
                       /* Set an offset value to insert the next chunk */
                       offset = offset + (long) bytes_read;

                       /* Read the next chunk for stdin */
                       bytes_read = read (fileno(stdin),
                               bulk_attr_structure->data,
                               SIZE_OF_DATA_CHUNK);

                       bulk_attr_structure->size_of_bulk = (long) bytes_read;
               }
               if (!mrsubend (record_des))
    	   {
    		fprintf (stderr, "mrsubend failed\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }

               /* cleanup */
           	   if (!mrfrrec (record_des))
    	   {
    		   fprintf (stderr, "ERROR in freeing record buffers\n");
    		   fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }
               if (!mrclose (tab_des))
    	   {
    		fprintf (stderr,
    			"Unable to close the table\n");
    		fprintf (stderr,
    			"mroperr='%d' : %s\n", mroperr,
    			mrerrmsg ());
    	   }
               free (input_data);
               return 0;
       }

To insert data from the recovery log file `log1.000` into the table `logs`, invoke the program `insert_in_chunks`:

       cp  /usr/sounds/log1.000  log

       insert_in_chunks 23454 1 < log

where `23454` is the size of the file `log1.000` and `1` is the `log_key` inserted in the table `logs`.

### 3.22.2 Example for Data Retrieval

The program `retrieve_in_chunks.c` illustrates how to retrieve bulk data in chunks.

The following is a general outline of the program that performs the retrieval of the bulk data in chunks:

       Open and allocate space for the resources
               table_desc = mrtopen (database_name, table_name, mode);
               record_desc = mrmkrec (table_desc);
               attr_desc = mrngeta (table_desc, attr_name);
               ...
               bulk_attr_desc = mrngeta (table_desc, attr_name);

       Compare an attribute value to a C integer
               qual = mrqieq (attr_desc, (int) key_value);

       Associate a given qualification with one or more records
               ret_desc = mrgetbegin (qual, record_desc, ADDRNIL)

       Get record qualified with the desired key
               mrtget (ret_desc)

       Get the length of the BULK data in bytes
               mrbuktxtlen (record_des, attr_desc);

               LOOP
                       Retrieve a segment of data from a BULK attribute
                       mrsubgeti (     record_desc,
                                       bulk_attr_desc,
                                       bulk_attr_structure,
                                       offset,
                                       bytes_to_read_after_offset);
               END LOOP

       Clean up the resources
               mrgetend (ret_desc);
               mrfrrec (record_desc);
               mrclose(tab_des);

The following is the listing of `retrieve_in_chunks.c`:

       /*
          This program will retrieve data from the database
          and write the results to an output stream.
       */

       #include <mscc.h>
       #include <string.h>
       #include <stdlib.h>
       #include <sys/errno.h>

       #define USAGE   "USAGE:  %s  log_key_value\n"

       static  int     retrieve_and_write (long        key_value);

       /* Program will read in 1K chunks */
       #define SIZE_OF_DATA_CHUNK      1024

       /* Attribute names */
       static  char*   attr_name[]= {"log_key","log_data"};

       /* Definition of Empress' bulk attribute */
       struct  bulk    {
                       long            size_of_bulk;
                       unsigned char   data[1];
       };

       int     msmain(
                       int     argc,
                       char*   argv[])
       {
               long    key_value = 0L;
               long    size_value = 0;
               int     res;

               if (argc == 2)
               {
                       key_value = (long) atoi (argv[1]);
                       res = retrieve_and_write (key_value);
               }
               else
               {
                       fprintf (stderr, USAGE, argv[0], argv[0]);
                       res = 1;
               }
               return res;
       }

       /* This routine will retrieve a bulk record based upon the key passed to it
          and write the bulk record to stdout */

       static  int     retrieve_and_write (long        key_value)
       {
               addr    tab_des;
               addr    record_des;
               addr    attr_des[2];
               unsigned char*  output_data;
               struct  bulk*   bulk_attr_structure;
               addr    ret_des;
               addr    qual;
               long    db_key;
               long    offset;
               long    flag;
               long    whats_left;
               long    total_bytes;

               /* Open database table */
               if ((tab_des = mrtopen ("sounds", "logs", 'r')) == ADDRNIL)
               {
                       fprintf(stderr,"Can't open table logs\n");
                       return 1;
               }

               if ((output_data = (unsigned char *) malloc (
                               sizeof (long) + SIZE_OF_DATA_CHUNK)) == 0)
               {

                       fprintf(stderr,"Unable to allocate data space\n");
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr, "Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       return 1;
               }
               bulk_attr_structure = (struct bulk *) output_data;

               /* Allocate a record descriptor */
               if ((record_des = mrmkrec (tab_des)) == ADDRNIL)
               {
                       fprintf(stderr,"Unknown error, check mr error\n");
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr, "Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       free (output_data);
                       return 1;
               }

               /* Get the attribute descriptors */
               if ((attr_des[0] = mrngeta (tab_des, attr_name[0])) == ADDRNIL)
               {
                       fprintf (stderr,
                               "Problem getting the attribute descriptor for %s\n",
                               attr_name[0]);
            	   if (!mrfrrec (record_des))
    		   {
    			fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr, "Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       free (output_data);
                       return 1;
               }
               if ((attr_des[1] = mrngeta (tab_des, attr_name[1])) == ADDRNIL)
               {
                       fprintf (stderr,
                               "Problem getting the attribute descriptor for %s\n",
                               attr_name[1]);
            	   if (!mrfrrec (record_des))
    		   {
    			fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr, "Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       free (output_data);
                       return 1;
               }

               qual = mrqieq (attr_des[0], (int) key_value);
               if ((ret_des = mrgetbegin (qual, record_des, ADDRNIL)) == ADDRNIL)
               {
                       fprintf (stderr, "Problem: Unable to perform 'mrgetbegin'\n");
            	   if (!mrfrrec (record_des))
    		   {
    			fprintf (stderr,
    				"ERROR in freeing record buffers\n");
    			fprintf (stderr,
    				"mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    		   }
            	   if (!mrclose (tab_des))
    		   {
    			fprintf (stderr, "Unable to close the table\n");
    			fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    		   }
                       free (output_data);
                       return 1;
               }

               /* Get record qualified with the desired key. */
               offset = 0L;
               if (mrtget (ret_des) == 1)
               {
                       whats_left =
                               mrbuktxtlen (record_des, attr_des[1]);

                       /* If the size of the bulk data is smaller than
                          the data chunk, just write it out */
                       if (whats_left > 0  && whats_left < SIZE_OF_DATA_CHUNK)
                       {
                               flag = mrsubgeti (record_des,
                                       attr_des[1],
                                       & bulk_attr_structure,
                                       offset, whats_left);
                               if (flag > 0)
                               {
                                       write (fileno (stdout),
                                               bulk_attr_structure->data,
                                               whats_left);
                                       fprintf (stderr, "Wrote %d bytes to stdout\n",
                                                       whats_left);
                               }
                               else
                                       fprintf (stderr,
                                               "Problem with 'mrsubgeti' at line %d\n",
                                                __LINE__);
                       }
                       else if (whats_left > 0) /* Read/write out the blocks of data */
                       {
                               while (whats_left > SIZE_OF_DATA_CHUNK)
                               {
                                       flag = mrsubgeti (record_des,
                                               attr_des[1],
                                               & bulk_attr_structure,
                                               offset, SIZE_OF_DATA_CHUNK);
                                       whats_left -= SIZE_OF_DATA_CHUNK;
                                       offset += SIZE_OF_DATA_CHUNK;
                                       if (flag > 0)
                                       {
                                               write (fileno (stdout),
                                                       bulk_attr_structure->data,
                                                       SIZE_OF_DATA_CHUNK);
                                               fprintf (stderr,
                                                       "Wrote %d bytes to stdout\n",
                                                       SIZE_OF_DATA_CHUNK);
                                       }
                                       else
                                               fprintf (stderr,
                                               "Problem with 'mrsubgeti' at line %d\n",
                                                __LINE__);
                               }
                               flag = mrsubgeti (record_des,
                                       attr_des[1],
                                       & bulk_attr_structure,
                                       offset, whats_left);
                               if (flag > 0)
                               {
                                       write (fileno (stdout),
                                               bulk_attr_structure->data,
                                               whats_left);
                                       fprintf (stderr, "Wrote %d bytes to stdout\n",
                                               whats_left);
                               }
                               else
                                       fprintf (stderr,
                                       "Problem with 'mrsubgeti' at line %d\n",
                                       __LINE__);
                       }
                       else
                               fprintf (stderr, "Problem with 'mrbuktxtlen'\n");
               }
               else
                       fprintf (stderr, "Record for %d key was not found\n",
                               key_value);
               mrgetend (ret_des);
               if (!mrfrrec (record_des))
    	   {
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }
               mrclose(tab_des);
               if (!mrclose (tab_des))
    	   {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    				mrerrmsg ());
    	   }
               free (output_data);
               fflush (stdout);
               return ((flag < 0) ? 1 : 0);
       }

In order to retrieve data from the recovery log file `log1.000` from the table `logs`, invoke `retrieve_in_chunks`:

       retrieve_in_chunks 1 > bulkfile1

where `1` is the `log_key` from the table `logs` and `bulkfile1` is the name of the file where `BULK` attribute data gets written to.

### 3.22.3 Example for Data Modification

The program `update_chunk.c` illustrates how to update a segment of bulk data.

The following is a general outline of the program that updates the `BULK` attribute starting from the defined offset:

       Open and allocate space for the resources
               table_desc = mrtopen (database_name, table_name, mode);
               record_desc = mrmkrec (table_desc);
               new_record_desc = mrmkrec (table_desc);
               attr_desc = mrngeta (table_desc, attr_name);
               ...
               bulk_attr_desc = mrngeta (table_desc, attr_name);

       Compare an attribute value to a C integer
               qual = mrqieq (attr_desc, (int) key_value);

       Associate a given qualification with one or more records
               ret_desc = mrgetbegin (qual, record_desc, ADDRNIL)

       Get record qualified with the desired key
               mrtget (ret_desc)

       Copy the attribute values from one record into another
               mrcopyr (new_record_desc, record_desc);

       Define the size of the new bulk attribute data
               mrbuktxtcrt (new_record_desc, bulk_attr_desc, size);

       Update a record
       	mrput (new_record_desc, record_desc);

       Initiate a start of an update operation
               mrsubbegin (new_record_desc);

               LOOP
                       Perform a data segment update for BULK attribute.
                       mrsubputi (new_record_desc, bulk_attr_desc, bulk_attr_structure, offset);
               END LOOP

       Indicate the end of an update operation.
               mrsubend (new_record_desc);

       Clean up the resources
               mrgetend (ret_des);
               mrfrrec (record_des);
               mrfrrec (new_record_des);
               mrclose(tab_des);

The following is the listing of `update_chunk.c`

       /*
       This program will read data from a stream and
       update the bulk attribute starting from the defined offset
       */

       #include <mscc.h>
       #include <string.h>
       #include <stdlib.h>
       #include <sys/errno.h>

       #define USAGE   "USAGE:  %s  size_of_file  log_key_value offset\n"

       static  int     read_and_update_data (
                                  long            size_of_file,
                                  long            key_value,
                                  long            offset);

       /* program will write in 1K chunks */
       #define SIZE_OF_DATA_CHUNK      1024

       /* attribute names */
       static  char*   attr_name[]= {"log_key","log_file"};

       /* Definition of Empress' bulk attribute */
       struct  bulk    {
                  long            size_of_bulk;
                  unsigned char   data[1];
       };

       int     msmain(
                  int     argc,
                  char*   argv[])
       {
          long    key_value = 0L;
          long    offset = 0L;
          long    size_value = 0;
          int     res;

          if (argc == 4 )
          {
                  size_value = (long) atoi (argv[1]);
                  key_value = (long) atoi (argv[2]);
                  offset = (long) atoi (argv[3]);
                  res = read_and_update_data (size_value, key_value, offset);
          }
          else
          {
                  fprintf (stderr, USAGE, argv[0], argv[0]);
                  res = 1;
          }
          return res;
       }

       /*
       This routine will read data from an input stream and store it in the
       database
       */
       static  int     read_and_update_data (
                                  long    size_of_file,
                                  long    key_value,
                                  long    offset)
       {
          addr    tab_des;
          addr    record_des;
          addr    record_des2;
          addr    attr_des[2];
          long    flag;
          unsigned char*  input_data;
          struct  bulk*   bulk_attr_structure;
          addr    ret_des;
          addr    qual;
          long    total_bytes;
          int     bytes_read;

          /* open database table */
          if ((tab_des = mrtopen ("sounds", "logs", 'u')) == ADDRNIL)
          {
                  fprintf(stderr,"Can't open table logs\n");
                  return 1;
          }

          /* Allocating only enough space for 1 bulk chunk */
          if ((input_data = (unsigned char *) malloc (
                          sizeof (long) + SIZE_OF_DATA_CHUNK)) == 0)
          {

                  fprintf(stderr,"Unable to allocate data space\n");
                  if (!mrclose (tab_des))
    	      {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  return 1;
          }
          bulk_attr_structure = (struct bulk *) input_data;

          /* Allocate a record descriptor */
          if ((record_des = mrmkrec (tab_des)) == ADDRNIL)
          {
                  fprintf(stderr,"Unknown error, check mr error\n");
                  if (!mrclose (tab_des))
    	      {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  free (input_data);
                  return 1;
          }

          if ((record_des2 = mrmkrec (tab_des)) == ADDRNIL)
          {
                  fprintf(stderr,"Unknown error, check mr error\n");
                  if (!mrclose (tab_des))
    	      {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  free (input_data);
                  return 1;
          }

          /* Get the attribute descriptors */
          if ((attr_des[0] = mrngeta (tab_des, attr_name[0])) == ADDRNIL)
          {
                  fprintf (stderr,
                          "Problem getting the attribute descriptor for %s\n",
                          attr_name[0]);
                  if (!mrfrrec (record_des))
    	      {
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  if (!mrclose (tab_des))
    	      {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  free (input_data);
                  return 1;
          }
          if ((attr_des[1] = mrngeta (tab_des, attr_name[1])) == ADDRNIL)
          {
                  fprintf (stderr,
                          "Problem getting the attribute descriptor for %s\n",
                          attr_name[1]);
                  if (!mrfrrec (record_des))
    	      {
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  if (!mrclose (tab_des))
    	      {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  free (input_data);
                  return 1;
          }

          qual = mrqieq (attr_des[0], (int) key_value);
          if ((ret_des = mrgetbegin (qual, record_des, ADDRNIL)) == ADDRNIL)
          {
                  fprintf (stderr, "Problem: Unable to perform 'mrgetbegin'\n");
                  if (!mrfrrec (record_des))
    	      {
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  if (!mrclose (tab_des))
    	      {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  free (input_data);
                  return 1;
          }

          /* get record qualified with the desired key. */
          if (mrtget (ret_des) == 1)
          {
               if (!mrcopyr (record_des2, record_des))
    	   {
    		fprintf (stderr, "Unable to copy the descriptors\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr,
    			mrerrmsg ());
    	   }

               flag = mrbuktxtcrt (record_des2, attr_des[1], size_of_file+offset);
               if (flag != 0)
               {
                  fprintf (stderr,
                          "ERROR could not get %d bytes for buffer flag='%ld'\n",
                          size_of_file, flag);
                  if (!mrfrrec (record_des))
    	      {
    		fprintf (stderr, "ERROR in freeing record buffers\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  if (!mrclose (tab_des))
    	      {
    		fprintf (stderr, "Unable to close the table\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	      }
                  free (input_data);
                  return 1;
               }
               else
                  fprintf (stderr,
                                  "Creates %d bytes in the overflow file\n",
                                  size_of_file);

               mrput (record_des2, record_des);

               /* Initiate a start of an update operation */
               if (!mrsubbegin (record_des2))
    	   {
    		fprintf (stderr, "mrsubbegin failed\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }

               /* Update a given record with a partial bulk chunk */
               bytes_read = read (fileno(stdin), bulk_attr_structure->data,
                          SIZE_OF_DATA_CHUNK);
               bulk_attr_structure->size_of_bulk = (long)bytes_read;
               total_bytes = 0L;
               while (bytes_read != 0)
               {
                       total_bytes = total_bytes + (long) bytes_read;
                       if ((flag = mrsubputi (record_des2, attr_des[1],
                                               bulk_attr_structure,
                                               offset)) < 0L)
                       {
                               fprintf (stderr, "Update for bulk attribute at");
                               fprintf (stderr," offset %ld was not successful",
                                               offset);
                               fprintf (stderr," flag='%ld'\n",
                                               flag);
                               break;
                       }
                       else
                               fprintf (stderr,"Total bytes read in %d\n",
                                               total_bytes);
                       /* Set an offset value to insert the next chunk */
                       offset = offset + (long) bytes_read;

                       /* Read the next chunk from stdin */
                       bytes_read = read (fileno(stdin),
                               bulk_attr_structure->data,
                               SIZE_OF_DATA_CHUNK);
                       bulk_attr_structure->size_of_bulk = (long) bytes_read;
               }
               if (!mrsubend (record_des2))
    	   {
    		fprintf (stderr, "mrsubend failed\n");
    		fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
    	   }
       }
          else
                  fprintf (stderr, "Record for %d key was not found\n",
                          key_value);

          mrgetend (ret_des);

          /* cleanup */
          if (!mrfrrec (record_des))
          {
    	fprintf (stderr, "ERROR in freeing record buffers\n");
    	fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
          }
          if (!mrfrrec (record_des2))
          {
    	fprintf (stderr, "ERROR in freeing record buffers\n");
    	fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
          }
          if (!mrclose (tab_des))
          {
    	fprintf (stderr, "Unable to close the table\n");
    	fprintf (stderr, "mroperr='%d' : %s\n", mroperr, mrerrmsg ());
          }
          free (input_data);
          return 0;
       }

In order to update bulk data in the the table `logs`, invoke the program `update_chunk`:

       update_chunk 23454 2 100  < bulkfile1

where `23454` is size of the file `bulkfile1` used to update the existing bulk data associated with `log_key` that has the value `2`. The update will be done starting from the offset of 100 bytes.

In the case when the bulk data contains several fixed size sections (e.g. sequence of images of fixed size), updating bulk data in chunks could be done in simpler and more efficient way. For example, for the bulk attribute containing the sequence of images of fixed size, this feature would be convenient for replacing one of the images from the sequence with another one.

The following is a general outline of the program that updates fixed size portions of the bulk attribute starting from the defined offset:

    Open and allocate space for the resources
            table_desc = mrtopen (database_name, table_name, mode);
            record_desc = mrmkrec (table_desc);
            attr_desc = mrngeta (table_desc, attr_name);
            ...
            bulk_attr_desc = mrngeta (table_desc, attr_name);

    Compare an attribute value to a C integer
            qual = mrqieq (attr_desc, (int) key_value);

    Associate a given qualification with one or more records
            ret_desc = mrgetbegin (qual, record_desc, ADDRNIL)

    Get record qualified with the desired key
            mrtget (ret_desc)

    Initiate a start of an update operation
            mrsubbegin (record_desc);

            LOOP
                    Perform a data segment update for BULK attribute.
                    mrsubputi (record_desc, bulk_attr_desc, bulk_attr_structure, offset);
            END LOOP

    Indicate the end of an update operation.
            mrsubend (record_desc);

    Clean up the resources
            mrgetend (ret_des);
            mrfrrec (record_des);
            mrclose(tab_des);