From: Daniel Martin Subject: Example Code: Interactive SELECT using sh & awk Date: Thu, 13 Jun 1996 19:20:11 GMT Newsgroups: comp.databases.informix 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-k-k-k...\c"; read lksjfg # debuggerie clear cat << FUR =======================< INFORMIX DATA-SELECTOR >======================= Database="$IDB" File="$TAB1" Records="$nrec" Fields: FUR # awk fields in $f1: # $1, $2, $3, $4, $5, $6, $7 $8 # tabid, tabname, tabtype, colno, colname, column_type, collength, nrows awk -F"^" '{if ($1 == tn) printf("%s\n",$5)} END {printf("ROWID\n")}' \ tn="$ans" $f1 | tee $f3 | pr -4to2 -w77 echo getit ' Select ll Fields above, or just ome? (A/S):' ESL 3 1 echo case "$ans" in A|S) : ;; *) phoo " Option \"$ans\" Not Offered!" ;; esac cols="$ans" [ "$cols" = "A" ] && cat $f3 >$f5 || { icnt=`awk 'END {print NR}' $f3` # Number of Columns sl=1 while [ "$sl" -le "$icnt" ] do cat $f3 | sed -n ${sl}p >$f4 awk -F"^" '{printf(" Select column %18s ? (Y/N): ", $1)}' $f4 read yorn yorn=`echo "$yorn" | tr "[a-z]" "[A-Z]"` # force UPPER CASE [ "$yorn" = "Y" ] && cat $f4 >>$f5 sl=`expr $sl + 1` done } # EOT [ "$cols" = "A" ] clear cat << FUR | tee $yak =======================< INFORMIX DATA-SELECTOR >======================= Database="$IDB" File="$TAB1" Records="$nrec" Fields: FUR awk '{printf("%d %s\n", NR, $1)}' $f5 | tee $f4 | pr -4to2 -w77 | tee -a $yak npg=`awk 'END {printf("%d\n", (rex/60))}' rex="$nrec" $f5` cat << EYE File "$TAB1" contains $nrec Records. Unless a search-for value is used to seek out specific data within one of its Fields, the output will include every Record in the File (approx. $npg Pages). Enter a Field Number within which you want to seek specific data, or enter 0 (zero) if you want the information for all $nrec Records. EYE getit ' Field NUMBER or 0 (zero):' GEZ 3 col="$ans" if [ "$col" -gt "0" ] then # did the [ab]User enter a valid Column Number? >$f3 awk '{if (NR == loc) print $0}' loc="$col" $f5 >$f3 [ ! -s "$f3" ] && phoo " Field Number \"$col\" is not available! " fld=`awk '{if ($1 == loc) {printf("%s\n", $2); exit}}' loc="$col" $f4` echo box='' while [ -z "$box" ] do echo " Enter search-value for Field \"$fld\": \c" ANY 3 read box done # Did the [ab]User enter Text or Numeric $box for for column $fld? t=`expr "$box" + 1 2>&1 | awk '{print $1}'` echo if [ "$t" = "expr:" ] then getit " Should \"$box\" be shifted into UPPER case? (Y/N):" ESL 3 1 [ "$ans" = "Y" ] && box=`echo "$box" | tr "[a-z]" "[A-Z]"` # Did [ab]User enter any "*"'s ? o=`echo "$box" | awk '{duh = index($0,"*")} END {print duh}'` if [ "$o" = "0" ] then WH="WHERE $fld = \"$box\"" else WH="WHERE $fld MATCHES \"$box\"" fi else WH="WHERE $fld = $box" fi else WH='' fi tput clear cat $yak [ "$col" -gt "0" ] && echo "\n NOTE: Limit Output to data $WH " getit '\n Enter Field NUMBER on which to Sort: ' GEZ 3 scol="$ans" # did the [ab]User enter a valid Column Number? >$f3 awk '{if (NR == loc) print $0}' loc="$scol" $f5 >$f3 [ ! -s "$f3" ] && phoo " Field Number \"$scol\" is not available! " sfld=`awk '{if ($1 == loc) {printf("%s\n", $2); exit}}' loc="$scol" $f4` OBY="ORDER BY $sfld ; " clear cat $yak cat << EYE ALERT: Your Data will be Extracted and placed into file "$out". As to file-content, you may choose either: 1 Non-delimited, fixed-length Records (the first two lines of this file in will be blank). 2 Field-delimited, variable-length Records (The delimiter used will be the caret character: ^). EYE getit ' Which type of content? (1/2):' PNO 3 case "$ans" in 1) PRE="OUTPUT TO \"$out\" WITHOUT HEADINGS " FIN="Non-delimited, fixed-length Records." ;; 2) PRE="UNLOAD TO \"$out\" DELIMITER \"^\" " FIN='Field-delimited (with ^), variable-length Records.' ;; *) phoo "Sorry, but the choice of \"$ans\" was not offered!" ;; esac # Now, Generate the SQL statement: awk ' BEGIN \ {up = 0 # How many fields are "up" on the current line cs = 0 # Comma Switch (0 = no comma, 1 = comma OK) printf(" %s\n", pre) printf(" SELECT ") } # main() {if (cs == 1) printf(", ") printf("%s", $1) ++up cs = 1 if (up == 4) {printf(",\n "); up = 0; cs = 0} } END \ {printf("\n") printf(" FROM %s\n", tb) if (wh > " ") printf(" %s\n", wh) printf(" %s\n", ob) }' tb="$TAB1" wh="$WH" ob="$OBY" pre="$PRE" $f5 >$sql clear cat $yak echo "\n The SQL COMMAND TO BE USED FOR DATA EXTRACTION IS:\n" cat $sql echo " " | tee -a $yak getit ' Begin data-extraction process using the Query above? (Y/N):' ESL 3 1 [ "$ans" != "Y" ] && phoo 'By Your Command:' # GET THE DATA (finally): dbaccess -e $IDB - < $sql 2>&1 | tee -a "$yak" # she's a screamer # cat $sql >>$yak echo " File \"$out\" has $FIN" >>$yak echo "\n END REPORT (ID: \"$0\") AS OF: `date`\n" >>$yak # SHELLS/help_me2.sh $yak pg $yak eraser # EOF select.sh