Output of command as input in MySQL-query

Question

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?

What are you trying to extract from /sys/bus/w1/devices/xxx/w1_slave? That line looks like it could be simplified greatly — Tom Fenech
– Tom Fenech, Commented Aug 14, 2014 at 18:24
xxx is just a serial number for a DS18B20 temperature sensor. — Bacchus
– Bacchus, Commented Aug 14, 2014 at 18:48

chepner · Accepted Answer · 2014-08-14 18:26:26Z

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:

Use a single awk command:

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

Use pure bash:

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

Jan de Nysschen · Accepted Answer · 2014-08-14 18:37:01Z

0

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

answered Aug 14, 2014 at 18:37

Jan de Nysschen

1277 bronze badges

1 Comment

Bacchus Over a year ago

Thank you (and the other guys answering), that worked perfect!

Technext · Accepted Answer · 2014-08-14 18:18:37Z

0

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.

answered Aug 14, 2014 at 18:18

Technext

8,16710 gold badges56 silver badges83 bronze badges

Collectives™ on Stack Overflow

Output of command as input in MySQL-query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related