Using a bash variables in sqlcmd

Question

I have been tasked with replacing ISQL in a lot of our bash scripts with sqlcmd. ISQL allows piping a variable in it's execution. An example would be:

    SQL_STATEMENT="SELECT TOP 1 SYS_USER_NAME FROM SYS_USER"
    echo $SQL_STATEMENT | isql -b -d, $DSN $DBUID $DBPWD >> setupdb_test.txt

From what I can tell this is not viable in sqlcmd. How can I do this? What flags does sqlcmd have to allow this to happen?

Here is what I have tried and have had a good result BUT I really do not want to create the file sql_command.sql every time a particular script runs:

    echo "SELECT TOP 1 SYS_USER_NAME FROM SYS_USER" > sql_command.sql
    sqlcmd -S $DB -U $DBUID -P $DBPWD -d $DSN -i sql_command.sql >> setupdb_test.txt

have you tried this? export SQL_COMMAND="SELECT TOP 1 SYS_USER_NAME FROM SYS_USER"; sqlcmd -S $DB -U $DBUID -P $DBPWD -d $DSN -i $SQL_COMMAND >> setupdb_test.txt — Sharuzzaman Ahmat Raslan
– Sharuzzaman Ahmat Raslan, Commented Jul 10, 2019 at 18:06
Unfortunately this did not work for me. I get an error of: Sqlcmd: 'SELECT': Invalid filename. — Clint
– Clint, Commented Jul 10, 2019 at 18:55
ok. the SQL_COMMAND contain space, so the $SQL_COMMAND must be quoted. Like this: sqlcmd -S $DB -U $DBUID -P $DBPWD -d $DSN -i "$SQL_COMMAND" >> setupdb_test.txt — Sharuzzaman Ahmat Raslan
– Sharuzzaman Ahmat Raslan, Commented Jul 11, 2019 at 8:05

that other guy · Accepted Answer · 2019-07-10 18:17:03Z

2

Programs originating on Windows can be picky about how they handle non-regular files and I don't have the opportunity to test, but you can try the typical Unix tricks for providing a "file" with data from an echo.

Either /dev/stdin:

echo "SELECT TOP 1 SYS_USER_NAME FROM SYS_USER" | sqlcmd -S "$DB" -U "$DBUID" -P "$DBPWD" -d "$DSN" -i /dev/stdin

or process substitution:

sqlcmd -S "$DB" -U "$DBUID" -P "$DBPWD" -d "$DSN" -i <(echo "SELECT TOP 1 SYS_USER_NAME FROM SYS_USER")

answered Jul 10, 2019 at 18:17

that other guy

125k12 gold badges187 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clint Over a year ago

This works, I also tried SQL_COMMAND="SELECT TOP 1 SYS_USER_NAME FROM SYS_USER" echo $SQL_COMMAND | sqlcmd -S $DB -U $DBUID -P $DBPWD -d $DSN -i /dev/stdin >> setupdb_test.txt and this works as well. Thank you.

Collectives™ on Stack Overflow

Using a bash variables in sqlcmd

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related