Arduino temperature logger to MySQL

As a first time blogger I decided to create a tutorial which is essentially my experiences of creating an Arduino sketch. The project is to log temperature recordings from the Arduino Ethernet directly to a local mysql database.

Components

• Arduino Ethernet
• Thermometer (TMP36)
• FTDI cable (or chip with usb cable)
• 5 wires

Step 1

Download the arduino software from here.

Step 2

Download FTDI driver from here. Choose the valid driver for your computer under the sub-heading 'Currently Supported VCP Drivers'.

Step 3

Follow the steps to create the Arduino example circ-10. A couple good guide can be found on the The Arduino Experience blog and oomalout. We will be using this code to calculate the temperature. When you upload the sketch you can go to Arduino -> Tools -> Serial Monitor to view the temperature output.

Step 4

Once you get that sketch working we can start to look at mysql. A great library can be found at Chuck's Blog. It takes a bit of effort locating the files, they can be found here. You will also need the 'sha1' library. I can't remember exactly where I downloaded it from but I think this is one is the same Arduino-oath-token. Put sha1.cpp and sha1.h in a folder called sha1 in your Arduino Library folder.

Step 5 - Preparing database

I'm going to assume you already got MySQL installed and running. Here is the SQL statement to create the table. The main thing to understand here is that 'created_at' is a timestamp defaulted to current_timestamp. This adds the time automatically when a record is inserted, so you don't have to code for it.

CREATE TABLE `temperature` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `temperature` float DEFAULT NULL,

  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Step 6

So hopefully everything is properly installed. Here is the code. 

Note. I have know prior knowledge of C programming and so I found building the insert string really difficult. It is most likely horrible code, if there is a better way to do this please post.

Replace the X's in the mac address with the characters on the back of your arduino. Replace the server_addr with the ip of the computer that has your mysql database.

#include "SPI.h"

#include "Ethernet.h"

#include "sha1.h"

#include "mysql.h"

/* Setup for Ethernet Library */

byte mac_addr[] = { 0xX, 0xX, 0xX, 0xX, 0xX, 0xX };

IPAddress server_addr(192, 168, 1, 1);

/* Setup for the Connector/Arduino */

Connector my_conn; // The Connector/Arduino reference

int temperaturePin = 0;

char user[] = "arduino";

char password[] = "arduino";

void setup() {

  Ethernet.begin(mac_addr);

  Serial.begin(14400);

  delay(1000);

  Serial.println("Connecting...");

  if (my_conn.mysql_connect(server_addr, 3306, user, password))

  {

      Serial.println("Query Success!");

  }

  else

    Serial.println("Connection failed."); 

}

void loop() {

  addTempEvent();

}

//taken straight from the adruino circ-10 example

float getVoltage(int pin){

return (analogRead(pin) * .004882814);

}

void addTempEvent()

{

  //taken straight from the adruino circ-10 example

  float temperature = getVoltage(temperaturePin);

  //temperature = (((temperature - 0.5) * 100)*1.8) + 32;  //Fahrenheit

  temperature = (temperature - 0.5) * 100; 

  

  //I'm sure there is a cleaner way to build this string

  char insert_sql[110] = "INSERT INTO tempMonitor.temperature (id,temperature) VALUES (ID,";

  dtostrf(temperature,1,2, &insert_sql[64]);

  char* array3 = ")";

  strcat(insert_sql,array3);

  

  //Serial.println(insert_sql);

  delay(5000);

  my_conn.cmd_query(insert_sql);

  delay(120000); 

}

I'm not sure if the temperature was very accurate. I may buy a better one or some of the numbers may need tweaked. However I consider the project a success as it does what I set out to do.

Any questions or improvements are more than welcome.