I am working in a Iot based project in which want to store my data in to data base as well as use mqtt for communication between client and esp8266 . I tried to implement both mysql and mqtt in esp8266 node mcu. In a loop i first check if mqtt message is arrived and then update the database with sensor value. Client.publish() works but Client.suscribe() doesnot work when update of databse is done.But when only mqtt is done it works fine.
#include <ESP8266WiFi.h>
#include <WiFiClient.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <PubSubClient.h>
IPAddress server_addr(***, , ,); // IP of the MySQL server
char user[] = "root"; // MySQL user login username
char password[] = ""; // MySQL user login password
char ssid[] = "***"; // your SSID
char pass[] = "*****"; // your SSID Password
const char mqtt_server = "192.168.0.109";
long lastMsg = 0;
char msg[50];
int value = 0;
WiFiClient espClient;
MySQL_Connection conn((Client *)&espClient);
PubSubClient client(espClient);
void setup() {
Serial.begin(115200);
WiFi.begin(ssid, pass); // initializing the WIFI library
while ( WiFi.status() != WL_CONNECTED ) { // while loop to write dots during connecting
delay ( 500 );
Serial.print ( "." );
}
// print out information about the WIFI connection
Serial.println ( "" );
Serial.print ( "Connected to " );
Serial.println ( ssid );
Serial.print ( "IP address: " );
Serial.println ( WiFi.localIP() );
client.setServer(mqtt_server, 1883);
client.setCallback(callback);
}
void callback(char* topic, byte* payload, unsigned int length) {
Serial.print("Message arrived [");
Serial.print(topic);
Serial.print("] ");
for (int i = 0; i < length; i++) {
Serial.print((char)payload[i]);
}
Serial.println();
}
void reconnect() {
// Loop until we're reconnected
while (!client.connected()) {
Serial.print("Attempting MQTT connection...");
// Attempt to connect
if (client.connect("ESP8266Client")) {
Serial.println("connected");
// Once connected, publish an announcement...
client.publish("outTopic", "SAAIL");
// ... and resubscribe
client.subscribe("say");
} else {
Serial.print("failed, rc=");
Serial.print(client.state());
Serial.println(" try again in 5 seconds");
// Wait 5 seconds before retrying
delay(5000);
}
}
}
void loop() {
if (!client.connected()) {
reconnect();
}
client.loop();
delay(1000);
long now = millis();
if (now - lastMsg > 2000) {
lastMsg = now;
int newTemp = sht1x.readTemperatureC();
int newHum = sht1x.readHumidity();
Serial.print("temp:");
Serial.print(newTemp);
char INSERT_SQL[] = "INSERT INTO test.users (humidity,temp) VALUES (%d, %d );";
char query[255];
sprintf(query, INSERT_SQL, newHum, newTemp);
Serial.println("Recording data.");
conn.connect(server_addr, 3306, user, password);
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
conn.close();
}
}
