Last active
March 14, 2023 12:06
-
-
Save pudelosha/5a42092bbcf0bfc9ddab20e6093e68b7 to your computer and use it in GitHub Desktop.
Arduino ESP8266 WiFi MySQL database connection with INSERT SQL command
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//#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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I'm getting this error on my Wemos D1 :
Exception (28):
epc1=0x4020152c epc2=0x00000000 epc3=0x00000000 excvaddr=0x00000049 depc=0x00000000
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<<<