loop variables stuck as constant when using sqlcmd

Question

While creating a bash script that calls sqlcmd for different variables in a loop, noticed it appears to be setting the loop variables that should update in the loop to be constant once it is called the first time (can tell that this is very likely the culprit by continueing and later and later segments of the loop until got to the sqlcmd segction). Ie. if we loop through a list (of length L) of MSSQL Server table names with sqlcmd, the loop will just do, not L, but infinite iterations of the loop instructions using only the first entry in the list.

A minimal example is below:

#!/bin/bash

tables_list=$1

while read -r line
do
    tablecols="$line"
    IFS=',' read -a arr_tablecols <<< "$tablecols"

    mssql_tablename=${arr_tablecols[0]}

    echo -e "\n\n\n##### Processing: $mssql_tablename #####\n"

    TO_SERVER_ODBCDSN="-D -S <ODBC DSN name for mssql host>"
    TO_SERVER_IP="-S <my mssql host IP>"
    DB="ClarityETL_test"
    TABLE="$mssql_tablename"
    USER=<my mssql username>
    PASSWORD=<my mssql login password>

    #uncomment to see that sqlcmd does in fact appear to be the problem
    #continue

    {
    echo -e "Counting destination table: $DB/$TABLE"
    sqlcmd -Q "PRINT '$mssql_tablename';" \
        $TO_SERVER_ODBCDSN \
        -U $USER -P $PASSWORD \
        -d $DB
    } || { echo -e "\nFailed to truncate MSSQL DB"; exit 255; } 

done < "$tables_list"

where the file being used as $table_list looks like

mymssqltable1
mymssqltable2
...

(since the example just uses the PRINT command, the list could be mostly anything you want to use, really).

This behavior is really weird to me and could not find anything mentioning this in the docs (https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017#create-and-query-data) (I'm on CentOS 7). If anyone knows what is going on here or any further debugging advice, please let me know.

Answer 1

I suspect sqlcmd is reading from standard input, so it's consuming the rest of the input file. I'm not sure why this is causing the loop to repeat infintely, rather than ending after the first iteration. But if this is what's going on, the solution is to redirect the input of sqlcmd.

sqlcmd -Q "PRINT '$mssql_tablename';" \
    $TO_SERVER_ODBCDSN \
    -U $USER -P $PASSWORD \
    -d $DB < /dev/null

When you redirect input of the while loop, all the commands inside the loop inherit that redirected input. So note that if you were calling sqlcmd from within another script nested in the loop (rather than directly within the loop itself), you would do something like

while read -r line
do
    tablecols="$line"
    IFS=',' read -a arr_tablecols <<< "$tablecols"

    mssql_tablename=${arr_tablecols[0]}

    bash scriptThatUsesSqlcmd.sh mssql_tablename < /dev/null

done < "$tables_list" 

to un-inherit the loop's standard input redirect in the script that will use sqlcmd.