SQLite 3 data entry error

ravirkeen · February 26, 2024, 11:29am

Hello,
I am new to the DATABASE.
I am using SQLite 3 database with esp32. I cant able to insert data more than 800 - 900 rows and number of entry changes every time when I restart the ESP32.
I can't find what is the problem.

wildbill · February 26, 2024, 8:19pm

Sounds like a memory issue, but without the code it’s hard to say.

ravirkeen · February 27, 2024, 5:21am

I created database named BMS1 using sqlite3 browser software.
Database has one table called BMS1_DATA and ID,DATE,VOLT,CURRENT are the column in the table.

Here is my code ,

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <SPI.h>
#include <FS.h>
#include "SD.h" 
hw_timer_t * timer = NULL;
portMUX_TYPE timerMux = portMUX_INITIALIZER_UNLOCKED;


bool first_time = false;
const char* data = "Callback function called";
char *zErrMsg = 0;
int openDb(const char *, sqlite3 **);
int db_exec(sqlite3 *, const char *);
int db__exec(sqlite3 *, const char *);
static int callback(void *data, int argc, char **argv, char **azColName);
static int callback2(void *data, int argc, char **argv, char **azColName);
String date_1 = "DATE = '11-02-2024'";
String id_1 = "ID = 2";
String send_db = "Select * from BMS1_DATA where ID = 1";
//int i = send_db.length();
char send__db [100] ;

String add_db = "insert into BMS1_DATA(ID,DATE , VOLT , CURRENT) values(1,'25-02-2024' , 1 , 1)";
char add__db[100];

String delete_db = "delete from BMS1_DATA where ID = 2";
char delete__db[100];

String BUFF = "";
bool String_complete = false;
char INCOMING_DATA[50];

char DATE[30];

int counter=0;

int ID = 0;
char ID_1[10];

sqlite3 *BMS1_DATA;
int rc;

void IRAM_ATTR onTimer() 
{
  portENTER_CRITICAL_ISR(&timerMux);
  counter++;
  ID++;
//  counter2++;
  portEXIT_CRITICAL_ISR(&timerMux);

}


void setup() {
  Serial.begin(115200);

    timer = timerBegin(0, 80, true); 
    timerAttachInterrupt(timer, &onTimer, true);
    timerAlarmWrite(timer, 500000, true);// time value 
    timerAlarmEnable(timer);

//  Serial.println(send_db);
//  send_db.replace("DATE = '14-02-2024'" , date_1);
//
//  send_db.toCharArray(send__db, sizeof(send__db));

//  Serial.println(send__db);
  Serial.println("START");
  pinMode(1,OUTPUT);
  digitalWrite(1,HIGH);
  digitalWrite(1,LOW);

  
  SPI.begin();
  SD.begin();

  sqlite3_initialize();

//  if(openDb("/sd/BMS1.db", &BMS1_DATA))
//     return;



///////////////////////////////////////////  To inseret data into database  ////////////////////

//  rc = db_exec(BMS1_DATA , "insert into BMS1_DATA(DATE , VOLT , CURRENT) values('15-02-2024' , 50 , 60)");
//  if(rc != SQLITE_OK){
//    sqlite3_close(BMS1_DATA);
//    return;
//  }
//   else
//    Serial.println("New record created");
//
//  rc = db_exec(BMS1_DATA , "Select * from BMS1_DATA");
//  if(rc != SQLITE_OK){
//    sqlite3_close(BMS1_DATA);
//    return;
//  }

/////////////////////////////////////////////////////////////////////////////////////////////////

/////////////////////////////// To read data from the database  ////////////////////////////////

//   rc = db_exec(BMS1_DATA, send__db);
//   if (rc != SQLITE_OK) {
//       sqlite3_close(BMS1_DATA);
//       return;
//   }


//   rc = db_exec(BMS1_DATA, "Select * from BMS1_DATA where DATE = '14-02-2024'");
//   if (rc != SQLITE_OK) {
//       sqlite3_close(BMS1_DATA);
//       return;
//   }


///////////////////////////////////////////////////////////////////////////////////////////////////.

/////////////////////////////////To delete data////////////////////////////////////////

//   rc = db_exec(BMS1_DATA, );
//   if (rc != SQLITE_OK) {
//       sqlite3_close(BMS1_DATA , "delete from BMS_1 where ID = 2");
//       return;
//   }
  
//sqlite3_close(BMS1_DATA);

}

void loop(){

//  Serial.print(BUFF);

      if(counter == 1)
      {
        counter = 0;
        digitalWrite(1,HIGH);
        String WRITE_DATA = "values(1,'25-02-2024' , 1 , 1)";
//        char WRITE__DATA[100];

        itoa(ID,ID_1,10);

        WRITE_DATA.replace("1" , ID_1);
//        Serial.println(WRITE_DATA);
//        Serial.println(ID_1);
        
        
        if(openDb("/sd/BMS1.db", &BMS1_DATA))
                 return;
                 
                add_db.replace("values(1,'25-02-2024' , 1 , 1)", WRITE_DATA);
//                Serial.println(add_db);
            
                add_db.toCharArray(add__db, sizeof(add__db));
              
                  rc = db__exec(BMS1_DATA , add__db);
              if(rc != SQLITE_OK){
                sqlite3_close(BMS1_DATA);
                return;
              }
               else
//                Serial.println("New record created");
            
              rc = db__exec(BMS1_DATA , "Select * from BMS1_DATA");
              if(rc != SQLITE_OK){
                sqlite3_close(BMS1_DATA);
                return;
              }
                sqlite3_close(BMS1_DATA);
                add_db.replace(WRITE_DATA,"values(1,'25-02-2024' , 1 , 1)");
                WRITE_DATA.replace( ID_1 , "1");

            digitalWrite(1,LOW);
      
          }

  BUFF.toCharArray(INCOMING_DATA, sizeof(INCOMING_DATA));
  
  if(String_complete)
  {
    String_complete = false;

//    if(INCOMING_DATA[0] == 'I' && INCOMING_DATA[1] == 'D')
//    {
//    
//
//      delete_db.replace("ID = 2" , BUFF);
//    
//      delete_db.toCharArray(delete__db, sizeof(delete__db));
//    
//    //  Serial.println(BUFF);
//    //  Serial.println(send__db);
//    
//      if(openDb("/sd/BMS1.db", &BMS1_DATA))
//         return;
//    
//           rc = db_exec(BMS1_DATA, delete__db);
//          if (rc != SQLITE_OK) {
//            sqlite3_close(BMS1_DATA);
//            return;
//          }
//    
//       sqlite3_close(BMS1_DATA);
//    
//       send_db.replace(BUFF,"ID = 2");
//      }


      

//    if(INCOMING_DATA[0] == 'v' && INCOMING_DATA[1] == 'a' && INCOMING_DATA[2] == 'l' && INCOMING_DATA[3] == 'u')
//    {

//        if(openDb("/sd/BMS1.db", &BMS1_DATA))
//         return;
//         
//        add_db.replace("values('15-02-2024' , 50 , 60)" , BUFF);
//    
//        add_db.toCharArray(add__db, sizeof(add__db));
//      
//          rc = db__exec(BMS1_DATA , add__db);
//      if(rc != SQLITE_OK){
//        sqlite3_close(BMS1_DATA);
//        return;
//      }
//       else
//        Serial.println("New record created");
//    
//      rc = db__exec(BMS1_DATA , "Select * from BMS1_DATA");
//      if(rc != SQLITE_OK){
//        sqlite3_close(BMS1_DATA);
//        return;
//      }
//        sqlite3_close(BMS1_DATA);
//        add_db.replace(BUFF,"values('15-02-2024' , 50 , 60)");
//        
//    }

    

    if(INCOMING_DATA[0] == 'I' && INCOMING_DATA[1] == 'D')
    {
    

      send_db.replace("ID = 1" , BUFF);
    
      send_db.toCharArray(send__db, sizeof(send__db));
    
    //  Serial.println(BUFF);
    //  Serial.println(send__db);
    
      if(openDb("/sd/BMS1.db", &BMS1_DATA))
         return;
    
           rc = db_exec(BMS1_DATA, send__db);
       if (rc != SQLITE_OK) {
           sqlite3_close(BMS1_DATA);
           return;
       }
    
       sqlite3_close(BMS1_DATA);
    
       send_db.replace(BUFF,"ID = 1");
      }
  }

//delay(1000);

     
  
}

static int callback(void *data, int argc, char **argv, char **azColName) {
  int i;
  if (first_time) {
     Serial.println((const char *) data);
     for (i = 0; i<argc; i++) {
         if (i)
           Serial.print((char) '\t');
         Serial.printf("%s", azColName[i]);
     }
     Serial.printf("\n");
     first_time = false;
  }
  for (i = 0; i<argc; i++) {
    if (i)
      Serial.print((char) '\t');
    Serial.printf("%s", argv[i] ? argv[i] : "NULL");
  }
  Serial.printf("\n");
  return 0;
  }

static int callback2(void *data, int argc, char **argv, char **azColName) {
  int i;
  if (first_time) {
//     Serial.println((const char *) data);
     for (i = 0; i<argc; i++) {
         if (i)
//           Serial.print((char) '\t');
//         Serial.printf("%s", azColName[i]);
//     }
//     Serial.printf("\n");
     first_time = false;
  }
  for (i = 0; i<argc; i++) {
    if (i)
//      Serial.print((char) '\t');
//    Serial.printf("%s", argv[i] ? argv[i] : "NULL");
//  }
//  Serial.printf("\n");
  return 0;
  }
  }
}

int openDb(const char *filename, sqlite3 **db) {
   int rc = sqlite3_open(filename, db);
   if (rc) {
       Serial.printf("Can't open database: %s\n", sqlite3_errmsg(*db));
       return rc;
   } else {
//       Serial.printf("Opened database successfully\n");
   }
   return rc;
}


int db_exec(sqlite3 *db, const char *sql) {
   Serial.println('\n');
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
       Serial.printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } 
   return rc;
}

int db__exec(sqlite3 *db, const char *sql) {
//   Serial.println('\n');
   int rc = sqlite3_exec(db, sql, callback2, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
       Serial.printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } 
   return rc;
}

void serialEventRun()
{
  while(Serial.available())
  {
    int counter = 0;
    BUFF = Serial.readString();
    String_complete = true;
 }
}

Topic		Replies	Views
Problema com inserção de dados sqlite esp32 Português	1	199	August 3, 2024
ESP8266 sqlite3: out of memory bei INSERT INTO Befehlen Deutsch	19	1080	October 23, 2022
SQLITE for ESP32 insert/update very fast, but slow when using "ON CONFLICT DO UPDATE" Programming	1	4961	July 5, 2022
ESP32 SD Card Error frequently when using SQLITE3 Programming	1	883	May 6, 2021
ESP32 SQlite3 databsae Library Programming	1	515	May 6, 2021

SQLite 3 data entry error

Related topics