Data logger and database interfacing

Dear all,

I'm building a data logger with arduino mega 2560, ethernet shield with micro sd and EM411 gps. What is the best setup to achieve the following features :

  1. Log gps data to the SD card (log.txt)
  2. Read SD card data and send to mysql database per line
  3. Send real time gps data to mysql database

the plan is first i want to log the gps data to SD card, read the SD card and send the data to mysql database using ethernet shield. When all the existing data in log.txt is uploaded to the database and ethernet connection is available, i want to send the real time gps data to mysql and sd card simultaneously. i don't know if it is possible or not.

So far i already did point 1 and point 3 but not simultaneously.
Another thing i want to know is how to handle a lot of data in a single file. since the gps data is logged every 10 second, is it good if i store it in a single file? because the file will become larger and larger. if i store it in a single file, how can i mark the last line sent to the database (if there is no connection to the database) and then continue send the data when the connection available.

Below is the log.txt format
CRM001, 2013-05-01 04:26:03 , -6.88121, 107.58238, 0.24, 63.41
CRM001, 2013-05-01 04:26:05 , -6.88133, 107.58239, 0.28, 96.32

Appreciate if you can advice

Thank you

Hi!

Intresting project.
Maybe you could solve the problem like this:

STEP1 Write row to SD-CARD. Filename sendqueue
Use a uniq rowid for every row.

STEP2 Read sendqueue file and send rowid+row to computer.
One row at a time.
If EOF is reached skip step.

  • Computer send a reply back after it has got a row.
    The reply should include the rowid.

  • When the Arduino recievs a reply the rowid is written to a separte file. Filename: succeded

STEP3 After X minutes the Arduino runs a cleanup task that reads the succeded file (maybe 6-10 rowids) and put them in a array.
Read the sendqueue file and put all succeded rows in a file called finished and the other rows in tempfile.
Delete sendqueue
Rename tempfile to sendqueue.
Run STEP 2 again

Every 10 seconds Write to SD-CARD = Filename sendqueue
Every 5 seconds Read one row from sendqueue filen and send row+rowid to computer.
Every minute (Directly after STEP1) Cleanup

An alternative could be to use a fast SPI-RAM (32Kb) for STEP1 and the succeded file.
Also you could store the row in a binary format.

struct GPSData {
char CRM[6];
byte year;
byte month;
byte day;
byte hour;
byte minute;
float a;
float b;
float c;
float d;
};

Then you could have >1000 rows in SPI-RAM.

CRM001, 2013-05-01 04:26:03 , -6.88121, 107.58238, 0.24, 63.41
6bytes 4bytes 3bytes 4bytes 4bytes 4bytes 4bytes
Float = 4 bytes
Total 29bytes

/Olof

Hi olof...

thank you for your advice, since i'm new to arduino and have a very limited knowledge about programming can you please tell me a bit more detail about your advice.

STEP1...
i already did this and i need to add a unique id for every row.

STEP2...
I can send the data to the computer one row at a time and if i can add unique id in step1 i think i can do this. my question in this step is how to determine if EOF has reached?
STEP3...
can you please tell me more detail about this. do you have reference or sample code where i can start learn.

i'll post my code later

Thank you

STEP3

you may use a software like Advanced Serial Data Logger to capture incoming strings, parse it and send to the MySQL database

or

I've used it in several projects. It is very flexible.

Hi!

I have never useded the SD-fat library. But I have thought about a logger project similar to yours.
The method succededFile.available() should report if you have have reached end of file.
Maybe someone on this excellent forum can help us :slight_smile:

The sdfatlib has a lot of good examples for renaming files, deleting files and reading lines.

Here are some pseudo code (maybe full of errors because I am a bit tired)

void setup()
{
  //Open succededFile for writing
  succededFile = SD.open("succeded", FILE_WRITE);
  bStep=1;
}

void loop()
{
  ulNow=millis();
  switch (bStep) 
  {
  case 1:
    ...bStep1 code...      
      close sendqueue

    //After 1 minute switch to bStep3
    if ((ulNow - ulbStep3Counter) > 60000) 
    {
      close succeded;
      close sendqueueFile;
      //Open sendqueue for reading
      succededFile = SD.open("succeded");
     iC=0; 
    while (succededFile.available()) 
    {
        //In this example rowId is an int
        //We read 2 bytes and convert them to an int
    	bHigh=succededFile.read();
        bLow=succededFile.read();  
        iRowID = (int)bHigh << 8 + bLow;
        succededArray[i]=iRowID;
        iC++;
    }
    iC--;
    // close the file:
    succededFile.close();      
      //Open sendqueue for reading
      sendqueueFile = SD.open("sendqueueFile");       
      //Open sendqueue for writing
      tempFile = SD.open("sendqueueFile", FILE_WRITE);    
      //Open finished for writing
      finishedFile = SD.open("finishedFile", FILE_WRITE);      
      bStep=3;
    }
    //Else switch to step 2
    else
    {
       //Open sendqueue for reading
      sendqueueFile = SD.open("sendqueue");
      bStep=2;
      ulbStep2Counter=ulNow;
      break;
    }
    
  case 2:
    //10 sec switch to step 1
    if ((ulNow - ulbStep2Counter) > 9999) 
    {
      succededFile.close;
      succededFile = SD.open("succeded", FILE_WRITE);
      bStep=1;
    }
    // read from the file until there's nothing else in it:
    if (sendqueueFile.available())
    {
      //function that reads 1 row
      //You could separate rows with char(10) = newline or something
      row=readline();
      sendRow(row);
    }
    else
    {
      //EOF  -- maybe switch to step3???
      sendqueueFile.close;
      sendqueueFile.Close();
    }
    break;
  case 3:
    while (sendqueueFile.available())
    {
      //function that reads 1 row from sendqueueFile
      //You could separate rows with char(10) = newline or something
      row=readline();
      //First 2 bytes is rowID
      iRowID = (int)row[0] << 8 + row[1];
      
      bOk=0;
      //Loop through array for every row
      for (int i=0;i<=iC;i++)
      {
        if (iRowID==succededArray[i])
        {
          bOk=1;
          break;  
        }
      }
      if (bOk==1)
      {
         // write to finishedFile
      }
      else
      {
        // write to tempFile
      }
    }
    //Clear iC counter
    iC=0;
    // Delete sendqueueFile 
    //Rename tempfile to sendqueue. 
    bStep=2;
    ulbStep3Counter=ulNow;
    break;
  }
  //Function that check for replies
  //Should write rowID to succededFile
  if (bStep<>3)
  {
    checkIncomingReplies();
  }
}

I think something like that should work.
Step1 and Step2 will not lock the program.
Step2 will just send 1 row at a time and the function checkIncomingReplies should be able to check for replies.

I can see one problem with the array. succededArray is not dynamic, it must be large enough to store all replies between step2 and step3.
Variable iC keeps track of number of succeded rowIDs.
Maybe a linked list would be better (and more complicated). Another alternative is to skip the array and loop through succededFile for every row in sendqueueFile.
Much slower but frees up RAM.

/Olof

Thanks for the advice, but i really confuse how to start. i use SD.h library to write the data to SD card. below is my code

#include <TinyGPS.h>
#include <SD.h>
#include <stdlib.h>
#include <SPI.h>
#include <Ethernet.h>

TinyGPS gps;
static char dtostrfbuffer[20];
int CS = 4;
int LED = 13;


//Define String
String SD_date_time = "invalid";
String SD_lat = "invalid";
String SD_lon = "invalid";
String SD_speed = "invalid";
String SD_heading = "invalid";
String dataString ="";

static void gpsdump(TinyGPS &gps);
static bool feedgps();
static void print_float(float val, float invalid, int len, int prec, int SD_val);
static void print_int(unsigned long val, unsigned long invalid, int len);
static void print_date(TinyGPS &gps);
static void print_str(const char *str, int len);

void setup()
{

  pinMode(CS, OUTPUT);  //Chip Select untuk SD Card
  pinMode(LED, OUTPUT);  //LED Indicator
  pinMode(53, OUTPUT); //chip select pin 

  //Serial interfaces
  Serial.begin(9600);
  Serial3.begin(4800);

  // ethernet
  while (!Serial) {
    ; // wait for serial port to connect. Needed for Leonardo only
  }

  //Connect to the SD Card
  if(!SD.begin(CS))
  {
    Serial.println("Card Failure");
    return;
  }
}

void loop()
{
  bool newdata = false;
  unsigned long start = millis();

  // get data 
  while (millis() - start < 2000)
  {
    if (feedgps())
      newdata = true;
  }

  gpsdump(gps);

  //write to sd card
  dataString = SD_date_time + "," + SD_lat + "," + SD_lon + "," + SD_speed + "," + SD_heading;
  if(SD_date_time != "invalid")
    digitalWrite(LED, HIGH);
  else
    digitalWrite(LED, LOW);

  //open file log.txt
  File dataFile = SD.open("LOG.txt", FILE_WRITE);
  if (dataFile)
  {
    dataFile.print("CRM001");
    dataFile.print(", ");
    dataFile.println(dataString);
    Serial.println(dataString);
    dataFile.close();
  }
  else
  {
    Serial.println("\nCouldn't open the log file!");
  }
}

static void gpsdump(TinyGPS &gps)
{
  float flat, flon;
  unsigned long age, date, time, chars = 0;
  unsigned short sentences = 0, failed = 0;

  print_int(gps.satellites(), TinyGPS::GPS_INVALID_SATELLITES, 5);
  print_int(gps.hdop(), TinyGPS::GPS_INVALID_HDOP, 5);
  gps.f_get_position(&flat, &flon, &age); 
  print_float(flat, TinyGPS::GPS_INVALID_F_ANGLE, 9, 5, 1); //LATITUDE
  print_float(flon, TinyGPS::GPS_INVALID_F_ANGLE, 10, 5, 2); //LONGITUDE
  print_int(age, TinyGPS::GPS_INVALID_AGE, 5);

  print_date(gps); //DATE AND TIME

  print_float(gps.f_altitude(), TinyGPS::GPS_INVALID_F_ALTITUDE, 8, 2, 0);
  print_float(gps.f_course(), TinyGPS::GPS_INVALID_F_ANGLE, 7, 2, 4);
  print_float(gps.f_speed_kmph(), TinyGPS::GPS_INVALID_F_SPEED, 6, 2, 3);
  print_str(gps.f_course() == TinyGPS::GPS_INVALID_F_ANGLE ? "*** " : TinyGPS::cardinal(gps.f_course()), 6);

}

static void print_int(unsigned long val, unsigned long invalid, int len)
{
  char sz[32];
  if (val == invalid)
    strcpy(sz, "*******");
  else
    sprintf(sz, "%ld", val);
  sz[len] = 0;
  for (int i=strlen(sz); i<len; ++i)
    sz[i] = ' ';
  if (len > 0) 
    sz[len-1] = ' ';
  //Serial.print(sz);
  feedgps();
}


static void print_float(float val, float invalid, int len, int prec, int SD_val)
{
  char sz[32];
  if (val == invalid)
  {
    strcpy(sz, "*******");
    sz[len] = 0;
    if (len > 0) 
      sz[len-1] = ' ';
    for (int i=7; i<len; ++i)
      sz[i] = ' ';
    //Serial.print(sz);
    if(SD_val == 1) SD_lat = sz;
    else if(SD_val == 2) SD_lon = sz;
    else if(SD_val== 3) SD_speed = sz;
    else if(SD_val== 4) SD_heading = sz;
  }
  else
  {
    //Serial.print(val, prec);
    if (SD_val == 1) SD_lat = dtostrf(val,10,5,dtostrfbuffer);
    else if (SD_val == 2) SD_lon = dtostrf(val,10,5,dtostrfbuffer);
    else if (SD_val == 3) SD_speed = dtostrf(val,10,2,dtostrfbuffer);
    else if (SD_val == 4) SD_heading = dtostrf(val,10,2,dtostrfbuffer);
    int vi = abs((int)val);
    int flen = prec + (val < 0.0 ? 2 : 1);
    flen += vi >= 1000 ? 4 : vi >= 100 ? 3 : vi >= 10 ? 2 : 1;
    for (int i=flen; i<len; ++i)
      Serial.print(" ");
  }
  feedgps();
}

static void print_date(TinyGPS &gps)
{
  int year;
  byte month, day, hour, minute, second, hundredths;
  unsigned long age;
  gps.crack_datetime(&year, &month, &day, &hour, &minute, &second, &hundredths, &age);
  if (age == TinyGPS::GPS_INVALID_AGE)
  {
    //Serial.print("*******    *******    ");
    SD_date_time = "invalid";
  }
  else
  {
    char sz[32];
    sprintf(sz, "%02d-%02d-%02d %02d:%02d:%02d   ",
    year, month, day, hour, minute, second);
    //Serial.print(sz);
    SD_date_time = sz;
  }
  print_int(age, TinyGPS::GPS_INVALID_AGE, 5);
  feedgps();
}

static void print_str(const char *str, int len)
{
  int slen = strlen(str);
  for (int i=0; i<len; ++i)
    //Serial.print(i<slen ? str[i] : ' ');
    feedgps();
}

static bool feedgps()
{
  while (Serial3.available())
  {
    if (gps.encode(Serial3.read()))
      return true;
  }
  return false;
}

the code above is taken from a tutorial somewhere on the net, now the first thing i want to do is how can i read the LOG.txt file and print it to serial. I've try readwrite sample and it works...but when i combine with the code above it become not working. may be it because i write and read at the same time. How can i log the data every 10 second and use the time between it to read the LOG.txt

Hi!

I don't think you can read and write to a file at the same time.
After reading you have to close the file and then reopen it for writing.

If you look at my code I have switch case statement that work with the different steps.
When I change to another step I have to close and reopen the files in the correct mode (reading/writing)

STEP1
Write a row to sendqueue.
If 1 minute has passed (ulbStep3Counter) switch to STEP3
Else switch to STEP2

STEP2 (the break statment is on the wrong line)
After 10sec (ulbStep2Counter) switch to STEP1

STEP3
When done switches to STEP2

Outside of the switch case statment the code checks for incoming replies from the computer.

/Olof

I try your code but it has a lot of error

test_switch_case.ino: In function 'void loop()':
test_switch_case:76: error: 'i' was not declared in this scope
test_switch_case:104: error: statement cannot resolve address of overloaded function
test_switch_case:109: error: 'class SdFile' has no member named 'available'
test_switch_case:113: error: 'row' was not declared in this scope
test_switch_case:113: error: 'readline' was not declared in this scope
test_switch_case:114: error: 'sendRow' was not declared in this scope
test_switch_case:123: error: 'class SdFile' has no member named 'available'
test_switch_case:127: error: 'row' was not declared in this scope
test_switch_case:127: error: 'readline' was not declared in this scope
test_switch_case:131: error: 'bOk' was not declared in this scope
test_switch_case:135: error: invalid types 'int[int]' for array subscript
test_switch_case:160: error: expected primary-expression before '>' token
test_switch_case:162: error: 'checkIncomingReplies' was not declared in this scope
#include <SD.h>
#include <TinyGPS.h>
#include <stdlib.h>
#include <SPI.h>
#include <Ethernet.h>
#include <SdFat.h>
#include <SdFatUtil.h>

SdFile succededFile;
SdFile sendqueueFile;
SdFile finishedFile;
SdFile tempFile;
int iRowID;
int bHigh;
int bLow;
int succededArray;
File succeded;
File sendqueue;
int bStep;
int ulbStep2Counter;
int iC;
int ulbStep3Counter;
unsigned long ulNow;

TinyGPS gps;
static char dtostrfbuffer[20];
int CS = 4;
int LED = 13;

void setup()
{
  //Open succededFile for writing
  File succededFile = SD.open("success.txt", FILE_WRITE);
  bStep=1;
}

void loop()
{
  ulNow=millis();
  switch (bStep) 
  {
  case 1:
    //...bStep1 code...      
      sendqueueFile.close();

    //After 1 minute switch to bStep3
    if ((ulNow - ulbStep3Counter) > 60000) 
    {
  succededFile.close();
      sendqueueFile.close();
      //Open sendqueue for reading
      File succededFile = SD.open("success.txt");
     iC=0; 
    while (succededFile.available()) 
    {
        //In this example rowId is an int
        //We read 2 bytes and convert them to an int
    	bHigh=succededFile.read();
        bLow=succededFile.read();  
        iRowID = (int)bHigh << 8 + bLow;
        succededArray[i]=iRowID;
        iC++;
    }
    iC--;
    // close the file:
    succededFile.close();      
      //Open sendqueue for reading
      File sendqueueFile = SD.open("send.txt");       
      //Open sendqueue for writing
      File tempFile = SD.open("temp.txt", FILE_WRITE);    
      //Open finished for writing
      File finishedFile = SD.open("finished.txt", FILE_WRITE);      
      bStep=3;
    }
    //Else switch to step 2
    else
    {
       //Open sendqueue for reading
      File sendqueueFile = SD.open("send.txt");
      bStep=2;
      ulbStep2Counter=ulNow;
      break;
    }
    
  case 2:
    //10 sec switch to step 1
    if ((ulNow - ulbStep2Counter) > 9999) 
    {
      succededFile.close;
      File succededFile = SD.open("finished.txt", FILE_WRITE);
      bStep=1;
    }
    // read from the file until there's nothing else in it:
    if (sendqueueFile.available())
    {
      //function that reads 1 row
      //You could separate rows with char(10) = newline or something
      row=readline();
      sendRow(row);
    }
    else
    {
      //EOF  -- maybe switch to step3???
      sendqueueFile.close();
    }
    break;
  case 3:
    while (sendqueueFile.available())
    {
      //function that reads 1 row from sendqueueFile
      //You could separate rows with char(10) = newline or something
      row=readline();
      //First 2 bytes is rowID
      iRowID = (int)row[0] << 8 + row[1];
      
      bOk=0;
      //Loop through array for every row
      for (int i=0;i<=iC;i++)
      {
        if (iRowID==succededArray[i])
        {
          bOk=1;
          break;  
        }
      }
      if (bOk==1)
      {
         // write to finishedFile
      }
      else
      {
        // write to tempFile
      }
    }
    //Clear iC counter
    iC=0;
    // Delete sendqueueFile 
    //Rename tempfile to sendqueue. 
    bStep=2;
    ulbStep3Counter=ulNow;
    break;
  }
  //Function that check for replies
  //Should write rowID to succededFile
  if (bStep<>3)
  {
    checkIncomingReplies();
  }
}

i didn't put any code yet for capturing the gps data. do you mean with case 1,2,3 is

case 1

  • Log gps data to the SD card every 10 second
    case 2
  • Read SD card data and send to mysql database per line
    case 3
  • Send real time gps data to mysql database and sd card

i appreciate if you could explain how can i make my code works with your code? and is it possible to use date as the unique id/rowID.

thank you

Hi!

My code is not completed. It's an idea of a solution.

To get it working you need to break up the project in different pieces.
It is much easier that way.

Start to download the sdfatlib. (You could use another lib if you like)
https://code.google.com/p/sdfatlib/downloads/list

Make a separat sketch where you get used to file handling.
Try to write rows to a file.
Write a function to read one line at a time (readline function).
Try to delete and rename a file.

You should also make a function to get GPS-data.
Maybe a function like "getGPSrow" that returns a chararray.

Then your code could look like:

GPSrow=getGPSrow();
writeline(sendqueue,GPSrow);

Use a int variable as a uniq rowID.

Just increment the variable after you use the ID. If you reboot the microcontroller you must be able to get the last used rowID.
Maybe you have to read sendqueue and succeded files to get the highest rowID.

There are a lot of compilation errors in my "code" because I do not declare any variabels.

Not much help but it is often a lot of work to finish a project.

/Olof