First published in the Washington Area Informix User Group Newsletter
WAIUG - Volume 3, No. 4 - July 1993
How often have you tired of retyping the same column names repeatedly in vi? One solution is to save the column names in a file and read them in as needed. However, vi has the capability to insert into a document the results of an operating system command. With a little work with shell scripts and awk, you can create a command that will get the columns names from any table in any database, and insert them in to your current document. And with a little more work, you can add formatting like commas, datatypes or the 4gl format of "variablename like tablename.columnname".
To insert the results of a unix command on the current line in vi use "!!command". For example, start vi and type "!!ls". This inserts, the names of the files in the current director into your document. To run the Informix sql info command for the item table in the stores database, and insert the results in the current document you would do the following:
1. Create an sql script called itemsinfo.sql with one line as follows:
info columns for items;
2. Load vi and type the following:
!!isql stores itemsinfo
3. This runs the sql script and inserts the results in the current document.
The following shell script is one I have created to get the column names from a database for sql, esql/c, 4gl programs. As long as the script getcol is in my path I can get columns names from any table. Type in the script, put it in your path and within vi type "!!getcol databasename tablename". I use awk to format the output so it's more useful. The script can also be run from unix command line and the output will display on standard out.
#############################################################################
# PROGRAM: getcol - this is a shell/awk script to get the columns from
# an Informix database in a format ready to use in a sql
# query, a 4gl program etc...
# In vi, put the cursor on a blank line where you want to
# insert the columns and type !!getcol database columns. The
# columns will be inserted into the current file.
#
# USAGE: getcol database table [-d\-l]
# -d displays as column data type
# -l displays as column like table.column
# The default displays column names only
#
# AUTHOR: Lester B. Knutsen
# Advanced DataTools Corporation
#
# Copyright (c) 1993, Advanced DataTools Corporation, All rights reserved.
#############################################################################
# function to display usage error message and exit
usage() {
echo "usage: $1 database table [-d|-l]"
echo "\t-d: column data type\n\t-l: column like table.column" ; exit 1
}
#############################################################################
# test for correct number of arguments - call usage function if incorrect
[ $# -lt 2 ] || [ $# -gt 3 ] && { usage $0
}
# test for valid third argument ( -l or -d ) - call usage function if
incorrect
case $3 in
-d|-l|"") ;;
*) usage $0 ;;
esac
#############################################################################
# check for dbaccess or isql
SQL=isql; [ -f $INFORMIXDIR/bin/dbaccess ] && SQL=dbaccess
# use the info columns command to get the data from informix and pipe to awk
# everything within the { } is excuted and piped to awk
{ echo $3 $2; $SQL $1 - <1 && $1 > "" && $1 != "Column" && $1 != "Error" && $1 != "Near" {
# add the comma and a newline to the end of the last line
if ( outcnt >= 1 ) printf(",\n")
# option -d print column and data type
if ( opt == "-d" ) printf("%-18s\t%s",$1 , $2)
# option -l print column like table.column
else if ( opt == "-l" ) printf("%-18s\tlike %s.%s",$1 , tab, $1 )
# no option print column name only
else printf("%s",$1)
outcnt++
}
# print a newline at the end
END { print "\n" } '
#############################################################################