Mac unix script to insert row into Mysql database

I have wired the whole house with thermometers to keep track of the temperatures in the house.  I did it using Arduino Mega 2560.

Anyway, I have my Macmini polling the Arduino every 5 minutes to pick up the temperature in every room and logging it to a file.  But, I would like to log it to a database instead.

I have set up a MySql database and have successfully loaded the existing file to it..  so I know the database works,  but instead I would like to load one line of data to it every 5 minutes.  The code to load the data is.

======= CODE ======= from the command line ======

mysql -u sean -psomepassword password -e "INSERT INTO temperatures VALUES ('2011.09.17 10:35', 12.4,10.0,21.4,19.5,19.9,21.0,21.4,21.4,22.0,21.1,20.9,21.4,18.3);"


============================

This works, because i've tried it....

My bash script nearly works, but dies when it comes to sending the above code,  Here is my bash script  (the echo and the final line are just checks I put in place to see if it was actually working properly.  I'm pretty sure the problem lies with my "escape" characters \  But I'm not sure. The buffer variable is just to prime the arduino, the first time I poll it, it doesn't actually work every time.


Could you take a look at it, or point me in the right direction?


Thanks


==========SCRIPT=============
#!/bin/bash


THEDATETIME=`date +%Y.%m.%d" "%H:%M`
DATETIME="'"$THEDATETIME"',"
BUFFER=`/Users/seanroulet/Documents/Arduino/apps/arduino-serial -b 115200 -p /dev/cu.usbmodem641 -r`
NEWLINE=$DATETIME`/Users/seanroulet/Documents/Arduino/apps/arduino-serial -b 115200 -p /dev/cu.usbmodem641 -r`


echo $NEWLINE


$QUERY=`mysql -u sean -psomepassword cedro_azul_temperature -e \"INSERT INTO temperatures VALUES ("$NEWLINE");\""`


$QUERY >> ~/Documents/Arduino/temp.txt


===================
sidartraAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ryan WeaverConnect With a Mentor Infrastructure ManagerCommented:
Two issues I can see with the script, both in this line:
$QUERY=`mysql -u sean -psomepassword cedro_azul_temperature -e \"INSERT INTO temperatures VALUES ("$NEWLINE");\""`

Open in new window


1, assigning to $QUERY should start the line without the '$'.
2, there is an extra " at the end of the line.
0
 
sidartraAuthor Commented:
Thanks,  I've removed the $ from Query. and removed the extra "

Is the "\" backslash the correct "escape" character?  IE, I want to pass the " to the command, not close the variable.

I tried with your ammendments, but the error message remains.

I've changed the script slightly to have it echo the command I'm sending in $Query, and this is what I see

mysql -u sean -psomepassword cedro_azul_temperature -e "INSERT INTO temperatures VALUES ('2011.09.20 16:41', 22.9,22.1,21.5,21.5,21.2,21.9,21.8,21.7,22.7,22.5,21.4,21.7,19.4);"

then I copied the "echo" and pasted it in the command line, and it loaded the data!

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.