# CHAPTER 3: SQL Precompiler

## 3.1 Introduction

This chapter is the reference for the Empress SQL Precompiler for Fortran of the Empress Relational Database Management System (RDBMS).

An overview of the Empress Query Language, details of its operating conventions, and the conventions of syntax notation used in the Empress manual set, are presented in the SQL: Reference.

The Empress SQL Precompiler allows you to access and process data stored in Empress databases, using SQL commands from within a Fortran program. The following operations on tables in Empress databases are supported:

- Adding rows to a table.

- Updating rows in a table.

- Deleting rows from a table.

- Selecting rows from a table.

- Adding, updating and deleting all or part of a context of selected rows.

- Use of the transaction facilities of Empress to maintain data integrity.

The following sections discuss the conventions for naming and calling databases, tables, and attributes as well as the notation used in presenting the SQL Precompiler command syntax.

## 3.2 Reserved Keywords

The Precompiler recognizes a number of keywords, related to both SQL and host languages. None of the keywords can be used as a program variable name in the SQL declaration section.

The following is a list of keywords recognized by the SQL Precompiler.

  `!=` | `~=` | `<`
  `<=` | `=` | `>`
  `>=` | `again` | `and`
  `asc` | `ascending` | `attr`
  `attribute` | `begin` | `between`
  `by` | `bypass` | `bypass_lock`
  `cancel` | `character` | `close`
  `close_table` | `commit` | `common`
  `complex` | `continue` | `current`
  `cursor` | `database` | `db`
  `declare` | `deferred` | `delete`
  `desc` | `descending` | `dimension`
  `distinct` | `double` | `end`
  `equivalence` | `excl` | `exclusive`
  `exec` | `exit` | `fetch`
  `fetch_again` | `for` | `found`
  `from` | `goto` | `in`
  `incl` | `include` | `inclusive`
  `init` | `insert` | `integer`
  `into` | `is` | `like`
  `logical` | `!match` | `~match`
  `match` | `next` | `not`
  `null` | `of` | `open`
  `open_table` | `or` | `order`
  `precision` | `prior` | `range`
  `read` | `real` | `rollback`
  `section` | `select` | `set`
  `!smatch` | `~smatch` | `smatch`
  `sort` | `sql` | `sqlca`
  `sqlerror` | `sqlwarning` | `start`
  `table` | `to` | `transaction`
  `unique` | `update` | `values`
  `whenever` | `where` | `work`

Although the Precompiler accepts keywords typed in any combination of upper- or lowercase, keywords specific to the host language should be typed according to the conventions adopted by the local compiler.

If a table or attribute has been named with one of the above keywords, this name must be treated as a complex name, rather than as a simple one. The attribute name should be enclosed in quotes preceded by the keyword `ATTR`.

## 3.3 Synonyms

The following is a list of synonyms of Precompiler keywords:

      !=
      !MATCH
      !SMATCH
      ASC
      ATTR
      CANCEL
      DATABASE
      DESC
      DISTINCT
      EXCL
      INCL
      ORDER
      TRANSACTION
      ~=
      ~MATCH
      ~SMATCH
      ASCENDING
      ATTRIBUTE
      ROLLBACK
      DB
      DESCENDING
      UNIQUE
      EXCLUSIVE
      INCLUSIVE
      SORT
      WORK

## 3.4 General Topics for the Precompiler

The following sections are a discussion on topics and methods pertinent to the use of the SQL Precompiler. These include:

- accessing databases and tables
- multiple record operations
- transactions
- using program variables
- the SQL Communications Area (SQLCA)
- error handling
- error messages
- invoking the Precompiler

These discussions apply only to the SQL Precompiler and to the use of Empress SQL commands embedded in Fortran programs. For syntax and command specific notes, see the Command Reference section in this chapter.

## 3.5 Databases and Tables

The SQL Precompiler allows you to perform operations on tables in Empress databases from within your Fortran program. Tables to be accessed are assumed to be in the default database. Unless otherwise specified, operations will take place on the default database.

A table in a database other than the default database can be accessed by preceding the table name in your SQL Precompiler command with a database specification. A database specification is a database name followed by a colon.

A database is made the default by issuing the `DATABASE IS` command. Until this command is issued, no database is considered the default and a database specification must precede each and every table name. A different database can be made the default using the `DATABASE IS` command at any time.

For every executed command, the Precompiler verifies that all of the required tables have been opened and, if so, that they have been opened in the correct mode (read or update). If the required tables are not open, they will be opened in a mode corresponding to the requirements of your SQL command.

Tables can be opened with the `OPEN_TABLE` command (see Command Reference section in this chapter). Opened tables will remain open until you explicitly close them or execute the `EXIT` command.

Tables can be closed with the `CLOSE_TABLE` command (see Command Reference section in this chapter). If you don't specify a list of tables in the `CLOSE_TABLE` command, all opened tables will be closed.

## 3.6 Multiple Record Operations

The SQL Precompiler allows you to store, retrieve or delete data from tables in Empress databases from within your Fortran programs. Supported commands include: `UPDATE`, `DELETE` and `SELECT INTO`. The `UPDATE` and `DELETE` commands will normally act on all records that satisfy the conditions specified in their `WHERE` clause. `SELECT INTO` will only act on the first record that meets the conditions in its `WHERE` clause.

### 3.6.1 The Cursor

The SQL Precompiler will also allow you to selectively manipulate records within a given context. Records can be updated or deleted selectively and the data contained in multiple records can be retrieved for processing by your program.

This is accomplished through the use of a cursor. The cursor allows you to control the rows that are processed by defining a context of rows with a `SELECT` statement and associating it with that cursor. (For more information on the `WHERE` clause, see the Command Reference section in this chapter.) The cursor points to a single record within its context. By "moving" the cursor, rows in the context can be made current and subsequently processed.

### 3.6.2 Declaring a Cursor

Before a cursor can be used, preparations must be made for its use. These preparations include declaring and opening the cursor.

A cursor is declared with the `DECLARE CURSOR` command. Declaring a cursor involves assigning the cursor a name and defining the context associated with that cursor through a `SELECT` statement. The cursor declaration must precede any other cursor related command.

By default, a cursor is declared for read operations only. If you wish to use the cursor for update operations as well, the `FOR UPDATE` or `FOR DEFERRED` option must be specified in the declaration command. A given cursor name may be declared only once within a program, although more than one cursor may be declared. As well, the same context can be defined for more than one cursor name.

Once a cursor has been declared, it must be opened before it can be used. A cursor is opened with the `OPEN CURSOR` command.

Opening a cursor initializes the selection process defined by the `SELECT` statement in the cursor declaration. A given cursor may be declared only once in a program, but it may be opened and closed many times. After opening the cursor, Empress will be ready to make the first row in the selected context the current row. Merely opening the cursor does not make any row the current row.

### 3.6.3 Processing a Selected Row

To process the next available row of values in the defined context from within your program, the desired attribute values must be read into appropriate program variables. This is accomplished with the `FETCH` command. `FETCH` makes the next available row in the context the current row and attempts to read the named attribute values into the corresponding named program variables.

If the next available row in the context is locked (`SQLCODE = 1`), `FETCH` will make it the current row but will not read the appropriate values into the named variables. You can attempt to re-select these values using the `FETCH AGAIN` command. `FETCH AGAIN` operates identically to `FETCH` except that it attempts to read the current row, rather than the next available row. To read all of the available rows, your program must `FETCH` the values in a loop until `SQLCODE = 100`.

### 3.6.4 Updating/Deleting a Selected Row

The `UPDATE` and `DELETE` commands can be used to update or delete the current row in a selected context. In this form of these commands, the `WHERE` clause is replaced with a `WHERE CURRENT OF` clause.

This form of the `UPDATE` and `DELETE` commands act on the current record in a selected context. Consequently, the commands will not be executed if a row has not been made current with the `FETCH` command.

After an `UPDATE` with a `WHERE CURRENT OF` clause, the cursor remains pointed to the updated row. The updated row remains the current row. After a `DELETE` with a `WHERE CURRENT OF` clause, the cursor will not be pointing to any row. No row will be considered current until after the next `FETCH` command. After the next `FETCH` command the next available row will be made the current row.

### 3.6.5 Closing a Cursor

After a selected context of rows has been processed with the aid of a cursor, the cursor should be closed. A cursor can be closed with the `CLOSE CURSOR` command. This command will close the named cursor.

Although the cursor has been closed, its declaration is not discarded and can be used to subsequently re-open the named cursor and initialize its associated context. To initialize the selected context, the cursor should be opened using the `OPEN CURSOR` command as discussed above.

### 3.6.6 Multiple Cursors

It is possible to declare more than one cursor within a single program. Each cursor can be defined to select different contexts, or the same contexts can be selected but associated with different cursor names.

It is also possible to have more than one cursor open at the same time. In this case however, only the last opened cursor can be referenced by a `FETCH` command or an `UPDATE` or `DELETE` command with a `WHERE CURRENT OF` clause. References to previously opened cursors will be met with an error message. To reference a previously opened cursor, all cursors opened after it must first be closed with the `CLOSE CURSOR` command.

## 3.7 Transactions

A transaction is a series of commands, starting with a `START TRANSACTION` command and ending with `COMMIT TRANSACTION` or `ROLLBACK TRANSACTION`, during which changes to the database are not permanently recorded until the transaction is committed. The effects of changes made while a transaction is in progress may be seen just like those of normal commands, but the option to reverse them exists, if so desired.

In the event of a disk, operating system or network crash, transactions will be resolved (either committed or rolled back) by the warm restart facility. This preserves data integrity on all of the databases involved in the transaction. The warm restart facility is described in the Empress: Database Administrator's Guide. A general introduction to transactions can be found in the Empress SQL: User's Guide.

## 3.8 Variables

Program variables can be used in SQL Precompiler commands. Variable names in executable commands must be preceded by a colon (:). These variables can be used to store the values obtained by a `FETCH` or `SELECT INTO` statement. Variables can also be used to store the name of a database, table, attribute or constant.

Efforts have been made to make the Precompiler work with variables belonging to practically all types implemented by its host languages. It is hoped that minor variations between commercially available compilers will not affect the users of the Precompiler.

For character variables, Empress data is manipulated in external format (i.e., as strings). Character variables allow you to manipulate all of the Empress data types.

In a Fortran program, character variables are of type character and must be declared as:

       character *length variable_name

For numeric variables, Empress data is manipulated in internal format (i.e., as it is stored in memory). This allows the direct manipulation of `SHORTINTEGER`, `INTEGER`, `LONGINTEGER`, `FLOAT` and `LONGFLOAT` data from within your program. In addition, it is possible to manipulate a numeric variable as a character variable in external format.

The usual correspondence (subject to machine architecture) between Empress data types and Fortran variable types is shown in the following table:

Table 3-1: Data Type Correspondence

Empress | Fortran
`SHORTINTEGER` | `integer*2`
`INTEGER` | `integer*4`
`LONGINTEGER` | `integer*4`
`FLOAT` | `real`
`LONGFLOAT` | `double precision`
`CHAR, TEXT` | `char`

### 3.8.1 Declaring Fortran Variables

The whole set of Fortran 77 types is supported by the Precompiler: `INTEGER`, `DOUBLE PRECISION`, `COMPLEX`, `LOGICAL`, and `CHARACTER`. All variables should be declared prior to their use; the Precompiler does not recognize any convention as to the type of undeclared variables. `DIMENSION` statements can be used within an SQL declaration section, but they must always refer to previously declared variables.

       DIMENSION A(10)

The above statement by itself will not be accepted and taken to mean the declaration of a real array named A, but the sequence:

       REAL A

       ...

       DIMENSION A(10)

will have the same effect. In such a case, it is always easier to use:

       REAL A(10)

`COMMON` blocks and `EQUIVALENCE` statements can be included in an SQL declaration section. They will be ignored by the Precompiler for all practical purposes, but some users might find this facility convenient.

Any variable declared within an SQL declaration section (and only such a variable) can be used with any SQL statement. The only restriction that applies is that you cannot use indexed variables as table names. Consider the following sequence:

       EXEC SQL BEGIN DECLARE SECTION;

       ...

          REAL A
           CHARACTER NAME*20, STRING(10:20)*50, NAMES(5)*20

       ...

          EXEC SQL END DECLARE SECTION;

Then a statement such as:

       EXEC SQL INSERT INTO :NAME (real, string) VALUES :A,
          :STRING(17);

will be syntactically correct, whereas:

       EXEC SQL INSERT INTO :NAME(4) (real), string) VALUES :A,
          :STRING(17);

will not be accepted by the Precompiler.

### 3.8.2 Shell Variables

Environment variables can be used in all statements accepting constants or variables as parameters. The name of an environment variable should be enclosed within double or single quotes and preceded by "$", for example:

       EXEC SQL SELECT amount FROM "$table"
          INTO :x WHERE id = "$name";

Here `table` and `name` are environment variables which should hold, at runtime, valid values for a table name and `id` attribute, respectively. A "$" sign can be quoted at the beginning of a literal string by preceding it by another "$" sign. For example, `"$$alpha"` means the string `"$alpha"`, with no relation whatsoever to any environment variable.

### 3.8.3 Control Variables

It is possible to test if an attribute value, obtained through a `FETCH` or `SELECT INTO` command and stored into a variable, is null. To do this, a control variable must be associated with each attribute to be tested.

Control variables take the following form and must be declared with a length of 2 bytes (`integer*2` for Fortran):

       :variable:control_variable

A single control variable may be used to evaluate multiple variables in separate statements. Within a single statement, however, a unique control variable must be appended to each program variable. If not, the control variable reflects the status of the last manipulated program variable.

### 3.8.4 Fetching and Control Variables

When retrieving information with a `FETCH` or `SELECT INTO` command, control variables are evaluated as follows:

`ctrl = 0` | Indicates that a value is not null and that it was successfully placed in the destination variable.
`ctrl < 0` | Indicates a null value.
`ctrl > 0` | Indicates that the destination variable is not big enough to contain the value and consequently, that value was truncated. In this case, the control variable contains the real length of the value and the `SQLWARN0` and `SQLWARN1` indicators contain the value "W".

### 3.8.5 Inserting and Control Variables

Control variables can be used to insert a null (or to update to a null) at runtime. During an `INSERT`, control variables are evaluated as follows:

`ctrl < 0` | A null is inserted into the row.
`ctrl = 0` | The value contained in the variable is inserted into the row.

### 3.8.6 WHERE Clause and Control Variables

Control variables can be used to alter the action caused by a `WHERE` clause. The control variable will be evaluated at runtime as follows:

`ctrl < 0` | `ATTR` will be tested against null.
`ctrl >= 0` | `ATTR` will be tested against the value contained in the variable.

Control variables can only be used in a `WHERE` clause that tests for equality (=) or inequality (!=).

## 3.9 SQL Communication Area (SQLCA)

The SQLCA (SQL Communication Area) is a data structure that is common to both the Precompiler and the user. It provides a method by which you can inquire about the exit status of each statement and, if errors have occurred, to receive more detailed information about the error.

### 3.9.1 Using the SQLCA

All programs must include the following statement before any other SQL statement in a program unit (main program or sub-routine):

       EXEC SQL INCLUDE SQLCA;

The SQLCA is updated after each Precompiler operation. Its primary purpose is to return the `SQLCODE` indicator. `SQLCODE` values have the following meaning:

Table 3-2: SQLCODE Values

Values | Meaning
`SQLCODE = 0` | No errors have occurred.
`SQLCODE < 0` | An error has occurred.
`SQLCODE = 100` | No more rows were found.
`SQLCODE > 0` | The operation was successful but an exception condition was detected.

### 3.9.2 SQLCA Structure

The SQLCA data structure is arranged as follows:

Table 3-3: SQLCA Data Structure

`SQLCAID` | Contains the marker string "SQLCA" and is updated the first time that the structure is used.
`SQLABC` | Contains the length of the SQLCA structure and is updated the first time that the structure is used.
`SQLTXL` | Contains the length of the error message in runtime.
`SQLERRMC` | Contains the error message.
`SQLWARN` | An area that contains 8 indicators, each of which may contain the value " " or "W". "W" indicates that an exceptional condition was detected. " " indicates that the statement was executed successfully.
`SQLWARN0` | If the value is "W", one of the other indicators was set during the last operation.
`SQLWARN1` | If the value is "W", a value in the last `FETCH` or `SELECT INTO` statement was truncated.

The remainder of the variables are reserved for SQL compatibility or for future use.

To access the various error variables, use the following in your program:

Table 3-4: Error Variables

    SQLCODE
    SQLWARN0
    SQLWARN1
    SQLCAID
    SQLABC
    SQLERRML
    SQLERRMC

The syntax for declaring the SQLCA in Fortran is:

       character*1 sqlcaid(8)
       character*1 sqlca(136)
       integer*4 sqlabc
       integer*4 sqlcod
       integer*4 SQLRET
       integer*2 sqltxl
       character*1 sqltxt (70)
       character*1 sqlerp(8)
       integer*4 sqlerr(6)
       character sqlwrn(0:7)
       character*1 sqlext(8)
       equivalence (sqlcaid, sqlca(1))
       equivalence (sqlabc, sqlca(9))
       equivalence (sqlcod, sqlca(13))
       equivalence (SQLRET, sqlca(13))
       equivalence (sqltxl, sqlca(17))
       equivalence (sqltxt, sqlca(19))
       equivalence (sqlerp, sqlca(97))
       equivalence (sqlwrn, sqlca(121))
       equivalence (sqlext, sqlca(129))

## 3.10 Error Handling

The course of action to be taken when an error or exception is detected can be defined with a `WHENEVER` command. (See the command `WHENEVER` in the "Command Reference" section).

The `WHENEVER` command lets you define the action that will be taken when:

- an error is detected.
- warning is detected.
- a row could not be returned or found.

The `WHENEVER` command uses the value of `SQLCODE`, to determine the course of action to follow:

`SQLCODE < 0` | The action specified for `SQLERROR` will be executed.
`SQLCODE = 100` | The action specified for `NOT FOUND` will be executed.
`SQLWARN0 ='W'` or `(SQLCODE >0` and `SQLCODE != 100`) | The action specified for `SQLWARNING` will be executed.

## 3.11 Restrictions

The following is a short list of rules to be followed when programming for the Empress SQL Precompiler:

- References to databases and tables within a given program must be consistent. For example, if you open a table using a database specification in the table name then all references to that table in the program should include a database specification.

- When your program starts, no database is considered the default. You must issue a `DATABASE IS` command to establish a default database.

- You should not begin a transaction from within a retrieval loop.

- Variable names across declaration sections should be unique within a given compilation unit (source file).

- Arrays of type `CHAR` may not be greater than a single dimension.

- The statement "`EXEC SQL INCLUDE SQLCA`" must precede any other SQL statement.

- The statement "`EXEC SQL INIT`" must be the first executable SQL statement in any given program. Failure to do so will result in runtime error messages and/or unpredictable results.

## 3.12 Using the Precompiler

The Empress SQL Precompiler allows you to issue SQL commands from within your Fortran program. This is accomplished through calls to the Empress mx routines. On precompilation, SQL commands embedded in your program are replaced with calls to supporting functions. A second C source code file, with the supporting functions functions calling mx routines is generated. Even in the case of a Fortran program, the supporting file is generated in C code.

The file containing your Fortran program with embedded SQL commands can be precompiled using the command:

       empsql infile.f [outfile.f [support_file.c]]

where:

`infile.f` | is the file containing your program with embedded SQL statements.
`outfile.f` | is your source code file with the SQL commands replaced by function calls. If outfile is not specified the default name `mpout.f` is used.
`support_file.c` | is a C source code file containing the supporting functions which calls the Empress mx routines. If `support_file` is not specified, the default `mpsup.c` is used.

The following sequence of commands can be used to precompile, compile, and link a Fortran program containing embedded SQL statements (`out` is the name of the executable file):

       empesql infile.f outfile.f support.c
       empecc -c support.c
       empef77 -o out outfile.f support.o

### 3.12.1 Separate Compilation

Currently you have to invoke the Precompiler separately for each input file. Any set of output files produced by the Precompiler can be compiled and linked to form a single executable program. However, certain restrictions apply to Fortran programs. In this case, an alphanumeric string has to be specified as a parameter of the `include sqlca` statement. For example:

       exec sql include sqlca s1;

The string should be unique across all files making up the same program and it is used as a prefix for all names of C procedures generated in the `support` file (default name: `mpsup.c`). This string is optional and does not need to be specified in the case of a single file program.

## 3.13 Example Program

The following Fortran program demonstrates the form of a program using embedded Empress SQL statements. It illustrates some of the commands and concepts involved. The program accesses the test database `repairs` and should be invoked from the directory containing this database.

       EXEC SQL INCLUDE SQLCA;
       EXEC SQL WHENEVER SQLERROR GOTO 10;
               integer comp<R>EXEC SQL BEGIN DECLARE SECTION ;
               character *25 name,date *20,amount *8
               character *25 name1,date1 *20,amnt1 *8
               character *25 name2,date2 *20,amnt2 *8
       EXEC SQL END DECLARE SECTION;
       EXEC SQL INIT;
       EXEC SQL DATABASE IS "repairs";
       EXEC SQL SELECT name,date,amount FROM loans INTO :name,:date,:amount
          WHERE name = "Kilroy";
               date2=date
               amnt2=amount
               name2=name
               print *,'select 1 ok'
       EXEC SQL UPDATE loans SET name,date,amount VALUES :name2,:date2,:amnt2
          WHERE name = "Kilroy";
               print *,'update ok'
       EXEC SQL SELECT name,date,amount FROM loans INTO :name1,:date1,:amnt1
          WHERE name = "Kilroy";
               print *,'select 2 ok'
               if(comp(name,name1,25).EQ.0.AND.
            x  comp(date,date1,20).EQ.0.AND.comp(amount,amnt1,8).EQ.0) then
               print *,'test ok'
               else
               print *,'bad results'
               endif
       EXEC SQL EXIT ;
               stop
       10      print *,'SQLRET number ',SQLRET
               stop
               end

               integer function comp(st1,st2,length)
               character*(*) st1,st2
               integer length
               comp=0
               do 20 i=1,length
                      if(st1(i:i).NE.st2(i:i)) comp=comp+1
       20      continue
               return
               end

## 3.14 Error Messages

Precompiler related error messages may be generated both at compile-time and at runtime.

### 3.14.1 Compile-time Error Messages

The following is a list and brief descriptions of the compile-time error messages:

1. `Syntax error in declaration section`

   A syntax error has been made in the section of your Precompiler program. This may be a language specific error or a Precompiler error.

2. `Invalid size for character variable`

   A character variable (i.e., a character array) has been declared with an index greater than 14 digits.

3. `Variable name too long`

   A variable has been declared the name of which exceeds the maximum of 128 characters.

4. `Multiple declaration of character variable`

   A character variable has been declared with a previously declared name.

5. `Missing character variable length`

   The index value has been left out of a character variable (i.e., array) declaration.

6. `Missing EXEC SQL BEGIN DECLARE section`

   An `EXEC SQL END DECLARE SECTION` has been encountered before an `EXEC SQL BEGIN DECLARE SECTION`.

7. `Missing EXEC SQL INIT`

   `EXEC SQL INIT` is not the first executable Precompiler statement in your program.

8. `Warning: missing EXEC SQL EXIT`

   Your program terminates without an `EXEC SQL EXIT` statement.

9. `Errors`

   An error has occurred during precompilation.

10. `Command line too long`

    An SQL command line has exceeded the maximum length of 2048 characters.

11. `Syntax error`

    A syntax error has occurred in an SQL command line.

12. `Missing EXEC SQL END DECLARE section`

    No `EXEC SQL END DECLARE SECTION` was found in your program to match an `EXEC SQL BEGIN DECLARE SECTION`.

13. `Multiple cursor declaration`

    You have declared a cursor with a name that has been previously declared.

14. `Cursor was not declared`

    You have attempted to open a cursor that was not declared.

15. `Numbers of attributes and variables do not match`

    The number of attributes do not match the number of values specified in a `FETCH`, `INSERT` or `SELECT INTO` statement.

16. `Cannot build mx qualification`

    The context specified in a `WHERE` clause could not be constructed.

17. `Undeclared variables:`

    You have attempted to use a variable that has not been declared for Precompiler use.

18. `Cursor name limited to 128 characters`

    You have declared a cursor with a name exceeding the 128 character maximum.

19. Variables maximum

    You have exceeded the maximum number of variables, 256, that can be declared for Precompiler use.

20. `Update not allowed in multi-tables join`

    You have attempted to perform an update operation on a complex join.

21. `Missing or out of place EXEC SQL INCLUDE SQLCA`

    You have not included `EXEC SQL INCLUDE SQLCA` as the first executable Precompiler statement in your program.

22. Data type not supported by `FETCH INTO` statement

    You have attempted to use a variable of a type not supported by the SQL Precompiler.

### 3.14.2 Runtime Error Messages

The following is a list and brief descriptions of the runtime error messages:

1. `open failed`

   Empress is unable to open the specified table.

2. `invalid WHERE clause (mxgetbegin() error)`

   You have specified an invalid `WHERE` clause.

3. `record cannot be deleted`

   Empress is unable to delete the record specified.

4. `conversion error during insert (mxputvs or mxputvi)`

   You attempted to insert a value of the wrong data type.

5. `cursor non active`

   You have attempted to use a cursor that has not been opened.

6. cannot add record

   Empress cannot add the specified record to the table.

7. `cannot begin transaction`

   Empress cannot begin the specified transaction. This is likely due to the presence of a currently active transaction.

8. `cannot update record`

   Empress cannot update the specified record.

9. `conversion error during update (mxputvs or mxputvi)`

   You attempted to update to a value of the wrong data type.

## 3.15 Command Reference

This section presents the syntax of command statements for the Empress SQL Precompiler for Fortran. Each SQL command line must begin with the keywords `EXEC SQL`. Each SQL command line must end with a semicolon (;). An SQL query can extend over more than one line.

### 3.15.1 BEGIN DECLARE SECTION

Indicates the start of the declaration section for variables that will be used in SQL commands in your program.

Syntax | `EXEC SQL BEGIN DECLARE SECTION;`
SQLCODE | No meaning.
Notes | The declaration section may be located anywhere allowed for variable declarations by the host language syntax. More than one declaration section may be used in a given program file. If more than one declaration section is used, the variable names across declaration sections should be unique. It is recommended that the declaration section be global to the file containing the Empress SQL Precompiler statements.
Example |
    EXEC SQL BEGIN DECLARE SECTION;
       INT i;
       SHORT j;
        FLOAT f;
        CHAR str [10];
    EXEC SQL END DECLARESECTION;

### 3.15.2 CANCEL TRANSACTION

Cancels the currently executing transaction.

Syntax | `EXEC SQL CANCEL TRANSACTION;`
SQLCODE | No meaning.
Notes | `CANCEL TRANSACTION` rolls back all transactions on all databases.

### 3.15.3 CLOSE CURSOR

`CLOSE CURSOR` closes the named cursor after it has been opened for row manipulations.

Syntax | `EXEC SQL CLOSE cursor_name;`
`cursor_name` | is the name assigned to the cursor when it was opened.
SQLCODE |
`0` | OK.
`-4` | Non-active cursor.
Notes | A cursor must be closed before it can be re-opened.

### 3.15.4 CLOSE_TABLE

Closes the named table.

Syntax | `EXEC SQL CLOSE_TABLE [table {, table}];`
SQLCODE | No meaning.
Notes | If no table name is specified in the `CLOSE_TABLE` command, all open tables will be closed.
Example | The following command will close all open tables: `EXEC SQL CLOSE_TABLE;` The following command will close only the named tables, `loans` and `personnel`: `EXEC SQL CLOSE_TABLE loans, personnel;`

### 3.15.5 COMMIT TRANSACTION

`COMMIT TRANSACTION` makes permanent the changes made to the database during the current transaction.

Syntax | `EXEC SQL COMMIT TRANSACTION;`
SQLCODE | No meaning.
Notes | `COMMIT TRANSACTION` commits all transactions over all databases.

### 3.15.6 DATABASE IS

Changes the current database.

Syntax | `EXEC SQL DATABASE IS database;`
SQLCODE | No meaning.
Notes | The database name may include a full file specification. In this case, the database name must be enclosed in quotes.
Example | The command: `EXEC SQL DATABASE IS "repairs";` will make the `repairs` database, located in the `repairs` directory, the default database. The command: `EXEC SQL DATABASE IS :base;` will make the database whose name is stored in the program variable "`:base`" the default database.

### 3.15.7 DECLARE CURSOR

Declares a cursor to be opened. This involves naming the cursor and associating a context with the name through a `WHERE` clause.

Syntax | `EXEC SQL DECLARE cursor_name CURSOR`
    FOR SELECT[
    |DISTINCT
    |UNIQUE
    |] [attr {, attr}]
    |
    FROM table
    [where_clause]
    [sort_clause]
| {, table} |
    FOR UPDATE [OF
    |*
    |attr {, attr}
    |]];
    |
where `sort_clause` is:
    |SORT
    |ORDER
    | BY
    |
    |ASCENDING
    |DESCENDING
    | {, attr
    |ASCENDING
    |DESCENDING
    |}
    |
SQLCODE | No meaning.
Notes | In order to open and use a cursor it must first be declared. After the cursor is declared it must be opened before it can be used. A cursor may be declared only once within a program. If you want to modify as well as read the selected rows, the cursor must be declared `FOR UPDATE` or `FOR DEFERRED`. Access to selected rows can be ordered using the `SORT` option.
Example | The command:
    EXEC SQL DECLARE c1 CURSOR FOR
        SELECT FROM personnel, loans
       WHERE personnel.name=`Jones'
          AND personnel.name = loans.name;
declares a cursor called `c1` that acts on selected records from the `personnel` and `loans` table. The criterion for record selection is defined in the `WHERE` clause. This context will consist of personnel records where the `name` attribute is equal to `Jones` and `personnel` and `loans` records where the `name` attributes are equivalent.

### 3.15.8 DELETE

Deletes one or more rows of a table or view.

Syntax |
`EXEC SQL DELETE FROM table` |
     |where_clause
    |WHERE CURRENT OF cursor_name
    |;
    |
SQLCODE |
`0` | OK.
`1` | One or more rows are locked.
`-1` | A wrong `WHERE` clause was built (`mxgetbegin()` error).
`-2` | Could not delete a row (`mxdel()` error).
`-4` | Non-active cursor.
Notes | All of the rows that satisfy the `WHERE` clause will be deleted unless a cursor is specified in the `WHERE` clause. If a cursor is specified, only the current row will be deleted. If no row is current, no record will be deleted and the appropriate `SQLCODE` returned.
Example | The command: `EXEC SQL DELETE FROM loans WHERE amount < 50;` will delete from the `loans` table all records in which the attribute `amount` has a value less than 50. The command: `EXEC SQL DELETE FROM loans WHERE CURRENT OF c;` will delete the current record from the context associated with the cursor named `c`. If no record is current, none will be deleted.

### 3.15.9 END DECLARATION SECTION

Indicates the end of the declaration section for variables that will be used in SQL commands in your program.

Syntax | `EXEC SQL END DECLARATION SECTION;`
SQLCODE | No meaning.
Notes | Every `BEGIN DECLARATION SECTION` must have a matching `END DECLARATION SECTION`.

### 3.15.10 EXIT

Performs cleanup operations, but does not terminate the program.

Syntax | `EXEC SQL EXIT;`
SQLCODE | No meaning.
Notes | You should always execute `EXIT` before leaving your program.

### 3.15.11 FETCH

In multiple row manipulations, `FETCH` places the cursor on the next available row in the defined context, makes it the current row, retrieves the values for the named attributes and stores the values in the specified variables.

Syntax |
    EXEC SQL FETCH cursor_name attr {, attr}
       INTO variable {, variable};
SQLCODE |
`0` | OK.
`1` | Locked row.
`-4` | Non-active cursor.
`100` | No more rows.
SQLWARN1 |
`' '` | Without truncation.
`'W'` | Truncation
Notes | If the next record in the context is not accessible (i.e., it is locked), it will be made the current record but the values of its attributes will not be read into the program variables. You can attempt to re-read these variables with the `FETCH_AGAIN` command.
Examples | The command:
    EXEC SQL FETCH c1
       name, credit_limit
       INTO :name, :crelim;
will make the next available record in the context associated with the cursor named `c1` the current record, retrieve the values for the attributes `name` and `credit_limit` and store them in the program variables `:name` and `:crelim`.

### 3.15.12 FETCH_AGAIN

If a `FETCH` is unsuccessful, `FETCH_AGAIN` will attempt to re-read the current record as indicated by the cursor rather than the next available record.

Syntax |
    EXEC SQL FETCH_AGAIN cursor_name attr {, attr}
       INTO variable {, variable};
SQLCODE |
`0` | OK.
`1` | Locked row.
`-4` | Non-active cursor.
`100` | No more rows.
SQLWARN1 |
`' '` | Without truncation.
`'W'` | Truncation
Notes | `FETCH_AGAIN` does not affect the status of the current record. After a `FETCH_AGAIN` the current record remains the current record.
Examples | The command:
    EXEC SQL FETCH_AGAIN c1
       name, credit_limit
       INTO :name, :crelim;
will attempt to retrieve the values for the attributes `name` and `credit_limit` and store them into the program variables `:name` and `:crelim` from the current record in the context associated with the cursor named `c1`.

### 3.15.13 INCLUDE SQLCA

SQLCA is the SQL Communications Area. It is used as a common area for the compiler and the user. You should have the `INCLUDE SQLCA` command as the first Precompiler statement in the program.

Syntax |
    EXEC SQL INCLUDE SQLCA [prefix]
SQLCODE | No meaning.
Notes | You should have an `INCLUDE SQLCA` statement as the first Precompiler statement of your program.

### 3.15.14 INIT

Executes initialization procedures for Empress.

Syntax |
    EXEC SQL INIT;
SQLCODE | No meaning.
Notes | `INIT` must be the first executable Empress Precompiler command that you use in a program, but this rule is not enforced by the Precompiler.
Examples |

### 3.15.15 INSERT

Inserts a new row of attribute values into a table.

Syntax |
    EXEC SQL INSERT INTO table
       (attr {, attr}) VALUES (value {, value});
where `value` is:
    |program variable
    |constant
    |
    |
SQLCODE |
`0` | OK.
`-3` | Conversion error (`mxputvs()` or `mxputi()` error).
`-5` | Impossible to add a row (`mxadd()` error).
Notes | If an attribute in the table is not named in the list of attributes in the `INSERT` command, a NULL will be inserted for it. You can use a control variable to insert a NULL at runtime. If the value is `:variable:control_variable`, at runtime `control_variable` is tested and if a null is inserted, else the value contained in the variable is inserted.
Examples | The command:
    EXEC SQL INSERT INTO personnel
       name, number, credit_limit
       VALUES ("Mosca", 10, 1500);
will insert a new row into the `personnel` table setting the value of the attribute `name` to `Mosca`, the attribute `number` to `10` and the attribute `credit_limit` to `1500`.

### 3.15.16 OPEN CURSOR

Opens the named cursor to perform multiple row manipulations.

Syntax |
    EXEC SQL OPEN cursor_name;
SQLCODE |
`0` | OK.
`-1` | A wrong `WHERE` clause was built (`mxgetbegin()` error).
Notes | Opening a cursor causes the `SELECT` statement defined in its declaration to be initialized. A cursor can be opened many times within a single program. However, a cursor must be closed before it can be re-opened.

### 3.15.17 OPEN_TABLE

Explicitly opens a table for operations.

Syntax |
    EXEC SQL OPEN_TABLE table For
    |UPDATE
    |READ
     |DEFERRED
    |, {, table FOR
    |
    |
    UPDATE
    |READ
    |DEFERRED
    |};
    |
    |
SQLCODE |
`0` | OK.
`<0` | Open error. The command is aborted and `SQLCODE` contains the value `-(100 + mxoperr)`. (See Empress Host Language: C Interface mx Routines manual)
Notes | For every executed command, the Precompiler verifies that all of the required tables have been opened and, if so, that they have been opened in the correct mode (read or update). If the tables are not open, they will be opened in a mode corresponding to the requirements of your SQL command.
Example | The command:
    EXEC SQL OPEN_TABLE
       loans FOR READ,
       personnel FOR UPDATE;
will open the `loans` table for read operations and the `personnel` table for update operations.

### 3.15.18 SELECT INTO

Retrieves one row of data from a table or view and stores it in the program variables specified.

Syntax |
    EXEC SQL SELECT <attr {, attr}FROM table {, table}
       INTO variable{, variable}
       [where_clause];
SQLCODE |
`0` | OK.
`1` | Locked row.
`-1` | `mxgetbegin()` error.
`100` | No row selected
SQLWARN1 |
`' '` | Without truncation.
`'W'` | Truncation.
Notes | `SELECT` retrieves one row of data from the specified table or view. If a `WHERE` clause is used, the first row that satisfies the `WHERE` clause is selected. If you want to access all the rows that satisfy the `WHERE` clause, you must use a cursor. (See "OPEN CURSOR command".) On retrieval, the value of the first selected attribute will be stored as the contents of the first named program variable. The value of the second selected attribute will be stored as the contents of the second named program variable and so on. In a `SELECT` which includes a join, the attribute which forms the join cannot be specified using a program variable on the right-hand side of the join.
Example | The command:
    EXEC SQL SELECT name, date FROM personnel
       INTO :var1, :var2 WHERE name='Jones';
will retrieve the values of the attributes `name` and `date` from the `personnel` table. The values will be taken from the first record that meets the conditions of the `WHERE` clause (i.e., that has the value `Jones` in the attribute `name`). They will then be stored in the program variables :`var1` and `:var2`, respectively.

### 3.15.19 START TRANSACTION

Starts a transaction on the following command.

Syntax |
    EXEC SQL START
    |TRANSACTION
    |WOKK
    |;
    |
SQLCODE |
`0` | OK.
`-6` | Impossible to start a transaction.
Notes | To avoid unpredictable results, do not start a transaction from within a retrieval loop (e.g., a `FETCH` loop).

### 3.15.20 UPDATE

Updates one or more records in the table specified with the values provided.

Syntax | `EXEC SQL UPDATE table SET attr = value {, attr = value}`
    |where_clause
    |WHERE CURRENT OF cursor_name
    |;
    |
SQLCODE |
`0` | OK.
`1` | One or more rows are locked.
`-1` | A wrong `WHERE` clause was built (`mxgetbegin()` error).
`-4` | Non-active cursor
`-7` | Impossible to modify a row (`mxput()` error).Command aborted if it contains a `WHERE` clause.
`-8` | Conversion error (`mxputvs()` or `mxputi()` error).Command aborted if it contains a `WHERE` clause.
Notes | All of the rows that satisfy the `WHERE` clause will be modified unless a cursor is specified in the `WHERE` clause. If a cursor is used, only the current row will be modified. A value to be entered can be a constant, a user variable, or NULL for deleting the contents of an attribute. A value and an attribute must be of the same type, or the Precompiler will return an error code. You can use a control variable to insert a NULL at runtime. If the value is `:variable : ctrl`, at runtime `ctrl` is tested. If it is greater than 0, a null or the value contained in the variable is inserted. (See the section on "Control Variables" in this chapter for more information.)
1. Examples | The command:
       EXEC SQL UPDATE personnel SET credit_limit=100,
       date='19921119'<R> WHERE name='Jones';
2. will update the attributes `credit_limit` and `date` with the supplied values, for all of the records in the `personnel` table where the attribute `name` contains the value `Jones`. The command:
       EXEC SQL UPDATE personnel
          SET credit_limit=100, date='19921119'
          WHERE CURRENT OF c;
will update only the current record in the context associated with the cursor named `c`.

### 3.15.21 WHERE

A `WHERE` clause is used to identify a context of records which satisfy the conditions in the `WHERE` clause. A `WHERE` clause is always used as an option with another SQL command.

A `WHERE` clause consists of boolean expressions; that is, expressions that evaluate to true or false. A complex `WHERE` clause may be built up using boolean expressions that are joined using `AND` and `OR`. Precedence in evaluating the parts of a complex `WHERE` clause is controlled by parentheses.

Syntax |
`WHERE` |
    |boolean_expr
    |(boolean_expr)
    |boolean_expr OR boolean_expr
    |boolean_expr AND boolean_expr
    |NOT boolean_expr
    |;
    |
    |
    |
    |
where `boolean_expr` is one of :
    attr
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    | =
    | !=
    | >
    | >=
    | <
    | <=
    | LIKE
    | MATCH
    | !MATCH
    | SMATCH
    | !SMATCH
    ||attr
    ||program_variable
    ||constant
    |
    |
    |
    |
    |
    |
    |
    |
    | =
    | !=
    | [IS]
    | [IS] NOT
    |NULL
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
    |
or
    attr BETWEEN
    |attr
    |program_variable
    |constant
    |[
    |
    |
    |INCLUSIVE
    |EXCLUSIVE
    |] AND
    |
    |
    |attr
    |program_variable
    |constant
    | [
    |
    |
    |INCLUSIVE
    |EXCLUSIVE
    |]
    |
SQLCODE | No meaning.
Notes | `MATCH` is a dual-case pattern match, while `SMATCH` is single-case. The "!" preceding a `MATCH` keyword selects all records which do not match. `LIKE` performs a dual-case pattern match. Note that `NOT` is not equivalent to "!." "! applies to an operator, but `NOT` applies to an expression. When `NOT` is used in front of an operator its effect is as if it were in front of the expression. For more information on the `WHERE` clause and its components, see the chapter on WHERE clauses in the Empress SQL: Reference manual.

### 3.15.22 WHENEVER

`WHENEVER` allows you to define the course of action that will be taken when an error is encountered during the execution of your program.

Syntax |
    EXEC SQL WHENEVER
    |SQLERROR
    |SQLWARNING
    |NOT FOUND
    ||CONTINUE
    ||GOTO Statement_label
    |
    |;
    |
SQLCODE | No meaning.
Notes | The action specified for `SQLERROR` will be used every time an error condition is detected. The action specified for `SQLWARNING` will be used whenever some exception condition is detected. The action specified for `NOT FOUND` will be used whenever a row could not be returned or found. `CONTINUE` and `GOTO` are the actions to be taken in the case of an error condition, exception condition or row-not-found condition. You can have several `WHENEVER` clauses in a program to change the action to be taken. The `WHENEVER` statement will apply to all errors encountered from the statement to the next `WHENEVER` statement with the same operator (`SQLERROR`, `SQLWARNING` or `NOT FOUND`) or to the end of the procedure.