Bash loop to read file line by line problem with sqlcmd

Question

I'm reading a .txt file using a while loop:

baseFileDir=./Base_Database_Files/
if [ -f "./SqlFiles.txt" ]; then
    while IFS= read -r line  || [[ -n "$line" ]]
    do  
        sqlFilePath=${baseFileDir}$line
        echo "Executing: $sqlFilePath"
        sqlcmd -S $server,$port -U $user -P $password -d $database -I -i "$sqlFilePath"  < /dev/null
    done <./SqlFiles.txt
fi

sqlcmd have problem with $lin. Here is the output:

Executing: ./Base_Database_Files/MyScript.sql
': Invalid filename.base_Files/MyScript.sql

but if I hardcode the line ./Base_Database_Files/MyScript.sql i.e.

baseFileDir=./Base_Database_Files/
if [ -f "./SqlFiles.txt" ]; then
    while IFS= read -r line  || [[ -n "$line" ]]
    do  
        sqlFilePath=./Base_Database_Files/MyScript.sql
        echo "Executing: $sqlFilePath"
        sqlcmd -S $server,$port -U $user -P $password -d $database -I -i "$sqlFilePath"  < /dev/null
    done <./SqlFiles.txt
fi

it seems to be executing the script properly. i.e.

Executing: ./Base_Database_Files/MyScript.sql
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'UIX_ALARM_NOTIFICATION_1' has maximum length of 2004 bytes. For some combination of large values, the insert/update operation will fail.

Note:The above Warning! is from SQL server depicting that the script was executed properly.

It looks like sqlcmd doesn't like the variable $line format.

Question: How to make $line variable used with the while IFS= read -r line statement workable/readable by sqlcmd?

You could debug this with a simple `echo $line` inside the while loop to make sure the value is what you are expecting. — Nic3500, Jan 04 '23 at 22:02
You can also get `read` to trim the problematic carriage return with `while IFS=$'\r' read -r ...`. See ["Are shell scripts sensitive to encoding and line endings?"](https://stackoverflow.com/questions/39527571/are-shell-scripts-sensitive-to-encoding-and-line-endings) and ["How to convert Windows end of line in Unix end of line (CR/LF to LF)"](https://stackoverflow.com/questions/3891076/how-to-convert-windows-end-of-line-in-unix-end-of-line-cr-lf-to-lf) for more info and options. — Gordon Davisson, Jan 04 '23 at 23:10
@Nic3500 `echo $line` is *not* a good debugging step, because it can be misleading in a number of ways. For one thing, it doesn't do anything at all about showing invisible/nonprinting characters (like the carriage return that's causing trouble here), and for another the lack of double-quotes around the variable means bash may change it before passing it to `echo`. See ["I just assigned a variable, but echo $variable shows something else"](https://stackoverflow.com/questions/29378566). `printf "'%s'\n" "$line" | LC_ALL=C cat -vt` is much better. — Gordon Davisson, Jan 04 '23 at 23:13
@GordonDavisson, Thanks for the detailed references. I was able to solve it. Please see my posted answer. — Farrukh Waheed, Jan 05 '23 at 18:41

score 0 · Accepted Answer · answered Jan 05 '23 at 18:40

References from the comments posted by @Gordon Davisson and @Barmar, I was able to solve this issue by adding sed -i 's/\r//g' $SqlFilesList which would convert the Dos/Windows CR/LR to Unix LF format: Here is the working snippet:

    SqlFilesList="./SqlFiles.txt"

    # converting the CR/LF to Unix LF Or sqlcmd won't recognize the paths properly due to Windows CR/LR chars like \r\n.
    sed -i 's/\r//g' $SqlFilesList

    #// Read the file list and concatenate the related Path and execute the .sql files.

    if [ -f $SqlFilesList ]; then
        while IFS=$'\r' read -r line  || [[ -n "$line" ]]
        do  
            printf "\nExecuting: %s\n" "$line"
            sqlcmd -S $server,$port -U $user -P $password -d $database -I -i "$line"  < /dev/null 
        done < $SqlFilesList
    else
        echo "The file $database/$SqlFilesList is missing, which should contain the sequence of *.sql to be executed."
        exit 1
    fi

Bash loop to read file line by line problem with sqlcmd

1 Answers1