Arduino MySQL connector (Arduino On-line/real-time DataBase)

Introductions:

Sometimes when computing machines are becoming smaller and smaller,we cant deny the fact that its not only the processing capabilities that we must consider , but also the capacity to store data.Since we have discussed awhile ago the speed and efficiency of this small machine/s,let's also tackle ways on how  to use them as client or server to save bulk of data.Traditionally ,  data logging in an embedded system should have  a  third party connector/s or hook-up interface  linked to the computers or servers before micro-controllers  are able to  store bulked signals or  raw data .A good  example  was MCU gathered signals which are fed to the serial port of a PC(server) then parse via PHP and dumped those data in mysql.Another example is dumping all data at the specified ROM built in to the MCU, but the former was a better recommendation. 

Just recently, I've came up to a data logger system design , comprises of different transducers  which are capable of  detecting   digital and analog  signals  considered as critical parameters inside  the Data Center.To name those signals: light, humidity,temperature,sounds,distance and etc. Our Arduino functions  as a controller to gather  data , and  its embedded program measures the equivalent units to  represent  the scale of data sense by these transducers.

In this scratch article, we will introduce a new mysql connector ported by Dr. Chuck on his blog http://drcharlesbell.blogspot.com/2013/04/introducing-mysql-connectorarduino_6.html 
.We've also used Wiznet Ethernet Shield available at the local store owned by "ThinkBox" (http://store.mytinkbox.com) ,you can order the module on-line by the way. So our contribution will be to utilize, maximize its given functions and procedure just like an ordinary SQL are  coded and give an example on programming it.You may try to explore and find out how it simplifies database in an embedded systems.

Requirements:
Mysql Connector
Mysql Server on PC
Arduino2560 Mega
Wiznet Ethernet Shield (http://store.mytinkbox.com  or  http://www.e-gizmo.com/ )
Source: Hi-Techno Barrio   http://code.google.com/p/hi-techno-barrio/downloads/list

Objectives:
(1)To enhance the capabilities of microprocessor and macro-processor as clients in a data base system.

(2) To test new Mysql connector


Methodologies:

1) Download Mysql connector
 https://launchpad.net/mysql-arduino

2) Unzip the Mysql connector zip files
root@localhost# unzip  mysql_connector.zip

3) Copy the files inside  the Arduino library folder
root@localhost# mv mysql_connector MYSQL
root@localhost# cp -r MYSQL /usr/share/arduino/libraries
root@localhost# cp -r sha1     /usr/share/arduino/libraries

4) Cut and paste the sample  code given in the Arduino IDE
root@localhost#  arduino dcms.ino

5) Compile the code with your preferred Arduino2560
Note:
5.1)  Select the proper baud rate (serial sets to 115200 kbs)
5.2) Just  point  mouse to the arrows and functions will be highlighted,click the arrows for compiling and loading
5.3) Choose the preferred sensors function in the sample program

6)   Design data schema (Mysql)
root@ localhost# mysql  -u root -p

6.1) create mysql database
mysql> create dcmsDB;

6.2) display mysql database
mysql>  show databases;

6.3) choose mysql databse
mysql>  use dcmsDB;

6.4) create table in mysql
mysql>  CREATE TABLE `dcmsData` (
  `sensorid` int(11) NOT NULL AUTO_INCREMENT,
  `Humidity` FLOAT DEFAULT NULL,
  `Light` INTEGER ,
  `Noise` INTEGER DEFAULT NULL,
  `Smoke` INTEGER DEFAULT NULL,
  `Temperature` FLOAT DEFAULT NULL,
  `AC` INTEGER DEFAULT NULL,
  `DayTime` DATETIME DEFAULT NULL,
  PRIMARY KEY (`sensorid`),
  UNIQUE KEY `rowid_UNIQUE` (`sensorid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

6.5) insert row data  mysql table
mysql> INSERT INTO  dcmsData (Humidity,Light,Noise,Smoke,Temperature,AC,DayTime) values('100','200','300','400','500','600',NOW());

6.6)  display table structure in mysql
mysql>  describe dcmsData

6.7)   display data
mysql> SELECT * FROM dcmsData;


7) Just to give an overview , here are some parts of  the program:

(6.1) Include files
#include "NewPing.h"
#include "autonomos.h"
#include "webcontrol.h"
#include "udpsend.h"
#include "DHT.h"

// include files for mysql
#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"


6.2) /* Setup for Ethernet Library */
/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192,168,1,2);
IPAddress ip(192,168,1,177);
IPAddress gateway(192,168,1, 1);
IPAddress subnet(255, 255, 255, 0);
char sqlbuf[128];
char sqlDbase[] = " USE dcmsDB";
 
6.3) /*Setup for MySQL  */
unsigned int mysqlPort=3306;
Connector my_conn; // The Connector/Arduino reference
char user[] = "root";
char password[] = "Igorotzki";
boolean sqlconnect= false;

(6.4) Mysql procedures
void mysqlBegin()
{

  delay(350);
    Serial.println("connecting MySQL Server..");
  
     if (my_conn.mysql_connect(server_addr, mysqlPort, user, password))
      {
       sqlconnect=true;
       Serial.println("Query Success!");
        delay(150);
        my_conn.cmd_query(sqlDbase);
       }
     else
      {
       Serial.println("Connection failed.");
      }
 
}

// sending data to mysql
void mysqldata()
{
  // uncomment to use the given functions
  // tmp = temperature();
  // ht  = humidity();
  // smk = smoke();
  // ns  = noise();
  // lt  = light();
  // ac  = ACData();
 
if (sqlconnect==true)
  {
    delay(150);
    //"INSERT INTO  dcmsData (Humidity,Light,Noise,Smoke,Temperature,AC,DayTime) values('100','200','300','400','500','600',NOW()) ";
   sprintf(sqlbuf, "INSERT INTO dcmsData (Humidity,Light,Noise,Smoke,Temperature,AC,DayTime) values ('%f','%d','%d','%d','%d','%f',NOW())",ht,lt,ns,smk,tmp,ac) ;
   my_conn.cmd_query(sqlbuf);
   Serial.println("Data stored!");
   }

} // end mysql...!


6.5)
// main program
Void loop()
{

     mysqldata();
     range(1);
     robotCommand("independent");
     cameraPosition();
}

6.6)
If you are interested with the complete code of this techno-blog, please include your e-mail account in the comment portion!


Details(1)  Data logging (DHT11,LM35,LDR,Sound,URF)

Details(2) Mysql Connector download site
 















Details(3) Mysql connector and its compilation

Details(4) Unzip the mysql connector files
 

















Details(5) Successful compilation of Mysql connector using Ubuntu

Details(6) Mysql shema ->database ->tables















Details(7) Php code




















Details(8): Web page Display (PHP)
















Summary:

Trouble(1)Serial.print function ("Error 255=." and then "Connection failed")

Shooting:
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password';


Trouble(2) Using database

Shooting: Please  add the  following procedure
my_conn.cmd_query("USE dcmsDB);

Trouble(3)
in sha1.h virtual void write(uint8_t);
error: conflicting return type specified for 'virtual void Sha1Class::write(uint8_t)'
do you know how to fix it?

Shooting:
Apply the diff included with the source code.

Trouble(4)  Declaring floating point in mysql query

Shooting:
float t = do_something();
char buf[128];
sprintf(buf, "INSERT INTO test.motion VALUES (NULL, '%f')", t);
my_conn.cmd_query(buf);

Conclusions:

So we have embedded mysql in MCU