PaulS:
You change where topicArr points. That changes where command points. Probably NOT what you want to do.
Thanks for the hint. Am still not too familiar with pointers.
I have changed this now, but still no success.
It looks like that somehow the combination of MQTT and mySQL doesn't work. I reduced the code to this now:
#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
int channels[4];
const char* ssid = "#####";
const char* WiFipassword = "######";
const char* mqtt_server = "X.X.X.X";
IPAddress server_addr(X,X,X,X); // IP of the MySQL *server* here
char user[] = "###"; // MySQL user login username
char password[] = "####"; // MySQL user login password
// Setup WiFi connection and MQTT client.
WiFiClient espClient;
PubSubClient MQTTclient(espClient);
// Setup up connection to SQL-Server
MySQL_Connection conn((Client *)&espClient);
void setup() {
Serial.begin(115200);
delay(10);
// Connect to WiFi network
Serial.println();
Serial.println();
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.begin(ssid, WiFipassword);
while (WiFi.status() != WL_CONNECTED) {
delay(100);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected with IP address:");
Serial.println(WiFi.localIP());
if (conn.connect(server_addr, 3306, user, password)) {
delay(100);
}
// Setup mqtt
MQTTclient.setServer(mqtt_server, 1883);
MQTTclient.setCallback(callback);
}
void writeChannelLog(int channelNo, char value[]) {
// Writes the beginning and end time of the irrigation cycle to the log table
char query[100];
// if value = ON, the beginning time needs to be written
if(strcmp(value, "ON") == 0) {
sprintf(query, "INSERT INTO RainControl.T_VentLog (VentID, TimeON) VALUES (%d, now())", channelNo) ;
Serial.printf("Test: %s\n", query);
}
// if value = OFF, the end time needs to be updated in the last record
if(strcmp(value, "OFF") == 0) {
sprintf(query, "UPDATE RainControl.T_VentLog SET TimeOFF = now() WHERE VentID = %d AND TimeOFF IS NULL", channelNo);
Serial.printf("Test: %s\n", query);
}
// execute statement
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
if (conn.connected()) {
Serial.printf("Test BEFORE SQL-Execute\n");
cur_mem->execute(query);
}
Serial.printf("Test after SQL-Execute\n");
delete cur_mem;
}
void reconnect() {
// Loop until we're reconnected
while (!MQTTclient.connected()) {
Serial.print("Attempting MQTT connection...");
// Attempt to connect
if (MQTTclient.connect("Irrigation", "user", "password")) {
Serial.println("connected");
// Once connected, resubscribe
MQTTclient.subscribe("IR/SET/#");
MQTTclient.subscribe("IR/GET/#");
//MQTTclient.unsubscribe("IR/STATUS/#");
} else {
Serial.print("failed, rc=");
Serial.print(MQTTclient.state());
Serial.println(" try again in 5 seconds");
// Wait 5 seconds before retrying
delay(5000);
}
}
}
void callback(char* topic, byte* payload, unsigned int length) {
}
void loop() {
// Check MQTT connection
if (!MQTTclient.connected()) {
reconnect();
}
writeChannelLog(1, "ON");
Serial.print("ON written\n");
delay(10000);
writeChannelLog(1, "OFF");
Serial.print("OFF written\n");
delay(100000);
MQTTclient.loop();
}
If I comment the last row (MQTTclient.loop()), the insert and the update work. As soon as I keep it in it doesn't work anymore, and the MQTT connection all well needs to be reinitiated again.