Mitja Sajovic

Important changes to forums and questions

All forums and questions are now archived. To start a new conversation or read the latest updates go to forums.mbed.com.

6 years, 7 months ago.

Parallel while loops + MySQL

I'm writing a program, which purpose is counting the number of pieces machine makes every minute and send the data to database on the server. What I'm trying to do, is taking the impulses from the machine (p12) and increasing the counter (one pulse = one count). Every minute (when the seconds hit 0) the time and counter values are sent to the database and counter is set to 0.

The problem I'm having is, when the timer hit 0 seconds, the program starts writing data to database, which takes around 3 secsonds. Meanwhile the counter is not counting. So if i get any pulse from the machine, within these 3 seconds, it won't increse the counter.

I'm trying to set up two while loops. One for the counter and one for the timer and MySQL. I've searched the internet and tried with rtos but cant find any rtos library for MySQL.

Any suggestions how else can I solve this?

I'm compiling for the mbed "LPC1768"

#include "mbed.h"
#include "EthernetNetIf.h"
#include "MySQLClient.h"
#include "C12832.h"
#include "NTPClient.h"

#define SQL_SERVER   "SQL_SERVER"
#define SQL_USER     "SQL_USER"
#define SQL_PASSWORD "SQL_PASSWORD"
#define SQL_DB       "SQL_DB"

DigitalIn trigger(p12);
C12832 lcd(p5, p7, p6, p8, p11);
NTPClient ntp;
EthernetNetIf eth; 
MySQLClient sql;

MySQLResult sqlLastResult;
void onMySQLResult(MySQLResult r)
{
  sqlLastResult = r;
}

int main()
{
     //Connect to ethernet
     EthernetErr ethErr = eth.setup();
     //connect to NTP pool server
     Host server(IpAddr(), 123, "xxx.xxx.xxx.xxx");
     int c = 0;
     while(1)
     {
        ntp.setTime(server);
        time_t t = time(NULL)+3601;
        struct tm *tm = localtime(&t);
        char date[20];
        strftime(date, sizeof(date), "%Y-%m-%d %H:%M:%S", tm);
        lcd.locate(0,3);
        lcd.printf(date);
        
        if(trigger)
        {
            c++;
            lcd.locate(0,15);
            lcd.printf("%d",c);
            wait(0.5);
        }
        
        if (tm->tm_sec == 00)
        {
          Host host(IpAddr(), 3306, SQL_SERVER);
          //Connect
          sqlLastResult = sql.open(host, SQL_USER, SQL_PASSWORD, SQL_DB, onMySQLResult);
          while(sqlLastResult == MYSQL_PROCESSING)
          {
            Net::poll();
          }
          //SQL Command
          //Make command
          char cmd[128] = {0};
          sprintf(cmd, "INSERT INTO mbed_stevci (time, machine00) VALUES('%s', '%d')",date,c);
          //INSERT INTO DB
          string cmdStr = string(cmd); 
          sqlLastResult = sql.sql(cmdStr);
          while(sqlLastResult == MYSQL_PROCESSING)
          {
            Net::poll();
          }
          sql.exit();
          c=0;
        }
    }
}

2 Answers

Graham S.
6 years, 7 months ago.

Using the rtos and setting up two parallel threads is a natural way to handle this especially when you have drivers like MySQL that take a long time to respond.

If you want to keep everything in a single loop, a way to handle this is to make sure none of your functions block. If a function requires waiting, then internally it becomes a state machine. You might have to modify whatever MySQL library you are using. So state 1 might be to send data to a server, and state 2 might be to wait for a response. Instead of stopping the whole program waiting for a response, you exit the function and then next time through the loop you check the wait condition again. You let the rest of the program run while you are waiting.

A third way to handle it would be to put your timing into an interrupt service routine. So you would attach a callback to the rising or falling edge of that external pulse signal and read and reset your timer from inside the ISR callback. When the interrupt fires it will go handle the timing stuff and then go back to the MySQL stuff. This is the way to handle it if you want the most accurate timing, vs trying to poll the pin state.

Another option is to simply not transmit every new piece of data to the server in realtime. Store them up and send a batch once every hour instead which will greatly reduce the potential for interference.

Accepted Answer
Andy A
6 years, 7 months ago.

Personally I'd use an interrupt for the counting up and then when you need to send the value disable interrupts, make a copy of the counter, reset the counter and re-enable interrupts.

Unless you get two pulses in the time it takes to make that copy you shouldn't miss a single count and it avoids the need for all of the extra complications of threads etc...

The code would be something along the lines of:

InterruptIn countUp(p12);

volatile int counter;

void onCountUp(void) {
  counter++;
}

main () {
  counter = 0;
  countUp.rise(&onCountUp);
  while(true) {
    if (needToSendData) {
      int countCopy;
      __disable_irq();
      countCopy = counter;
      counter = 0;
      __enable_irq();
      sendDataToDatabase(countCopy,time);
    }
  }
}