Riotboard webserver : Part3 - Capture sensor data to database ..

Contents:

Part1: Environment Setup

Part2: Monitering Temperature with Xtrinsic Sensor

Part3: Capture sensor data to database  <-- You are here

Part4: Remote datalogger

In this part of the series we will setup a database structure to store our recorded temperature to mysql database.

We have seen how to install mysql along with other packages in previous blog.

From here on we will be operating in Root Mode.

we need to trim off certain default stuff from mysql.

This certainly not required if database used locally, but we got to be secure while using mysql remotely.

Lets start...

# sudo mysql_secure_installation

It will ask for certain setting, hit enter for all recommended defaults.

[ basic old school stuff ]

1.remove the default anonymous user from mysql.

Remove anonymous users? [Y/n] [HIT ENTER]

... Success!

 

2.makes sure ONLY root(you) can access remotely over ssh.

Disallow root login remotely? [Y/n] [HIT ENTER]

... Success!

 

3.removes a database public access anyone can access

Remove test database and access to it? [Y/n] [HIT ENTER]

- Dropping test database...

ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist

... Failed!  Not critical, keep moving...

- Removing privileges on test database...

... Success!

 

4. Load the new rules so that mysql gets it immediately.

Reload privilege tables now? [Y/n] [HIT ENTER]

Completing the above we got :

1. an admin called root with a password root (from blog1).

2. a setup ready for database creation.

Lets create a database.

You can setup the database and tables two ways.

1. Manually
2. Automatically  (Recommended)

Setting Database Manually:

Login to mysql:

# mysql -u root -p

Enter password: <YOUR ROOT PASSWORD>

 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 260

- ----------

- ---------

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

create the database and start using it.

mysql> create database db1;

mysql> use db1;

Creating a table in db1.

mysql> CREATE TABLE sensordata(temperature INT,time DATETIME);

Setting Database Automatically

There is nothing automatic here, you have to still compile the code

Code

//create db & table
//file : mysql_createdb.c
#include <mysql.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <mysql/my_global.h>
/*#include <mysql/mysql.h>*/

void mysql_custom_close(MYSQL *con)
{
  fprintf(stderr, "%s\n", mysql_error(con));
  mysql_close(con);
  exit(1);
}

int main(int argc, char **argv)
{
  MYSQL *con = mysql_init(NULL);

  if (con == NULL)
  {
      fprintf(stderr, "%s\n", mysql_error(con));
      exit(1);
  }

  if (mysql_real_connect(con,"localhost", "root", "root", NULL, 0, NULL, 0) == NULL)
  {
      mysql_custom_close(con);
  }

  if (mysql_query(con, "CREATE DATABASE IF NOT EXISTS db1"))
  {
        mysql_custom_close(con);
  }

  if (mysql_query(con, "use db1"))
  {
        mysql_custom_close(con);
  }

if (mysql_query(con, "CREATE TABLE IF NOT EXISTS sensordata(temperature INT,time DATETIME)"))
  {
        mysql_custom_close(con);
  }

  mysql_close(con);
  exit(0);
}

Compile and run the program

# gcc mysql_createdb.c -o mysql_createdb  $(mysql_config --cflags) $(mysql_config --libs)

# ./mysql_createdb

To verify the table creation:

1. Login to mysql

2. select database

3. display table

# mysql -u root -p
mysql> use db1;

mysql> show tables;

mysql> select * from sensordata;

The tables are going to be updated by a program that monitors temperatures and will have access to database .

Next we will update our sensor code to push data to the database.

bits of SQL code inserted in xtrinsic_temp.c to push sensor data to mysql database.

xtrinsic_temp.c >> xtrinsic_mysql.c

//file : xtrinsic_mysql.c

#include <mysql.h>

#include <mysql/my_global.h>

----- ------

----- ------

 

void mysql_custom_close(MYSQL *con)

{

  fprintf(stderr, "%s\n", mysql_error(con));

  mysql_close(con);

  exit(1);     

}

- - - - - -

- - - - - -

- - - - -

 

  MYSQL *con = mysql_init(NULL);

  char buf1[200];

  if (con == NULL)

  {

      fprintf(stderr, "%s\n", mysql_error(con));

      exit(1);

  }

 

  if (mysql_real_connect(con,"localhost", "root", "root", NULL, 0, NULL, 0) == NULL)

        mysql_custom_close(con);

 

  if (mysql_query(con, "use db1"))

        mysql_custom_close(con);

 

- - - - - - - -

- - - - - - - -

- - - - - - -

sprintf(buf1, "INSERT INTO sensordata VALUES(%d,NOW())", t_m);

  if (mysql_query(con, buf1))

      mysql_custom_close(con);

complete program code available as attachment.

Compiling and execute the above code :

# gcc xtrinsic_mysql.c -o  xtrinsic_mysql  -std=c99 $(mysql_config --cflags --libs)

# ./xtrinsic_mysql

Now the data will be displayed in terminal as well as pushed into database.

Checking back at database:

So temperature capturing is working fine.

Next time we will display sensor data in browser and may be create a small graph using collected data .