One of the advantages of UNIX is the power of shell scripts for developing systems. This article is an introduction to using shell scripts with embedded SQL to access your database. I am using the Informix database for these examples, and the Informix SQL command interpreter "dbaccess". However, these examples will also work with Informix "isql" and should work with any database that lets you redirect standard input and output. The basic items we will examine are; redirecting input and output, passing shell variables to SQL, and setting shell variables with the results of SQL commands.
1. Redirecting Input
One way to include SQL commands in a shell script is to redirect standard input to "dbaccess" from within the shell script. The following is an example shell script:
Figure 1.
#!/bin/sh dbaccess - - <<SQLSTMT database stores5; select customer_num, fname, lname, company from customer; SQLSTMT
When dbaccess starts, it expects a database name and sql script name as its arguments, or it will display its menus and prompt you for them. The two dashes "- -" indicate that the database and commands will come from standard input. The "<<" indicates to the shell that standard input is redirected, and that everything between the two statements "SQLSTMT" is to be passed to dbaccess as standard input. This is called a "here document" in shell scripts. The program dbaccess treats these lines as if you had typed them in from the keyboard. This is like typing dbaccess - - <filename where filename is a file with the SQL commands. You do not have to use the words SQLSTMT, but you do need two identical words to mark the beginning and end of input redirection. Running this script will start dbaccess and process SQL commands. The first command will open the stores5 database and the next command will display the name and company of all the customers.
2. Redirecting Output
If you have a large customer table the output will scroll off the screen. Dbaccess sends its output to two standard devices that are normally defined as your terminal. Data goes to standard output, and processing messages to standard error. In the above example the names and companies are sent to standard output and the two messages, "Database selected" and "99 row(s) retrieved" are sent to standard error. These can be redirected to a file by changing line number 3 in the Figure 1 example to:
dbaccess - - >cust.rpt 2>error.log <<SQLSTMT
The first ">" sends standard out (data) to a file "cust.rpt" and the "2>" sends standard error (messages) to a error.log.
What is more useful is to send data to a paging program like "more" and messages to a log file. Figure 2 is an example:
Figure 2.
#!/bin/sh
{
dbaccess - - 2>error.log <<SQLSTMT
database stores5;
select customer_num, fname, lname, company from customer;
SQLSTMT
} | more
The first ">" has been removed and a pair of "{ }" added. The pair of "{ }" instruct the shell to execute the inclosed statements as a group. This is useful to pipe the output to another program like more.
3. Using Shell Variables
Shell variables and prompts can be used with SQL. The following example prompts for a company name, and passes the variable to SQL to be used in the select statement. Entering an "A*" at the prompt would select all companies which name begins with the letter "A".
Figure 3.
#!/bin/sh echo "Enter company name (use * for wildcard matches) to select" echo "Company : \c" read comp dbaccess - - 2>error.log <<SQLSTMT database stores5; select customer_num, fname, lname, company from customer where company matches "$comp"; SQLSTMT
4. Getting Data Into Shell Variables
The results of an SQL command can be inserted into shell variables. The following is a simple example of a mail-merge program, selecting names and companies from a database, and setting shell variables and merging that data with some text. There are better ways to do this with the programming tools that come with a database, but this illustrates the power of embedding SQL in shell scripts.
Figure 4.
#!/bin/sh
today=`date +%m/%d/%y` # get today's date
{
dbaccess - - 2>error.log <<SQLSTMT
database stores5;
output to pipe "pr -t" without headings
select customer_num, fname,lname,company from customer;
SQLSTMT
} | while read line # pipe the output to while read
do
if [ "$line" ] # check if line is not NULL
then
# First parse the line into words/variables using set
set $line# assign the line to positional variables
name="$2 $3"# get the second and third variable for name
# company name may include spaces, $4 is only the first word
# so we discard the first 3 positions and assign the
# rest of the line to the comp variable
shift 3# discard the first three variables
comp="$*"# let all remaining variables = the company
## Start of simple form letter`
echo "Date: $today"
echo "To: $name"
echo " $comp"
echo "Thank you for your business"
fi
done
Figure 4. Example output
Date: 11/15/93 To: Frank Lessor Phoenix University Thank you for your business
In this example the output is sent to a while loop. The while loop reads each line of output until its done. Each line is broken apart into words by the set command. The first word is assigned $1, the second $2. This gets the name of the person. The company name is more difficult because it may contain spaces. The name "Big Company" would be broken into to two variables. The command "shift 3" discards the first three variables and what was $4 becomes $1. All remaining variables are assigned to the company name with "comp=$*".
This has just touched the surface of what can be done with embedded SQL in shell scripts. It does have its limits, but a lot can be accomplished. Complex applications like billing systems and scheduling systems can be developed using SQL in shell scripts.