OK, besides a small typo when declaring cur_mem, I now realised this must have to do with the overall sketch.
I minimised the sketch to the above function only and it worked actually perfectly fine. So I'm not sure whether this could be a memory issue?
Here the complete sketch now:
/*
* Sketch to run a garden irrigation system.
* Receives ON/OFF commands via MQTT.
* Channel topic: CH
*
* It will reconnect to the server if the connection is lost using a blocking
* reconnect function. See the 'mqtt_reconnect_nonblocking' example for how to
* achieve the same result without blocking the main loop.
*
*/
#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);
}
// Create an instance of the cursor passing in the connection
// cur_mem = new MySQL_Cursor(&conn);
// Initialise GPIO
Serial.printf("Get GPIO pins\n");
for (int i=1;i<=(sizeof(channels)/sizeof(channels[0]));i++) {
//get pinnumber from config table, set all pins to OUTPUT and LOW state
channels[i] = getGPIOpin(i);
pinMode(channels[i], OUTPUT);
digitalWrite(channels[i], LOW);
}
// Setup mqtt
MQTTclient.setServer(mqtt_server, 1883);
MQTTclient.setCallback(callback);
}
void callback(char* topic, byte* payload, unsigned int length) {
char* topicArr = strtok(topic, "/");
char newTopic[100];
char MQTTmsg[10];
int iStart, iEnd;
int channelNo;
char* command;
// get subtopic: Command
topicArr = strtok( NULL, "/");
command = topicArr;
// get sub-subtopic: channel number
topicArr = strtok( NULL, "/");
channelNo = atoi(topicArr);
// get message
payload[length] = '\0';
memcpy(MQTTmsg, (char*)payload, length+1);
Serial.printf ("Message arrived! Channel: %d; Command: %s; value: %s\n", channelNo, command, MQTTmsg);
// execute command
// if command is SET, set pin accordingly, and send back the new status via MQTT
if (strcmp(command, "SET") == 0 && channelNo != 0 ) {
if (setChannel(channelNo, channels[channelNo], MQTTmsg)) {
sprintf(newTopic, "IR/STATUS/%d", channelNo);
MQTTclient.publish(newTopic, MQTTmsg);
Serial.printf("Channel %d set to %s - MQTT: %s\n", channelNo, MQTTmsg, newTopic);
} else {
Serial.printf("ERROR: Setting channel %d failed\n", channelNo);
}
}
// if command is GET, send back the status of the requested pin. If pin 0 was requested, send back all pins.
if (strcmp(command, "GET") == 0) {
if (channelNo == 0) {
iStart = 0;
iEnd = (sizeof(channels)/sizeof(channels[0]));
} else {
iStart = channelNo;
iEnd = channelNo + 1;
}
for (int i=iStart; i<iEnd; i++) {
if (digitalRead(channels[i]) == HIGH) {
strcpy(MQTTmsg, "ON");
} else {
strcpy(MQTTmsg, "OFF");
}
sprintf(newTopic, "IR/STATUS/%d", i);
MQTTclient.publish(newTopic, MQTTmsg);
Serial.printf("Status of channel %d: %s\n", i, MQTTmsg);
}
}
}
boolean setChannel(int channelNo, int pin, char* value) {
// sets the defined GPIO to HIGH or LOW depending on value
int val;
if(strcmp(value, "ON") == 0) {
val = HIGH;
} else {
val = LOW;
}
digitalWrite(pin, val);
writeChannelLog(channelNo, value);
Serial.printf("Pin: %d, digitalRead: %d --- val: %d\n", pin, digitalRead(pin), val);
if(digitalRead(pin) == val) {
return true;
} else {
return false;
}
}
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 testSQL() {
char query[100];
row_values *row = NULL;
sprintf(query, "SELECT TimeON, TimeOFF FROM RainControl.T_VentLog WHERE VentID = 1 ORDER BY TimeON DESC");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
column_names *columns = cur_mem->get_columns();
for (int i=0; i<3; i++) {
row = cur_mem->get_next_row();
if (row != NULL) {
Serial.printf("Ventil 1: Start: %s - Ende: %s\n", row->values[0], row->values[1]);
}
}
delete cur_mem;
Serial.print("SQL-Test abgeschlossen.\n");
}
int getGPIOpin (int channelNo) {
// retriebves the pin number from config table on SQL server
row_values *row = NULL;
int pin = 0;
char query[100];
sprintf(query, "SELECT GPIO_PIN, VentName FROM RainControl.T_Ventile WHERE VentID = %d", channelNo);
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
column_names *columns = cur_mem->get_columns();
do {
row = cur_mem->get_next_row();
if (row != NULL) {
pin = atoi(row->values[0]);
Serial.printf("Ventil %d: %s - GPIO-Pin: %d\n", channelNo, row->values[1], pin);
}
} while (row != NULL);
delete cur_mem;
return pin;
}
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 loop() {
// Check MQTT connection
if (!MQTTclient.connected()) {
reconnect();
}
MQTTclient.loop();
}