Bash MySQL and quotes for SQL queries.

[RimBlock]

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

 

[RimBlock]

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