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).
CamelCamelius
  • 183
  • 8
  • 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 ` – meuh Sep 22 '16 at 14:58
  • Thanks @meuh ! The `sqlcmd` was actually reading from the `stdin` _[facepalm]_ so appending ` – CamelCamelius Sep 22 '16 at 16:57
  • Interesting, and complicated, corner case. Thanks for the link. – meuh Sep 24 '16 at 08:18

1 Answers1

1

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!"
CamelCamelius
  • 183
  • 8