Pravin Pravin - 1 month ago 13
Bash Question

How to break loop in shell script used to truncate table?

I'm writing a script truncating tables. The problem is that the script goes in infinite loop.

contains environment variables that are used in script.


if [ ! -e $INPUT_FILE ];
echo "Error: Input file require"
exit 1

source $INPUT_FILE

while read Table

echo "Table : $TABLE"

QUERY="truncate table $TABLE"
echo $QUERY > ./tmp_file
sqlcmd -m 1 -U $DSTN_USER -P $DSTN_PASSWORD -D -S $DSTN_SERVER -m1 -i ./tmp_file

if [ $RET_VALUE -ne 0 ]
echo "Error $TABLE"
done < $TABLE_LIST
exit 0

How do I break the loop? I have tried to remove
from script and verified that it was working. It's working as expected. Observed the same behavior with
sqlcmd -Q

file contains only one table name.


You don't need to write the query into temporary file. Use -q, or -Q options instead:

q="truncate table ${TABLE};"
sqlcmd -m 1 -U "$DSTN_USER" -P "$DSTN_PASSWORD" -S "$DSTN_SERVER" -q "$q"

Note the ; at the end of the query. Probably, that's the reason why the script "stalls". That may look like an infinitely running loop.

Also note the use of double quotes. You should wrap variables in double quotes to prevent reinterpretation of the special characters.

By the way, you can locate the exact command that is causing the issue by adding set -x at the beginning of the script. set -x turns on debugging mode. With debugging mode on, you see the commands being executed.

It's very unlikely that the content of $TABLE_LIST file is causing such behavior, unless the file is enormously big. The loop construct is correct, and the number of iterations should match the number of lines in the file.