# CHAPTER 2: Empress Conventions

## 2.1 Command Syntax Notation

For the purposes of brevity, system prompts and Empress prompts are not reproduced for many of the shorter examples. In the case of longer and more complex illustrations, the operating system prompt (represented as %) and the Empress asterisk prompt (*) are often displayed to indicate the beginning and end of commands.

The following typographical conventions are used in this manual to aid the reader in locating and identifying information within notations. You should read this section carefully since you will need this information to read the syntax of commands.

Table 2-1: Typographical Conventions - General

Examples | Description
`ALTER table` | Command syntax and scripts are appear in courier typeface.
`Ready.e` | Responses to Empress prompts (to be typed in by the user) are in courier bold.
`MSTERMDB` | Empress system variables are in uppercase and must be typed in uppercase. These variables always start with `MS`.
SQL Reference | Italics are used for new terms being introduced or explained and for titles of books, manuals, documents and other publications.
Important Message | This indicates the following information is important.

Table 2-2: Typographical Conventions - SQL Statements

Examples | Description
`SELECT` `DISPLAY` | In the syntax definition of the command, words in uppercase are keywords. These are not case-sensitive and may consist of a mixture of uppercase and lowercase when typed into Empress.
`table` `attr` | Items in lowercase refer to categories. When typed into Empress, appropriate substitutions of actual names or expressions must be used. For example, `personnel` for table. Note that for user defined names, case is significant and must be typed exactly as originally defined.
|`item 1`| |`item 2`| | Items stacked between vertical lines "|" are options of which one must be chosen.
`[ ]` | Square brackets are used to indicate that the items enclosed are optional.
`{ }` | Braces may contain more than one item or none at all.
`SELECT` command | When a command is referred to in the text (anywhere other than in the syntax) of the manual, it will usually be written in uppercase.

Table 2-3: Typographical Conventions - Keyboard Keys

Examples | Description
<Return> | Keys on your keyboard appear in between angled brackets
<Ctrl+A> | Key chords which require pressing more than one key at time appear with a "+" separating the keys.

## 2.2 Entering Commands

### 2.2.1 Free-Form Commands

Empress commands are normally entered into the SQL interface as a free form list of words separated by one or more blanks, tabs, or new lines. Command lines should end with a semicolon (;). The semicolon can be omitted if all options available for a command have been exhausted. The command line is entered into Empress by pressing the <Return> key.

Thus, a command may be entered on one line, over several lines, or even one word per line, with tabs, blanks, and new lines being used freely. For example, a command might be typed as follows:

       SELECT number, name FROM personnel WHERE phone="922-1743";

or broken up to emphasize the logic:

       SELECT number, name FROM personnel
          WHERE phone="922-1743";

When you span a command over more than one line, Empress will prompt you with a period (.) to indicate it is expecting more input. At some installations, both the Empress primary asterisk prompt (*) and the secondary period prompt (.) may be different. Both prompts may be customized, by the Database Administrator or by yourself, so the prompts that you see on your terminal may differ from the ones indicated in this manual. For more information refer to the Empress: Database Administrator's Guide under Empress System Variables for the procedure to change the prompts.

### 2.2.2 Single-Line Commands

If the Empress system variable `MSQLONELINE` is set, commands must be entered on a single line, with <Return> signifying the end of the command. In this case, if you need to span a command over more than one line, the system variable `MSLINECONT` may be set to a string defining a line continuation sequence. If this string is placed immediately before the end of a line, it will mask the new line and allow the command to continue into the next line.

For example, to have <Return> signify the end of a command, use:

       SET MSQLONELINE TO 'x';

To have the character "-" as the line continuation string, use:

       SET MSLINECONT TO '-';

With these two variables set, the command used as an example above may be typed as:

       SELECT number, name FROM personnel WHERE phone="922-1743"

Note that the terminating semicolon is no longer required. However, the following syntax would cause a syntax error after `name`. With `MSLINECONT` set as "-":

       SELECT number, name FROM personnel
          WHERE phone = "922-1743";

This command may be typed as to achieve the desired result:

       SELECT number, name  FROM personnel -
          WHERE phone = "922-1743"

## 2.3 Entering Comments

There are several ways of embedding comments in commands. Any text between "/*" and "*/" or any single line preceded by a double dash (--) are taken to be comments and ignored in executing the command. This is most useful for annotating prepared scripts of commands.

In the following example, the first line is ignored:

       -- Print all Mosca's loans

       SELECT * FROM loans WHERE name = 'Mosca';

In the next example, the first two lines are ignored:

       /* Print all Mosca's loans and
          then the total amount */

       SELECT * FROM loans WHERE name = 'Mosca';
       SELECT SUM amount FROM loans WHERE name = 'Mosca'

Refer to the Empress Host Language Interface: Command Language Interface manual for further details on submitting commands in a shell script or batch file.

## 2.4 Databases, Tables, Attributes and Filenames

In syntax definitions, words in lowercase italics are used to indicate categories for which you are to supply the appropriate name when using the command. These categories are:

Table 2-4: Name Categories

Category | Meaning
`database` | The name of a database. This is equivalent to the name of the directory created when the database was created.
`table` | The name of a table optionally preceded by `database:`.
`attr` | The name of an attribute optionally preceded by `table.`.

A `database` may reside on multiple directories or file systems, as along as there is a reference directory for the database. When a database is created, a directory is created along with the database having the same name as the database name. Consequently, a database name corresponds to the name of the reference directory and may include the full path specification for the directory. This manual also refers to the reference directory as the database directory.

It is important that a database directory be used only for the files which comprise the database. Do not locate non-database files in the directories used by an Empress database. This rule also applies to any other directories used for remote tables of the database. Failure to abide by this rule may result in the loss of the non-database files.

For more information on creating a database refer to the Empress SQL: User's Guide.

Table and attribute names may be up to 32 characters long and consist of any printable ASCII character, with no leading blanks. A distinction must be made, however, between simple and complex names.

### 2.4.1 Simple Names

Simple names begin with a letter, and consist only of letters (upper- and lowercase), digits, and underscores. No blanks, tabs, periods, or non-printing characters are allowed. Simple attribute and table names need not be enclosed in quotes when used in Empress commands (unless they happen to coincide with any of the Empress keywords, in which case they are treated as complex names). Examples of simple names are `personnel` and ``number``.

### 2.4.2 Complex Names

Complex names may begin with digits or other non-letter characters, and may include blanks, tabs, periods, etc., except non-printing characters. When used in Empress commands, complex names must be enclosed in quotes so that Empress knows where they begin and end. Quotes are discussed in more detail in the following section. An example is the table name `auto parts` which must be typed as `"auto parts"` in any query.

When referring to a table, the complex table name may be preceded by the keyword `TABLE`. When referring to an attribute, the complex attribute name must be preceded by the keyword `ATTR`.

### 2.4.3 Accessing Tables in Other Databases

When a table name is typed in a command line, Empress expects to find the table in the current database, unless the table name is prefixed with a database name. The database name and table name are separated by a colon. In the command syntax, a table may be interpreted as `database:table`.

The following are examples of legal table names which may be preceded by the keyword `TABLE`:

       TABLE personnel

       TABLE repairs:personnel

For instance, the following command causes Empress to search the current database for the table `loans`:

       SELECT * FROM loans;

And the following command causes Empress to search the database `repairs` for the table `loans`:

       SELECT * FROM repairs:loans;

Data may be accessed from more than one database in one command. If the table `loans` is in the database called `accounts` and the table `personnel` is in the database `repairs`, you can enter the following:

       SELECT * FROM accounts:loans, repairs:personnel
          WHERE personnel.name = loans.name;

### 2.4.4 Distinguishing Attributes from Each Other

If two or more tables have attributes with the same name, attributes may be distinguished by specifying the table and the attribute name, separated by a period. A database name and a table name may be separated by a colon if they are used together. Examples of legal attribute names are:

       number
       personnel.number

The attribute `name` in the `personnel` table in the database `repairs` may be specified as:

       repairs:personnel.name

Remember that if the attribute or table names are complex, they must be enclosed in quotes.The `part name` attribute in the `auto parts` table may be specified as `"auto parts"."part name"` with quotes around the names or just as `"part name"`.

### 2.4.5 Distinguishing Attributes from Values

In some commands, it may be necessary to distinguish between complex attributes (which must be surrounded by quotes) and strings. The situation may arise in a `WHERE` clause, for instance, where both attributes and strings may appear on either side of a comparison, and in lists of items to be selected. A complex attribute can always be explicitly distinguished from a quoted string by preceding it with the keyword `ATTR`. A simple attribute may be preceded by `ATTR` as well.

For instance, to select values for the `part name` attribute from the `auto parts` table:

       SELECT ATTR "part name" FROM "auto parts";

This generates the following output:

       part name

       Hubcap
       Fender - rubber
       Fender - chrome
       Spark Plugs
       Rear Wing
       Front Wing
       Radiator
       Spray paint
       Chrome paint

Omitting the keyword `ATTR` causes Empress to treat `part name` as a string value, so that the string part name is displayed for each record retrieved:

       SELECT "part name" FROM "auto parts";

This generates the output:

       EXPRESSION_1

       part name
       part name
       part name
       part name
       part name
       part name
       part name
       part name
       part name

Another way of identifying an attribute is with the `table.attr` convention described previously.

### 2.4.6 Operating System Filenames

Names of operating system files (and hence also of databases) must follow the normal operating system conventions. If the filename contains any slashes, special characters, or periods, it must be enclosed in quotes when used in an Empress command.

In UNIX, filenames may be specified as the full pathname of the file:

       /usr/joe/work/loanlist

File specifications in Windows or NT may include the drive, path and filename:

       C:\usr\joe\work\loanlist.dat

This allows a file to be created in or read from a directory other than the one from which Empress was invoked.

## 2.5 The Use of Quotes

Quotation marks must be used around character strings when they are entered as values in Empress commands. Even single-word `CHARACTER` attribute values must be quoted. This is essential to avoid confusion with attribute names in expressions. Complex attribute or table names must also be surrounded by quotes. Both single and double quotes are effective. The following examples mean exactly the same thing to Empress:

       SELECT * FROM loans WHERE name = 'Jones';
       SELECT * FROM 'auto parts';

       SELECT * FROM loans WHERE name = "Jones";
       SELECT * FROM "auto parts";

However, the nature of the surrounding quotes does matter if the name or character string itself includes a quote. If you have a single quote in the character string or complex name, you should either use double quotes to surround the value or type the single quote inside the value twice. The same is true for a double quote inside a name or value use single quotes to surround it, or type the inner double quote twice.

For example, to look up O'Brien's phone number you may enter:

       SELECT phone FROM personnel
          WHERE name = "O'Brien";

## 2.6 Missing Data Values

Missing data values are indicated in free form commands by the keyword `NULL`. This is simply typed in upper- or lowercase, as a data value, and is accepted as valid input or comparison for any data type. Note, however, that `NULL` is invalid as input for an attribute which has been created as `NOT NULL` or which has range checking excluding null values. Empress keeps track of null values to differentiate them from blanks, dashes, or reserved numbers.

When using the Interactive Interface, missing values are indicated by entering ".~", rather than `NULL`.

## 2.7 Reserved Keywords

For a complete list of all reserved keywords, please refer to our Reserved Keywords document.

Beside the keyword sets mentioned above, the name of the functions that are stored as Persistent Stored Modules (PSM) are also considered as Keywords for that database.

Unlike attribute and table names, keywords are the same in either case. They may be typed in uppercase, lowercase, or any mixture of the two.

If it is essential to name a table or attribute with one of the above keywords, this name must be treated as a complex name. The attribute name should be enclosed in quotes and either preceded by the keyword `ATTR` or prefixed with the table name and a period (following the `table.attr` convention). Refer to the section Distinguishing Attributes from Values in this chapter .

## 2.8 Synonyms

The following is a list of synonyms

|  |  | `ANY` | `SOME`
|  |  | `ASCENDING` | `ASC`
|  |  | `ATTRIBUTE` | `ATTR`
|  |  | `CENTER` | `CENTRE`
|  |  | `CONSTRAINT` | `CHECK`
|  |  | `DATABASE` | `DB`
|  |  | `DESCENDING` | `DESC`
|  |  | `DISTINCT` | `UNIQUE`
|  |  | `EXCLUSIVE` | `EXCL`
|  |  | `INCLUSIVE` | `INCL`
|  |  | `PRIVILEGE` | `PRIV`
|  |  | `RECALL` | `RC`
|  |  | `REFERENCIAL` | `REFER`
|  |  | `ROLLBACK` | `CANCEL`
|  |  | `SORT` | `ORDER`
|  |  | `STOP` | `EXIT`
|  |  | `TRANSACTION` | `WORK`

Synonyms are used to provide:

- Shorter forms of important keywords
- Compatibility with SQL
- Backwards compatibility with earlier versions of Empress.

## 2.9 Variables and Strings

Empress allows you to define character string variables, the contents of which are referred to by placing a dollar sign ($) in front of the variable name. For example, if you do the following, you may then use $p to stand for loans:

       SET p TO "loans";

To prevent a dollar sign from causing an attempt to expand a variable, use a double dollar sign ($$). That is, `$$p` evaluates to the string `$p`. This applies to dollar signs ($) anywhere in a string.

The character used to obtain the value of a variable is defined in the Empress system variable `MSQLVARCHARS`. It is the dollar sign ($) by default but may be changed by the user. Variables are discussed under the `SET` command.

## 2.10 Error Messages

There are three possible sources of error messages when using Empress:

1. Operating system error messages arise from operating system related operations such as specifying the wrong directory, or not having read or write access to the database files. Contact your local operating system consultant if you need help.
2. Empress error messages arise from a variety of sources such as parsing invalid Empress commands, accessing non-existent tables, lack of access privileges to a database, an unresolvable expression (e.g., division by 0), or entering incorrect data. They are reported in the form:

          *** error_message_type: *** description of error

3. The syntax error message is by far the most common kind of error. It occurs when Empress finds an error in typing a command:

          *** syntax error *** description of error

   Examples are:

          ** syntax error *** expecting an attr_name
          ** syntax error *** extra chars after valid command

The command should be corrected using the line editor, or by re-typing it.

## 2.11 Erasing Characters and Aborting Commands

In Empress, characters and lines are deleted with the corresponding operating system convention.

On most UNIX systems, this means that <Backspace> will erase characters on the current line, and <Ctrl+X> (where X is some letter of the alphabet) will erase the entire line. If these do not work, the UNIX `stty(1)` command can be used to check your local settings for the erase and kill characters.

Pressing <Rubout> or <Delete> will cause Empress to abort the current command. Again, if <Rubout> seems to have no effect on your system, use `stty(1)` to check the `intr` character.

On some UNIX systems, it may be necessary to press <Return> after a <Rubout> before the <Rubout> takes effect.

Special characters may be escaped by using the convention of a backslash ( \ ) preceding the character to be escaped.

On Windows or NT systems, <Backspace> will erase characters on the current line and <Ctrl+Break> or <Ctrl+C> will abort the current command. The command will be interrupted in the same way that Windows interrupts when the Windows environment string BREAK is set to "off".

## 2.12 Access to an Empress Database

Access to Empress databases and tables requires normal operating system file access, with additional Empress access privileges.

If you are running Empress on a UNIX system, you will need UNIX execute permission for a database (directory) or read permission for a table (file) within it to access the table, regardless of Empress privileges. Similarly, you must have write permission to add to or modify a table.

Under Windows or NT, you will need read access to a database table and write permission is necessary to add to or modify a table.

If you do have appropriate operating system access, Empress checks the data dictionary for your privileges (see the Empress: Database Administrator's Guide for details on the data dictionary). There are many different kinds of access privileges, allowing you to select data from a table, update or insert data, create new tables, alter old ones, etc. These may all be set independently. They are discussed fully under the `GRANT PRIVILEGE` command of this manual.

The command to access the database is:

       %empsql database

where:

`database` is the name of the database (i.e., the system directory name).

## 2.13 Empress Operational Parameters

The following table lists various Empress operational parameters. Figures are given for 32-bit machines. For practical purposes, sizes are limited by the availability of virtual memory on your system and allowable sizes of files and file systems on your disk, as well as the number of files and directories allowed on your system.

Table 2-5: Empress Operational Parameters

Parameter | Value
Number of Databases | No Empress imposed limit.
Size of Database | A single database may occupy an entire file system. A single database mat also span several file system.
Size of Table | Size of an integer for that CPU. On most 32-bit machines, this is 2,147,483,647 characters
Size of Record | Size of an integer for that CPU. On most 32-bit machines, this is 2,147,483,647 characters
Size of Attribute | Size of an integer for that CPU. On most 32-bit machines, this is 2,147,483,647 characters
Number of Table/Database | 32,767
Number of Attributes/Table | 32,767
Number of Keys/Sort | No Empress imposed limit.
Number of Indices/Table | No Empress imposed limit.
Number of Attributes/Index | No Empress imposed limit.
Number of Levels-of-Nesting/Subquery | 100