Re: A solution to SQL reformatting problem
AS some other readers pointed out, setting columns/COLUMNS does
not appear to work in many cases.
As an alternative, I whipped up a awk script to do this.
I've given some limitted testing to it, but it appears to do
basically what is desired here. Note that ALL fields from
the file are selected - if you want a subset, then the extension
to this script is left as an excersise for the user. Also,
feel free to re-write in perl, etc.
Also note that if the normal isql works, this script attempts to
detect that - but I haven't tested the boundary conditions around
80, so be prepared.
Also, I've only typed some of the obvious types of data - others
will probably need to be added for total correctnes [e.g. float
and date types].
Standard disclaimers apply - and no support is promised!!!
ENJOY.
Ihor Kinal
att!trumpet!ijk
------------------------
# /bin/ksh
# dump_flat: 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 '
{ 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 \\",$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