Bash + MySQL -d backtick issue

Question

I am helping to create a solution for a community project to monitor river levels for free, in the community spirit. The eventual product of this effort will be a system that takes data from a river level probe, and generates a graph for an online community.

I am early on with the project, and as I am familiar with Bash am using this to take a text file from the probe containing the data and push it into a MySQL database. All has been going really nicely until I hit a stumbling block. A number of my database table columns are numbers and so to interact with these using the MySQL command line tool backticks have to be used. Unfortunately I want to take the output from a MySQL statement and put it into a variable, but I think due to the two sets of back ticks MySQL is not getting the right command.

I have fudged it with a nasty work around like so which works all be it inefficiently:

mysql -N -D $targetDatabase -e "select \`"$timeSample"\` from RiverDataDays where date="$dateOfFile";" >tmpValue

dbEntry=`cat tmpValue`

echo $dbEntry

But actually, what I want to do is push it straight into an variable like this:

dbEntry=`mysql -N -D $targetDatabase -e "select \`"$timeSample"\` from RiverDataDays where date="$dateOfFile";"`

echo $dbEntry

Answer 1

Try doing this :

dbEntry="$(printf "SELECT \140%s\140 FROM 'RiverDataDays' WHERE date = '%s';\n" "$timeSample" "$(<tmpValue )" | mysql -N -D "$targetDatabase")"
echo "$dbEntry"

or

dbEntry="$(printf "SELECT \`%s\` FROM 'RiverDataDays' WHERE date = '%s';\n" "$timeSample" "$(<tmpValue )" | mysql -N -D "$targetDatabase")"
echo "$dbEntry"

The backquote (`) is used in the old-style command substitution, e.g.

foo=`command`

The

foo=$(command)

syntax is recommended instead. Backslash handling inside $() is less surprising, and $() is easier to nest. See http://mywiki.wooledge.org/BashFAQ/082

---

\140

is the octal representation of a backtick, see

man ascii