How do i connect arduino due using esp8266 module server to mysql database

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <WiFi.h>
#include <DHT.h>
#include <SPI.h>
#include <stdlib.h>
#include <RFID.h>
#include <LiquidCrystal.h>  
#define BLYNK_PRINT Serial
#include <ESP8266_Lib.h>
#include <BlynkSimpleShieldEsp8266.h>

#include <Adafruit_Sensor.h>

 IPAddress server_addr(***,***,***,**);          // MySQL server IP
char user[] = "root";           // MySQL user
char password[] = "";       // MySQL password

WiFiClient client;
MySQL_Connection conn((Client *)&client);

char INSERT_SQL[] = "INSERT INTO fire_contents(Temperature1) VALUES ( %d)";
char query[128];

/*=====================BLYNK============================*/
char auth[] = "7390e1f9b6e14cb7bd5b452e3b222f1b";

// Your WiFi credentials.
  char ssid[] = "No Wifi ";
  char pass[] = "unityenz123";
  
// Hardware Serial on Mega,Due0
  #define EspSerial Serial1
  
// Your ESP8266 baud rate:
  #define ESP8266_BAUD 115200
  
  ESP8266 wifi(&EspSerial);
  
/*=====================SENSOR VALUE=======================*/

//sensor values 
  int fval;
  int fval2;
  int gval;
  int gval2;
  int tval;
  int tval2;
  int hval;
  int hval2;
/*=====================THRESHOLD VALUES=====================*/  
//threshold Value
  int flameth =500;
  int gasth = 300;
  int tempth = 40;
/*=======================OUTPUT PINS=======================*/
//All digital outputs
  int ledf = 22;
  int ledf2 = 23;
  int ledg = 24;
  int ledg2 = 25;
  int ledt = 26;
  int ledt2 = 27;
  int buzzer = 28;
  int waterpump=29;
  int breaker = 50;
  
/*=======================INPUT PINS======================*/
//All analog Inputs
  int Iflame = A0;
  int Iflame2 = A1;
  int Igas = A2;
  int Igas2 = A3;
  int Itemp = 30;
  int Itemp2 = 31;
  
/*====================RFID DEFINITION=====================*/
/* Define the DIO used for the SDA (SS) and RST (reset) pins.*/
#define SDA_DIO 10
#define RESET_DIO 9
/* Create an instance of the RFID library */
RFID rfid(SDA_DIO, RESET_DIO); 

int key1 [5]={154,31,192,73,12};

/*==============HARDWARE/SOFTWARE DEFINITION=============*/
// temp  
  DHT dht1(Itemp, DHT22);
  DHT dht2(Itemp2, DHT22);

//LED Widget for app  
  WidgetLED led1(V10); //flame
  WidgetLED led2(V11);
  WidgetLED led3(V6); //temp
  WidgetLED led4(V7);
  WidgetLED led5(V13); //gas
  WidgetLED led6(V14);
  WidgetLED led7(V15); //Waterpump

//LCD Widget for app 
  WidgetLCD lcd1(V12); //lcd

// timer for blynkTimer 
  BlynkTimer timer;
  BlynkTimer timer1;
  BlynkTimer timer2;
  
  BlynkTimer timer3;

int al;
/*===============================================================================*/
/*                                  SETUP                                        */
/*===============================================================================*/

void setup() {
  
// put your setup code here, to run once:
  Serial.begin(9600);
  
// Set ESP8266 baud rate
  EspSerial.begin(ESP8266_BAUD);
  delay(10);
  
  Blynk.begin(auth, wifi, ssid, pass);
// You can also specify server:
//////////////////////////////////////////////////////////////////////////////////
Serial.println("Connecting to database");

  while (conn.connect(server_addr, 3306, user, password) != true) {
    delay(200);
    Serial.print ( "." );
  }

  Serial.println("");
  Serial.println("Connected to MySQL Server!");  
////////////////////////////////////////////////////////////////////////////////////
// Setup a function to be called every second
  timer.setInterval(500L, ValuesUpdate);
  timer1.setInterval(1000L, APP_LED);
  timer2.setInterval(1000L, Alarm_APP);
  timer3.setInterval(1000L, disarm);

  
// PINMODE DFINITION 
  for (int i = 22; i<31 ; i++){
    pinMode(i,OUTPUT);
  }
  pinMode(Iflame,INPUT);
  pinMode(Iflame2,INPUT);
  pinMode(Igas,INPUT);
  pinMode(Igas2,INPUT);
  
// TEMP SENSOR 
  dht1.begin();
  dht2.begin();

// RFID 
  /* Enable the SPI interface */
  SPI.begin(); 
  /* Initialise the RFID reader */
  rfid.init();
  }
/*===============================================================================*/
/*                                  LOOP                                         */
/*===============================================================================*/
void loop() {
  Blynk.run();
  timer.run(); // Initiates BlynkTimer
  timer1.run();
  timer2.run();
  if(digitalRead(buzzer)||digitalRead(waterpump)||digitalRead(breaker)){
  timer3.run();
  }
}
/*===============================================================================*/
/*                                  FUNCTIONS                                    */
/*===============================================================================*/

void Sensor_Readings(){
  fval = analogRead(Iflame);
  fval= abs(fval-1023);
  Serial.print("Flame 1 =");
  Serial.println(Iflame);
  
  fval2 = analogRead(Iflame2);
  fval2= abs(fval2-1023);
  Serial.print("Flame 2 =");
  Serial.println(fval2);
  
  gval = analogRead(Igas);
  Serial.print("Gas 1 ="); 
  Serial.println(gval);
  
  gval2 = analogRead(Igas2);
  Serial.print("Gas 2 =");
  Serial.println(gval2);
 
  if(!(isnan(dht1.readTemperature()))){
  tval = dht1.readTemperature();
  Serial.print("Temp 1 =");
  Serial.println(tval);
  sprintf(query, INSERT_SQL,tval);
  Serial.println(query);
  }
   if(!(isnan(dht2.readTemperature()))){
  tval2 = dht2.readTemperature();
  Serial.print("Temp 2 =");
  Serial.println(tval2);
   }
  
  if(!(isnan(dht1.readHumidity()))){
  hval = dht1.readHumidity();
  Serial.print("Humi 1 =");
  Serial.println(hval);
  }
   if(!(isnan(dht2.readHumidity()))){
  hval2 = dht2.readHumidity();
  Serial.print("Humi 2 =");
  Serial.println(hval2);
   }
  }
/*=====================================================================*/
  void ValuesUpdate(){
    Sensor_Readings();
    Blynk.virtualWrite(V2,tval);
    Blynk.virtualWrite(V3,tval2);
    Blynk.virtualWrite(V4,hval);
    Blynk.virtualWrite(V5,hval2);
    Blynk.virtualWrite(V8,gval);
    Blynk.virtualWrite(V9,gval2);  
  }
/***********************************************************************/
 void APP_LED(){
//flame 1
    if(fval>flameth){
      led1.on();
         }
     else{
      led1.off();
     }
// flame 2
     if(fval2>flameth){
      led2.on();
          }
      else{
      led2.off();
      }
// temp 1
     if(tval>tempth){
      led3.on();
     }
     else{
      led3.off();
     }
// temp 2 
     if(tval2>tempth){
      led4.on();
    }
    else{
      led4.off();
    }
// gas 1 
     if(gval>gasth){
      led5.on();
    }
    else{
      led5.off();
    }
// gas 2 
     if(gval2>gasth){
      led6.on();
    }
    else{
      led6.off();
    }
}

/**********************************************************************************/

void Alarm_APP(){
  if(Sys_state(fval,tval,gval)||Sys_state(fval2,tval2,gval2)){
    lcd1.clear();
      if(Sys_state(fval,tval,gval)){
        led7.on();
        lcd1.print(0,0,"ALARM TRIGGERED");
        lcd1.print(0,1,"SHARED_FAC Room1");
        }
      else{
        led7.on();
        lcd1.print(0,0,"ALARM TRIGGERED");
        lcd1.print(0,1,"SHARED_FAC Room2");
        }
  }
  else if(thres_state(fval2,tval2,gval2)){
        lcd1.clear();
       // led7.off();
        lcd1.print(0,0,"SYSTEM UNSAFE");
        lcd1.print(0,1,"SHARED_FAC Room2");
        }
  else if(thres_state(fval,tval,gval)){
        lcd1.clear();
       // led7.off();
        lcd1.print(0,0,"SYSTEM UNSAFE");
        lcd1.print(0,1,"SHARED_FAC Room1");
      }
else if(!digitalRead(buzzer)&&!digitalRead(waterpump)&&!digitalRead(breaker)){
        led7.off();
        lcd1.print(0,0,"SYSTEM SAFE.....");
        lcd1.print(0,1,"Rm1-OK....Rm2-OK");
}
}

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
BLYNK_WRITE(V16){
  flameth = param.asInt(); // assigning incoming value from pin V1 to a variable
  // You can also use:
  // String i = param.asStr();
  // double d = param.asDouble();
  Serial.print("flame threshold is = : ");
  Serial.println(flameth);
}

BLYNK_WRITE(V17){
  gasth = param.asInt(); // assigning incoming value from pin V1 to a variable
  // You can also use:
  // String i = param.asStr();
  // double d = param.asDouble();
  Serial.print("Gas threshold is = : ");
  Serial.println(gasth);
}

BLYNK_WRITE(V18){
  tempth = param.asInt(); // assigning incoming value from pin V1 to a variable
  // You can also use:
  // String i = param.asStr();
  // double d = param.asDouble();
  Serial.print("Tempurature threshold is = : ");
  Serial.println(tempth);
}

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
int Sys_state(int flame,int temp,int gas){      //buzzerr alarm indicating function
  if((flame > flameth) && (temp > tempth)){
    return 1;
  }
  else if(gas >= gasth){
    return 1;
  }
  else{
    return 0;
  }
  }
  
int thres_state(int flame,int temp,int gas){      //buzzerr alarm indicating function
  if((flame > flameth) || (temp > tempth)||(gas > gasth)){
    return 1;
  }
  else{
    return 0;
  }
  }
  
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void led(int flame,int temp,int gas,int Room){ //Led function turn on and off
  switch (Room){
    case 1:
      if(flame> flameth){
              digitalWrite(ledf,HIGH);
            }
              else{
               digitalWrite(ledf,LOW);
              }
     if(temp > tempth){
              digitalWrite(ledt,HIGH);
            }
              else{
               digitalWrite(ledt,LOW);
            }
     if(gas> gasth){
              digitalWrite(ledg,HIGH);
            }
              else{
               digitalWrite(ledg,LOW);
              }
    case 2:
      if(flame> flameth){
                digitalWrite(ledf2,HIGH);
              }
                else{
                 digitalWrite(ledf2,LOW);
                }
       if(temp > tempth){
                digitalWrite(ledt2,HIGH);
              }
                else{
                 digitalWrite(ledt2,LOW);
              }
       if(gas> gasth){
                digitalWrite(ledg2,HIGH);
              }
                else{
                 digitalWrite(ledg2,LOW);
                }
  }
}

void disarm(){
  if(digitalRead(buzzer)||digitalRead(waterpump)||digitalRead(breaker)){
      /* Has a card been detected? */
  if (rfid.isCard()){
    /* If so then get its serial number */
    rfid.readCardSerial();
    int disarmval=0;
   
   for(int i=0;i<5;i++)
    {
     if((rfid.serNum[i])==key1[i])
    {
     disarmval++;
    }
    }
    if(disarmval==5){
    led7.off();
    }
  }
}
}

Tobe honest, you’re trying to do quite a lot with one single device.
Doing three digital reads in the void loop isn’t something that the Blynk library is going to like very much.
You do t actually need 4 Blynk Timer objects, one object can support up to 16 instances so that would save you some processing power, but keeping-up a Blynk and MySQL server connection at the same time is asking quite a lot of the ESP.

Unless the MySQL database is essential for other things, I’d think about using the Blynk database to store that data instead.
Alternatively, I suppose you could have a separate ESP dvi e with its own Blynk Auth code and Bridge code that takes the Blynk actions and writes them to MySQL.

Using Node-Red and MQTT is also an option, as there are Blynk and MySQL plugins available for Node-Red.

Pete.

how would i read the values from the blynk server to nodered… your advice would be greatly appreciated…

Using the Blynk ws contrib for Node-Red.
More info here:

Pete.

Pete,
Good day that is quite an awesome project you have their, but i was trying to create a node red flow to read the temperature values from blynk server to the output panel of node red but nothing showed here is my flow

and

as you see i’m getting error for the second flow. this is how i filled out the http request node

You’ll get he temperature reading from V2 when the value changes (in just the same way that BLYNK_WTRITE(V2) is triggered in C++).
To force the temperature value to be outputted from the server you’ll need to add a Sync node and inject something in to it to trigger it (a timestamp is sufficient).

Try reading this to troubleshoot the HTTP GET error:

Pete.

ok Pete,

so what your saying in the first flow i would recieve the value…

in the first flow you should see the temperature value when the Blynk server value is updated. If you’re pushing a new temperature value every 5 seconds from a device then it should appear in Node-Red every 5 seconds.
If the value is sitting on the server but isn’t being refreshed then a Sync-all or Sync-V2 will force the server to push the value out.

Pete.

Pete, thanks i was able to retrieve the values

1 Like

I have one more question i want to write the values to a mysql database but i am encountering an error in the function node. here is my node flow

here is the error

msg.topic=‘INSERT Into fire_contents (Temperature1) values(?)’;
return msg;

that is what is in my function node

Pete, i must say thanks the problem that was encountered is solved

1 Like

That’s great.
So have I managed to convert another follower over to the dark side of Node-Red?

Pete.

:sweat_smile: i guess you have

1 Like

Yay!