# CHAPTER 7: The Executive Interface

## 7.1 Introduction

Empress provides a set of commands dealing with the management of DDL, DML and DCL commands in a given SQL interface session. A summary of the available Executive Interface commands is listed below:

Table 7-1: Executive Interface Commands

Command | Description
`CHANGE WORD` | Allows quick, simple alterations to commands.
`DATABASE` | Shows the current database, or when followed by a new database name, enables you to change databases without having to leave Empress and invoke it again.
`DO` | Allows you to communicate with the operating system and use other system utilities, such as the editor, without leaving Empress.
`EDIT` | Invokes a simple line editor for altering a command.
`EXIT` | Exits from an Empress SQL session.
`HELP` | Provides an on-line reference summary of the SQL language available using the current interface.
`HOLD` | Parses a command without executing it, allowing commands to be checked for syntax or table/attribute name errors. `HOLD` is also used to make a previous command from the history list the current command.
`RECALL` | Examines the command history list and shows previous commands.
`RUN` | Re-executes a previous command from the history list without having to re-type it. `RUN` followed by a filename executes the commands stored in the named file, allowing convenient packaging of frequently-used commands.
`RUN REPORT` | Invokes the Empress Report Writer, on a report script.
`SET` | Allows you to set variables. You can use variables to save typing or in files of commands to make them more general. Special system variables can also be set to customize aspects of your working environment.
`SHOW` | Shows the value of a variable.
`STORE` | Saves a command from the history list in an operating system file.
`UNSET` | Removes variables.

## 7.2 CHANGE WORD

The `CHANGE WORD` command may be used to change a single word or phrase in a given command, providing a convenient alternative to the line editor for simple changes. The command may be run immediately, or simply displayed on the terminal.

Syntax

      CHANGE [number] [ALL] word [TO] newword [RUN];

  where:

  `number` | a number of a command from the history list generated by `RECALL ALL` command.
  `word` | is a word or phrase in a given command.
  `newword` | is the new word or phrase.

Notes

1. This changes the first occurrence of `word` to `newword` in the current command (default), or in command number.

2. If `ALL` is specified, all occurrences of word are changed.

3. If `RUN` is included, the command is executed immediately; otherwise it is just displayed.

Privileges Required

  None.

Example

1. Change Single Occurrence of Word

   Select first Jones' loans and then Mosca's with the following:

          SELECT * FROM loans WHERE name = 'Jones';

          CHANGE Jones TO Mosca;
          SELECT * FROM loans WHERE name = 'Mosca';

          RUN;

   Note that single words need not be enclosed in quotes unless they are keywords.

2. Change All Occurrence of Word

   Change all occurrences of `100` in the fifth command of the current work session to `200` and run the command immediately with:

          CHANGE 5 ALL 100 TO 200 RUN;

## 7.3 DATABASE

The `DATABASE` command either shows the name of the current default database, or changes the default to the database specified.

Syntax

  `|DATABASE |DB` | `| [database] [ |` | `|INTO| file]; |ONTO|`

Notes

1. The keywords `DATABASE` and `DB` are synonyms.

2. The `database` may be given as a full pathname or explicit file specification if desired, in which case it must be enclosed in quotes.

3. If no `database` is specified, the name of the current default database is shown. If a `database` is specified, it becomes the default working database.

4. It is not necessary to change the current database to access other databases. Tables in databases other than the current one can be accessed by preceding the table name with `database:`, i.e. `database:table`.

5. If `INTO file` or `ONTO file` are used, the results of the operation are diverted from the terminal to the named file. `INTO` overwrites any existing file, while `ONTO` appends to it. Both create the file if necessary.

Privileges Required

  None.

Example

  If you are working in a database called `repairs` in the subdirectory `joe` in the directory `usr`, you may check the name of the database with:

         DATABASE;

  The response is:

         Current Database '/usr/joe/repairs'

  If you have a second database directory called `accounts`, in which you keep all your accounting records, then you may change from `repairs` to `accounts` by entering the following:

         % empsql repairs

         * DB;
         Current Database 'repairs'

         * (execute several commands)

         * DB "/usr/joe/accounts";
         Current Database '/usr/joe/accounts'

         * (carry on working with the new database)

## 7.4 DO and !

There are two interfaces to the operating system from Empress: the DO command and the escape command, (!).

Syntax

  Syntax 1:

  |  |  | `DO 'os_command {` | `|<newline> |;` | `| os_command}'; |`

  Syntax 2:

         ![os_command {; os_command}];

  where:

  `os_command` | is the operating system command.

Notes

1. The `DO` command enables one or more operating system commands to be executed without stopping Empress. If more than one operating system command is to be executed, the operating system command must be separated by a new line or by a semicolon.

2. The operating system escape (!) takes a single line only as its input, so that if more than one command is to be executed, the commands must be separated by semicolons.

   If no command is requested in UNIX, an escape to the shell (as specified by the system variable `MSSHELL`) is performed.

Privileges Required

  None.

Example

1. Single Operating System Command

   To obtain a list of files in the current directory, use:

          do 'ls';

2. Multiple Operating System Commands

   To obtain a list of files and the date, use:

          do 'ls
             date';

3. Execute Single OS Command Using Operating System Escape

   To obtain a list of the files in the current directory via the operating system escape, use:

          !ls

4. Execute Multiple OS Commands Using Operating System Escape

   To obtain a list of files and the date, use:

          !ls; date

5. Escape to Operating System to Edit a File

   The operating system escape can also be used to edit a file of commands, thus:

          * !ed printlist
          62
          .
          .
          .
          .
          .
          w
          73

          *

## 7.5 EDIT

Empress allows the user to edit the SQL commands from the history list through the `EDIT` command.

Syntax

      EDIT [number] [run_command];

  where:

  `number` | is the history number of the command to be edited.
  `run_command` | is any valid `RUN` command as discussed in the section under `RUN`.

Notes

1. If number is not given, the last command entered is edited. A command number may be found by a `RECALL ALL` command.

2. If `RUN` is included, the command is executed immediately after the editing is completed. Otherwise it is just displayed on the screen.

3. During editing, you may issue editing commands and editing instructions. Editing commands are used to manipulate entire command lines, while editing instructions are used to make changes within a line. Pressing <Return> displays each line of the command or data in turn. If the line contains non-printing characters it is printed twice, first with the characters interpolated, then with the characters replaced by @ sign.

4. Each editing command begins with a period (.) and is ended by pressing <Return>. An editing session is ended by pressing <Return> on the last line with no editing commands or editing instructions.

   The editing commands are:

   Table 7-2: Editing Commands

   Command | Interpretation
   .`^` | Go to the previous line.
   .`d` | Display all lines.
   .`j` | Join (concatenate) this line and the following line into a single line.
   .`u` | Cancel all changes, start editing from the original lines.
   .`x` | Delete this line and show the next line. If the line deleted is the last line, show the previous line.

5. An editing instruction determines how the current line is to be changed. It consists of regular characters, positioning characters and special characters.

   Regular characters replace the character immediately above them on the displayed line. Past the end of the display line, characters on the editing line are simply appended to the display line.

   Positioning characters (spaces, backspaces, and tabs) move the cursor to the desired positions. Note that backspacing over a tab will place you before the tab.

   Special characters issue the following instructions:

   Table 7-3: Editing Instructions

   Instruction | Interpretation
   `#` | Delete the above character.
   `%` | Replace the above character with a space.
   `^` | Insert the characters after the `^` sign before the above character.
   `$` | Delete from the above character to the end of the display line and replace with any characters typed after the `$` sign.

   A new line may be included as part of the editing line by preceding it with a backslash ( \ ). Thus, a line may be split in two, or a new line inserted in the middle of an old one, by using the sequence:

           ^ \ <Return>

   Once all lines of the command have been edited, the entire command is re-displayed. Check the command before executing it by entering `RUN`.

6. Non-printing characters such as backspaces, escape sequences, and any of the control characters are sometimes inadvertently typed into text. These characters are illegal in the Query Language, and are also disallowed as input for most Empress data. If they are typed they are difficult to detect and remove, since they are not usually visible on a terminal.

   If a non-printing character is typed when entering an SQL interface command, an Invalid Character error will result. If one is entered when inserting or updating entries using the Interactive Interface, a Conversion Error results, with immediate invocation of the line editor.

   The Empress line editor provides a way of making these characters visible and showing their position in a line of text, facilitating their removal or alteration. When a command is edited line by line, a line which contains a non-printing character is printed twice, first with the non-printing character interpolated, and then with the character shown as a @ sign. The table below gives the interpolations:

   Table 7-4: Non-Printing Characters

   Interpolation | Character
   `{bs}` | <Backspace>
   `{esc}` | <Escape>
   `{^c}` | <Ctrl+C> (where C is the appropriate character)
   `{tab}` | <Tab>

   The invisible character may then be deleted using the # sign, or changed to something else, as desired.

   Although tabs are not illegal in data or commands, they are shown as {tab}, when using the line editor.

7. There are several commands which may not be edited; generally, it would make no sense to try to do so. These commands are:
   - `CHANGE`
   - `HOLD`
   - `RECALL`
   - `RUN`

   If an attempt is made to edit any of these commands, the previous command is edited instead. All other commands, including `DO`, `!`, and `RUN FROM`, may be edited.

Privileges Required

  None.

Example

1. Editing Examples

   In order to show the use of the editing instructions, a number of examples dealing with the correction of syntax errors and changing Query Language commands are given below.

   - Deleting Characters

            SELECT aamount FROM loans;
                   #

            SELECT amount FROM loans;

   - Changing a Character to a Space

            SELECT aamount from loans;
                   %

            SELECT  amount FROM loans;

   - Inserting Characters

            SELECT amount FROM loans;
                   ^name,

            SELECT name, amount FROM loans;

   - Deleting Rest of Line

            SELECT name, amount FROM loans WHERE name = 'Mosca';
                                           $

            SELECT name, amount FROM loans

   - Replacing Ordinary Characters

            SELECT name, amount FROM loans;
                   date

            SELECT date, amount FROM loans;

   - These editing instructions can also be used in combination. For example:

            SELECT name, aamount FROM loans;
                         #######^ credit_limit

            SELECT name, credit_limit FROM loans;
                                           $personnel;

            SELECT name, credit_limit FROM personnel;
                                                    %INTO 'temp';

            SELECT name, credit_limit FROM personnel INTO 'temp';

     If at anytime you wish to restore the original line, any changes can be undone by typing `.u`.

            SELECT name, credit_limit FROM personnel INTO 'temp';
            .u

            SELECT name, aamount FROM loans;

2. Editing a Command Spanning More Than One Line

   The following is an example of editing a command spanning more than one line. The intention is to ask for all loans by Jones over $100.

          * SELECT FROM personnel
          . WHEERE amount>$100;

          *** syntax error *** error occurs somewhere in:
          select from personnel
          wheere

          * EDIT;

          SELECT FROM personnel
          <Return>
          WHEERE amount>$100
             #   <Return>
          WHERE amount>$100;
                           \    <Return>
          AND name = Jones   <Return>
          WHERE amount>$100
                       %      <Return>
          WHERE amount> 100
          <Return>
          AND name = Jones;
                     ^'    <Return>
          AND name = 'Jones;
                           ^'   <Return>
          AND name = 'Jones';
          .^   <Return>
          WHERE amount> 100
          .^   <Return>
          SELECT FROM personnel
                      loans$   <Return>
          SELECT FROM loans
          <Return>
          WHERE amount> 100
          <Return>
          AND name= 'Jones';
          <Return>

          SELECT FROM loans
          WHERE amount> 100
          AND name= 'Jones';

          * RUN;

3. Edit Command on the History List

   Edit the sixth command on the history list and run it immediately with:

          EDIT 6 RUN;

4. Edit Non-printable Character

   Since many terminal keyboards have the control key next to the `A` key, entering <Ctrl+A> rather than `a` is a common mistake. The example below shows how to deal with a <Ctrl+A> entered in the middle of the attribute `name`:

          * SELECT name, amount FROM loans;

          *** Invalid Character *** error occurs somewhere in: select na
          SELECT na{^a}me, amount FROM loans;

          * EDIT;
          SELECT na@me, amount FROM loans;
                   #   <Return>
          SELECT name, amount FROM loans;
          <Return>

          * RUN;

## 7.6 EXIT

The `EXIT` command is used to exit from Empress Interactive SQL session.

Syntax

  `|EXIT |STOP` | `| |`

Note

  `STOP` can be used as a synonym for `EXIT`. Empress will stop, returning control to the operating system.

Privileges Required

  None.

Example

  To exit the Interactive SQL session:

         EXIT;

  or,

         STOP;

## 7.7 HELP

Empress provides on-line help with the Interact SQL Interface

Syntax

      HELP [topic];

  where:

  `topic` | is the item to be queried.

Notes

1. If no `topic` is requested, a list of topics on which help is available is printed.

2. Refer to the Empress: Database Administrator's Guide for information on adding help files and altering the help file dictionary.

Privileges Required

  None.

Example

  To get help with the `DATABASE` command, use:

         HELP DATABASE;

  which produces:

         ________________________Database________________________

         This command prints the name of the current database or changes
         to a new database.

         Syntax

  |  |  |  |  |  | |`DATABASE` |`DB` | | [`database`] [ | | |`INTO`| `file`]; |`ONTO`|

         The keyword "DATABASE" alone prints the name of the
         current database. It may be shortened to "DB". Specifying
         another database name makes DATABASE the current database.

         The output of a "DATABASE" command may be diverted from
         the terminal into a new file or appended to an existing file by
         specifying "INTO" or "ONTO", respectively,
         and a filename.

         For further information, enter:

            HELP display;

## 7.8 HOLD

HOLD is used to make a previous command current without executing it. It may also be used to parse an Empress command but not execute it, in order to check for possible errors in syntax or table attribute names.

Syntax

  `HOLD` | `|command |number` | `|; |`

  where:

  `command` | is any Empress SQL command.
  `number` | is the number of a command in the history list.

Note

  The command `number` becomes the current command. Command numbers are found by a `RECALL ALL` command. If an invalid `number` is given, the following message is printed:

         **** User Error **** Invalid Command

Privileges Required

  None.

Example

1. To Make a Command Current Command

   Make the fifth command on the history list the current command with:

          HOLD 5;

2. Check for Command Syntax

   Check the syntax of a `SELECT` command with:

          HOLD SLECT FROM loans;

   which in this case will give a syntax error for the missing `E` from `SLECT`. You may now edit the command to correct it if you wish.

## 7.9 RECALL

`RECALL` is used to examine the Empress command history list.

Syntax

  `|RECALL |RC` | `| [ |` | `|ALL |number |string` | `|] [ | |` | `|INTO| file]; |ONTO|`

  where:

  `number` | is the number of a command in the history list.
  `string` | is any character string in quotes.

Notes

1. Here `RC` is a synonym for `RECALL`.

2. `RECALL` by itself shows the current command.

3. The `ALL` keyword retrieves all the commands in the history list.

4. If a `number` is specified, the command with that number from the history list is shown. If `number` is negative, the command `number` from the end of the history list is recalled. The last-issued command is recalled by `RECALL -1`.

5. If a string of characters is specified, all commands in the history list containing that string are shown.

   The number of commands remembered is set by the Empress system variable `MSQLCMDSAVE`; its default is 50. To change this, reset the variable; for example, to limit the history list to 20 commands, use:

          SET MSQLCMDSAVE TO 20;

   Note that if `MSQLCMDSAVE` is changed, `RECALL ALL` commands will continue to show the previous history list until the list itself is actually altered by issuing the next command.

6. Commands not added to the history list are not shown by a `RECALL` command. These include `CHANGE`, `EDIT`, `HOLD`, `RECALL`, `STORE`, `RUN` or `START`.

Privileges Required

  None.

Example

  If `MSQLCMDSAVE` is currently set to 5, a `RECALL ALL` command during a work session could show something like the following:

         * RECALL ALL;
            8 DISPLAY DB;
            9 SELECT * FROM loans;
           10 UPDATE loans
              SET date TO '28 jun 1990'
              WHERE date = '27 jun 1990';
           11 SELECT * FROM loans WHERE date MATCH "*jun*";
           12 !date

  If you now issue some `RECALL` commands, you will see the following (each command is considered as acting on the list above but not adding to it):

         * RECALL;
           !date

         * RC 9;
           SELECT * FROM loans;

         * RC -2;
           UPDATE loans
           SET date to '28 jun 1990'
           WHERE date = '27 jun 1990';

         * RC 'loans';
            9 SELECT * FROM loans;
           10 UPDATE loans
              SET date TO '28 jun 1990'
              WHERE date = '27 jun 1990';
           11 SELECT * FROM loans WHERE date MATCH "*jun*";

## 7.10 RUN

Any of the commands in the Empress history list, or commands in an operating system file, may be executed by the RUN command.

Syntax

  Syntax 1:

  |  |  | `|RUN |START` | `| [number] [ |` | `|INTO| file] [ |ONTO|` | `|SET parameter {, parameter} |(parameter {, parameter})` | `|]; |`

  Sytax 2:

  |  |  | `|RUN |START` | `| [FROM] file [ |` | `|INTO| file] [ |ONTO|` | `|SET parameter {, parameter} |(parameter {, parameter})` | `|]; |`

  where:

  `number` | is the number of the command in the history list.
  `file` | is a file name.
  `parameter` | are values for Empress numeric variables.

Notes

1. If `[FROM] file` is used, the commands are read from the operating system file `file` containing Empress Query Language commands. If `[FROM] file` is not used, command number is executed.

2. Command numbers are shown by a `RECALL ALL` command. If no number is given the last command entered is executed.

3. If `INTO file` or `ONTO file` are used, the output is sent to the named file rather than to the terminal. `INTO` overwrites any existing file, while `ONTO` appends to it. Both create the file if necessary.

4. If `parameters` are specified, all instances of `$1` in the command or the entire file of commands are replaced by the first `parameter`, all instances of "$2" by the second `parameter`, and so on. (See also the discussion on parameters in the section on the `SET` command in this chapter.)

5. If you run a `file` that contains `SET` commands the Empress environment will be changed.

Privileges Required

  None.

Example

1. Execute Command from History List

   Execute command 9 on the history list with the command:

          RUN 9;

2. Re-execute the Previous Command

   Any command such as:

          SELECT number FROM loans WHERE name = 'Jones';

   can be immediately re-executed with:

          RUN;

3. Executed an Edited Command

   `RUN` is also used to execute a command which has been edited, as is shown in the following example:

          * SELCT amount FROM loans WHERE name = 'Peterson';

          *** syntax error *** error occurs somewhere in:
          selct

          * EDIT;

          SELCT amount FROM loans WHERE name = 'Peterson';
             ^e
          SELECT amount FROM loans WHERE name = 'Peterson';

          * RUN;

          amount

          $50.00

4. Execute Commands from a File

   You may run commands from an operating system file. First create a file called `showlist` which contains the two commands:

          SELECT date, amount FROM loans
             WHERE name = 'Peterson';

          SELECT SUM (amount) FROM loans
             WHERE name = 'Peterson';

   Now type the Empress commands:

          * RUN FROM showlist;

          date            amount

          June 6, 1983    $50.00

          SUM (amount)
          50.00

          * INSERT INTO loans (name, date, amount)
               VALUES ('Peterson', '19 Aug 1990', '45');

          Number of Records Entered = 1

          * RUN FROM 'showlist';

          date                amount

          June 6, 1990        $50.00
          August 19, 1990     $45.00

          SUM (amount)
          95.00

          *

5. Execute Commands from a File with Parameters

   To illustrate the use of setting parameters in a RUN command, suppose you have a file called `seeloans` containing:

          SELECT FROM loans
             WHERE name = '$1' AND amount > '$2';

          SELECT SUM (amount) FROM loans
             WHERE name = '$1';

   The command:

          RUN FROM seeloans SET Jones, 200;

   will replace all instances of `$1` in the file by `Jones`, and all instances of `$2` by `200`, listing all Jones' loans over $200.00 and the total amount Jones owes:

          name        date                   amount

          Jones       August 12, 1990        $300.00

          SUM (amount)
          358.95

## 7.11 RUN REPORT

The `RUN REPORT` command invokes the Empress Report Writer, on a report script.

Syntax

  `[` | `|RUN |START` | `|] REPORT [FROM] script [[ON] data_file] [ |` | `|INTO| file] |ONTO|`

  |  |  | `[` | `|SET parameter {, parameter} |(parameter {, parameter})` | `|]; |`

  where:

  `script` | is any Empress Report Writer script.
  `data_file` | is a data file which must be in a `REPORT DUMP` format.
  `file` | is a file name.
  `parameter` | are values for Empress numeric variables.

Notes

1. The Empress Report Writer script may select data from a table (or tables) using `SELECT` statements within the script, or read data from a file in the `REPORT DUMP` format. (See the Empress: Report Writer manual for more information).

2. If `INTO file` or `ONTO file` is used, the output is sent to the named file rather than to the terminal. `INTO` overwrites any existing file, while `ONTO` appends to it. Both create the file if necessary.

3. The `SET` clause passes any parameters required by the Empress Report Writer reference script.

Privileges Required

  None.

Example

1. Run Report with Parameters

   Suppose you have a script called `statement` which produces a list of outstanding loan totals from each employee. The script expects the name of the database, `repairs`, as a parameter and looks like:

          PARAMETERS
             CHAR database;
          END;
          LET PAGELENGTH = 12;
          SELECT * FROM loans;
             FOR EACH GROUP OF name
                 PRINT name, col 20, SUM amount, newline;
             END;
          END;

   Run this script with:

          RUN REPORT FROM statement
          SET repairs;

   This generates the output:

          Mosca         150
          Jones          33.95
          Kilroy        250
          Wladislaw      55
          Jones          25
          Mosca         200
          Wladislaw      25
          Peterson       50
          Wladislaw      75
          Jones         300
          Scarlatti     275

2. Run Report from Data File

   To run a report `loanlist` reading only those loans outstanding to Jones from a data file `loandata`.

   For the sake of this example, we generate a data file using `SELECT` statement with `REPORT DUMP` option.

          SELECT * FROM loans
             REPORT DUMP INTO 'loandata'
             WHERE name = 'Jones';

   Report Writer script `loanlist` as follow:

          PARAMETERS
             CHAR database;
          END;
          LET PAGELENGTH = 12;
          FOR EACH RECORD
          PRINT name, col 20, amount, newline;
          END;

   To run the report and sending the output to the file `jonesrpt` using:

          RUN REPORT FROM loanlist
             ON loandata
             INTO jonesrpt
             SET repairs;

## 7.12 SET and UNSET

The `SET` command sets Empress variables.

Syntax

  `SET` | `| | | | | |` | `FROM file`
  `[ALL] [` | `|INTO |ONTO` | `| file] |`
  `variable [TO] value value {, value}` | `|; | | | | |`

         UNSET variable;

  where:

  `variable` | is an Empress variable.
  `value` | is a numeric value form 1 to 99.

Notes

1. Specifying `SET` by itself prints out all non-standard variables and their values, as well as system variables with non-standard default values. Using `SET ALL` prints out all variables.

2. The output of a `SET` command may be diverted into a new file or appended to an existing file by specifying `INTO` or `ONTO`, respectively, and a filename.

3. Using `FROM file` allows the setting of variables from an operating system file which contains the variable assignments.

4. Variables, including alphanumeric variables, may be assigned values using `SET variable TO value`. If the variable already exists, its value is changed to the new value. If there are any blanks, tabs, periods, etc. in the alphanumeric value, it must be enclosed in quotes.

5. The numeric variables 1 through 99 may be assigned values in order using `SET value { , value}`. These values can then be passed to command files as parameters. The values are assigned in order to the variables; that is, the first value can be referenced as $1, the second as $2, and so on. Unlike the alphanumeric variables, commands to set the numeric variables are not cumulative. Each `SET` value command unsets any numeric variables which had previously been set.

6. Issuing a `RUN FROM file` command that contains `SET` commands will change the Empress environment.

7. Changing the value of inherited UNIX environment variables does not affect the UNIX environment.

8. The `SHOW` command shows the value of variables and the `UNSET` command unsets variables.

Privileges Required

  None.

Example

1. Set Empress Variables from a File

   If you have a file called `msvars` containing:

          MSQLCMDSAVE=20
          MSQLSELCOLSEP="|"
          l=loans
          p=personnel

   then the command:

          SET FROM msvars;

   will set the Empress system variables `MSQLCMDSAVE` and `MSQLSELCOLSEP` to `20`, and `|` , respectively, and the alphanumeric variables `l` and `p` to `loans` and `personnel`.

2. Set an Alphanumeric Variable

   This example shows how to set a variable called `p` and then uses its value in a command:

          SET p TO personnel;
          SELECT FROM $p;

   this produces the following result:

          number     name          phone             credit_limit

          10         Kilroy        426-9681          $500.00
          5          Mosca         426-9681          $750.00
          17         Wladislaw     811-5110           $10.00
          3          Jones         667-2951          $500.00
          8          Peterson      978-6060          $250.00
          4          Scarlatti     961-7363          $100.00
          9          Jordan        964-3335          $250.00

3. Set a Frequently Used Command to a Variable

   Frequently used commands may also be set as variables. For example, you might set a variable `getloans` to `SELECT FROM loans;`:

          SET getloans TO "SELECT * FROM loans;";

   Note the quotes around `SELECT * FROM loans;` they are necessary because this phrase has spaces in it. Now, simply typing:

          $getloans

   gives exactly the same effect as the whole command `SELECT * FROM loans;`.

4. Examples on Setting Numeric Variables

   The command:

          SET personnel;

   This command will set `$1` to `personnel`. Numeric variable settings are not cumulative, for example, the two commands:

          SET personnel;
          SET loans;

   do not set `$1` to `personnel` and `$2` to `loans`; instead set `$1` to `personnel` and reset `$1` to `loans`.

   Similarly,

          SET loans, personnel;
          SET Jones;

   the first command sets `$1` to `loans` and `$2` to `personnel`. The second command reset `$1` to `Jones` and unset `$2`.

   Although they can be used in ordinary queries just like the alphanumeric variables, the numeric variables are typically used with a `RUN` command. Any numeric variables which have been set previously are unset when a `RUN` command is entered, and must be explicitly reset in the `RUN` command if they are to be used in that command.

   The two commands:

          SET personnel;
          SELECT * FROM $1;

   are equivalent to the single command:

          SELECT * FROM personnel;

   and `$1` will continue to have the value `personnel` until another `SET VALUE` command is entered, or any `RUN` command (even the `RUN` after an `EDIT`) is entered.

   A powerful way to use numeric variables is in connection with prepared files of commands. Consider a file called `jnsloans` which contains:

          SELECT * FROM loans
             WHERE name = 'Jones' AND amount > 200;

   Whenever you wish to see the large loans Jones has outstanding, you can use the command:

          RUN FROM 'jnsloans';

   This is useful but limited; to have the same convenience for all employees, you would need half a dozen different files, one for each employee. If you are also interested in small loans, you would need another complete set of files as well.

   Now, consider instead a file called `fndloans` which contains:

          SELECT * FROM loans
             WHERE name = '$1' AND amount RANGE $2 TO $3;

   Here, `$1`, `$2` and `$3` are parameters. Values for the parameters must be passed to the file when it is used. Commands of the form:

          RUN FROM 'fndloans' SET Jones, 200, 5000;

   will find loans between `$200` and `$5,000`. In the file, `$1` is replaced by `Jones`, `$2` is replaced by `200`, and `$3` by `5000` when the command is run.

   To see small loans by Kilroy, you can use:

          RUN FROM "fndloans" SET Kilroy, 0, 200;

   This kind of prepared file is far more general and of considerably more use, when combined with the numeric variables, than the more specific files.

## 7.13 SHOW

The `SHOW` command shows the value of variables that are set.

Syntax

      SHOW variable {, variable};

  where:

  `variable` | is an Empress variable.

Note

  This command will show the value of any Empress system variable and any variable you have set with the `SET` command. If the variable is not set an error message is printed.

Privileges Required

  None.

Example

  Example of `SHOW` command:

         SHOW MSQLPROMPT1, MSQLPROMPT2;

  It produces the following result:

         MSQLPROMPT1=*
         MSQLPROMPT2=.

## 7.14 STORE

Any Empress command in the history list may be stored in an operating system file by the `STORE` command.

Syntax

  `STORE [number] [` | `|INTO|] file; |ONTO|`

  where:

  `number` | is the number of a command in the history list.

Notes

1. If no number is specified, the latest command is stored. A command number may be found by a `RECALL ALL` command.

2. When command is stored into a file, the `INTO` option is the default which will overwrite the file. To append the output to an existing file, use the `ONTO` option. Both will create a file when necessary.

Example

With the following history list:

       * RECALL ALL;
          8 DISPLAY DB;
          9 SELECT * FROM loans;
         10 UPDATE loans
            SET date TO '28 jun 1990'
            WHERE date = '27 jun 1990';
         11 SELECT * FROM loans WHERE date MATCH "*jun*";
         12 !date

the following command will store `SELECT * FROM loans;` into a file name `loans_file`:

       STORE 9 INTO loans_file;