Example Code: Interactive SELECT using sh & awk
Hoping the below will be one or more of:
A) Entertaining
B) Inspirational
C) Useful
Also hoping not to humiliate myself :^)
: "select.sh by Dan Martin (DRM/CTG) Mon Jun 10 07:52:39 CDT 1996"
# The Almost-Ultimate Data-Selector squipt.
# Written & tested on SCO UNIX 3.2v4.2 - your awk and /bin/sh may vary
# Temp Files
f1=/tmp/f1x$$ # List of Tables and their Columns
f2=/tmp/f2x$$ # List of Tables only
f3=/tmp/f3x$$ # Scratch File
f4=/tmp/f4x$$ # Scratch File
f5=/tmp/f5x$$ # File of User-Selected columns
sql=/tmp/sql$$ # SQL SELECT Command (as written by awk)
yak=/tmp/yak$$ # Users info to be mailed to him/her/alt
out="/tmp/`logname`.out" # Will contain Users SELECTed data
>$out
chmod 666 $out
eraser() {
rm -f $f1 $f2 $f3 $f4 $f5 $yak $sql
} # EOF eraser()
phoo() { # Required Argument: $1 = A User-Abuse blabbery
echo "\\n $RN $1 $RO"
echo "\\n This Program (\\"$0\\") has been Terminated.\\n"
eraser
exit 0
} # EOF phoo()
trap 'echo "\\n PROGRAM \\"$0\\" CANCELLED!!\\n"; eraser; sleep 2; exit 1' 2
clear
# /u/DB/DAT_otr/data_otr.dbs
# /u2/DB/DAT_pms/inpo_pms.dbs
cat << PAW
=======================< INFORMIX DATA-SELECTOR >=======================
The following Informix Databases are available:
PAW
##################################################################
## Substitute all of your own *.dbs that you want to be listed: ##
##################################################################
cat << BOX | awk '{printf("%d^%s^\\n", NR, $0)}' >$f1
/u/DB/DAT_bea/inpo_bea.dbs
/u2/DB/DAT_dod/inpo_dod.dbs
/u2/DB/DAT_eni/inpo_eni.dbs
/u/DB/DAT_ftd/inpo_ftd.dbs
/u2/DB/DAT_law/inpo_law.dbs
/u/DB/DAT_pol/inpo_pol.dbs
/u2/DB/DAT_ter/inpo_ter.dbs
/u/spims2/ifxdir/to_do.dbs
BOX
awk -F"^" '{printf(" %2d %27s\\n", $1, $2)}' $f1
# . /bin_drm/coddle # See internal Documentation
# Note: The above contains the getit() function
# which is used profusely herein.
# Immediately below is a simplified getit()
# which will suffice in place of the original:
getit() { # Required argument: A User-Instruction blabbery
ans=''
while [ -z "$ans" ]
do
echo "$1 \\c"
read ans
done
ans=`echo "$ans" | tr "[a-z]" "[A-Z]"` # force UPPER CASE
} # EOF getit()
mxx="`awk 'END {print NR}' $f1`"
echo
getit " Choose a Database NUMBER (1 thru $mxx):" PNO 3
# Did the [ab]User pick a valid #?
>$f3
awk -F"^" "\\$1 == \\"$ans\\" {print \\$2}" $f1 >$f3
[ ! -s "$f3" ] && phoo " Database Number \\"$ans\\" does not exist! "
# cat $f3 # debuggerie
IDB=`awk '{len = length($0); printf("%s\\n", substr($0,1,(len - 4)))}' $f3`
# echo "\\nIDB = \\"$IDB\\"." # debugerrie# eraser; exit 0 # debuggerie
echo '\\n Now Reading Database Structure (takes about two minutes)...\\c'
# dbaccess $IDB - << STOP # she only whimpers
# dbaccess -e $IDB - << STOP 2>&1 | tee /tmp/drm # she's a screamer
dbaccess $IDB - << STOP 2>/dev/null # she's real quiet
CREATE TEMP TABLE colkind (type smallint, column_type char(13));
INSERT INTO colkind (type, column_type) VALUES (0, "character");
INSERT INTO colkind (type, column_type) VALUES (256, "char nonull");
INSERT INTO colkind (type, column_type) VALUES (1, "small integer");
INSERT INTO colkind (type, column_type) VALUES (257, "sm_int nonull");
INSERT INTO colkind (type, column_type) VALUES (2, "integer");
INSERT INTO colkind (type, column_type) VALUES (258, "int nonull");
INSERT INTO colkind (type, column_type) VALUES (3, "float");
INSERT INTO colkind (type, column_type) VALUES (259, "float nonull");
INSERT INTO colkind (type, column_type) VALUES (4, "small float");
INSERT INTO colkind (type, column_type) VALUES (260, "sm_flt nonull");
INSERT INTO colkind (type, column_type) VALUES (5, "decimal");
INSERT INTO colkind (type, column_type) VALUES (261, "dec nonull");
INSERT INTO colkind (type, column_type) VALUES (262, "serial");
INSERT INTO colkind (type, column_type) VALUES (7, "date");
INSERT INTO colkind (type, column_type) VALUES (263, "date nonull");
INSERT INTO colkind (type, column_type) VALUES (8, "money");
INSERT INTO colkind (type, column_type) VALUES (264, "money nonull");
INSERT INTO colkind (type, column_type) VALUES (10, "datetime");
INSERT INTO colkind (type, column_type) VALUES (266, "dttim nonull");
INSERT INTO colkind (type, column_type) VALUES (11, "byte");
INSERT INTO colkind (type, column_type) VALUES (267, "byte nonull");
INSERT INTO colkind (type, column_type) VALUES (12, "text");
INSERT INTO colkind (type, column_type) VALUES (268, "text nonull");
INSERT INTO colkind (type, column_type) VALUES (13, "varchar");
INSERT INTO colkind (type, column_type) VALUES (269, "varch nonull");
INSERT INTO colkind (type, column_type) VALUES (14, "interval");
INSERT INTO colkind (type, column_type) VALUES (270, "intvl nonull");
UPDATE STATISTICS;
UNLOAD TO "$f1" DELIMITER "^"
SELECT syscolumns.tabid, tabname, systables.tabtype,
colno, colname, column_type, collength, systables.nrows
FROM systables, syscolumns, colkind WHERE systables.tabid >= 100 AND {no system tables}
systables.tabtype = "T" AND {no Views}
systables.nrows > 0 AND {no empty Tables}
systables.tabid = syscolumns.tabid AND
syscolumns.coltype = colkind.type
ORDER BY syscolumns.tabid, colno ;
DROP TABLE colkind ;
UNLOAD TO "$f2" DELIMITER "^"
SELECT tabid, tabname
FROM systables
WHERE tabid >= 100 AND {no system tables}
tabtype = "T" AND {no Views}
nrows > 0 {no empty Tables}
ORDER BY tabid ;STOP
clear
cat << FUR
=======================< INFORMIX DATA-SELECTOR >=======================
Database Selected: "$IDB" Files available:
FUR
awk -F"^" '{printf("%d %s\\n", $1, $2)}' "$f2" | pr -4to2 -w77
echo
getit " Choose a File NUMBER:" PNO 3
# Did the [ab]User pick a valid #?
>$f3
awk -F"^" "\\$1 == \\"$ans\\" {print \\$2; exit}" $f1 >$f3
[ ! -s "$f3" ] && phoo " File Number \\"$ans\\" does not exist! "
TAB1=`head -1 $f3`
awk -F"^" "\\$1 == \\"$ans\\" {print \\$8; exit}" $f1 >$f3
nrec=`head -1 $f3`
# echo "GAWWKK_K_K-