Store array for later submission

Hello.

I'm having trouble understanding how I could do to store a QUERY if the database connection is interrupted.

My project consists of saving every 10 seconds a value in the database, and if the connection with the database drops, this query must be stored in the queue for sending when the network returns, thus creating a pending send queue.

I currently use the code below with ChuckBell's lib.

#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10, 0, 1, 35); // IP of the MySQL *server* here

char user[] = "****";              // MySQL user login username
char password[] = "****";        // MySQL user login password

int status_db; // variable that receives bank status, 0 for not available and 1 for available.
int val_1, val_2, val_3, val_4;
int maq = 100;

unsigned long millisTask1 = millis();

//Analog Input
#define ANALOG_PIN_0 35
#define ANALOG_PIN_1 34
#define ANALOG_PIN_2 36
#define ANALOG_PIN_3 39

// Query
char INSERT_SQL[] = "INSERT INTO db.test (maq,val_1,val_2,val_3,val_4) VALUES ('%d','%d','%d','%d','%d')";
char query[512];
EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup()
{
  Serial.begin(115200);
  while (!Serial);
  Ethernet.begin(mac_addr);
  Serial.println("Connecting...");
  func_connect_db();
}

void loop()
{
  // function to check database connection
  func_connect_db();

  // receiving values in the variables...
  val_1 = analogRead(ANALOG_PIN_0);
  val_2 = analogRead(ANALOG_PIN_1);
  val_3 = analogRead(ANALOG_PIN_2);
  val_4 = analogRead(ANALOG_PIN_3);

  // millis to run every 10s
  if ((millis() - millisTask1) < 10000)
  {
    if (status_db == 1) // if database is available...
    {
      func_post(); // function that executes the query
    }
    if (status_db == 0) // if database unavailable...
    {
      // at this point it creates the query queue to send when the connection comes back.
    }
  }
}

void func_connect_db()
{
  if (conn.connect(server_addr, 3306, user, password))
  {
    status_db = 1;
    delay(1000);
  } else {
    Serial.println("Connection failed.");
    status_db = 0;
  }
}

void func_post()
{
  sprintf(query, INSERT_SQL, maq, val_1, val_2, val_3, val_4);
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(query);
  delete cur_mem;
}

Have you done the calculations how much memory will be required if connection to the DB failed for one day? Does your board have enough RAM for that? You might have to opt for a bigger storage.

To answr the question, I suggest that you use a struct (or class) to store the 4 associated values; below uses a struct.

// a struct to store the measurements
struct MEASUREMENT
{
  int value1;
  int value2;
  int value3;
  int value4;
};

// array to store one hour worth of measurements
MEASUREMENT measurements[360];
// where to store in array
uint16_t measurementIndex;

void setup()
{
  // put your setup code here, to run once:

}

void loop()
{
  if (status_db == 0) // if database unavailable...
  {
    // save to array
    measurement[measurementIndex].value1 = val_1;
    measurement[measurementIndex].value2 = val_2;
    measurement[measurementIndex].value3 = val_3;
    measurement[measurementIndex].value4 = val_4;

    measurementIndex++;
  }
}

It will probably be easier if you store the measurements directly in the measurements array and send the data that is stored in the array

void loop()
{
  // function to check database connection
  func_connect_db();

  // if there is still space in the measurements array
  if (measurementIndex < sizeof(measurements) / sizeof(measurements[0]))
  {
    // store
    measurements[measurementIndex].value1 = analogRead(ANALOG_PIN_0);
    measurements[measurementIndex].value2 = analogRead(ANALOG_PIN_1);
    measurements[measurementIndex].value3 = analogRead(ANALOG_PIN_2);
    measurements[measurementIndex].value4 = analogRead(ANALOG_PIN_3);
    measurementIndex++;
  }

  if (status_db == 1) // if database is available...
  {
    // for each stored set of measurements
    for (uint16_t cnt = 0; cnt < measurementIndex; cnt++)
    {
      func_post(uint16_t index);
    }
    // reset measurement index
    measurementIndex = 0;
  }
}

And the func_post() needs a modification to take a number indicating which record to send.

void func_post(uint16_t index)
{
  sprintf(query, INSERT_SQL, maq, measurements[index].value1, measurements[index].value2, measurements[index].value3, measurements[index].value4);
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(query);
  delete cur_mem;
}

Notes:

  1. problems might arise if the DB connection fails while transfering data as measurementIndex is only reset once all records are transmitted. Better solution would be to use a FIFO or queue.
  2. not tested nor compiled

Hi Sterretje;

I'm using ESP32.

Thanks for the tips, I'll do the tests with this code.
About FIFO, what would the code look like?

Never actually thought about it; I would place in the array and after every succesful submit of one record, move all records one up using memmove; or use two pointers, one for the head and one for the tail.

Maybe CircularBuffer - Arduino Reference might be useful.

thanks for the feedback.

I know, not much help with the original issue but a comment of a potential issue. That could cause data transmission loses.

As can be seen at the end of setup() the function to connect to db is run. Going from setup the db connection function is run again. I recommend only running connect to the db once instead of trying to open a connection when a connection may be already open, network stack corruption will occur.

I'd have func_connect_db() issue a disconnect before making a connection to prevent stack issues.

1 Like

Hello,
This function checks the connection, and if the connection is dropped it reconnects. It does not make a connection again if it is still connected.