Storing sensor measurements in a MariaDB (open MySQL) database #MakewithMaxim

Published by gboscardin in the blog gboscardin's blog. Views: 244


Introduction

A fundamental part of the internet of things is the capacity to log measurements of sensors in databases, and then allow access of the data thru the internet. The aim of this project is managing to achieve this with the use of easily accessible components and open source software.

While there are faster solutions, like Arduino kits that make the process much easier and faster, understanding the integration of different components is necessary for most projects. Also, the ideas proposed thru this project are adaptable to a range of situations.

The project consists of three "parts". First being the communication of the microcontroller with the sensor, communication of the microcontroller with a personal computer thru USB protocol, and then communication from the computer to a MariaDB database.

While all these parts worked well, there was one issue: There is bug in the code that transmits the reading from the sensor to the personal computer. I did not manage to properly transmit "int" variables between the controller and the computer thru USB protocol; consequently, the database logs the gibberish like information. Unfortunately, I do not have time at this moment to correct this issue. But will do so in the near future.

BOM

As for hardware, the project requires:
1 - Protoboard;
2 - Tiva Launchpad;
3 - MAX31875 Thermo 6 Click board;
4 - 2 x 10k ohm resistors.

Then, in order to log the information:
1 - Personal computer.
2 - A personal server with a MariaDB database. My suggestion is the XAMPP package, available at:
https://www.apachefriends.org

In order to modify existing examples and program the interface (a C++ program) between the computer and the database:
1 - Code Composer Studio, which is available with the Tiva Launchpad.
2 - Visual Studio 2018. There is a version available for personal non commercial use.

The C++ program that connects the computer and the server has two important dependencies.
1 - Boost C++ libraries, available at: http://www.boost.org/
2 - Oracle's MySQL C++ connector, which is available at: https://dev.mysql.com/downloads/connector/cpp/

Schematics


The following circuit diagram illustrates the project:

schematics overview.png


Instructions

I've had the Tiva Launchpad board for about three years, but did not have an opportunity to test its USB and I2C capabilities. Managing to acquire data from a sensor and log it in a personal database has been one of my interests since I bought the board, but I did not have the time or right motivation to do it. Maxim's contest gave me the motivation to put my ideas in practice.

While I am used to programming MCUs, I've never had used USB or I2C communication. Overall, I was quite ignorant of the code required to make these things work. So, my main objective was managing to make the project work first, and later make it work well. Things went well until I hit an issue with the C code required to transfer the data stored in the MCU to the USB buffer: I did not manage to copy the information from the "data" variable to the buffer properly, so the database receives wrong information.

Despite this issue, other parts work well. The MCU does manage to read data from the sensor and the C++ program at the computer does manage to send information to the database. I did manage to transfer strings from the MCU memory to the database but failed to convert "int" variables to the buffer.

Overall, it was about modifying existing examples and mixing them into a final code. Due to limitations in time and my poor knowledge of the USB and I2C functions, I have likely made some mistake that either copied the wrong information at the MCU memory, or pulled a poorly formatted buffer from the sensor. I'll likely try to fix this issue, but only a few weeks in the future.

The project was divided in three "problems". First the communication between the sensor and the MCU, second the communication of the MCU with the personal computer, and third the communication from the computer to the database. The final part was mixing all of these, and observing the results. I'll describe each step and its obstacles:

Communication between the sensor and the MCU: utilizing the I2C protocol.

As can be seen thru the project schematic, the circuit required for I2C communication between the sensor and the board is relatively simple. Resistors are required, and their value is said be between 1k and 10k Ohm. While there is an equation for the appropriate value, I've used to 10k ones because they were at my desk.

The challenge was the code to interface the components, and I've only managed to make it work due to the code provided thru the following link:
https://eewiki.net/display/microcontroller/I2C+Communication+with+the+TI+Tiva+TM4C123GXL

My only modification to their was the location of a variable. In order to make the program work, I've mixed their functions with the I2C example provided by Texas Instruments.

The program worked, and I managed to confirm this thru the debug interface at Code Composer Studio. Below, a picture of the temperature measurement, with ice.

20180212_222607.jpg


Communication between the MCU and the computer: Modifying examples.

TI's "usb_bulk" example was my main source for this part of the project. It consists of a program for the MCU, and a host program in C which runs at a personal computer. It is used to transmit a string between the computer to the MCU, and then to the MCU back to the computer.

I supposed that this was enough and only noticed my failure at transmitting "integer" variables later, when all parts were put together.

Database connection:

Transmitting data from a C++ program to a MariaDB / MySQL database is more challenging than it appears to be, because there are several small issues with the libraries available. Fortunately, I had worked on some programs that required it, and this step was already done.

First, I copied TI's usb bulk C example into my own C++ database connection code. However, the C code had some issues which required modifications. But it worked, and I managed to transmit strings to the database.

All parts together, and the issue with the int variable

Last part was about joining all different parts and praying for them to work well together. It was then that the following issue appeared:


dbpicture.png

The issue is the transfer of the int variable "data", that stores the sensor readings, to the USB buffer. While I have tried every possible solution, from changing the variable to char or any possible way to copy its contents to the buffer, I did not manage to find a solution. Unfortunately, I do not have time to work on this at the moment.

There are two possible issues: First is the way by which I copied data from the sensor to the MCU, I did not format the data because I was eager to see the whole thing working. Second is that I did not manage to understand the MCU USB functions as well as I should.

Results and Conclusions:

I'd say that the "bulk" of the idea was implemented. Now, I do know how to read data from the sensor, read strings from the MCU to the PC, and then transmitting it to a database. The issue that remains is managing to transfer data correctly, and this is likely to require a considerable time over the code and the available documentation.

It is very likely that, in my rush to complete the project as soon as possible, I did not look at some code with the amount of care that it required. Plus, I lacked experience with the I2C and USB functions. Also, there was a lot of code involved, as can be seem below:

environment.png

Considering that there was very little to make it work as desired, I'll try to fix the buffer issue at some time in the future. Also, each part of the project required many changes in code or other things, such as setting the database and describing the program that connects to it. Since some people might find it useful, I'll work on future blog posts that properly describe these parts in the future.

Video
Unfortunately, there was not enough material to make a video out of the project. All it could be seem is the MCU flashing its LEDs as it transfers data to the computer, and the database storing wrong information.

Source Code

For some reason, I'm not managing to upload the files containing the code. I'll upload the codes as separate blog posts because they are large and require some explanations.

CAD Files
There are no CAD files.
You need to be logged in to comment