CHAPTER 2: Empress Conventions

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

Table 2-2: Typographical Conventions - SQL Statements

Table 2-3: Typographical Conventions - Keyboard Keys

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.

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"

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.

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

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.

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.

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.

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;

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

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.

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.

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";

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.

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 .

The following is a list of synonyms

Synonyms are used to provide:

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

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.

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.

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

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:

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