Skip to content

Instantly share code, notes, and snippets.

@Electronza
Created December 13, 2019 09:53
Embed
What would you like to do?
Arduino data logger with MySql
/*******************************************************************
____ __ ____ ___ ____ ____ __ __ _ ____ __
( __)( ) ( __)/ __)(_ _)( _ \ / \ ( ( \(__ ) / _\
) _) / (_/\ ) _)( (__ )( ) /( O )/ / / _/ / \
(____)\____/(____)\___) (__) (__\_) \__/ \_)__)(____)\_/\_/
Project name: Arduino: data logger with MySQL
Project page: https://electronza.com/arduino-data-logger-mysql/
********************************************************************/
#include <SPI.h>
#include <Ethernet.h>
#include <Dns.h>
#include <Time.h>
#include <Wire.h>
#include <DS1307RTC.h>
#include <SHT1x.h>
#include <sha1.h>
#include <mysql.h>
#include <string.h>
/* ******** Ethernet Card Settings ******** */
// Set this to your Ethernet Card Mac Address
byte mac[] = {0x90, 0xA2, 0xDA, 0x0D, 0xFE, 0x43 };
/* ******** NTP Server Settings ******** */
/* us.pool.ntp.org NTP server
(Set to your time server of choice) */
IPAddress timeServer;
/* Set this to the offset (in seconds) to your local time
This example is GMT + 2 */
const long timeZoneOffset = 7200L;
/* Syncs to NTP server every 15 seconds for testing,
set to 1 hour or more to be reasonable */
unsigned int ntpSyncTime = 15;
/* ALTER THESE VARIABLES AT YOUR OWN RISK */
// local port to listen for UDP packets
unsigned int localPort = 8888;
// NTP time stamp is in the first 48 bytes of the message
const int NTP_PACKET_SIZE= 48;
// Buffer to hold incoming and outgoing packets
byte packetBuffer[NTP_PACKET_SIZE];
// A UDP instance to let us send and receive packets over UDP
EthernetUDP Udp;
/* ********** RTC Time settings ********* */
tmElements_t tm;
int minutes_now;
int minutes_next;
/* ******* Settings for the SHT1x sensor */
#define dataPin 9
#define clockPin 8
SHT1x sht1x(dataPin, clockPin);
float temp_c;
float humidity;
/* ***** MySQl Server settings ********** */
Connector my_conn; // The Connector/Arduino reference
IPAddress server_addr(192, 168, 1, 133); // IP address of MySQL server
char user[] = "arduino";
char password[] = "arduino";
// maximum tries to connect
int num_fails;
#define MAX_FAILED_CONNECTS 5
// We define some strings
char myquery[150]; // more than enough; stores command to print table content
char insquery[200]; // more than enough; stores command to insert new data
const char testselect[] ={"SELECT * FROM ardulogger.ardudata ORDER BY id DESC LIMIT %s"};
// general format to insert data is:
//const char insertdata[] = "INSERT INTO ardulogger.ardudata (date,time,temp,humi) VALUES ('2014-11-11','11:45:11', 25.8 , 31.21)";
//const char insertdata[] = {"INSERT INTO ardulogger.ardudata (date,time,temp,humi) VALUES ('%s-%s-%s','%s:%s:%s', %s , %s)"};
const char insertdata[] = {"INSERT INTO ardulogger.ardudata (date,time,temp,humi) VALUES ('%s-%s-%s','%s:%s:%s', %s , %s)"};
// buffer for number to character conversion
char yearbuf[5];
char monthbuf[3];
char daybuf[3];
char hourbuf[3];
char minutebuf[3];
String temp_buf;
char secbuf[3];
char tempbuf[6];
char humibuf[5];
int timebuf;
void setup() {
Serial.begin(9600);
// Ethernet shield and NTP setup
int i = 0;
int DHCP = 0;
DHCP = Ethernet.begin(mac);
//Try to get dhcp settings 30 times before giving up
while( DHCP == 0 && i < 30){
delay(1000);
DHCP = Ethernet.begin(mac);
i++;
}
if(!DHCP){
Serial.println("DHCP FAILED");
for(;;); //Infinite loop because DHCP Failed
}
Serial.println("DHCP Success");
//Just for testing we print the obtained IP address
Serial.print("My IP address: ");
for (byte thisByte = 0; thisByte < 4; thisByte++) {
// print the value of each byte of the IP address:
Serial.print(Ethernet.localIP()[thisByte], DEC);
Serial.print(".");
}
Serial.println();
// We get the address of a NTP server from NTP pool
DNSClient dns;
dns.begin(Ethernet.dnsServerIP());
dns.getHostByName("ro.pool.ntp.org",timeServer);
Serial.print("NTP IP from the pool: ");
Serial.println(timeServer);
// Now we get the time
Serial.println("trying to get the time");
int trys=0;
while(!getTimeAndDate() && trys<10){
trys++;
}
if(trys<10){
Serial.println("Ntp server update success");
}
else{
Serial.println("Ntp server update failed");
}
// Print the time
clockDisplay();
// We have the right time, now we set the RTC clock
if (RTC.read(tm)) {
Serial.println("The DS1307 is running.");
Serial.println("Updating the RTC time.");
Serial.println();
tm.Day = day();
tm.Month = month();
tm.Year = year()-1970;
tm.Hour = hour();
tm.Minute = minute();
tm.Second = second();
RTC.write(tm); //update the time
} else if (RTC.chipPresent()) {
Serial.println("The DS1307 stopped.");
Serial.println("Setting the RTC time.");
Serial.println();
tm.Day = day();
tm.Month = month();
tm.Year = year()-1970;
tm.Hour = hour();
tm.Minute = minute();
tm.Second = second();
RTC.write(tm); //update the time
}
else {
Serial.println("DS1307 read error! Please check the circuitry.");
Serial.println();
}
// Just in case we read the RTC time for debugging purposes
// Serial.println("Computing the next DB access time");
RTC.read(tm);
minutes_now=tm.Minute;
// minutes_next=floor(tm.Minute/10)*10+10;
minutes_next=minutes_now+1; //debgging - write every minute to DB
if (minutes_next==60)
minutes_next=0;
Serial.print("Current minute: ");
Serial.print(minutes_now);
Serial.println();
Serial.print("Minute for the next database write: ");
Serial.print(minutes_next);
Serial.println();
} //end of setup
// functions for NTP server
// Do not alter this function, it is used by the system
int getTimeAndDate() {
int flag=0;
Udp.begin(localPort);
sendNTPpacket(timeServer);
delay(1000);
if (Udp.parsePacket()){
Udp.read(packetBuffer,NTP_PACKET_SIZE); // read the packet into the buffer
unsigned long highWord, lowWord, epoch;
highWord = word(packetBuffer[40], packetBuffer[41]);
lowWord = word(packetBuffer[42], packetBuffer[43]);
epoch = highWord << 16 | lowWord;
epoch = epoch - 2208988800 + timeZoneOffset;
flag=1;
setTime(epoch);
//ntpLastUpdate = now(); not needed anymore as we update the time only once
}
return flag;
}
// Do not alter this function, it is used by the system
unsigned long sendNTPpacket(IPAddress& address)
{
memset(packetBuffer, 0, NTP_PACKET_SIZE);
packetBuffer[0] = 0b11100011;
packetBuffer[1] = 0;
packetBuffer[2] = 6;
packetBuffer[3] = 0xEC;
packetBuffer[12] = 49;
packetBuffer[13] = 0x4E;
packetBuffer[14] = 49;
packetBuffer[15] = 52;
Udp.beginPacket(address, 123);
Udp.write(packetBuffer,NTP_PACKET_SIZE);
Udp.endPacket();
}
// Clock display of the time and date (Basic)
void clockDisplay(){
Serial.print(hour());
printDigits(minute());
printDigits(second());
Serial.print(" ");
Serial.print(day());
Serial.print(" ");
Serial.print(month());
Serial.print(" ");
Serial.print(year());
Serial.println();
}
// Utility function for clock display: prints preceding colon and leading 0
void printDigits(int digits){
Serial.print(":");
if(digits < 10)
Serial.print('0');
Serial.print(digits);
}
// End of NTP functions
void print2digits(int number) {
if (number >= 0 && number < 10) {
Serial.write('0');
}
Serial.print(number);
}
void loop(){
RTC.read(tm);
minutes_now=tm.Minute;
if (minutes_now==minutes_next){
//if (time_now.Minute==minutes_next){
// compute the next time for write
//minutes_next=floor(tm.Minute/10)*10+10; // write every 10 minutes - normal mode
minutes_next=minutes_now+1; // write every minute - debug mode
if (minutes_next==60)
minutes_next=0;
temp_c = sht1x.readTemperatureC();
humidity = sht1x.readHumidity();
// we construct the INSERT code here
// first we initialize the buffers
temp_buf=String(tm.Year+1970);
temp_buf.trim();
temp_buf.toCharArray(yearbuf,5);
temp_buf=String(tm.Month);
temp_buf.trim();
temp_buf.toCharArray(monthbuf,3);
temp_buf=String(tm.Day);
temp_buf.trim();
temp_buf.toCharArray(daybuf,3);
temp_buf=String(tm.Hour);
temp_buf.trim();
temp_buf.toCharArray(hourbuf,3);
temp_buf=String(tm.Minute);
temp_buf.trim();
temp_buf.toCharArray(minutebuf,3);
temp_buf=String(tm.Second);
temp_buf.trim();
temp_buf.toCharArray(secbuf,3);
// float to char conversion
dtostrf(temp_c, 5 , 2, tempbuf);
dtostrf(humidity, 5, 2, humibuf);
// now we build the command for data insertion
sprintf(insquery,insertdata,yearbuf,monthbuf,daybuf,hourbuf,minutebuf,secbuf,tempbuf,humibuf);
//ready to work with the database
Serial.println("DB write: ");
// Here comes the MySQL data code
if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
// check for connection
if (my_conn.is_connected()) {
Serial.println("Last 5 DB entries!");
sprintf(myquery,testselect,"5");
Serial.println(myquery);
my_conn.cmd_query(myquery);
delay(250); // small delay to wait for MySQl server to respond
my_conn.show_results();
delay(1000);
Serial.println("Inserting new data!");
Serial.println(insquery);
my_conn.cmd_query(insquery);
delay(1500); // small delay to wait for MySQl server to respond
// We verify the last entered data
Serial.println("New data entered!");
sprintf(myquery,testselect,"1");
Serial.println(myquery);
my_conn.cmd_query(myquery);
delay(250); // small delay to wait for MySQl server to respond
my_conn.show_results();
num_fails = 0;
} else {
//my_conn.disconnect();
Serial.println("Connecting again...");
if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
delay(500);
Serial.println("Success!");
} else {
num_fails++;
Serial.println("Connect failed!");
if (num_fails == MAX_FAILED_CONNECTS) {
Serial.println("No MySQL server available...");
delay(2000);
}
}
}
// close the database connection
my_conn.disconnect();
}
}
// Just prints the time so we know we are not stuck
Serial.print("Current time is ");
print2digits(tm.Hour);
Serial.write(':');
print2digits(tm.Minute);
Serial.write(':');
print2digits(tm.Second);
Serial.println();
// Basically we check the time every 5 seconds. You can increase this
delay(5000);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment