Bash MySQL and quotes for SQL queries.

Associate
Joined
10 Nov 2004
Posts
2,237
Location
Expat in Singapore
Hi,

I am having very little sucess in trying to get a stored proc run from a Bash script via MySql with the MySql command and the SQL being held in variables.

e.g.

# Defining SQL connection and SQL
MYSQL_SERVER="192.168.1.1"
MYSQL_DB="dbname"
MYSQL_READER="user1"
MYSQL_RPW="user1pw"
MYSQL_COMMAND="mysql -h$MYSQL_SERVER -u$MYSQL_READER -p$MYSQL_RPW $MYSQL_DB -e"
SQL="call sp_name;"

# Call SQL
echo $MYSQL_COMMAND $SQL
SQL_RETURN=`$MYSQL_COMMAND $SQL`
The echo statement returns;
mysql -h192.168.1.1 -uuser1 -puser1pw dbname -e 'call sp_name;'
which is correct.

The statement to fire against the MySQL database transposes the command to;
mysql -h192.168.1.1 -uuser1 -puser1pw dbname -e ''\''call' 'sp_name;'\'''
which is wrong (note the single quotes seperating the 'call' and the 'sp_name' sections).

So the question is how to be able to get the vale of the variable SQL in one set of single quotes including spaces.

Thanks
RB
 
After a lot more searching I have come across using subshells (am used to ksh and not bash). From that I have found that just putting double quotes around the SQL variable quotes it correctly.

so;
SQL_RETURN=`$MYSQL_COMMAND $SQL`

becomes;
SQL_RETURN=`$MYSQL_COMMAND "$SQL"`

returns;
mysql -h192.168.1.1 -uuser1 -puser1pw dbname -e 'call sp_name;'

So obvious really, when you know ;).

RB
 
Back
Top Bottom