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;

1z2lisn.jpg

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:

zlxehh.jpg

 

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 .