# CHAPTER 4: Interfacing with the Operating System

## 4.1 Introduction

This chapter deals with the relationship between Empress Query Language and the operating system. Using the Query Language, you can:

- Submit queries from the operating system.
- Read data from the operating system.
- Send output to the operating system.
- Issue operating system commands.

Empress utilities which run from the operating system (such as backup/recovery programs, the database loader, import/export utilities and the warm restart utility) are described in the Empress: Database Administrator's Guide .

## 4.2 Issuing Operating System Commands

The `DO` command takes one or more operating system commands, enclosed in quotes, executes them, and returns control to Empress. The operating system escape, (!), can be used to execute an operating system command.

The general form of the `DO` command is:

       do 'os_command';

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

       do 'ls';

To see the contents of a file called `staff` in your current directory, use:

       do 'more staff';

When the command finishes, it returns control to Empress.

## 4.3 Submitting Queries from the Operating System

When you have a file containing Empress SQL commands, `RUN` or `START` followed by the file name tells Empress to execute Query Language commands contained in that file. The general form for submitting queries from the Operating System file is:

|  |  | `|RUN |START` | `| [FROM] file_name [SET parameter {, parameter }]; |`

where the `parameters` are values for numeric variables (see the section on Numeric Variables under the `SET` command in the Empress SQL: Reference for a description of how parameters are used). The command:

       RUN FROM 'phonelt';

will read and execute the file `phonelt` which contains Empress SQL statements to produce the required list.

The next example shows the use of `RUN FROM file_name SET value`. This use of `SET` is described more fully in the section on Numeric Variables under the `SET` command in the Empress SQL: Reference. Suppose you have a file called `findlns` containing the command:

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

To find loans for Mosca between $100 and $200, use:

       RUN FROM 'findlns' SET Mosca, 100, 200;

which is exactly equivalent to the command:

       SELECT FROM loans WHERE name = 'Mosca'
          AND amount RANGE 100 TO 200;

and produces:

       name                                date        amount

       Mosca                   2 February  1992       $150.00
       Mosca                   4 May       1992       $200.00

## 4.4 Reading Data from the Operating System

Empress allows loading of data from the operating system files into a table as well as updating of a table from a file. A table can also be created from a file containing the table definition.

Attribute values in the operating system file may be separated by new lines or a special separator string which is defined by the Empress system variable `MSVALSEP`. The default is normally <Ctrl+V> or octal 26. Records are separated by new lines. Data may be split over more than one line by masking a new line with the line continuation string defined by `MSLINECONT`. The default is normally a backslash ( \ ).

### 4.4.1 Inserting Records from a File

When you have a file containing data that you want to use in your database, the syntax for inserting records from an operating system file is:

|  |  | `INSERT [INTO] table [` | `|(*) |(attr {, attr }) |SET * |SET attr {, attr }` | `|] | | |` | `|FROM |DIRECT_FROM` | `| file_name; |`

For example, suppose you have a file called `employees` containing the following data:

       22
       Williams
       743-0772
       50023
       Matthews
       704-9221
       250

To load the contents of this file into the table `personnel`, use:

       INSERT INTO personnel FROM 'employees';

Empress will list the total number of records loaded. In the event of an error, Empress will print the offending line number.

Files of data in a form suitable for loading into a table may be produced by selecting data from other tables, using the keyword `DUMP` in the `SELECT` command.

### 4.4.2 Updating Records from a File

The syntax for updating records from an operating system file is:

|  |  | `UPDATE table [` | `|(*) |(attr {, attr}) |SET * |SET attr {, attr}` | `|] | | |` | `|FROM |DIRECT_FROM` | `| file_name [WHERE clause]; |`

To upgrade all credit limits, you can create a file called `credits` containing:

       550
       800
       100
       550
       300
       150

       300
       550
       300

You can then update the `personnel` table from this file with the command:

       UPDATE personnel SET credit_limit FROM 'credits';

### 4.4.3 Creating Tables from a File

A table may also be created from a file of data. The syntax for this is:

       CREATE table_name FROM file_name;

The file must be in the format produced by the `DISPLAY TABLE` command with `DUMP` specified. With `MSVALSEP` set to "|", a dumped display of the `personnel` table looks like this:

       repairs|personnel|number|integer|integer|0|0|0|n
       repairs|personnel|name|char|char|25|1|0|n
       repairs|personnel|phone|char|char|15|1|0|n
       repairs|personnel|credit_limit|decimal|dollar|6|1|0|n

If you have named this dump file `persdump`, then to create a table called `newpersonnel` from it, use:

       CREATE newpersonnel FROM 'persdump';

Error messages are printed out if the `CREATE` fails.

### 4.4.4 Reading From Pipes

In any Empress command where the clause `FROM file_name` is used, the `file_name` may be replaced by `!os_command`. The `!os_command` is executed and its output used instead of an operating system file.

For Unix:

  For example, suppose you wish to enter some comments into the `customers` table, and have a file containing the comments ready to be run through `nroff`. If the file of comments is called `comments.n`, you can `nroff` it and insert it into the table using the command:

         INSERT INTO customers SET COMMENTS DIRECT_FROM
              'nroff comments.n';

  To try out this feature of reading from pipes you can create a table with a `CHAR` attribute and insert the output of `ls`:

         CREATE tab1 (attr1 CHAR);
         INSERT tab1 FROM '!ls';

For Windows NT:

  You can create a table with a `CHAR(40,1)` attribute and insert the output of `dir`:

         CREATE tab1 (attr1 CHAR(40,1));
         INSERT tab1 FROM '!dir';

## 4.5 Sending Output to the Operating System

### 4.5.1 Redirecting Output to a File

The `INTO` or `ONTO` keywords followed by a filename put the results of the Empress command in the named file. If the file does not already exist, `INTO` and `ONTO` behave in the same manner, creating the file and writing the specified data to it. If the file does already exist, `INTO` replaces the data in it with the specified data, while `ONTO` appends the new data to the file. A file so created is an ordinary operating system file. It may be printed on the terminal or line printer, edited, copied, mailed to another user, etc.

To put the results of selecting all of Mosca's outstanding loans into a file called `moscloan`, use:

       SELECT amount FROM loans INTO 'moscloan'
            WHERE name = 'Mosca';

To put a list of all tables in the database into the file `dblist`, use:

       DISPLAY DATABASE INTO 'dblist';

To add the structure of the `personnel` table to the end of the file you just created, use:

       DISPLAY personnel ONTO 'dblist';

using `ONTO` to append to the file's contents rather than `INTO` to replace them.

To store the last Query Language command into the file `lastql`, use:

       STORE INTO 'lastql';

### 4.5.2 Displaying Output by Pages

In any Empress command where the clause `INTO` file may be used, the keywords `PAGER` may be used instead.

`PAGER` sends the output of the command to a paging program which displays one screenful of output lines at a time, and prompts the user for the next screenful. The operating system utility program used for paging is defined by the Empress system variable `MSPAGER`. The default for `MSPAGER` under UNIX is "more". The default for `MSPAGER` under Windows NT is "more <".

Paging mode can be set automatically by setting the Empress system variable `MSQLAUTOPAGE` to anything; this in effect appends `INTO PAGER` to every command. `MSQLAUTOPAGE` is not set by default, so Empress output is not normally paged.

To set auto paging mode, use:

       SET MSQLAUTOPAGE TO x;

To unset it, use:

       UNSET MSQLAUTOPAGE;

For example, you can view the output of the command one screenful at a time, then send the same output to a printer, with the commands:

       SELECT FROM loans INTO PAGER;

The loans table is displayed 23 lines at a time

### 4.5.3 Sending Output to the Printer

In any Empress command where the clause `INTO` file may be used, the keywords `PRINTER` may be used instead.

`PRINTER` directs the output of the command to a printer via the operating system utility program defined by the Empress system variable `MSPRINTER`. The default for `MSPRINTER` under UNIX is "`pr | lpr`". The default for `MSPRINTER` under Windows NT is "`print`".

If there is no printer on the system, the command will execute but nothing will happen.

For example, you can send the output to a printer, with the commands:

       SELECT FROM loans INTO PRINTER;

### 4.5.4 Writing To Pipes

In any Empress command where the clause `INTO` file (or `ONTO` file ) may be used, file can be replaced by `!os_command`. The output from the Empress command will be used as input to the operating system command.

For example, you can send the output from a `SELECT` command to the UNIX utility `pr`, which paginates its input and prints a date-and-time header for each page, with the command:

       SELECT FROM loans INTO '!pr';

### 4.5.5 Output in Dump Format

The `DUMP` keyword produces a form of output that Empress can use for loading, updating or creating tables.

Using `DUMP` in a command results in a compressed form of output in which all titles, padding, and extra blank lines are suppressed. In a `SELECT`, `DUMP` output consists of records whose attribute values are separated by a separator string specified by the Empress variable `MSVALSEP`. The normal default for this string is <Ctrl+V> or octal 26. Each record is placed on a new line. This output is suitable for use in an `INSERT FROM file_name` command.

In a `DISPLAY DATABASE` command, `DUMP` causes just the table names to be listed, with no heading giving the database name. In a `DISPLAY TABLE` command, `DUMP` output contains much more information about the structure of a table than is normally shown. Attribute definitions appear on separate lines, with the information fields separated by the string defined by `MSVALSEP`. This produces a file suitable for use in a `CREATE TABLE FROM file_name` command.

If you set `MSVALSEP` to '|', then a dump of the personnel number and name of all employees with the command:

       SELECT number, name FROM personnel DUMP;

produces:

       10 |Kilroy
        3 |Jones
        5 |Mosca
       17 |Wladislaw
        8 |Petersen
        4 |Scarlatti
        9 |Jordan

Generally, when the `DUMP` keyword is used, the results are put into a file as in the next example which dumps the personnel number and name into a file called `numbers`.

       SELECT name, number
          FROM personnel
          DUMP INTO 'numbers';

To dump the definition of the `loans` table into a file called `loandefs`, use:

       DISPLAY TABLE loans DUMP
          INTO 'loandefs';

This file can then be used to create a similar table.

### 4.5.6 Generating a Data File for Empress Report Writer

If the `REPORT` option is used with the `DUMP` option, it prefaces the `DUMP` output with information about the attributes selected and their data types. Output produced with the `REPORT` option may be placed in a file to be accessed by Empress Report Writer. This output format is necessary when the Report Writer is invoked from the operating system with the `-f` option (refer to the Empress: Report Writer manual for details on invoking Empress Report Writer).

If the Empress separator string is changed to a visible character:

       SET MSVALSEP TO "|";

then the command:

        SELECT FROM personnel REPORT DUMP;

produces the following output:

       repairs|personnel|number|integer|integer|0|0|0|n
       repairs|personnel|name|char|char|25|1|0|n
       repairs|personnel|phone|char|char|15|1|0|n
       repairs|personnel|credit_limit|decimal|dollar|6|1|0|n

       10 |Kilroy    |426-9681 |500.00
        5 |Mosca     |544-2243 |750.00
       17 |Wladislaw |723-6073 | 50.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