1

I have a MariaDb database on a local server and I am using it to store data from various Arduino's around my shop. The database currently has 12 tables. I also have another Arduino Uno Wifi Rev2 that is able to connect to its two respective tables; it collects setpoint information from one table and inserts data perfectly into the other.

The Arduino I'm having a problem with can insert data perfectly fine into its data table (veg2_data) but fails when trying to select data from it's sp table (veg2_sp). It can also connect to any of the other tables in my database, including the one for the first arduino. Only veg2_sp is malfunctioning

Here is my code:

#include <SPI.h>
#include <WiFiNINA.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include "config.h"
//Node Libraries
#include <DHT.h>

#define O_UPPER 9
#define O_LOWER 10


//Wifi
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; //Do I need this?
char ssid[] = CONFIG_SSID;
char pass[] = CONFIG_PASS;
int status = WL_IDLE_STATUS;
int keyIndex = 0;
WiFiClient client;


//MySQL
IPAddress server_addr(192, 168, 1, 133);
char user[] = CONFIG_MYSQL_USER;
char password[] = CONFIG_MYSQL_PASS;
char db[] = CONFIG_MYSQL_DB;
char query[128];
char DATA_QUERY[] = "INSERT INTO veg2_data VALUES (now(), %d, %d, %s, %s, %s, %s, %s, %s, %d);";
char SP_QUERY[] = "SELECT * FROM veg2_sp ORDER BY timestamp DESC LIMIT 1;";
char rawTime[30];
byte refreshRate = 1;
uint32_t dbContactTimer;
MySQL_Connection conn((Client *)&client);
MySQL_Cursor cur = MySQL_Cursor(&conn);


//Node Specific
bool o_UpperState, o_LowerState, daytime = 0;
byte dayTempSP = 25, dayHumiditySP = 30, nightTempSP = 10, nightHumiditySP = 20,
     o_UpperLock = 0, o_LowerLock = 0, sunrise = 7, sunset = 19;
int soilMoisture;
float intakeTemp, intakeHum, roomTemp, roomHum, exhaustTemp, exhaustHum ;
char intakeTempArray[7], intakeHumArray[7], roomTempArray[7], roomHumArray[7],
     exhaustTempArray[7], exhaustHumArray[7];

DHT exhaust(6, DHT11);
DHT intake(7, DHT11);
DHT room(8, DHT11);


void setup() {
  Serial.begin(115200);
  wifiInit();

  exhaust.begin();
  intake.begin();
  room.begin();

  pinMode(O_UPPER, OUTPUT);
  pinMode(O_LOWER, OUTPUT);
  digitalWrite(O_UPPER, LOW);
  digitalWrite(O_LOWER, LOW);

  //spCheck();
  dbContactTimer = millis();
}

void loop() {

  //Default refesh rate is 5 seconds. Some nodes need to increase refesh rate at specific times
  if (millis() - dbContactTimer >= (refreshRate * 1000)) {
    dbUpdate();
    spCheck();

    //If set to auto (2), my lights will turn on and off depending on day/night. Currently unfinished
    if (o_UpperLock == 2) {
      if (daytime) {
        digitalWrite(O_UPPER, HIGH);
        o_UpperState = 1;
      }
      else {
        digitalWrite(O_UPPER, LOW);
        o_UpperState = 0;
      }
    }
    dbContactTimer = millis();
  }

}

//------------------------------------MySql Functions-------------------------
void spCheck() { //Updates setpoints
  if (!conn.connected()) {
    conn.close();
    if (!conn.connect(server_addr, port, user, password, db)) {
      return 0;
    }
  }
  row_values *row = NULL;
  cur.execute(SP_QUERY);
  column_names *columns = cur.get_columns();
  do {
    row = cur.get_next_row();
    if (row != NULL) {
      //Serial.println(row->values[0]);
      o_UpperLock = atol(row->values[1]);
      //o_LowerLock = atol(row->values[2]);
      //dayTempSP = atol(row->values[3]);
      //dayHumiditySP = atol(row->values[4]);
      //nightTempSP = atol(row->values[5]);
      //nightHumiditySP = atol(row->values[6]);
      //sunrise = atol(row->values[7]);
      //sunset = atol(row->values[8]);
    }
  } while (row != NULL);

  if (o_UpperLock < 2) {
    if (o_UpperLock) {
      digitalWrite(O_UPPER, HIGH);
      o_UpperState = 1;
    }
    else {
      digitalWrite(O_UPPER, LOW);
      o_UpperState = 0;
    }
  }
  return 1;
}

void dbUpdate() { //Updates data
  if (!conn.connected()) {
    conn.close();
    if (!conn.connect(server_addr, port, user, password, db)) {
      return 0;
    }
  }
  
  intakeTemp = intake.readTemperature();
  dtostrf(intakeTemp, 4, 2, intakeTempArray);
  
  intakeHum  = intake.readHumidity();
  dtostrf(intakeHum, 4, 2, intakeHumArray);
  
  roomTemp = room.readTemperature();
  dtostrf(roomTemp, 4, 2, roomTempArray);
  
  roomHum = room.readHumidity();
  dtostrf(roomHum, 4, 2, roomHumArray);
  
  exhaustTemp = exhaust.readTemperature();
  dtostrf(exhaustTemp, 4, 2, exhaustTempArray);
  
  exhaustHum = exhaust.readHumidity();
  dtostrf(exhaustHum, 4, 2, exhaustHumArray);
  
  soilMoisture = analogRead(A0);

  sprintf(query, DATA_QUERY, o_UpperState, o_LowerState, intakeTempArray,
          intakeHumArray, roomTempArray, roomHumArray, exhaustTempArray, exhaustHumArray, soilMoisture);
  cur.execute(query);

  return 1;
}

void wifiInit() {

  String fv = WiFi.firmwareVersion();
  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
    Serial.println("Please upgrade the firmware");
  }
  if (WiFi.status() == WL_NO_MODULE) {
    Serial.println("No wifi Module - Abort");
    while (true);
  }
  while (status != WL_CONNECTED) {
    Serial.println("Connecting to wifi");
    for (byte i = 0; i < 2; i++) {
      digitalWrite(LED_BUILTIN, HIGH);
      delay(2000);
      digitalWrite(LED_BUILTIN, LOW);
      delay(1000);
    }
    status = WiFi.begin(ssid, pass);
  }

  IPAddress ip = WiFi.localIP();
  long rssi = WiFi.RSSI();
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());
  Serial.print("IP Address: ");
  Serial.println(ip);
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

The problem is with the SP_QUERY. I can change the table to any other in my database, and the code functions fine. If I read from veg2_sp, I get the error.

Tables: The first one is the one im having problems with, the second is the one that is currently functioning. Tables:

I'm utterly baffled.

2
  • firewall on PC?
    – Juraj
    Apr 11 at 5:04
  • I thought so too, but I can still connect to the other table in the database. If I couldn't connect to anything, I'd agree, but what is stopping me from accessing a single table?? Apr 12 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.