Capturing Mysql output in a bash variable

[dbrb2]

Evening. I am trying to capture the output of a mySQL query into a bash variable, without much luck...

I have:

#!/bin/bash

HOURS=0
MINS=0
SECS=0

SMTP=""

DB_USER="xxxx"
DB_PASS="yyyy"
TABLE="zzzz"

QUERY='mysql -u ${DB_USER} -p${DB_PASS} -e"<<EOF
        SELECT update_time
        FROM information_schema.TABLES
                WHERE TABLE_NAME = \'${TABLE}\'"
'EOF

But this is failing. I have tried various permutations on the above - any ideas?

Cheers,

Ben

 

[DonQuichote]

I do not really see what you are doing here. First, the single quotes do not compute anything, so ${} constructs are passed literally. Furthermore, I would just use a pipe to feed the mysql client a query. At my development machines, I have a user "automatic" that has no password and can perform database updates after a subversion update.

I would do it like this:

echo "SELECT update_time FROM TABLES WHERE TABLE_NAME = '$TABLE'" | mysql -u automatic -e information_schema

To get the output on screen and to put it into a variable:

QUERY=`echo "SELECT update_time FROM TABLES WHERE TABLE_NAME = '$TABLE'" | mysql -u automatic -e information_schema`

(Note that you have to use backtics to capture a result into a variable).

Hope this helps.



+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[dbrb2]

Ah - it was the backticks I was missing - I was using single quotes instead. This works fine:

QUERY=`mysql -u ${DB_USER} -p${DB_PASS} -e"
        SELECT NOW()-update_time AS ''
        FROM information_schema.TABLES
                WHERE TABLE_NAME = '${TABLE}'"`

I added the

SELECT update_time AS ''

So that the colum header was hidden, and thus not read into my variable. I also added

NOW()-update_time

Becuase I decided dealing with timestamps would be simpler in my bash script

Cheers,

Ben