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

Insert Data to mySQL

#1

Hello, thank you for your awesome app! I love it
the project I’m working on is getting data from pins and logging the using (insert into) in mySQL

this is a draft code because I’m using temp as a placeholder for what I want
I get this error

Exception (3):
epc1=0x402064c2 epc2=0x00000000 epc3=0x00000000 excvaddr=0x40232a24 depc=0x00000000

I have commented the execute out because I just want to see the value for now, but I want that to work too


#define BLYNK_PRINT Serial    // Comment this out to disable prints and save space
#include <SPI.h>
#include <ESP8266WiFi.h>
#include <BlynkSimpleEsp8266.h>
#include <SimpleTimer.h>
#include <DHT.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

 #include <Adafruit_Sensor.h>

 
#define sensorPin1 0
// You should get Auth Token in the Blynk App.
// Go to the Project Settings (nut icon).
char auth[] = "auth";

// Your WiFi credentials.
// Set password to "" for open networks.
char ssid[] = "ssis";
char pass[] = "pass";

#define DHTPIN 2          // What digital pin we're connected to

// Uncomment whatever type you're using!
#define DHTTYPE DHT11     // DHT 11
//#define DHTTYPE DHT22   // DHT 22, AM2302, AM2321
//#define DHTTYPE DHT21   // DHT 21, AM2301

DHT dht(DHTPIN, DHTTYPE);
SimpleTimer timer;

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

char INSERT_SQL[] = "INSERT INTO officeto_plants.TBL_READINGS(ID_PLANT, AIR_HUMIDITY, AIR_TEMPERATURE, SOIL_MOISTURE_1) VALUES (1, NULL, NULL, %d)";
//char INSERT_SQL[] = "INSERT INTO officeto_plants.TBL_READINGS(ID_PLANT, AIR_HUMIDITY, AIR_TEMPERATURE, SOIL_MOISTURE_1, SOIL_MOISTURE_2) VALUES (1, NULL, NULL, %d, %d)";
char query[128];

// This function sends Arduino's up time every second to Virtual Pin (5).
// In the app, Widget's reading frequency should be set to PUSH. This means
// that you define how often to send data to Blynk App.
void sendSensor()
{
  
  float h = dht.readHumidity();
  float t = dht.readTemperature(); // or dht.readTemperature(true) for Fahrenheit

  if (isnan(h) || isnan(t)) {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }
  // You can send any value at any time.
  // Please don't send more that 10 values per second.
  Blynk.virtualWrite(V5, h);
  Blynk.virtualWrite(V6, t);
  

  //delay(10000); //10 sec

  sprintf(query, INSERT_SQL, t);

  Serial.println(query);

  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
 // cur_mem->execute(query);

 // delete cur_mem;

}

void setup()
{
  pinMode(sensorPin1, INPUT);
  Serial.begin(9600); // See the connection status in Serial Monitor
  Blynk.begin(auth, ssid, pass);

  dht.begin();

  // Setup a function to be called every second
  timer.setInterval(1000L, sendSensor);
}

void loop()
{
  Blynk.run(); // Initiates Blynk
  timer.run(); // Initiates SimpleTimer
}

Thank you for your help!

0 Likes

#2

this is what I did:



#define BLYNK_PRINT Serial    // Comment this out to disable prints and save space
#include <SPI.h>
#include <ESP8266WiFi.h>
#include <BlynkSimpleEsp8266.h>
#include <SimpleTimer.h>
#include <DHT.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

 #include <Adafruit_Sensor.h>

 IPAddress server_addr(192, 168 ,200, 233);          // MySQL server IP
char user[] = "roshna";           // MySQL user
char password[] = "P@ssword";       // MySQL password

#define sensorPinD4P2 2

int pinVal = 0;     // variable to store the read value

// You should get Auth Token in the Blynk App.
// Go to the Project Settings (nut icon).
char auth[] = "...";

// Your WiFi credentials.
// Set password to "" for open networks.
char ssid[] = "...";
char pass[] = "...";

#define DHTPIN 2          // What digital pin we're connected to

// Uncomment whatever type you're using!
#define DHTTYPE DHT11     // DHT 11
//#define DHTTYPE DHT22   // DHT 22, AM2302, AM2321
//#define DHTTYPE DHT21   // DHT 21, AM2301

DHT dht(DHTPIN, DHTTYPE);
SimpleTimer timer;

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

char INSERT_SQL[] = "INSERT INTO testdh.dh_11(fld_value) VALUES ( %d)";
char query[128];

// This function sends Arduino's up time every second to Virtual Pin (5).
// In the app, Widget's reading frequency should be set to PUSH. This means
// that you define how often to send data to Blynk App.
void sendSensor()
{
 
  //delay(10000); //10 sec
  int buttonpin=16;
  int button=0;
pinMode(buttonpin, INPUT);
button=digitalRead(buttonpin);

if (button == HIGH) {
 pinVal = 1;
Serial.println(" pinVal: "+pinVal  ) ;
sprintf(query, INSERT_SQL, pinVal);
Serial.println(query);

}else{
 pinVal = 0;
Serial.println(" pinVal: "+pinVal  ) ;
sprintf(query, INSERT_SQL, pinVal);
Serial.println(query);
}

  pinMode(sensorPinD4P2, INPUT);
  
 int myPinVal = digitalRead(sensorPinD4P2); 
if (myPinVal == HIGH) {
Serial.println("myPinVal sensorPinD4P2 is HIGH");}else{
Serial.println("myPinVal sensorPinD4P2 is LOW");}
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query);

 delete cur_mem;

}

void setup()
{
  Serial.begin(9600); // See the connection status in Serial Monitor
  Blynk.begin(auth, ssid, pass);
  
  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!");  
  dht.begin();

  // Setup a function to be called every second
  timer.setInterval(1000L, sendSensor);
}

void loop()
{
  Blynk.run(); // Initiates Blynk
  timer.run(); // Initiates SimpleTimer
}

0 Likes

#3

where you can see IPAddress server_addr(192, 168 ,200, 233); ? please :sob:

0 Likes

#4

I knew the IP address of the server, it’s the same as the one you use to login to mysql (if you are not using local ip)

or find yours using ipconfig in command line window

0 Likes

#5

serial monitor always says Connecting… :sob:

0 Likes

#6

It’s not clear if this is “Connecting …” to Blynk or to the SQL database but I would strongly suspect the former.

Paste your Serial Monitor data and ensure you have the correct Blynk token in your sketch etc.

0 Likes

#7

I’m using local server and my server adderss is my pc ip add but i also want to store some data to mysql database :sob:

 #define BLYNK_PRINT Serial


  #include <SPI.h>
  #include <Ethernet.h>
  #include <BlynkSimpleEthernet.h>
  #include <MySQL_Connection.h>
  #include <MySQL_Cursor.h>

  // You should get Auth Token in the Blynk App.
  // Go to the Project Settings (nut icon).
  char auth[] = "42f90c53c83c4816b1483b8c18ef8c97";
  BlynkTimer timer;

  IPAddress server_ip (192, 168, 1, 47);

  // Mac address should be different for each device in your LAN
  byte arduino_mac[] = { 0xDE, 0xED, 0xBA, 0xFE, 0xFE, 0xED };
  IPAddress arduino_ip ( 192, 168, 1, 55);
  IPAddress dns_ip     (192, 168, 1, 1);
  IPAddress gateway_ip (192, 168, 1, 1);
  IPAddress subnet_mask(255, 255, 255,   0);
  char user[] = "root";              // MySQL user login username
  char password[] = ""; 

  #define W5100_CS  10
  #define SDCARD_CS 4


char INSERT_SQL[] = "INSERT INTO test_arduino.hello_arduino (message) VALUES ('Hello, Arduino!')";
EthernetClient client;
MySQL_Connection conn((Client *)&client);


const int sensorIn = A0;
int mVperAmp = 66; // use 100 for 20A Module and 66 for 30A Module
double Voltage = 0;
double VRMS = 0;
double AmpsRMS = 0;



void getACS712() {  // for AC
 Voltage = getVPP();
 VRMS = (Voltage/2.0) *0.707; 
 AmpsRMS = (VRMS * 1000)/mVperAmp;
 Serial.print(AmpsRMS);
 Serial.println(" Amps RMS");
 Blynk.virtualWrite(V5, String(AmpsRMS, 2));
}

float getVPP()
{
  float result;
  
  int readValue;             //value read from the sensor
  int maxValue = 0;          // store max value here
  int minValue = 1024;          // store min value here
  
   uint32_t start_time = millis();
   while((millis()-start_time) < 1000) //sample for 1 Sec
   {
       readValue = analogRead(sensorIn);
       // see if you have a new maxValue
       if (readValue > maxValue) 
       {
          
           maxValue = readValue;
       }
       if (readValue < minValue) 
       {
         
           minValue = readValue;
       }
   }
   
   // Subtract min from max
   result = ((maxValue - minValue) * 5.0)/1024.0;
      
   return result;
 }
   
  void setup()
  {
    // Debug console
    Serial.begin(115200);

    while (!Serial); // wait for serial port to connect
    Ethernet.begin(arduino_mac);
    Serial.println("Connecting...");
      if (conn.connect(server_ip, 3306, user, password)) {
        delay(1000);
    }
      else
      Serial.println("Connection failed.");

    pinMode(SDCARD_CS, OUTPUT);
    digitalWrite(SDCARD_CS, HIGH); // Deselect the SD card

     Blynk.begin(auth, server_ip, 8442, arduino_ip, dns_ip, gateway_ip, subnet_mask, arduino_mac);
    timer.setInterval(1000L, getACS712); // get data every 2s

    //Blynk.begin(auth);
    // You can also specify server:
    //Blynk.begin(auth, "blynk-cloud.com", 8442);
    //Blynk.begin(auth, IPAddress(192,168,1,46), 8443);
     
    
  }


  void loop()
  {
    Blynk.run();
    // You can inject your own code or combine it with other sketches.
    // Check other examples on how to communicate with Blynk. Remember
    // to avoid delay() function!
    timer.run();
  }
0 Likes

#8

Serial Monitor?

0 Likes

#9

I’m checking it through serial monitor and serial monitor only says connecting…

0 Likes

#10

You posted your sketch not your Serial Monitor data.

0 Likes

#11

serial monitor. is that what you mean?

Connecting…

0 Likes

#12

Are you using the Arduino IDE?

This is data in my Serial Monitor:


[24055] Connecting to GargoyleTest
[24556] Connected to WiFi
[24556] IP: 192.168.10.226
[24556] 
    ___  __          __
   / _ )/ /_ _____  / /__
  / _  / / // / _ \/  '_/
 /____/_/\_, /_//_/_/\_\
        /___/ v0.4.10 on Arduino

[24562] Connecting to blynk-cloud.com:8441
[25263] Certificate OK
[25340] Ready (ping: 75ms).
0 Likes

#13

serial monitor always says that. yeah I’m using arduino IDE

0 Likes

#14

Try this amended setup() and then paste Serial Monitor data again:

 void setup()
  {
    pinMode(SDCARD_CS, OUTPUT);
    digitalWrite(SDCARD_CS, HIGH); // Deselect the SD card    
    Serial.begin(115200);
    Serial.println("\nRebooted");
    while (!Serial); // wait for serial port to connect
    //Ethernet.begin(arduino_mac);
    Blynk.begin(auth, server_ip, 8442, arduino_ip, dns_ip, gateway_ip, subnet_mask, arduino_mac);
    Serial.println("Connecting to SQL");
      if (conn.connect(server_ip, 3306, user, password)) {
        delay(1000);
    }
      else
      Serial.println("Connection to SQL failed.");

    //pinMode(SDCARD_CS, OUTPUT);
    //digitalWrite(SDCARD_CS, HIGH); // Deselect the SD card
    //Blynk.begin(auth, server_ip, 8442, arduino_ip, dns_ip, gateway_ip, subnet_mask, arduino_mac);
    timer.setInterval(1000L, getACS712); // get data every 2s    
  }
0 Likes

#15
Rebooted
[0] Using static IP
[1300] IP:192.168.1.55
[1300] 
    ___  __          __
   / _ )/ /_ _____  / /__
  / _  / / // / _ \/  '_/
 /____/_/\_, /_//_/_/\_\
        /___/ v0.4.8 on Arduino Uno

[5001] Connecting to 192.168.1.47
[5190] Ready (ping: 34ms).
Connecting to SQL
0 Likes

#16

OK so you can connect to Blynk but not to your SQL server.

Is your SQL server using port 3306?

0 Likes

#17

yeah its 3306.

0 Likes

#18

i would like to know how was this problem solved i’m kinda lost

0 Likes

#19

This is an old topic. I would recommend you start your own topic, complete with FULL details about your own issue :wink:

PS, Blynk now uses a new Reporting Widget.

0 Likes

closed #20
0 Likes