The UNIX Shell Interface allows a shell program to execute an Empress Query Language command in the UNIX environment. There are two commands you can use to execute Empress Query Language commands from the UNIX shell:
The Shell Interface allows rapid packaging of prototype database management interactions. It allows existing UNIX programs, C routines, and other shell programs to be invoked by simple commands, encouraging the rapid design and development of custom interfaces to the database management system.
The command empcmd is used to have the UNIX shell execute one Empress SQL command. The syntax of the command is as follows:
empcmd database "command"
where:
| database | is the name of the database directory. |
| command | is the Empress SQL command to be executed. |
Example 1: Retrieve an Attribute Values from a Table
The following command is issued from the UNIX prompt which lists all employees from the personnel table in the database repairs:
empcmd repairs "SELECT name FROM personnel"
The above command executes the Empress SQL command "SELECT name FROM personnel" on the database called repairs, without the need to invoke empsql directly. Once the command has been executed, control returns to the UNIX shell.
The above empcmd command can also be stored in a command text file called names. Once this is done, typing in names at the UNIX prompt will produce the list of employee names.
Example 2: Passing Argument to the Command File
The following command, stored in an executable file called phone, produces the telephone number of the employee given as its argument.
empcmd repairs "select phone from personnel dump
where name = '$1'"
The dump keyword has been included in the empcmd command to suppress printing the heading phone. To find Jones' phone number, type in:
phone Jones
and get the answer:
667-2951
Example 3: Command File with Error Checking
A more fool proof version of the phone program would check to see that one and only one name has been supplied, and give a suitable error message if this is not so. The improved version is:
USAGE = "Usage: phone employee's name"
case "$#" in
1)
mscmd repairs "select phone from
personnel
dump where name = '$1'"
;;
*)
echo $USAGE
exit 1
;;
esac
Example 4: Interactive Command File
If a more interactive implementation is preferred, another version of the phone program can be written as follows:
echo "
Find phone number given employee's name.
To stop, enter '.q'
"
NAME =
NULL =
: reprompt for name if no name is given
while true
do
echo -n "Employee name: "
read NAME
case $NAME in
$NULL)
continue
;;
.q)
exit 0
;;
esac
echo ""
empcmd repairs "select phone from personnel
dump where name = '$NAME'"
echo ""
done
This version also has the advantage of more than one phone number being retrieved at a time.
A sample dialogue for running the interactive phone program is given below. A percent sign (%) is to indicate UNIX operating system prompt. User responses are shown in boldface.
% phone Find phone number given employee's name. To stop enter '.q' Employee name: Jones 667-2951 Employee name: Petersen 978-6060 Employee name: (You press <Return> by mistake) Employee name: Mosca 544-2243 Employee name: .q %
Example 5: A Simple Report
Another example of a small shell program with tabular output is the following, called employees, which retrieves the names and phone numbers for all employees, counts them, and prints them with a heading:
: PRINT LIST OF CURRENT EMPLOYEES echo "Current Employees" echo ""empcmd repairs "select name, phone from personnel" EMPLOYEES='empcmd repairs "select count(*) from personnel dump"' echo "" echo "Total number of Employees = $EMPLOYEES"
Typing in employees produces the following output:
Current Employees name phone Kilroy 426-9681 Jones 667-2951 Mosca 544-2243 Wladislaw 723-6073 Peterson 978-6060 Scarlatti 961-7363 Jordan 964-3335 Total number of Employees = 7
Example 6: More Complex Report
As a further example, a shell program called loans which takes a number of employee names as arguments and prints out details of all loans currently outstanding for each, the total amount for each, and the grand total over all the specified employees, is:
: PRINT details, total, and grand total of loans
: outstanding to the specified employees
DATABASE=repairs
USAGE="Usage: $0 Employee_name ..."
case "$#" in
0)
echo $USAGE
exit 1
;;
esac
for I
do
echo "Loans currently outstanding to $I:"
echo ""
empcmd $DATABASE "select date, amount from loans
where name = '$I'"
echo ""
echo -n "Total amount owing for $I: "
empcmd $DATABASE "select sum (amount) from loans
dump where name = '$I'"
echo ""
echo ""
done
: construct a WHERE clause with all the names
: given as arguments, joined by ORs
clause="where name = '$1'"
shift
for I
do
CLAUSE="$clause or name = '$I'"
done
echo -n "Total amount outstanding over all of the above: "
empcmd $DATABASE "select sum (amount) from loans dump $CLAUSE"
To find the loans outstanding for Jones and Mosca, type in:
loans Jones Mosca
which gives the following output:
Loans currently outstanding to Jones:
date amount
7 February 1992 $33.95
3 April 1992 $25.00
12 August 1992 $300.00
Total amount owing for Jones: 358.95
Loans currently outstanding to Mosca:
date amount
2 February 1992 $150.00
4 May 1992 $200.00
Total amount owing for Mosca: 350.00
Total amount outstanding over all of the above: 708.95
The command empbatch allows you to submit a batch of Empress SQL commands from the operating system. The syntax of empatch is as follows:
empbatch database
where:
| database | is the name of the database directory. |
The command empbatch works in interactive mode; it is similar to being in Empress SQL but there are no prompts. A semicolon (;) is used to end each Query Language command, and a command may span several lines. The program exits on a syntax error.
For example if you type in from UNIX:
empbatch repairs
the cursor will wait on the next line for a command. Typing in a command brings an immediate response:
SELECT * FROM loans
WHERE name = "Jones";
will produce:
name date amount Jones 7 February 1992 $33.95 Jones 3 April 1992 $25.00 Jones 12 August 1992 $300.00
<Ctrl+D> or the <Rubout> or <Interrupt> key will return you to the operating system.
You can store a batch of Empress SQL commands in a UNIX command file, for example, a file called queries contains the following:
empbatch repairs << '}'
set MSPAGER to 'more -c';
set MSQLAUTOPAGE to true;
select * from loans where name = "Jones";
}
Then submit the batch by typing:
queries
The queries uses input redirection to tell UNIX that input for the empbatch command follows, in this case, until a "}" character is read.