# CHAPTER 4: Command Reference

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

- BEGIN DECLARE SECTION
- CALL
- CANCEL TRANSACTION
- CLOSE CURSOR
- CLOSE_TABLE
- COMMIT TRANSACTION
- DATABASE IS
- DECLARE CURSOR
- DELETE
- END DECLARE SECTION
- EXIT
- FETCH
- FETCH_AGAIN
- INCLUDE SQLCA
- INIT
- INSERT
- OPEN CURSOR
- OPEN_TABLE
- SELECT INTO
- START TRANSACTION
- UPDATE
- WHERE
- WHENEVER

## 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. Every `BEGIN DECLARE SECTION` must have a matching `END DECLARE SECTION`.
Example |
    EXEC SQL BEGIN DECLARE SECTION;
       int i;
       short j;
       float f;
       char str [10];
    EXEC SQL END DECLARE SECTION;

## CALL

Calls a procedure or evaluate an expression.

Syntax |
`EXEC SQL CALL` | `|expr |procedure_name [([expr {, expr}])]` | `|; |`
    EXEC SQL :var = [CALL] expr;
SQLCODE |
`0` | OK.
`-11` | Failed.
Notes | Aggregate functions and operators can not be called using this statement.

## 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. If your program is within a retrieval loop, the current record will no longer be valid. However, the next `FETCH` statement will return the proper next record.

## CLOSE CURSOR

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

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

## CLOSE_TABLE

Closes the named table.

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

## COMMIT TRANSACTION

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

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

## 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. `DATABASE IS` cannot be used in PSM routines and triggers.

Example 1

The command:

       EXEC SQL DATABASE IS "repairs";

will make the `repairs` database, located in the `repairs` directory, the default database.

Example 2

The command:

       EXEC SQL DATABASE IS :base;

will make the database whose name is stored in the program variable "`:base`" the default database.

## 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 |] [attr {, attr}]
                    |UNIQUE   |
       FROM table {, table}
       [where_clause]
       [sort_clause]
|  |  | `[FOR UPDATE [OF |* |attr {, attr}` | `|]]; |`
| where:
`sort_clause` |
`|SORT |ORDER` | `| BY attr[ |` | `|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.

## 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.
Note | 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. A variable `SQLCNT` can be used for checking the number of deleted records.
Example 1 | 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.
Example 2 | 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.

## END DECLARE SECTION

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

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

## 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. No further SQL operations may be done, including `INIT`. `EXIT` cannot be used in PSM routines and triggers.

## 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 [|NEXT |[AGAIN][FROM]] cursor_name
                    |PRIOR|

         attr {, attr} INTO variable {, variable};
SQLCODE |
`0` | OK.
`1` | Locked row.
`4` | Non-active cursor.
`100` | No more rows.
SQLWARN1 |
`''` | Without truncation.
'`W`' | Truncation.
Note | 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 attributes with the `FETCH_AGAIN` command.
Example | 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.`

## 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.
Note | `FETCH_AGAIN` does not affect the status of the current record. After a `FETCH_AGAIN` the current record remains the current record.
Example | 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`.

## 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`
SQLCODE | No meaning.
Note | You should have an `INCLUDE SQLCA` statement as the first Precompiler statement of your program.

## INIT

Executes initialization procedures for Empress.

Syntax | `EXEC SQL INIT;`
SQLCODE |
`-1` | `INIT` has already been called.
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. `INIT` may be called several times during a program; subsequent invocations have to be proceeded by `EXIT` call. `INIT` cannot be used in PSM routines and triggers.

## INSERT

Inserts a new row of attribute values into a table.

Syntax |
    EXEC SQL INSERT INTO table
         (attr {, attr}) VALUES (value {, value});
where:
`value` |
    |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.
Example | 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`.

## OPEN CURSOR

Opens the named cursor to perform multiple row manipulations.

Syntax | `EXEC SQL OPEN cursor_name;`
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.
SQLCODE |
`0` | OK.
`-1` | A wrong `WHERE` clause was built (`mxgetbegin() error`).

## OPEN_TABLE

Explicitly opens a table for operations.

Syntax |
`EXEC SQL OPEN_TABLE table FOR` | |`UPDATE` |`READ` |`DEFERRED` |`BYPASS_LOCK` | |`` {`, table FOR` | | | | |`UPDATE` |`READ` |`DEFERRED` |`BYPASS_LOCK` | |}`;` | | |
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)
Note | 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, update or dirty read). 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.

## 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.

## START TRANSACTION

Starts a transaction on the following command.

Syntax |
`EXEC SQL START` | |`TRANSACTION` |`WORK` | |``; |
SQLCODE |
`0` | OK.
`-6` | Impossible to start a transaction.
Note | Transactions may be started from within retrieval loops.

## 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`).
`-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.) A variable `SQLCNT` can be used for checking the number of updated records.
Example 1 | The command:
       EXEC SQL UPDATE personnel
          SET credit_limit=100, date='19851119'
          WHERE name='Jones';
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`.
Example 2 | The command:
       EXEC SQL UPDATE personnel
          SET credit_limit=100, date='19851119'
          WHERE CURRENT OF c;
will update only the current record in the context associated with the cursor named `c`.

## WHERE

A `WHERE` clause determines the set of records which satisfy the conditions in the 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` | `| | | | | | | | | | | | | | | | | |` | `| = | != | > | > = | < | < = |MATCH |!MATCH |SMATCH |!SMATCH | = | != |[IS] |[IS] NOT BETWEEN` | `| |attr | |program_variable | |constant | | | | |program_variable | |constant | | | NULL | | | operand AND operand` | `| | | | |` | `| | | | | | | | | | | | | | | | | |`
where `operand` is:
|  |  | `|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. 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.

## 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.