CHAPTER 4: Interfacing with the Operating System

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 .

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.

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:

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

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 ( \ ).

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:

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.

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

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

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.

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

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

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

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;

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

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.

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