Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Arduino ESP8266 WiFi MySQL database connection with INSERT SQL command
//#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;
}
@dioxik

This comment has been minimized.

Copy link

@dioxik dioxik commented Jan 25, 2017

the lamest way to do that !

@mserra1

This comment has been minimized.

Copy link

@mserra1 mserra1 commented Feb 2, 2017

How is that lame? It uploads directly into the database from a $2 device over wifi.

@lucasmarcellborges

This comment has been minimized.

Copy link

@lucasmarcellborges lucasmarcellborges commented Sep 11, 2017

It´s really works?

I have not to this ESP8266 works directly with my data base MYSQ...

Where are the problem?

@pudelosha

This comment has been minimized.

Copy link
Owner Author

@pudelosha pudelosha commented Sep 27, 2017

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 code.

@inforaudio

This comment has been minimized.

Copy link

@inforaudio inforaudio commented Dec 20, 2017

How can select the db???
I try with the sample and have the next error:

Connected to server version 5.5.46

Connected to SQL Server!
Recording data.
SELECT * FROM DATOS
Error: 29 = No database selected.

@DaniNedo

This comment has been minimized.

Copy link

@DaniNedo DaniNedo commented Dec 28, 2017

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.

@irtesamh

This comment has been minimized.

Copy link

@irtesamh irtesamh commented Jan 9, 2018

simplest way to select database : select * from dbname.tablename

example: "INSERT INTO mydatabase.temp_log (Location,temperature,humidity) VALUES (1,2,3)";

@epfam126

This comment has been minimized.

Copy link

@epfam126 epfam126 commented Jan 11, 2018

Hi I read your code and thanks to upload your code.
I want to insert data to mysql that is made on Linux(Raspberry pi)
If I use this code I can insert data from arduino to mysql except coding in Linux?

@bhumijain

This comment has been minimized.

Copy link

@bhumijain bhumijain commented Feb 16, 2018

Hi, can I use this code to send data to MS SQL server instead of mysql ?

@programmer131

This comment has been minimized.

Copy link

@programmer131 programmer131 commented Mar 15, 2018

i'll try it. thanks for sharing.

@nithish21896

This comment has been minimized.

Copy link

@nithish21896 nithish21896 commented Mar 16, 2018

how do i use the code to upload it on arduino uno rather than esp8266 directly. because this is only a part of the code and the rest of it needs arduino as the main processor. When I try to upload the code onto a arduino uno it shows that esp8266wifi.h file is missing. any suggestions will be appericiated.
and thanks in advance
this is my code

#include <ESP8266WiFi.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <WiFiClient.h>
#include <SPI.h>
#include <MFRC522.h>
#include <SoftwareSerial.h>

//pin connecction configuration
#define rxPin 2 // Serial input (connects to Emic 2 SOUT)
#define txPin 3 // Serial output (connects to Emic 2 SIN)
#define SS_PIN 10
#define RST_PIN 9
#define RX 5
#define TX 6
SoftwareSerial esp8266(RX,TX);
MFRC522 mfrc522(SS_PIN, RST_PIN); // Create MFRC522 instance.
SoftwareSerial emicSerial = SoftwareSerial(rxPin, txPin);

char ssid[] = "Airtel-WD670-71CE";// Network Name
char pass[] = "D8F171CE"; // Network Password
byte mac[6];

WiFiServer server(80);
IPAddress ip(192, 168, 1, 100);//192.168.1.100
IPAddress gateway(192, 168, 1, 1);
IPAddress subnet(255, 255, 255, 0);

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

IPAddress server_addr(104,20 ,67,46); // MySQL server IP
char user[] = "id4857053_nithish"; // MySQL user
char password[] = "arjun4694"; // MySQL password

//query instruction
const char QUERY_POP[] = "SELECT name,cost,description FROM id4857053_database.details WHERE id='content';";
char query[128];

void setup() {

//setup of mfrc522
Serial.begin(9600); // Initiate a serial communication
SPI.begin(); // Initiate SPI bus
mfrc522.PCD_Init(); // Initiate MFRC522
Serial.println("Approximate your card to the reader...");
Serial.println();

esp8266.begin(115200);

//emic2 pinout details
pinMode(rxPin, INPUT);
pinMode(txPin, OUTPUT);

// set the data rate for the SoftwareSerial port
emicSerial.begin(9600);
emicSerial.print('\n'); // Send a CR in case the system is already up
while (emicSerial.read() != ':'); // When the Emic 2 has initialized and is ready, it will send a single ':' character, so wait here until we receive it
delay(10); // Short delay
emicSerial.flush(); // Flush the receive buffer

//setting up esp8266 connection
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() {

// Look for new cards
if ( ! mfrc522.PICC_IsNewCardPresent())
{
return;
}
// Select one of the cards
if ( ! mfrc522.PICC_ReadCardSerial())
{
return;
}
//Show UID on serial monitor
Serial.print("UID tag :");
String content= "";
byte letter;
for (byte i = 0; i < mfrc522.uid.size; i++)
{
Serial.print(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " ");
Serial.print(mfrc522.uid.uidByte[i], HEX);
content.concat(String(mfrc522.uid.uidByte[i] < 0x10 ? " 0" : " "));
content.concat(String(mfrc522.uid.uidByte[i], HEX));
}
Serial.println();
Serial.print("Message : ");
content.toUpperCase();

delay(10000); //10 sec

Serial.println("> Running SELECT with dynamically supplied parameter");

// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Supply the parameter for the query
// Here we use the QUERY_POP as the format string and query as the
// destination. This uses twice the memory so another option would be
// to allocate one buffer for all formatted queries or allocate the
// memory as needed (just make sure you allocate enough memory and
// free it when you're done!).
sprintf(query, QUERY_POP, 9000000);
// Execute the query
cur_mem->execute(query);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(cols->fields[f]->name);
emicSerial.print('S');
emicSerial.print(cols->fields[f]->name);
emicSerial.print('\n');
while (emicSerial.read() != ':'); // Wait here until the Emic 2 responds with a ":" indicating it's ready to accept the next command
delay(500);
if (f < cols->num_fields-1) {
Serial.print(',');
}
}
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(row->values[f]);
emicSerial.print(row->values[f]);
emicSerial.print('S');
emicSerial.print(cols->fields[f]->name);
emicSerial.print('\n');
while (emicSerial.read() != ':'); // Wait here until the Emic 2 responds with a ":" indicating it's ready to accept the next command
delay(500);
if (f < cols->num_fields-1) {
Serial.print(',');
}
}
Serial.println();
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;

}

@CREW8

This comment has been minimized.

Copy link

@CREW8 CREW8 commented Mar 25, 2018

Hey,

how can I read Data from the table? And save it to strings or ints?

Thanks Timo

@supermarcie

This comment has been minimized.

Copy link

@supermarcie supermarcie commented Apr 2, 2018

the program does not want to connect to my SQL database.
I have a maria DB on my synology NAS.
Can someone help me?

@Prasadgiot

This comment has been minimized.

Copy link

@Prasadgiot Prasadgiot commented Apr 13, 2018

I tried to connect ESP8266 with MySQL DB. Done multiple attempts in last 7 days, BUT NO LUCK. Can someone help me please?

Please do reply on email - prasadgiot@gmail.com

@BoRisom

This comment has been minimized.

Copy link

@BoRisom BoRisom commented Apr 30, 2018

Great post
Have a question...
How is password to MySQL sent?
Clear text or ?
Might be a stupid question, but just want to be sure.

@GANESHBANDI9948

This comment has been minimized.

Copy link

@GANESHBANDI9948 GANESHBANDI9948 commented Jun 12, 2018

hai, iam working on nodeMCU ,i want to send the url to the mysql database using apache tomcat webserver. using below code i sent to the server but it's not send.plaese help me.

code
#include <ESP8266HTTPClient.h>
#include <ESP8266WiFi.h>

void setup() {

Serial.begin(115200); //Serial connection
WiFi.begin("iota_telecom", "iota160616"); //WiFi connection

while (WiFi.status() != WL_CONNECTED) { //Wait for the WiFI connection completion

delay(500);
Serial.println("Waiting for connection");

}

}

void loop() {

if(WiFi.status()== WL_CONNECTED){ //Check WiFi connection status

HTTPClient http; //Declare object of class HTTPClient

http.begin("http://10.132.16.241:8080/HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45"); //Specify request destination
http.addHeader("Content-Type", "text/plain"); //Specify content-type header

int httpCode = http.POST("http://10.132.16.241:8080/HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45"); //Send the request
String payload = http.getString(); //Get the response payload

Serial.println(httpCode); //Print HTTP return code
Serial.println(payload); //Print request response payload

http.end(); //Close connection

}else{

Serial.println("Error in WiFi connection");   

}

delay(30000); //Send a request every 30 seconds

}

serial monitor:

Waiting for connection
state: 2 -> 0 (2)
reconnect
scandone
state: 0 -> 2 (b0)
Waiting for connection
state: 2 -> 3 (0)
state: 3 -> 5 (10)
add 0
aid 2
cnt
Waiting for connection
Waiting for connection
Waiting for connection

connected with iota_telecom, channel 6
dhcp client start...
Waiting for connection
ip:10.132.39.81,mask:255.255.0.0,gw:10.132.0.1
Waiting for connection
[HTTP-Client][begin] url: http://10.132.16.241:8080/HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45
[HTTP-Client][begin] host: 10.132.16.241 port: 8080 url: /HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45
[HTTP-Client] connected to 10.132.16.241:8080
[HTTP-Client] sending request header

POST /HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45 HTTP/1.1
Host: 10.132.16.241:8080
User-Agent: ESP8266HTTPClient
Connection: close
Accept-Encoding: identity;q=1,chunked;q=0.1,*;q=0
Content-Type: text/plain
Content-Length: 136


[HTTP-Client][returnError] error(-11): read Timeout
[HTTP-Client][returnError] tcp stop
[HTTP-Client][returnError] error(-4): not connected
-11

[HTTP-Client][end] tcp is closed
[HTTP-Client][begin] url: http://10.132.16.241:8080/HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45
[HTTP-Client][begin] host: 10.132.16.241 port: 8080 url: /HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45
[HTTP-Client] failed connect to 10.132.16.241:8080
[HTTP-Client][returnError] error(-1): connection refused
[HTTP-Client][returnError] error(-4): not connected
-1

[HTTP-Client][end] tcp is closed
[HTTP-Client][begin] url: http://10.132.16.241:8080/HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45
[HTTP-Client][begin] host: 10.132.16.241 port: 8080 url: /HTTPHITURL/RequestHandler?p1=300&p2=78&p3=500&p4=600&p5=500&p6=iota66&p7=iota77&p8=iota83&p9=iota84&p10=iota45
[HTTP-Client] failed connect to 10.132.16.241:8080
[HTTP-Client][returnError] error(-1): connection refused
[HTTP-Client][returnError] error(-4): not connected
-1
thank you

@emfarih

This comment has been minimized.

Copy link

@emfarih emfarih commented Jun 13, 2018

Hi, i use that code to trying connect my ESP8266 to my SQL Server, but till now, i'm still failed to connect it, do you have any idea about what happen, this is my code..
`#include <ESP8266WiFi.h>
#include <OneWire.h>
#include <DallasTemperature.h>
#include <WiFiClient.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266Ping.h>

//--------------------------------------------------------
//DS18B20
#define ONE_WIRE_BUS 2
OneWire oneWire(ONE_WIRE_BUS);
DallasTemperature DS18B20(&oneWire);
long lastTime;
const int durationTemp = 1000; //The frequency of temperature measurement

//--------------------------------------------------------
//WIFI
const char* ssid = "xxx"; //type your ssid
const char* password = "xxxXXX"; //type your password

WiFiServer server(80);
WiFiClient client;

//--------------------------------------------------------
//SQL
MySQL_Connection conn((Client *)&client);
char INSERT_SQL[] = "INSERT INTO db_test.table_temp(ID, TIME, TEMP) VALUES (1, NULL, %f)";
char query[128];
IPAddress server_addr(192, 168, 43, 66); //MySQL server IP (I USE LAPTOP AS SERVER)
char sqlUser[] = "xxx"; //MySQL user
char sqlPassword[] = "xxxXXX"; //MySQL password

//--------------------------------------------------------
//Setting The Temperature Sensor
void SetupDS18B20(){
DS18B20.begin();
Serial.print("Parasite power is: ");
if(DS18B20.isParasitePowerMode()){
Serial.print("On");
}
else{
Serial.print("Off");
}

lastTime = millis();
DS18B20.requestTemperatures();
}

//--------------------------------------------------------
void setup() {
Serial.begin(115200);

Serial.println();
Serial.print("Connecting to ");
Serial.println(ssid);

Serial.println("Set to STA mode");
WiFi.mode(WIFI_STA);

WiFi.begin(ssid, password);

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

Serial.println("");
Serial.print("Connected to ");
Serial.println(ssid);
Serial.print("IP address: ");
Serial.println(WiFi.localIP());

// Start the server
server.begin();
Serial.println("Server started");

SetupDS18B20();

IPAddress pingt (192, 168, 43, 66); // The remote ip to ping
bool ret = Ping.ping(pingt);

Serial.println("");
Serial.print("SQL Server is ");
if(ret){
Serial.print("Reachable");
}
else{
Serial.print("Unreachable");
}

Serial.println("");
Serial.println("Connecting to database");

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

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

It fail to connect SQL Server. my ESP also have same network as my laptop (as server). Do you happen know whats wrong.. thanks..

@nacosta-cl

This comment has been minimized.

Copy link

@nacosta-cl nacosta-cl commented Aug 4, 2018

Guys, if you have access to a server, it's better to use a MQTT broker, and then use a local app to manage the connection between the db and MQTT

@StoneAgeSkillz

This comment has been minimized.

Copy link

@StoneAgeSkillz StoneAgeSkillz commented Aug 24, 2018

Hi guys,
how does one redefine the sql credentials? I have them stored in EEPROM i can load them, but they are not global, so the function that uses it restarts the ESP8266 module.

@StoneAgeSkillz

This comment has been minimized.

Copy link

@StoneAgeSkillz StoneAgeSkillz commented Aug 24, 2018

Oh yes, to the connection problem. Had the same issue: permission. Could not access MariaDB on my RasPi server, had to add a user with permissions to mysql>user with % as host... Did that via phpMyAdmin, work like a charm now.

@jjasonsolomon

This comment has been minimized.

Copy link

@jjasonsolomon jjasonsolomon commented Sep 23, 2018

Could anyone explain these queries?

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

  2. sprintf(query, INSERT_SQL, soil_hum);

@augisbud

This comment has been minimized.

Copy link

@augisbud augisbud commented Nov 1, 2018

@jasontechie

  1. it's the sql command the %d is the soil_hum in the sprintf
  2. sprintf add all of these values to one
@NizwarAlraf

This comment has been minimized.

Copy link

@NizwarAlraf NizwarAlraf commented Nov 22, 2018

can u explain line 25 for me, Sir..
i didnt understand cause i not see ur database.

@pudelosha

This comment has been minimized.

Copy link
Owner Author

@pudelosha pudelosha commented Nov 27, 2018

The above code was working with MySQL databases.
I still recommend using ESP8266WebServer. MySQL library used in this project was not reliable.
It consumes too much memory and the program crashed very often.

@pudelosha

This comment has been minimized.

Copy link
Owner Author

@pudelosha pudelosha commented Nov 27, 2018

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.

@sajibkarmaker

This comment has been minimized.

Copy link

@sajibkarmaker sajibkarmaker commented Apr 20, 2019

thanks for sharing

@Sandeep4510

This comment has been minimized.

Copy link

@Sandeep4510 Sandeep4510 commented Jun 7, 2019

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

@phlpjo

This comment has been minimized.

Copy link

@phlpjo phlpjo commented Apr 18, 2020

Nice! - Thank you, works very well.

@montielgonzalo

This comment has been minimized.

Copy link

@montielgonzalo montielgonzalo commented Feb 15, 2021

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?

@Sandeep4510

This comment has been minimized.

Copy link

@Sandeep4510 Sandeep4510 commented Feb 16, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment