Arduino data logger with MySql

mysql.ino

/*******************************************************************
 ____  __    ____  ___  ____  ____   __   __ _  ____   __  
(  __)(  )  (  __)/ __)(_  _)(  _ \ /  \ (  ( \(__  ) / _\ 
 ) _) / (_/\ ) _)( (__   )(   )   /(  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);
}