CODEWITHSUNDEEP
mysqlbashvariables
Bacchus
Bacchus

Reputation: 3

Output of command as input in MySQL-query

I'm trying to use the output of a bash command as an input variable for a sql-query:

#!/bin/bash

mysql --user=root --password=root database << QUERY_INPUT

#Get temp from sensor.
current_temp=$(sed -n 2,2p /sys/bus/w1/devices/xxx/w1_slave | awk '{print $10}' | awk -F= '{print $2}')

#SQL query
INSERT INTO temperatures (tdate, ttime, temperature)
VALUES (CURRENT_DATE,CURRENT_TIMESTAMP,'$current_temp')
QUERY_INPUT

Im getting:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_temp=24750

INSERT INTO temperatures (tdate, ttime, temperature)
VALUES' at line 1"

How can I pass only the numbers to the query?

Upvotes: 0

Views: 354

Answers (3)

Jan de Nysschen
Jan de Nysschen

Reputation: 127

Try this 

#!/bin/bash
current_temp=$(sed -n 2,2p /sys/bus/w1/devices/xxx/w1_slave | awk '{print $10}' | awk -F= '{print $2}')
mysql --user=root --password=root database -e "INSERT INTO temperatures (tdate, ttime, temperature) VALUES (CURRENT_DATE,CURRENT_TIMESTAMP,$current_temp)"

I can unfortunately not test it at the moment

Upvotes: 0

chepner
chepner

Reputation: 531185

The command that sets current_temp is not part of the SQL output. Move it outside the here document.

Only code meant to be passed to mysql can appear in the here document, which means you need to move the command that sets current_temp, and all shell comments, outside the here document.

#!/bin/bash

#Get temp from sensor.
current_temp=$(sed -n 2,2p /sys/bus/w1/devices/xxx/w1_slave | awk '{print $10}' | awk -F= '{print $2}')

mysql --user=root --password=root database << QUERY_INPUT
INSERT INTO temperatures (tdate, ttime, temperature)
VALUES (CURRENT_DATE,CURRENT_TIMESTAMP,'$current_temp')
QUERY_INPUT

As mentioned in the comments, there are several ways to simplify setting current_temp. A sample:

  1. Use a single awk command:

    current_temp=$( awk 'NR==2 {print $10}' /sys/bus/w1/devices/xxx/w1_slave )

  2. Use pure bash:

     { read; read -a fields; current_temp=${fields[9]}; } < /sys/bus/w1/devices/xxx/w1_slave

Upvotes: 2

Technext
Technext

Reputation: 8107

Try this:

INSERT INTO temperatures (tdate, ttime, temperature)
VALUES (CURRENT_DATE,CURRENT_TIMESTAMP,'"$current_temp"')
QUERY_INPUT

Quoting "$current_temp" within single quote should pass the string as a quoted string, which is valid for SQL.

Upvotes: 0

Related Questions