Re: A solution to SQL reformatting problem
It occurred to me after I posted that my prgram would only work for
one record. Here's the modified program works for more. Other
disclaimers still apply.
Ihor Kinal
-------------------------------------
# /bin/ksh
# script to give you column headings and fields
# $1 is dbname, $2 is file name. This script attempts to insure alignment.
# Note that it only works for ALL the columns in a table - if you want
# a subset, some more work is needed.
if [ $# -ne 2 ]; then
echo "USAGE: $0 dbname filename"
exit 1
fi
export DB=$1
export FILE=$2
cd /tmp
(
isql $DB - <<!
info tables columns for $FILE;
!
) | tail +5 | egrep -v "^$" > cols
# use the columns info to create awk scripts to format first the column
# names, and then actual data. Assume smallints take 5 spaces, and integers
# 10 spaces.
cat cols | tr ")" " " | tr "(" " " | egrep -v "^$" | nawk '
{ len=length($1); if ($2=="smallint" && len < 5)
len=5
else if ($2=="integer" && len < 10)
len=10
else if ($2=="char" && len < $3)
len=$3
printf "{ if (NR==%d) printf \\"%%%ds \\",$1} \\n",NR,len;
tot_len = tot_len + len +1;
}
END { if (tot_len <= 80)
printf "TOTAL length too short - %d\\n",tot_len
}
' > t1.awk
# if the length of the fields is <= 80, a normal dump will suffice.
grep TOTAL t1.awk > /dev/null
if [ $? -eq 0 ]; then
isql $DB - <<!
select * from $FILE;!
else
nawk -f t1.awk cols
echo "\\n"
cat cols | tr ")" " " | tr "(" " " | egrep -v "^$" | nawk '
BEGIN { printf "{ if (NF==0) {cnt =0; printf \\"\\\\n\\"} else cnt++}\\n" }
{ len=length($1); if ($2=="smallint" && len < 5)
len=5
else if ($2=="integer" && len < 10)
len=10
else if ($2=="char" && len < $3)
len=$3
printf "{ if (cnt==%d) printf \\"%%%ds \\",$2} \\n",NR,len}
' > t2.awk
(
2> /dev/null isql $DB - <<!
select * from $FILE;!
) | tail +4 |nawk -f t2.awk
fi
/bin/rm -f t[12].awk cols
exit 0