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 set point 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.
I'm utterly baffled.
