Arduino: data logger with MySQL

In the previous post, I explained how to install MySQL and how to create a database for use with Arduino projects. Now it’s time to go further and to explain how to create an Arduino temperature and humidity data logger.

Part 1 – the hardware

The project uses the following parts:

– one Seeeduino Mega (or any other Arduino Mega board) – this is mandatory as the code is quite large
– one Ethernet Shield
– one SEN-08257 temperature / humidity sensor from Sparkfun (based on SHT15x)
– one BOB-12708 real time module from Sparkfun (based on DS1307 RTC)
– one breadboard
– some connecting wires

In this schematic the RTC clock uses the I2C pins SDA and SCL, which in the case of Seeeduino MEga are on a separate small connector. For the SHT15 sensor, although is I2C compatible, I preferred to use the pins 7 and 8. The Ethernet shield comes on top of the Seeeduino Mega. The SPI data lines are from the ICSP connector, and the CS signals are on pins 4 and 10. Some pictures of the completed project are presented below. Note that the Seeeduino Mega has the holes in a different placement than in the classical Arduino Uno. To install it on the Arduino holder (DEV-11235) I had to remove the original fixing pins, then fix the Seeeduino board using two layers of thick double sided tape.

Part 2 – the software

In this project we first set the RTC timer using NTP, then we monitor the RTC, and every 10 minutes we log the time, temperature and humidity into the ardulogger database. Further, we will use DBPlot to create graphs of temperature and humidity.

For this project some prerequisites are needed:

First, we need the updated time library. Just download it and extract the files in the library folder of Arduino IDE.

Next, we need the SHT1x library from https://github.com/practicalarduino/SHT1x. After extracting the files into the library folder of Arduino IDE we have to rename it from SHT1x-master to SHT1xmaster, otherwise, we will get errors when compiling.

Finally, we have to install the MySQL connector libraries, which can be downloaded from https://launchpad.net/mysql-arduino. The version I used is mysql_connector_arduino-1.0.2b.zip.

To work with MySQL a small modification is needed: in mysql.h you need to uncomment the line:

#define WITH_SELECT // Uncomment this for use without SELECT capability

Try to compile the example files. Apply any diff files in the sha1 library if needed (I highly recommend http://winmerge.org/ to do this). If something goes wrong, you may take a look on Chuck’s blog, which gives a very good introduction to the MySQL connector. All we care about in this step is to be sure that the examples compile with no errors.

The code is as follows:

Some explanations:

First, we try to obtain an IP address for our Arduino project using DHCP. If everything is OK we then go to a Network Time Protocol server to obtain the current time and date. This is done only once, when the power is applied, to avoid overloading the NTP server, as this is a very important issue. You can read more about the problems related to abusing NTP servers in the Arduino forums.

The NTP routine first resolves ro.pool.ntp.org to obtain the IP address of an NTP server. Please observe that I’m using the national pool of NTP servers instead of the global one. More information on NTP servers can be found on http://www.pool.ntp.org/en/.

Once we have the IP address of a working NTP server we get the time. For this I used a slightly modified version of the NTP code provided by OpenReefs.

The DS1307RTC library requires the time to be stored in the tm structure. Most important, the years are measured starting with 1970 – thus the need to subtract this from the value returned by the year() function. We then call the RTC.write() function to update the RTC timer.

We wish to write into the MySQL database every 10 minutes. For this, we use two variables of integer type, minutes_now which store the current minute and minutes_next which stores the time for the next database operation. We read the RTC time every 5 seconds. If minutes_now equals minutes_next we first compute the next value for minutes_next, then we measure the temperature and humidity, and we write the time and the recorded values into the MySQL database.

To insert the data into the database we first connect to the MySQL database. Just for testing we show the last 5 entries. To insert the data we have to construct the query string. Special attention must be given to the time and date fields. Insert a space into the wrong position and you will have a MySQL error. The workaround was to convert the date and time to a string, then I used the trim() function to remove the spaces. Further, I converted the strings into character arrays, which are later used to construct the query. The temperature and humidity data are truncated to two decimal places, thus precision of the recorded data is around 1%.

We then run the query and we check the result by reading the last entry from the database. Finally, we close the database connection – this is useful if you log the data at large intervals.

The final code is quite large, occupying a huge area of both ROM and RAM memories. In the future I intend to create a smaller version of this code, to run on Arduino Uno boards.