server..database..table#!/bin/sh
err_flg=0
progname=`basename $0`
#
# set command-line argument defaults
#
SVR=<some server>
DBN=<some dbname>
UID=<some uid>
OUF=""
END=".tmp"
SKIP="N"
#
# Get command-line arguments
#
while getopts D:d:O:o:P:p:S:s:U:u:Xx argument
do
case $argument in
D|d) DBN=$OPTARG;;
O|o) OUF=$OPTARG;;
P|p) PWD=$OPTARG;;
S|s) SVR=$OPTARG;;
U|u) UID=$OPTARG;;
X|x) SKIP="Y";;
?) err_flg=1;;
esac
done
shift `expr $OPTIND - 1`
SCR=$1
if [ "${SKIP}" = "Y" ]
then
echo ""
echo "WILL SKIP ASKING TO LOAD TO ANOTHER TABLE"
fi
#
# Exit if there were argument errors
#
if [ $err_flg -eq 1 ]
then
echo ""
echo "USAGE: $progname [-d database] [-h] [-o out_file] -p password [-s server] [-u user] query_file"
echo ""
exit 2
fi
if [ -f "$SCR" ]
then
continue
else
echo ""
echo File \"$SCR\" does not exist in this directory.
echo ""
exit 1
fi
if [ "$OUF" = "" ]
then
OUF=${SCR}.dat
fi
if [ -f ${OUF} ]
then
rm ${OUF}
fi
END=".tmp"
if [ -f $OUF ]
then
echo "Deleting $OUF from previous run..."
/usr/bin/rm $OUF
fi
echo ""
echo "Running isql query......"
isql -U $UID -P $PWD -S $SVR -D $DBN -i $SCR -o $OUF -w 9000 -s \| -h
if [ ! -s $OUF ]
then
echo ""
echo "Query <$SCR> failed or returned no rows. Result file <$OUF> not created."
rm $OUF
echo ""
exit 1
fi
echo "Done"
echo ""
echo "Formatting BCP file....."
#
# initialize variables
#
FILE_NAME=$OUF
END=".tmp"
FILE_NAME2=$FILE_NAME$END
#
# Format the file
#
echo "1..."
sed 's/ *$//' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "2..."
sed 's/^\| *//' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "3..."
sed 's/^\|//' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "4..."
sed 's/\| */\|/g' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "5..."
sed 's/ *\|/\|/g' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "6..."
sed 's/\|$//' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "7..."
sed 's/\|\|/\| \|/g' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "8..."
sed 's/\|NULL/\|/g' $FILE_NAME > $FILE_NAME2
mv $FILE_NAME2 $FILE_NAME
echo "Done"
echo ""
if [ "${SKIP}" = "Y" ]
then
return 0
fi
#
# If we've been successful, maybe the user wants to bcp the file now?
#
echo "Do you want to bcp <$OUF> into another database table [y/n]? \c"
read run_bcp
case $run_bcp in
Y|y) echo "Please provide the following:"
echo "Target Server: \c"
read TSVR
echo "Target Database: \c"
read TDBN
echo "User on <$TDBN>: \c"
read TUID
echo "Password for <$TUID>: \c"
read TPWD
echo "Target Table: \c"
read TTAB
echo "Batch Size: \c"
read BSZ
echo ""
echo "Errors will be in <bcp.err>.\n"
echo "Loading....."
echo "bcp $TDBN..$TTAB in $OUF -b$BSZ -c -t \| -U$TUID -P$TPWD -S$TSVR -e bcp.err -m 99999"
bcp $TDBN..$TTAB in $OUF -b$BSZ -c -t \| -U$TUID -P$TPWD -S$TSVR -e bcp.err -m 99999
;;
*) echo ""
echo "Data in bcp-format can be found in <$OUF>."
echo "To load database, the command is:"
echo " bcp $DBN..<tablename> in $OUF -b500 -c -t \| -U$UID -P$PWD -S$SVR -e bcp.err -m 99999"
echo ""
;;
esac
exit 0