Too many sql statements and it fails

[zaplakkies]

I'm trying to create a routine that initializes the database if we want to do a factory reset. When I run the commands in sequence like this the last few table creates do not happen and subsequent inserts fail with the table does not exist. Ive tried big delays between statements but I still get the error. Swapping the order only changes which ones don't work anymore.
If I run each statement separately from the sql.html sample in the example directly, all the statements pass without issue.

Please can someone help me see where I'm going wrong or how to split it up so I can get it working.

String simplesql(const char *sql)
{
  String resp = "";
  sqlite3_stmt *res;
  const char *tail;
  int rc = sqlite3_prepare_v2(db1, sql, -1, &res, &tail);
  if (rc != SQLITE_OK)
  {
    resp += "Error executing query: ";
    resp += sqlite3_errmsg(db1);
    return resp;
  }

  int rec_count = 0;
  while (sqlite3_step(res) == SQLITE_ROW)
  {
    int count = sqlite3_column_count(res);
    for (int i = 0; i < count; i++)
    {
      const char *col_text = (const char *)sqlite3_column_text(res, i);
      if (col_text)
      {
        resp += col_text;
        if (i < count - 1)
        {
          resp += ", ";
        }
      }
    }
    resp += "\n";
    rec_count++;
  }

  sqlite3_finalize(res);

  if (rec_count == 0)
  {
    resp += "No records found.";
  }

  return resp;
}
String initdb() {
  Serial.println("Initializing database");

  if (openDb("/spiffs/data.db") != SQLITE_OK)
  {
    return "Error opening database";
  }
  const char *initCommands[] = {
    "DROP TABLE IF EXISTS idpoints",
    "DROP TABLE IF EXISTS pointtype",
    "DROP TABLE IF EXISTS pointsclocked",
    "DROP TABLE IF EXISTS eventlog",
    "DROP TABLE IF EXISTS wifisettings",
    "DROP TABLE IF EXISTS audittrail",
    "DROP TABLE IF EXISTS settings",
    "DROP TABLE IF EXISTS eventtype",
    "CREATE TABLE wifisettings (apname TEXT, password TEXT, ip TEXT)",
    "CREATE TABLE idpoints (id INTEGER, value TEXT, name TEXT, lastclocked INTEGER, type INTEGER)",
    "CREATE TABLE pointtype (id INTEGER, name TEXT)",
    "CREATE TABLE pointsclocked (datetime INTEGER, idpoint INTEGER)",
    "CREATE TABLE eventlog (id INTEGER, datetime INTEGER, event INTEGER, data TEXT, sent INTEGER)",
    "CREATE TABLE audittrail (id INTEGER PRIMARY KEY, datetime INTEGER, event TEXT)",
    "CREATE TABLE settings (name TEXT, value TEXT)",
    "CREATE TABLE eventtype (id INTEGER, shortform TEXT, name TEXT)",
    "INSERT INTO pointtype VALUES (1,'Fixed')",
    "INSERT INTO pointtype VALUES (2,'User')",
    "INSERT INTO pointtype VALUES (3,'Asset')",
    "INSERT INTO settings VALUES ('APpass','')",
    "INSERT INTO settings VALUES ('ntp','pool.ntp.org')",
    "INSERT INTO settings VALUES ('gmtoffset','7200')", // TODO: test negative values
    "INSERT INTO eventtype VALUES (1,'PU','Power up')",
    "INSERT INTO eventtype VALUES (2,'PD','Power down')",
    "INSERT INTO eventtype VALUES (3,'CN','Charge On')",
    "INSERT INTO eventtype VALUES (4,'CF','Charge Off')",
    "INSERT INTO eventtype VALUES (5,'X','Inactivity')",
    "INSERT INTO eventtype VALUES (6,'ST','Unit Settings')",
    "INSERT INTO eventtype VALUES (7,'C','Tamper')",
    "INSERT INTO eventtype VALUES (8,'c','Tamper restore')",
    "INSERT INTO eventtype VALUES (9,'D','Touch port short')",
    "INSERT INTO eventtype VALUES (10,'G','Baton date and time set')",
    "INSERT INTO eventtype VALUES (11,'N','Battery OK')",
    "INSERT INTO eventtype VALUES (12,'n','Battery low')",
    "INSERT INTO eventtype VALUES (13,'S','ID Point')",
    "INSERT INTO eventtype VALUES (14,'CW','Tamper Moisture')",
    "INSERT INTO eventtype VALUES (15,'CT','Tamper over temperature')"
  };

  for (auto &cmd : initCommands)
  {
    Serial.println(simplesql(cmd));
  }
}

[siara-cc]

Hi can you please post the entire sketch?

[zaplakkies]

Thank you for the help. Ive removed as much as possible so that I can reproduce the error without all the other stuff. Below is the complete code that creates the error and the output on my pc.

#include <Arduino.h>
#include <ESPAsyncWebServer.h>
#include <AsyncTCP.h>
#include <SPIFFS.h>
#include <sqlite3.h> //26% mem
#include <WiFi.h>

#define BUILTIN_LED 2

#define version 0.1b
#define versiondate "2024-07-04"

sqlite3 *db1;
int rc;
sqlite3_stmt *res;
int rec_count = 0;
const char *tail;
char current_db[255];
String db_name = "/spiffs/data.db";
String tempS;

void fatalerror(int errnum)
{
  while (true)
  {
    for (int i = 0; i < errnum; i++)
    {
      digitalWrite(BUILTIN_LED, HIGH);
      delay(200);
      digitalWrite(BUILTIN_LED, LOW);
      delay(200);
    }
    delay(500);
  }
}

int openDb(const char *filename)
{
  if (db1 != NULL)
  {
    sqlite3_close(db1);
  }
  int rc = sqlite3_open(filename, &db1);
  if (rc)
  {
    Serial.printf("Can't open database: %s\n", sqlite3_errmsg(db1));
    memset(current_db, '\0', sizeof(current_db));
    return rc;
  }
  else
  {
    Serial.printf("Opened database successfully\n");
    strcpy(current_db, filename);
  }
  return rc;
}

String simplesql(const char *sql)
{
  String resp = "";
  sqlite3_stmt *res;
  const char *tail;
  int rc = sqlite3_prepare_v2(db1, sql, -1, &res, &tail);
  if (rc != SQLITE_OK)
  {
    resp += "Error executing query: ";
    resp += sqlite3_errmsg(db1);
    return resp;
  }

  int rec_count = 0;
  while (sqlite3_step(res) == SQLITE_ROW)
  {
    int count = sqlite3_column_count(res);
    for (int i = 0; i < count; i++)
    {
      const char *col_text = (const char *)sqlite3_column_text(res, i);
      if (col_text)
      {
        resp += col_text;
        if (i < count - 1)
        {
          resp += ", ";
        }
      }
    }
    resp += "\n";
    rec_count++;
  }

  sqlite3_finalize(res);

  if (rec_count == 0)
  {
    resp += "No records found.";
  }

  return resp;
}

String initdb()
{
  Serial.println("Initializing database");

  if (openDb("/spiffs/data.db") != SQLITE_OK)
  {
    return "Error opening database";
  }

  const char *initCommands[] = {
    "DROP TABLE IF EXISTS idpoints",
    "DROP TABLE IF EXISTS pointtype",
    "DROP TABLE IF EXISTS pointsclocked",
    "DROP TABLE IF EXISTS eventlog",
    "DROP TABLE IF EXISTS wifisettings",
    "DROP TABLE IF EXISTS audittrail",
    "DROP TABLE IF EXISTS settings",
    "DROP TABLE IF EXISTS eventtype",
    "CREATE TABLE wifisettings (apname TEXT, password TEXT, ip TEXT)",
    "CREATE TABLE idpoints (id INTEGER, value TEXT, name TEXT, lastclocked INTEGER, type INTEGER)",
    "CREATE TABLE pointtype (id INTEGER, name TEXT)",
    "CREATE TABLE pointsclocked (datetime INTEGER, idpoint INTEGER)",
    "CREATE TABLE eventlog (id INTEGER, datetime INTEGER, event INTEGER, data TEXT, sent INTEGER)",
    "CREATE TABLE audittrail (id INTEGER PRIMARY KEY, datetime INTEGER, event TEXT)",
    "CREATE TABLE settings (name TEXT, value TEXT)",
    "CREATE TABLE eventtype (id INTEGER, shortform TEXT, name TEXT)",
    "INSERT INTO pointtype VALUES (1,'Fixed')",
    "INSERT INTO pointtype VALUES (2,'User')",
    "INSERT INTO pointtype VALUES (3,'Asset')",
    "INSERT INTO settings VALUES ('APpass','')",
    "INSERT INTO settings VALUES ('ntp','pool.ntp.org')",
    "INSERT INTO settings VALUES ('gmtoffset','7200')", // TODO: test negative values
    "INSERT INTO eventtype VALUES (1,'PU','Power up')",
    "INSERT INTO eventtype VALUES (2,'PD','Power down')",
    "INSERT INTO eventtype VALUES (3,'CN','Charge On')",
    "INSERT INTO eventtype VALUES (4,'CF','Charge Off')",
    "INSERT INTO eventtype VALUES (5,'X','Inactivity')",
    "INSERT INTO eventtype VALUES (6,'ST','Unit Settings')",
    "INSERT INTO eventtype VALUES (7,'C','Tamper')",
    "INSERT INTO eventtype VALUES (8,'c','Tamper restore')",
    "INSERT INTO eventtype VALUES (9,'D','Touch port short')",
    "INSERT INTO eventtype VALUES (10,'G','Baton date and time set')",
    "INSERT INTO eventtype VALUES (11,'N','Battery OK')",
    "INSERT INTO eventtype VALUES (12,'n','Battery low')",
    "INSERT INTO eventtype VALUES (13,'S','ID Point')",
    "INSERT INTO eventtype VALUES (14,'CW','Tamper Moisture')",
    "INSERT INTO eventtype VALUES (15,'CT','Tamper over temperature')"
  };

  for (auto &cmd : initCommands)
  {
    Serial.println(cmd);
    Serial.println(simplesql(cmd));
  }

  if (db1 != NULL)
  {
    sqlite3_close(db1);
    db1 = NULL;
  }

  return "T";
}

void setup()
{
  // setup the debug out comms
  Serial.begin(115200);
  if (!SPIFFS.begin(true))
  {
    Serial.println("An Error has occurred while mounting SPIFFS");
    fatalerror(1);
  }
  File root = SPIFFS.open("/");
  Serial.println();
  Serial.println();
  Serial.println();
  Serial.println();
  Serial.print("Booting system, ");
  Serial.println(versiondate);

  if (SPIFFS.exists("/data.db"))
  {
    SPIFFS.remove("/data.db");
    Serial.println("erase dbase");
    
  }
  initdb();

}

void loop()
{

}

The output

Booting system, 2024-07-04
erase dbase
Initializing database
Opened database successfully
DROP TABLE IF EXISTS idpoints
No records found.
DROP TABLE IF EXISTS pointtype
No records found.
DROP TABLE IF EXISTS pointsclocked
No records found.
DROP TABLE IF EXISTS eventlog
No records found.
DROP TABLE IF EXISTS wifisettings
No records found.
DROP TABLE IF EXISTS audittrail
No records found.
DROP TABLE IF EXISTS settings
No records found.
DROP TABLE IF EXISTS eventtype
No records found.
CREATE TABLE wifisettings (apname TEXT, password TEXT, ip TEXT)
No records found.
CREATE TABLE idpoints (id INTEGER, value TEXT, name TEXT, lastclocked INTEGER, type INTEGER)
No records found.
CREATE TABLE pointtype (id INTEGER, name TEXT)
No records found.
CREATE TABLE pointsclocked (datetime INTEGER, idpoint INTEGER)
No records found.
CREATE TABLE eventlog (id INTEGER, datetime INTEGER, event INTEGER, data TEXT, sent INTEGER)
No records found.
CREATE TABLE audittrail (id INTEGER PRIMARY KEY, datetime INTEGER, event TEXT)
No records found.
CREATE TABLE settings (name TEXT, value TEXT)
No records found.
CREATE TABLE eventtype (id INTEGER, shortform TEXT, name TEXT)
No records found.
INSERT INTO pointtype VALUES (1,'Fixed')
No records found.
INSERT INTO pointtype VALUES (2,'User')
No records found.
INSERT INTO pointtype VALUES (3,'Asset')
No records found.
INSERT INTO settings VALUES ('APpass','')
No records found.
INSERT INTO settings VALUES ('ntp','pool.ntp.org')
No records found.
INSERT INTO settings VALUES ('gmtoffset','7200')
No records found.
INSERT INTO eventtype VALUES (1,'PU','Power up')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (2,'PD','Power down')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (3,'CN','Charge On')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (4,'CF','Charge Off')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (5,'X','Inactivity')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (6,'ST','Unit Settings')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (7,'C','Tamper')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (8,'c','Tamper restore')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (9,'D','Touch port short')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (10,'G','Baton date and time set')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (11,'N','Battery OK')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (12,'n','Battery low')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (13,'S','ID Point')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (14,'CW','Tamper Moisture')
Error executing query: no such table: eventtype
INSERT INTO eventtype VALUES (15,'CT','Tamper over temperature')
Error executing query: no such table: eventtype

[siara-cc]

@zaplakkies I modified your sketch and found that it is throwing I/O error when creating the tables for which insert fails. SPIFFS is known to cause I/O errors and people recommend LITTLEFS instead: #50 (comment)

#include <Arduino.h>
#include <SPIFFS.h>
#include <sqlite3.h> //26% mem

#define BUILTIN_LED 2

#define version 0.1b
#define versiondate "2024-07-04"

sqlite3 *db1;
int rc;
sqlite3_stmt *res;
int rec_count = 0;
const char *tail;
char current_db[255];
String db_name = "/spiffs/data.db";
String tempS;

void fatalerror(int errnum)
{
  while (true)
  {
    for (int i = 0; i < errnum; i++)
    {
      digitalWrite(BUILTIN_LED, HIGH);
      delay(200);
      digitalWrite(BUILTIN_LED, LOW);
      delay(200);
    }
    delay(500);
  }
}

int openDb(const char *filename)
{
  if (db1 != NULL)
  {
    sqlite3_close(db1);
  }
  int rc = sqlite3_open(filename, &db1);
  if (rc)
  {
    Serial.printf("Can't open database: %s\n", sqlite3_errmsg(db1));
    memset(current_db, '\0', sizeof(current_db));
    return rc;
  }
  else
  {
    Serial.printf("Opened database successfully\n");
    strcpy(current_db, filename);
  }
  return rc;
}

const char* data = "Callback function called";
static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   Serial.printf("%s: ", (const char*)data);
   for (i = 0; i<argc; i++){
       Serial.printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   Serial.printf("\n");
   return 0;
}

char *zErrMsg = 0;
int db_exec(sqlite3 *db, const char *sql) {
   Serial.println(sql);
   long start = micros();
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
       Serial.printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } else {
       Serial.printf("Operation done successfully\n");
   }
   Serial.print(F("Time taken:"));
   Serial.println(micros()-start);
   return rc;
}

String simplesql(const char *sql)
{
  if (db_exec(db1, sql) != SQLITE_OK)
    return "Error";
  return "Success";
}

String initdb()
{
  Serial.println("Initializing database");

  if (openDb("/spiffs/data.db") != SQLITE_OK)
  {
    return "Error opening database";
  }

  const char *initCommands[] = {
    "DROP TABLE IF EXISTS idpoints",
    "DROP TABLE IF EXISTS pointtype",
    "DROP TABLE IF EXISTS pointsclocked",
    "DROP TABLE IF EXISTS eventlog",
    "DROP TABLE IF EXISTS wifisettings",
    "DROP TABLE IF EXISTS audittrail",
    "DROP TABLE IF EXISTS settings",
    "DROP TABLE IF EXISTS eventtype",
    "CREATE TABLE wifisettings (apname TEXT, password TEXT, ip TEXT)",
    "CREATE TABLE idpoints (id INTEGER, value TEXT, name TEXT, lastclocked INTEGER, type INTEGER)",
    "CREATE TABLE pointtype (id INTEGER, name TEXT)",
    "CREATE TABLE pointsclocked (datetime INTEGER, idpoint INTEGER)",
    "CREATE TABLE eventlog (id INTEGER, datetime INTEGER, event INTEGER, data TEXT, sent INTEGER)",
    "CREATE TABLE audittrail (id INTEGER PRIMARY KEY, datetime INTEGER, event TEXT)",
    "CREATE TABLE settings (name TEXT, value TEXT)",
    "CREATE TABLE eventtype (id INTEGER, shortform TEXT, name TEXT)",
    "INSERT INTO pointtype VALUES (1,'Fixed')",
    "INSERT INTO pointtype VALUES (2,'User')",
    "INSERT INTO pointtype VALUES (3,'Asset')",
    "INSERT INTO settings VALUES ('APpass','')",
    "INSERT INTO settings VALUES ('ntp','pool.ntp.org')",
    "INSERT INTO settings VALUES ('gmtoffset','7200')", // TODO: test negative values
    "INSERT INTO eventtype VALUES (1,'PU','Power up')",
    "INSERT INTO eventtype VALUES (2,'PD','Power down')",
    "INSERT INTO eventtype VALUES (3,'CN','Charge On')",
    "INSERT INTO eventtype VALUES (4,'CF','Charge Off')",
    "INSERT INTO eventtype VALUES (5,'X','Inactivity')",
    "INSERT INTO eventtype VALUES (6,'ST','Unit Settings')",
    "INSERT INTO eventtype VALUES (7,'C','Tamper')",
    "INSERT INTO eventtype VALUES (8,'c','Tamper restore')",
    "INSERT INTO eventtype VALUES (9,'D','Touch port short')",
    "INSERT INTO eventtype VALUES (10,'G','Baton date and time set')",
    "INSERT INTO eventtype VALUES (11,'N','Battery OK')",
    "INSERT INTO eventtype VALUES (12,'n','Battery low')",
    "INSERT INTO eventtype VALUES (13,'S','ID Point')",
    "INSERT INTO eventtype VALUES (14,'CW','Tamper Moisture')",
    "INSERT INTO eventtype VALUES (15,'CT','Tamper over temperature')"
  };

  for (auto &cmd : initCommands)
  {
    Serial.println(cmd);
    Serial.println(simplesql(cmd));
  }

  if (db1 != NULL)
  {
    sqlite3_close(db1);
    db1 = NULL;
  }

  return "T";
}

void setup()
{
  // setup the debug out comms
  Serial.begin(115200);
  if (!SPIFFS.begin(true))
  {
    Serial.println("An Error has occurred while mounting SPIFFS");
    fatalerror(1);
  }
  File root = SPIFFS.open("/");
  Serial.println();
  Serial.println();
  Serial.println();
  Serial.println();
  Serial.print("Booting system, ");
  Serial.println(versiondate);

  if (SPIFFS.exists("/data.db"))
  {
    SPIFFS.remove("/data.db");
    Serial.println("erase dbase");
    
  }
  initdb();

}

void loop()
{

}

CREATE TABLE idpoints (id INTEGER, value TEXT, name TEXT, lastclocked INTEGER, type INTEGER)
CREATE TABLE idpoints (id INTEGER, value TEXT, name TEXT, lastclocked INTEGER, type INTEGER)
Operation done successfully
Time taken:39088
Success
CREATE TABLE pointtype (id INTEGER, name TEXT)
CREATE TABLE pointtype (id INTEGER, name TEXT)
Operation done successfully
Time taken:40197
Success
CREATE TABLE pointsclocked (datetime INTEGER, idpoint INTEGER)
CREATE TABLE pointsclocked (datetime INTEGER, idpoint INTEGER)
SQL error: disk I/O error
Time taken:44485
Error
CREATE TABLE eventlog (id INTEGER, datetime INTEGER, event INTEGER, data TEXT, sent INTEGER)
CREATE TABLE eventlog (id INTEGER, datetime INTEGER, event INTEGER, data TEXT, sent INTEGER)
SQL error: disk I/O error
Time taken:50021
Error
CREATE TABLE audittrail (id INTEGER PRIMARY KEY, datetime INTEGER, event TEXT)
CREATE TABLE audittrail (id INTEGER PRIMARY KEY, datetime INTEGER, event TEXT)
SQL error: disk I/O error
Time taken:50520
Error
CREATE TABLE settings (name TEXT, value TEXT)
CREATE TABLE settings (name TEXT, value TEXT)
Operation done successfully
Time taken:45761
Success
CREATE TABLE eventtype (id INTEGER, shortform TEXT, name TEXT)
CREATE TABLE eventtype (id INTEGER, shortform TEXT, name TEXT)
SQL error: disk I/O error
Time taken:45006
Error
INSERT INTO pointtype VALUES (1,'Fixed')
INSERT INTO pointtype VALUES (1,'Fixed')
Operation done successfully
Time taken:47063
Success

[zaplakkies]

Thank you for the help, I'll give LITTLEFS a try and give some feedback. I wish there was another system other than the ESP. I'm finding so many quirks like this.

[zaplakkies]

I can confirm that this problem does not appear on LittleFS. It was a bit of a mission to get LittleFS working as intended, but so worth it it seems.
Thank you so much for the help