double quote single quote combination BCP on Linux

Question

Can someone show me the correct way to structure this command?

FYI - I think this boils down to an escape issue. There are several posts close but I have yet to find a solution which will run as a command (example)

This command queries the db to create a file based on the results.

bcp \
"SELECT * FROM [database].[dbo].[table] CAST(timestamp as DATE) = '2015-11-01'" \
queryout /tmp/2015-11-01.txt \
-S server \
-U user \
-P pwd \
-c

I want to script this for, say, each day in a month.

for i in 01 02 03

do

bcp \
"\"SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as  DATE) = '2015-11-${i}';"\"
queryout /tmp/2015-11-${i}.txt \
-S server \
-U user \
-P pwd \
-c 

done

The output from bash shows that it is being modified/escaped

bash -x ./command.sh
bcp '"SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as DATE) = '\''2015-11-01'\'';"' ....

This modified sql is still passed to sql server but is nonsensical and gets no rows. bcp will only accept a double quoted string exactly as shown below (including double and single quotes):

"SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as DATE) = '2015-11-01';"

Can this be done ?

Thanks for any help you can give. TD

score 1 · Answer 1 · answered Sep 30 '16 at 08:55

You're overthinking the problem. bcp expects a string as its first parameter that describes either a table or a select... statement. As usual the shell strips the outmost quotes away, and because they're double quotes "..." it interpolates the value of any shell variables it finds.

for i in 01 02 03
do
    bcp "SELECT * FROM [database].[dbo].[table] where CAST(timestamp_field as DATE) = '2015-11-${i}'" \
        queryout /tmp/2015-11-${i}.txt \
        -S server -U user -P pwd -c
done

and similarly

test -z "$odbc_dsn" && read -p 'ODBC DSN: ' odbc_dsn
test -z "$username" && read -p 'Username: ' username
test -z "$password" && read -sp 'Password: ' password

for tb in databases all_columns all_views
do
    bcp "master.sys.$tb" out "$tb.txt" -c -D -S "$odbc_dsn" -U "$username" -P "$password"
    echo "Table $tb"
    sed 's/^/|   /' "$tb.txt"
    echo
done

double quote single quote combination BCP on Linux

1 Answers1