Arduino and SQL

Forum 2005-2010 (read only) Software Interfacing
1

Guys here is my problem,

I have built a temperature and home heating (HVAC) controller from the Arduino, it's working great but..

Problem one*
My existing project can send the sensor readings out on the serial port every few min, which I'd like to log to a database of some sort (my sql, sql lite etc whatever's easiest)

So I'd have the following setup Sensors-----Arduino-----SERIAL/USB-----MacOSX---Database

The data I'll be sending from the Arduino can be in any format, as I can change this on the Arduino it would probably be something like this

room_1,21.3
room_2,22.2
room_3,22.7

What I'd like to do is place the values into a database (with a time stamp) not fussy what sortof DB as long as I can read the values back out later Using a web browser (PHP probably)?.so I end up with something like this

temp_database_room_1_table
Value Date Time
21.3 10-06-08 10:25
21.4 10-06-08 10:30
22.1 10-06-08 10:35

temp_database_room_2_table
Value Date Time
22.2 10-06-08 10:25
22.8 10-06-08 10:30
22.4 10-06-08 10:35

temp_database_room_3_table
Value Date Time
22.7 10-06-08 10:25
22.2 10-06-08 10:30
22.7 10-06-08 10:35

I can generate the timestamp from the project clock (a 1307 RTC) and send it with the sensor reading to my serial port if that's an easier way to do things. eg: I can send

Room_1,89.3, 10-06-08, 10:25 If that's simpler (I have no idea)

Can I use AS Proxy to do this with apple script? If so can anyone point me to some “hello world” example? OR Is there a simpler way I'm missing

Here's some sudo code to show you what I'm thinking.

Applescript sudo code

loop
Tell application AS proxy “Get serial input”
If serial available then
Serial input = temp_value, room_number
Tell application mySQL database “open database_room_number_table”
Tell application mySQL database “Write temp_value to table”
Tell application mySQL database “Write date and time to table”
Tell application mySQL database “Close database room_number_table”
end

Problem two*
I need to be able to send characters back to the serial port from a browser which should be simple enough with a bit of PHP (I guess) my problem is I don't want to stop the logging function while I do that
Eg: Currently if I send a “U”(ascii) it sets the temperature UP by 1deg c

Can I log the serial data to the database (Via AS proxy) while still having the ability to send characters back to the Arduino from the Mac?

rgds
cwhitto

2

If you're already using PHP for part of it, why not just use PHP to interface to mysql? Here's an ok tutorial on using mysql w/ PHP: http://www.php-mysql-tutorial.com/

!c

3

hi in the PHP you have someting like direct connect to com1, you can send and read the commands.

with PHP you all so have the tools to connect to mySQL, send and read the information of a table (its simple as fast development)

if you have 3 or 4 rooms in the house, maby the best way is set up the arduido like this,

Send command ,

send rom1
receive rom1 20.7
send rom2
receive rom 19.5
send rom....

send up rom1 1
receive up rom1 1 (confirm)

send stop all
send start all

or

send start rom1
receive start rom1 (confirm)

in the PHP, you receive the commands, put the date and time and send to mySQL

Simple...

http://www.mysql.com/

and you can make in flash the display with grafics, and control all from flash,

http://library.creativecow.net/articles/brimelow_lee/php_mysql.php

http://www.spyfu.com/Term.aspx?t=1187537

if you need some help...

Lagarto Oliveira from Portugal