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: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.
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
