2

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
    Commented Apr 11, 2021 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?? Commented Apr 12, 2021 at 11:59

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.