BLYNK
HOME       📲 GETTING STARTED       📗 DOCS       ❓HELP CENTER       👉 SKETCH BUILDER

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

#1
#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();
    }
  }
}
}
0 Likes

#2

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.

0 Likes

#3

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

0 Likes

#4

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

Pete.

0 Likes

#5

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

0 Likes

#6

and

0 Likes

#7

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

0 Likes

#8

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.

0 Likes

#9

ok Pete,

0 Likes

#10

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

0 Likes

#11

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.

0 Likes

#12

Pete, thanks i was able to retrieve the values

1 Like

#13

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

0 Likes

#14

here is the error

0 Likes

#15

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

that is what is in my function node

0 Likes

#16

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

1 Like

#17

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

Pete.

0 Likes

#18

:sweat_smile: i guess you have

1 Like

#19

Yay!

0 Likes