In-Line Input or Bust

Leor Zolman

All standard UNIX shells support a nifty method for embedding standard input data within the very shell script that reads that data. The syntax for using such in-line input is as follows:

command-name parameters <<-END
input line 1
input line 2
.
.
.
input line n
END

With this form of the construct, all text between the END markers (any text pattern may be used in place of END) is fed to the standard input of the command-name command. The hyphen before the first END says to strip all whitespace from the beginning of each line of the input data; this allows the source text to be formatted with arbitrary indentation for clarity, even when such indentation might confuse the command that will actually read the input.

To see the technique in action, submit the following command to your shell:

wc <<-STOP
this is
a
test
STOP

The system should respond with:

3  4  15

So far, this is all basic shell stuff. Any command that accepts data on its standard input stream can be supplied with in-line data using this technique. However, what do you do with a program that does not accept any input from the standard input stream, but only knows how to take its input from a physical file named on the program's command line? The only way to feed literal input to such a program is to manually create a temporary file containing the required text, run the program with the name of that file as a parameter, and then remove the temporary file after the program has finished running.

Even when you can be certain that only one instance of the script that drives such an application is ever running at one time, the temporary file method is bad enough . . . but when multiple instances of such a script must be able to run simultaneously, then you also have to guard against temporary file name collisions. To borrow a line from The Color of Money: "It just keeps getting worse and worse, doesn't it?"

I ran into this dilemma while working with the Informix database system. R&D had just upgraded from the old Informix 3.3 to the new SQL-based version 4 for our Xenix system. While the old Informix informer command supported in-line queries on the standard input stream, the new isql command does not. In the old shell scripts, I had gotten very used to doing things like unloading database tables with one compact command, e.g.:

informer accounts <<END
unload ascii customers to "customers.txt"
bye
END

To do this with isql, write the command sequence

database accounts;
unload to "customers.txt"
select * from customers;

into a file, and then run isql, specifying the name of that file on the command line. Considering how often we perform operations such as this in our shell scripts, a way to specify the SQL commands in-line was sorely needed.

Listing 1 shows the runsql/runsql2 script I wrote to support such in-line SQL commands. While this particular script is specific to the Informix isql command, the technique may easily be adapted to any other application. This script has two names, runsql and runsql2, to support two distinct modes of operation. When invoked as runsql, the stdout and stderr streams are discarded (directed to /dev/null, the appropriate place for most of the useless status information generated by the isql command). When invoked as runsql2, stdout and stderr are passed along so they may be recorded, if desired. Any references to runsql below apply to runsql2 as well.

Both forms of the command take one mandatory command-line parameter, the name of the database to be operated on. If a second parameter is specified, it is the name of a text file from which to read the SQL commands. When the second parameter is omitted, the text is read from the standard input. Most of the time, we use the standard input method. The filename method, being essentially the same as invoking isql directly, is there mainly so the user need not memorize the precise argument flags needed on the isql command line.

runsql works by creating a uniquely named temporary file (using the tmpname program shown in Listing 2), copying the input text into that file, and submitting it to isql. If you supply a filename to runsql instead of in-line text, then that file is passed along to isql only if it ends with the .sql extension; without such an extension, isql would refuse to process it. Thus, if you supply a filename and it does not end in .sql, then runsql copies your file to a temporary file whose name does end in .sql and submits the temporary file to isql for processing.

Since all SQL scripts processed by isql must begin with a database command, runsql automatically inserts such a command (using the database name supplied on the command line) at the beginning of the temporary SQL script file it creates. If you use the filename version of runsql, then your file is passed along to isql without modification and you must therefore include an explicit database command.

Using runsql is just like using the old Informix informer command, except of course for isql's up-to-date SQL query syntax. Thus, to run the example SQL command shown earlier, you'd simply write:

runsql accounts <<END
unload to "customers.txt"
select * from customers;
END

I've found using runsql invaluable for simplifying the source code for applications intermixing small, on-the-fly SQL queries with various file-manipulating operations. One e-mail correspondent of mine has already indicated that the technique could be adapted to streamline ESQL operations; there are probably other applications out there that would benefit from such an in-line input utility as well.

About the Author

Leor Zolman wrote BDS C, the first C compiler targeted exclusively for personal computers. He is currently a system administrator and software developer for R&D Publications, Inc., and columnist for both The C Users Journal and Windows/DOS Developer's Journal. Leor's first book, Illustrated C, has just been published by R&D. He may be reached in care of R&D Publications, Inc., or via net E-mail as leor@rdpub.com ("...!uunet!rdpub!leor").