Run SQL from the Command Line

Although isql is a menu-driven program, it follows the standard UNIX I/O conventions. This allows input and output to and from I-SQL to be redirected and to come from or go to UNIX devices such as terminals and files. It is also possible to pass parameters to isql from the command line or from files or scripts.

Note that in all of the included scripts, we are assuming that you are using isql and not dbaccess. If you use dbaccess instead of isql, just replace all instances of isql with dbaccess and the programs should work the same.

As example, take the sample script dbcat:

#!/bin/csh
echo "select * from $2;" I $INFORMIXDIR/bin/isql $1

The syntax is dbcat database_name table_name. The database_name is passed as parameter $1 and the table_name is passed as parameter $2. This concept can be extended in the C-Shell by using aliases to provide complete SQL services directly from the command line. To do this, place the following aliases in a file called aliases.rc.

alias alter "echo 'alter !:*;' | $INFORMIXDIR/bin/isql $DB"
alias create "echo 'create !:*;' | $INFORMIXDIR/bin/isql $DB"
alias delete "echo 'delete !:*;' | $INFORMIXDIR/bin/isql $DB"
alias drop "echo 'drop !:*;' | $INFORMIXDIR/bin/isql $DB"
alias grant "echo 'grant !:*' |  $INFORMIXDIR/bin/isql $DB"
alias insert "echo 'insert !:*;' | $$INFORMIXDIR/bin/isql $DB"
alias rename "echo 'rename !:*;' | $INFORMIXDIR/bin/isql $DB"
alias revoke "echo 'revoke !:*;' | $INFORMIXDIR/bin/isql $DB"
alias select "echo 'select !:*;' | $INFORMIXDIR/bin/isql $DB"
alias update "echo 'update !:*;' | $INFORMIXDIR/bin/isql $DB"
alias info "echo 'info !:*;' |   | $INFORMIXDIR/bin/isql $DB"

These aliases receive the name of the requested database through the $DB environmental variable. The aliases are defined when you run the command source aliases.rc. Each time this file is sourced, the aliases are redefined. To make these aliases point to another database, reset the $DB environmental variable and then run source aliases.rc. If you need to access only one database, or if you work predominantly with one database, you may want to set the $DB environmental variable in your .cshrc file and add the contents of aliases.rc to the end of your .cshrc file.

The thing that makes these aliases work is the !:* in the first half of the aliases. It takes the first word of the command, either alter, create, delete, etc., and turns it into an alias. Whenever you type the first word, that is interpreted as an alias. The rest of the command line is indicated by the !:* structure. It is passed to the echo statement that is sent to isql by the magic !:* clause. This construct works only in the C-Shell.

Another way to pass parameters to a script running isql is to use a UNIX here document. This type of script instructs the shell to take the command and the input from the same source. For example, look at the sample script, get_accounting_data:

#!/bin/csh
$INFORMIXDIR/bin/isql $1 << EOF
SELECT  * from accounts_payable
WHERE  date = "$2" and
code = "S3";
EOF

Running this script would allow you to pass a database name as $1 and date and code as $2 and $3. In the here document, the key is the << construct. This means that the shell is to use the code immediately following the << as a marker delimiting input to the script. Using "EOF" as a marker, the first "EOF" indicates the beginning of the input.

The entire select statement, up to the semicolon that immediately precedes the last "EOF" is then taken as input to the isql command. The handy part is that the shell first interprets any shell parameters before it evaluates the input. This is what allows you to pass the parameters.