Chapter 1: Introduction

Empress provides two sets of database manipulation procedures for direct access to database structures using the C/C++ programming language: the mx and mr routines. These interfaces provide faster execution times than any of the query language interfaces, since the parser is not invoked for each call. They also provide much greater control over what is done with the retrieved data, allowing you to perform complex arithmetic functions, generate custom-defined report formats, develop high-level user interfaces, and so on. It should be noted, however, that using the database manipulation procedures represents a greater programming effort than using the Shell, Batch or Standard C Interfaces.

The mx routines are considerably simpler than the mr routines, but should be used only for prototyping and simple programs. The mr routines should always be sed where the application is particularly complex, or where speed is of the utmost importance. The mr routines are documented in the rest of this manual. The mx routines are documented in the Empress Host Language: C/C++ Interface - mx Routines manual.

The examples in this manual are illustrated in C programming language syntax.

From the user's perspective, the Empress RDBMS can be seen as a collection of interfaces (SQL, 4GL, etc.) providing structured access to Empress databases. All these interfaces are in fact specialized programming languages addressing, at various levels of abstraction, the most common problems facing a database user. From an implementation point of view, most components of the system (the SQL processor empsql, the 4GL processor emp4gl, etc.) are written in terms of the mr layer, a set of C functions implementing the full range of Empress capabilities. Therefore, mr is the only software layer offering totally unrestricted access to Empress databases. To put differently, mr is the underlying virtual database machine supporting all the other layers of Empress RDBMS.

It is a unique feature of Empress RDBMS that it is giving users access to part of its lowest level libraries. mr functions are providing the fastest means of accessing Empress databases but, on the other hand, their use is quite complex. So who should use the mr layer? A simple answer would be: experienced C/C++ programmers working on time critical applications. An mr program lets programmers control, at a very fine level, all the details of their database access algorithms (as opposed to SQL scripts where all the details as to how to access data are "frozen" in the SQL processor). But given the complexity of programming at the mr level, we would advise most users to write a higher level prototype of their application first. If the response time of this first cut proves to be unsatisfactory, then they might consider the idea of writing an mx or an mr program.

mr is not the only Empress library of C functions for accessing databases: the mx layer offers part of the mr functionality at a higher abstraction level (but well below empsql or precompilers). It is probably clear by now that the mx layer is implemented in terms of the mr layer, hence it is less efficient. At the same time, there are some operations that cannot be programmed by making use of the mx routines, for example, joining a table to itself. For most applications requiring programmed access to databases, the mx layer is efficient enough. Highly demanding applications however, can take full advantage of the efficiency and flexibility of the mr layer.

The mr and mx interfaces are provided for programmers who wish to access Empress databases with a finer degree of control than is possible from the higher level SQL or precompiler interfaces. There are differences in implementation between the mr and mx interfaces as well, with the mr interface providing considerably more functionality from a programmer's standpoint than the mx interface. The following points summarize the main functional differences between the mr and mx programming interfaces.

1. Multiple instances of a table can be manipulated through the mr layer. Only one instance of a table can be accessed at a given time with the mx layer.

2. With the mr interface, it is possible to lock/unlock a retrieved record that has been made current in the application. The mr interface also provides routines to place and release table level locks.

3. Aggregate function results can be evaluated within programs that use the mr routines (the routine mrgfunc() is used).

4. The mr layer provides routines to open and close the database, making subsequent table accesses faster.

5. The mr layer provides a routine to test whether an operating system directory is also an Empress database.

6. Complex expressions can be built with mr level routines, which can then be used to build qualification descriptors. Thus, it is possible to evaluate built-in functions in Empress, user defined functions and system math functions within a qualification.

7. There are more routines available with the mr interface for building complex qualifications, for example, subquery-type qualifications.

8. The mr routines generally allow programmers to work with descriptors which reference or access tables, records, attributes, expressions and qualifications. The mx routines do not give programmers direct access to these structures.

9. The mr level interface provides programmers with more routines than the mx level interface for manipulating data in internal format rather than external format (i.e., strings).

10. In general, mr routines are faster than mx routines.

11. There are mr routines that enable programmers to store pointers to previously retrieved records and retrieve those records at a later time.

For the UNIX systems, the mr routines support any version of the C programming language and they are callable from the C++ programs.

For Windows NT systems, the mr routines support Microsoft VC++.

Unless otherwise specified, the program examples used to illustrate the mr routines will access a database called repairs. This database contains the tables personnel and loans. The personnel table stores information about employees: the employee's personnel number, name, phone number, and credit limit. The loans table stores information about loans made to these employees: the employee's name, the date the loan was made, and the amount involved. The commands used to create the two tables, and their contents, are shown below:

   CREATE personnel (number INTEGER, name CHAR (25, 1),
        phone CHAR (15, 1), credit_limit dollar (6, 1));

   CREATE loans (name CHAR (25, 1),
        date DATE (1), amount DOLLAR (6, 1));

   *** Table: personnel ***
   
   number     name          phone        credit_limit
   
   10         Kilroy        426-9681     $500.00
   3          Jones         667-2951     $500.00
   5          Mosca         544-2243     $750.00
   17         Wladislaw     723-6073     $600.00
   8          Peterson      978-6060     $250.00
   4          Scarlatti     961-7363     $100.00

They contain the following records:

   *** Table: loans ***
   
   name          date                 amount
   
   Mosca         2 February 1992      $150.00
   Jones         7 February 1992       $33.95
   Kilroy        16 February 1992     $250.00
   Wladislaw     27 February 1992      $55.00
   Jones         3 April 1992          $25.00
   Mosca         4 May 1992           $200.00
   Wladislaw     12 May 1992           $25.00
   Peterson      6 June 1992           $50.00
   Wladislaw     25 June 1992          $75.00
   Jones         12 August 1992       $300.00
   Jones         25 December 1992     $100.00

You are encouraged to create a database containing these two tables, and use them to work through the examples presented in this manual.

However, do not run or test database applications from within your database directory. Work from a separate directory, as this reduces the danger of destroying database files if an application creates new files.

Throughout this manual we assume that you are very familiar with the C programming language, as well as database principles. If you are not, please obtain copies of the references listed below, and read them thoroughly. This manual is intended as a guide and reference for accessing Empress from a C program, and is not intended as a general guide to programming or creating Empress applications.

Database

UNIX

Windows NT

VC++ 5.0 On-Line Help from Microsoft Corporation.