dgodson dgodson - 1 month ago 12
Bash Question

Escaping special character when SQL output to variable in shell script

Trying to assign output from SQL query on an object containing special characters to a variable in shell script.

Running directly on the database:

db2 -x 'select count(*) from <SCHEMA>."/BIC/TEST"'

11000


Yet when I include this in script I need to use double quotes as I am using variables passed into the sql. Using single quotes

Output=$(db2 -x 'select count(*) from ${_SCHEMA}."/BIC/TEST"')

echo -e "$Output"


Results in:

SQL20521N Error occurred processing a conditional compilation directive near
"_". Reason code="7". SQLSTATE=428HV


When I use double quotes I hit:

SQL0104N An unexpected token "'/BIC/TEST'" was found following "ount(*)

Tried to escape the double quotes using another set of double quotes:

db2 -x 'select count(*) from ${_SCHEMA}.""/BIC/TEST""'

But this doesn't seem to work in script. It works for tables where there is no special characters/requirement to encase in quotations.

Any help is appreciated.

mao mao
Answer Source

The code below works fine for me, notice the escaped quotes. If it fails for you, you need to give more details of your DB2-version and the DB2-server operating system platform.

#!/bin/ksh

db2 connect to sample
(($? > 0 )) && print "Failed to connect to database" && exit 1

db2 -o- "drop table \"/bin/test\" "

db2 -v "create table \"/bin/test\"(a integer)"
(($? > 0 )) && print "Create table failed" && exit 1

db2 -v "insert into \"/bin/test\"(a) values(1),(2),(3),(4)"
(($? > 0 )) && print "insert rows failed" && exit 1

db2 -v describe table \"/bin/test\"

typeset -i count_rows=$(db2 -x "select count(*) from \"/bin/test\"" )
(($? > 0 )) && print "query count rows failed" && exit 1

print "\nRow count is: ${count_rows}\n"

db2 -o- connect reset