Arduino ESP8266 WiFi MySQL database connection with INSERT SQL command

PlantMoistureSensor.ino

//#include <DHT.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>

#define sensorPin1 0

//#define sensorPin2 D2
//#define typeDHT DHT11
//DHT dht(sensorPin1, typeDHT);

char ssid[] = "xxx";                 // Network Name
char pass[] = "xxx";                 // Network Password
byte mac[6];

WiFiServer server(80);
IPAddress ip(192, 168, x, x);
IPAddress gateway(192, 168, x, x);
IPAddress subnet(255, 255, 255, 0);

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];

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

void setup() {

  Serial.begin(9600);

  pinMode(sensorPin1, INPUT);
  //pinMode(sensorPin2, INPUT);

  Serial.println("Initialising connection");
  Serial.print(F("Setting static ip to : "));
  Serial.println(ip);

  Serial.println("");
  Serial.println("");
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.config(ip, gateway, subnet); 
  WiFi.begin(ssid, pass);

  while (WiFi.status() != WL_CONNECTED) {
    delay(200);
    Serial.print(".");
  }

  Serial.println("");
  Serial.println("WiFi Connected");

  WiFi.macAddress(mac);
  Serial.print("MAC: ");
  Serial.print(mac[5],HEX);
  Serial.print(":");
  Serial.print(mac[4],HEX);
  Serial.print(":");
  Serial.print(mac[3],HEX);
  Serial.print(":");
  Serial.print(mac[2],HEX);
  Serial.print(":");
  Serial.print(mac[1],HEX);
  Serial.print(":");
  Serial.println(mac[0],HEX);
  Serial.println("");
  Serial.print("Assigned IP: ");
  Serial.print(WiFi.localIP());
  Serial.println("");

  Serial.println("Connecting to database");

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

  Serial.println("");
  Serial.println("Connected to SQL Server!");  

}

void loop() {

  int soil_hum = 1024 - analogRead(sensorPin1);
  //float t = dht.readTemperature();

  //Serial.println(t);

  delay(10000); //10 sec

  sprintf(query, INSERT_SQL, soil_hum);
  //sprintf(query, INSERT_SQL, soil_hum, t);

  Serial.println("Recording data.");
  Serial.println(query);
  
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  
  cur_mem->execute(query);

  delete cur_mem;

}

And as some people mentioned, the connection issues may be caused by your server as well.
I had to grant permission to arduino IP so that i could insert the records.

Someone asked about selects. The are some sample sketched attached to MySQL library but I did not try them.

thanks for sharing

Thanks a lot bro ..
Make sure while entering the database parameters .....

Nice! - Thank you, works very well.

Hi, thank you for sharing the code. I've been trying to implement it with a Wemos NodeMCU V3.

So far the board is connecting to the WIFI network but isn't able to connect to the SQL Server, it just keeps printing dots in the Serial Monitor. Does it have to be a local Server? I'm using an external host, which IP I got using a 3rd party online tool because I only had the Hostname. Is it possible that I'm writing the wrong IP?

I saw an example of the MYSQL_Connection library that uses the Hostname instead of the IP, however it is written for Ethernet connection and I'm not really sure how to make it work with esp8266's WIFI. Here is what I've tried, but it doesn't compile:

`#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>
#include <Dns.h> //EDIT

#define sensorPin1 0

//#define sensorPin2 D2
//#define typeDHT DHT11
//DHT dht(sensorPin1, typeDHT);

char ssid[] = "-----"; // Network Name
char pass[] = "-----"; // Network Password
byte mac[6];

WiFiServer server(80);
IPAddress ip(192, 168, x, x);
IPAddress gateway(192, 168, x, x);
IPAddress subnet(255, 255, 255, 0);

WiFiClient client;
MySQL_Connection conn((Client *)&client);
DNSClient dns_client; //EDIT

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];

//EDIT
IPAddress server_addr;

//char hostname[] = "hostname";
//IPAddress server_addr(x,x, x, x); // MySQL server IP
char user[] = "----"; // MySQL user
char password[] = "----"; // MySQL password
//End EDIT

void setup() {

Serial.begin(9600);

Serial.println("Initialising connection");
Serial.print(F("Setting static ip to : "));
Serial.println(ip);

Serial.println("");
Serial.println("");
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.config(ip, gateway, subnet);
WiFi.begin(ssid, pass);

while (WiFi.status() != WL_CONNECTED) {
delay(200);
Serial.print(".");
}

Serial.println("");
Serial.println("WiFi Connected");

WiFi.macAddress(mac);
Serial.print("MAC: ");
Serial.print(mac[5],HEX);
Serial.print(":");
Serial.print(mac[4],HEX);
Serial.print(":");
Serial.print(mac[3],HEX);
Serial.print(":");
Serial.print(mac[2],HEX);
Serial.print(":");
Serial.print(mac[1],HEX);
Serial.print(":");
Serial.println(mac[0],HEX);
Serial.println("");
Serial.print("Assigned IP: ");
Serial.print(WiFi.localIP());
Serial.println("");

Serial.println("Connecting to database");

// EDIT
dns_client.begin(Ethernet.dnsServerIP());
dns_client.getHostByName(hostname, server_addr);
Serial.println(server_addr);
// End EDIT

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

Serial.println("");
Serial.println("Connected to SQL Server!");

}`

Thank you in advance.

Hi @DaniNedo , did you find a way to do that?

First Point Connecting to WiFi Network doesn't mean you have internet connection; Once check the internet through the WiFi you have connected.
Second It need not be a local server; I had connected to a external host using a IP provided by the 3rd party;
Most probably your giving the wrong IP.

I'm getting this error

Exception (28):
epc1=0x402017d0 epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000049 depc=0x00000000

It works with Wemos D1 R2 boards.
Check IP settings and database details. MySQL is also case sesitive so this must be reflecred in the c

I'm getting this error on my Wemos D1 :

Exception (28):
epc1=0x4020152c epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000049 depc=0x00000000

stack>>>

ctx: cont
sp: 3ffffc10 end: 3fffffc0 offset: 0190
3ffffda0: 4000050c 2c9f0300 4000050c 3fffc278
3ffffdb0: 40102ce4 3fffc200 00000001 4020157f
3ffffdc0: 40227bf4 00000000 3ffe85c8 4020156c
3ffffdd0: 40203d8c 3fffefa0 00000000 402069cc
3ffffde0: fffffffc 00000000 00000000 fffffffe
3ffffdf0: ffffffff 3fffc6fc 0000005c 3ffee5b0
3ffffe00: 40206998 3ffe85c8 3ffee638 40203240
3ffffe10: 3ffef82c 3ffee638 00000000 40203264
3ffffe20: 3ffef82c 3ffee638 3ffee638 402032f4
3ffffe30: 3ffeeb48 00000000 00000000 4020198f
3ffffe40: 3ffffec0 40206e08 40100209 40215632
3ffffe50: 00000170 00000170 3ffe861c 401006c3
3ffffe60: 40202f34 3ffffebd 00000040 00000001
3ffffe70: 3ffefb9c 00000020 3ffefb9c 401008a2
3ffffe80: 007a1200 00000000 00000000 40100298
3ffffe90: 401059f5 001a24ca 3ffee828 40216098
3ffffea0: 00000102 00000102 3ffe861c 401006c3
3ffffeb0: 3ffe85c8 00000000 3ffef9c4 3ffe85c8
3ffffec0: 00000010 00000035 00000020 3ffe85c8
3ffffed0: 00000010 00000035 3ffee5b0 40201ad2
3ffffee0: 3ffef82c 00000050 3ffee5b0 40201842
3ffffef0: 0000004d 000003e8 3ffe8846 00000001
3fffff00: 3fffff60 00000000 3ffee5b0 40201382
3fffff10: 40206e60 73752ab9 3ffee738 40202f40
3fffff20: 00000cea ffffffff 00000001 3ffe85dd
3fffff30: 3ffe85c8 00000016 3ffee738 40203324
3fffff40: 3ffe889a 3ffee630 3ffee738 3ffe8846
3fffff50: 3ffe889a 3ffee630 3ffee738 40201224
3fffff60: 40206e60 73752ab9 40206e60 00000000
3fffff70: 40206e60 00ffffff 40206e60 0181a8c0
3fffff80: 40206e60 b181a8c0 feefeffe feefeffe
3fffff90: feefeffe feefeffe feefeffe 3ffee7c4
3fffffa0: 3fffdad0 00000000 3ffee7b0 40203eb0
3fffffb0: feefeffe feefeffe 3ffe8618 40100b55
<<<stack<<<