Skip to content

disk I/O error on transaction with two tables #21

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
deividi21 opened this issue Feb 5, 2020 · 3 comments
Closed

disk I/O error on transaction with two tables #21

deividi21 opened this issue Feb 5, 2020 · 3 comments

Comments

@deividi21
Copy link

deividi21 commented Feb 5, 2020

I'm having troubles trying to do a transaction involving two tables.

Here is the code, just like the spiffs example:

/*
    This creates two empty databases, populates values, and retrieves them back
    from the SPIFFS file 
*/
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <SPI.h>
#include <FS.h>
#include "SPIFFS.h"

/* You only need to format SPIFFS the first time you run a
   test or else use the SPIFFS plugin to create a partition
   https://github.com/me-no-dev/arduino-esp32fs-plugin */
#define FORMAT_SPIFFS_IF_FAILED true

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;
}

int db_open(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;
}

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;
}

void setup()
{

  Serial.begin(115200);
  sqlite3 *db1;
  int rc;

  if (!SPIFFS.begin(FORMAT_SPIFFS_IF_FAILED))
  {
    Serial.println("Failed to mount file system");
    return;
  }

  // list SPIFFS contents
  File root = SPIFFS.open("/");
  if (!root)
  {
    Serial.println("- failed to open directory");
    return;
  }
  if (!root.isDirectory())
  {
    Serial.println(" - not a directory");
    return;
  }
  File file = root.openNextFile();
  while (file)
  {
    if (file.isDirectory())
    {
      Serial.print("  DIR : ");
      Serial.println(file.name());
    }
    else
    {
      Serial.print("  FILE: ");
      Serial.print(file.name());
      Serial.print("\tSIZE: ");
      Serial.println(file.size());
    }
    file = root.openNextFile();
  }

  // remove existing file
  SPIFFS.remove("/test1.db");

  sqlite3_initialize();

  if (db_open("/spiffs/test1.db", &db1))
    return;

  rc = db_exec(db1, "CREATE TABLE table_one (id_table_one  INTEGER, first_data INTEGER, second_data TEXT);");
  if (rc != SQLITE_OK)
  {
    sqlite3_close(db1);
    return;
  }

  rc = db_exec(db1, "CREATE TABLE table_two (id_table_two  INTEGER, first_data INTEGER, second_data TEXT);");
  if (rc != SQLITE_OK)
  {
    sqlite3_close(db1);
    return;
  }

  rc = db_exec(db1, "BEGIN TRANSACTION;"
                    "INSERT INTO table_one(first_data,second_data) VALUES ('111','abc');"
                    "INSERT INTO table_two(first_data,second_data) VALUES ('222','def');"
                    "COMMIT;");
  if (rc != SQLITE_OK)
  {
    sqlite3_close(db1);
    return;
  }

  sqlite3_close(db1);
}

void loop()
{
}

@deividi21 deividi21 changed the title disk I/O error on transaction between two tables disk I/O error on transaction with two tables Feb 5, 2020
@siara-cc
Copy link
Owner

siara-cc commented Feb 16, 2020

Please clone the latest version of this repo and try this in your code:

  if (!SPIFFS.begin(FORMAT_SPIFFS_IF_FAILED, "/spiffs", 10))

@deividi21
Copy link
Author

Problem soveld. Thank you a lot!

It seems to be working just fine. I have a few more questions about the changes that you have made:
1 - Shouldn't the path of SPIFFS always be /spiffs?
2 - What does the maxOpenFiles means?

@siara-cc
Copy link
Owner

siara-cc commented Feb 19, 2020

1 - Shouldn't the path of SPIFFS always be /spiffs?

This is the mount point - the second parameter allows us to change it.

2 - What does the maxOpenFiles means?

Number of files that can be open simultaneously. SQLite library creates additional temporary files when changes are made to database.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants