Go Down

Topic: MYSQL Insert doesn't insert anything (Read 184 times) previous topic - next topic

matzr

Hi,

I'm trying to insert a row into a mysql-database using the MySQL Connector from Dr. Charles Bell. I can read table data, but when trying to insert a row nothing happens. I don't even get an error message.
This is my code:
Code: [Select]
void writeChannelLog(int channelNo, String 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(value == "ON") {
    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(value == "OFF") {
    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;
}


The connection is created at the beginning with:
Code: [Select]
MySQL_Connection conn((Client *)&espClient); which obviously works as I can retrieve data from a SELECT statement.

Here the output from the serial monitor:
Quote
Connecting to Home_less
..............................
WiFi connected with IP address:
10.0.1.65
Get GPIO pins
Ventil 1: Obstwald & Gem⸮se - GPIO-Pin: 5
Ventil 2: Gro⸮e Wiese - GPIO-Pin: 4
Ventil 3: Kreuzgasse - GPIO-Pin: 0
Attempting MQTT connection...connected
Message arrived! Channel: 1; Command: SET; value: ON
Test: INSERT INTO RainControl.T_VentLog (VentID, TimeON) VALUES (1, now())
Test BEFORE SQL-Execute
Test after SQL-Execute
Pin: 5, digitalRead: 1 --- val: 1
Channel 1 set to ON - MQTT: IR/STATUS/1
Attempting MQTT connection...connected
So the NodeMCU waits to get a command via MQTT, once it receives it should set a GPIO pin HIGH or LOW and put a log-entry into the database (obviously between the BEFORE SQL-Execute and after SQL-Execute messages in the monitor). But latter never happens.

When I copy/paste the command from the monitor to an sql-client it works perfectly fine.

Anybody an idea what I can do to find out why the record does not get inserted?

Thanks
Matthias

matzr

#1
May 25, 2018, 05:31 pm Last Edit: May 25, 2018, 05:35 pm by matzr
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:
Code: [Select]

/*
 *  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();
}


PaulS

Code: [Select]
  command = topicArr;

  // get sub-subtopic: channel number
  topicArr = strtok( NULL, "/");
  channelNo = atoi(topicArr);

You change where topicArr points. That changes where command points. Probably NOT what you want to do.

The art of getting good answers lies in asking good questions.

matzr

#3
May 25, 2018, 10:59 pm Last Edit: May 25, 2018, 11:07 pm by matzr
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:
Code: [Select]

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

PaulS

Code: [Select]
PubSubClient MQTTclient(espClient);

// Setup up connection to SQL-Server
MySQL_Connection conn((Client *)&espClient);

So, what do you think happens when the MySQL_Connection instance uses the espClient instance to talk to the database? What is the PubSubClient instance supposed to do with that information? And, what is the MySQL_Connection instance supposed to do with the PubSubClient communications?
The art of getting good answers lies in asking good questions.

matzr

#5
May 26, 2018, 08:36 am Last Edit: May 26, 2018, 08:56 am by matzr
So, what do you think happens when the MySQL_Connection instance uses the espClient instance to talk to the database? What is the PubSubClient instance supposed to do with that information? And, what is the MySQL_Connection instance supposed to do with the PubSubClient communications?
My understanding is, that WiFiClient espClient; initiates the WiFi connection and then both the SQL-client as well as MQTT use the WiFi connection.

EDIT: got it. I set up two instances (?) of the WiFiClient now. One for the MQTT connection and one for SQL-Server. Works now.
Many thanks for your help!

Go Up