2

Is there a way to use sqlcmd with -i input_file option without creating an infinite loop in my ksh script?

  • The code reads lines from $file and parses it line-by-line to extract data and process other stuff.
  • It uses file descriptors redirection to "read the $file from the stdin".

Infinite loop code:

exec 3<&0
exec 0<"${file}"
while read -r line || [[ -n ${line} ]]
do
    echo "${line}"
    sqlcmd -S svr -U usr -P pwd -i input_file >/dev/null 2>&1
done
exec 0<&3
exec 3<&-
echo "Script completed successfully!"

Output:

line 1 ...
line 1 ...
...
line 1 ...^C

Workaround (use here document instead of -i input_file option):

exec 3<&0
exec 0<"${file}"
while read -r line || [[ -n ${line} ]]
do
    echo "${line}"
    sqlcmd -S svr -U usr -P pwd <<-EOF
        -- SOME SQL CODE HERE
    EOF
    # here document lines are indented with tabs, not whitespaces.
done
exec 0<&3
exec 3<&-
echo "Script completed successfully!"

Output:

line 1 ...
line 2 ...
line 3 ...
Script completed successfully!

Even if there's a workaround for the issue I would like to know what's the reason for that behavior and how to use the sqlcmd tool without banning it's -i input_file option.

Notes:

  • Microsoft ODBC Driver 11 for SQL Server.
  • Red Hat Enterprise Linux 6.7 (KornShell).
  • Your code should work, though if your ksh does not clear the variable line when it reads end-of-file that might cause the problem. It is not clear why you do read ... || [[ -n $line ]]. Perhaps you meant read ... && ...? Your sqlcmd is probably reading stdin. Add </dev/null to the sqlcmd command. – meuh Sep 22 '16 at 14:58
  • Thanks @meuh ! The sqlcmd was actually reading from the stdin [facepalm] so appending </dev/null fixed the problem. Regarding the code read ... || [[ -n $line ]], I use that in case the last line of the file to be read doesn't end with a new line (if the EOF is found in a line with some content, that content will be "discarded"). Refer to this amazing answer, hope you find it useful. – CamelCamelius Sep 22 '16 at 16:57
  • Interesting, and complicated, corner case. Thanks for the link. – meuh Sep 24 '16 at 8:18
0

As @meuh mentioned in the comments, sqlcmd was reading stdin so appending </dev/null fixed the issue. The problem was that the while loop was iterating over the stdin (previously redirected from a file exec 0<"${file}") and the sqlcmd that is inside the while attempted to read from the stdin. The solution was making the sqlcmd to read from /dev/null instead of stdin.

Fix

exec 3<&0
exec 0<"${file}"
while read -r line || [[ -n ${line} ]]
do
    echo "${line}"
    sqlcmd -S svr -U usr -P pwd -i input_file </dev/null
done
exec 0<&3
exec 3<&-
echo "Script completed successfully!"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.