A tool to get column names in vi
A tool to get Informix database column names within vi
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. 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.
The following shell script (getcol) is a tool I created to get the column
names from any Informix database into vi for developing sql, esql/c,
and 4gl programs. To use the script in vi type:
!!getcol databasename tablename
I use awk to format the output so it's more useful. There are two options,
-d displays as column data type and -l displays as column like table.column.
The script can also be run from unix command line.
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Providing Informix Database Tools and Consulting #
#############################################################################
-------------------------getcol-------------------------------------------
:
#############################################################################
# PROGRAM: getcol - this will get the columns from an Informix database
# In vi, put the cursor on a blank line where you want to
# insert the columns and type !!getcol database tablename. 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 ( lester@acces.digex.net )
# Advanced DataTools Corporation
# 4510 Maxfield Drive, Annandale, VA 22003
# 703-256-0267
# Copyright (c) 1993, Advanced DataTools Corporation, All rights reserved.
#############################################################################
# function to display usage error 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
[ $# -lt 2 ] || [ $# -gt 3 ] && { usage $0
}
# test for valid third argument ( -l or -d )
case $3 in
-d|-l|"") ;;
*) usage $0 ;;
esac
#############################################################################
# use the info columns command to get the data from informix and pipe to awk
# check for dbaccess or isql
SQL=isql;
[ -f $INFORMIXDIR/bin/dbaccess ] && SQL=dbaccess
{ echo $3 $2; $SQL $1 - <<EOF
info columns for $2;
EOF
} | awk '
#############################################################################
# awk commands to format the data
# set variables
BEGIN { opt = " "; tab = " " ; outcnt = 0 }
# check options from echo $3 $2 statement piped to awk on first line
NR==1 && $1 == "-l" { opt = $1; tab = $2; getline }
NR==1 && $1 == "-d" { opt = $1; getline }
# check lines after first line and format based on options
NR >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" } '
#############################################################################