vfs, unable to open database file

[michelpa]

I have an issue but i'm not an expert in C programming neither esp32.

I have a TTGO T5 device with micro sdcard on board. I have a library for managing sd file access over sdfat.

I'm just trying to open a db file and i think the problem is vfs related. The db file is located at the root of sd card.

I tried

openDb("mdr512.db", &db1);
openDb("/sd/mdr512.db", &db1);
openDb("/sdcard/mdr512.db", &db1);
openDb("/SD0/mdr512.db", &db1);

And i always have an error "unable to open database file". I don't know anything about VFS but the file is present:

    if (SDHAL_SD.exists("/mdr512.db"))
    {
        SerialPort.println("File exist");
    }

Can you give me any lead? list VFS partition prefix? I'm stuck for now...

Thank you.

[siara-cc]

If you are just trying to log sensor data you could try https://github.com/siara-cc/sqlite_micro_logger_arduino. It can support any IO Library.

[michelpa]

Thank you for your answer. Unfortunately i need to query the db... Is there a way to dump mount points?

[tobozo]

@siara-cc TTGO-T5 is using VSPI for the SD Card, could that be a hint ?

[siara-cc]

@michelpa @tobozo I will check what I can do..

[peragwin]

@michelpa I'm seeing the same issue, except for me it's only manifesting within a test case when using unity. I wonder if the test runner executes them as tasks which are somehow not picking up the VFS entries.

// relevant code from my test_sqlite.cpp

#include <unity.h>

void test_sqlite(void)
{
    sqlite3 *db;
    sqlite3_open("/sd/data.db", &db);
    /* etc */
    sqlite3_close(db);
}

void run_tests(void)
{
    UNITY_BEGIN();
    RUN_TEST(test_sqlite);
    UNITY_END();
}

void setup()
{
    test_sqlite(); // able to open file and run the test
    run_tests(); // always fails with "unable to open database file" error
}

[hassin23ayz]

@siara-cc @siara-in thanks for this library, after a lot of tweaking , I found that the right sequence of using this library is

`void test_spiffs() { sqlite3 *db1; int rc;

ESP_LOGI(TAG, "Initializing SPIFFS");

esp_vfs_spiffs_conf_t conf = {
  .base_path = "/spiffs",
  //.partition_label = "storage",
  .partition_label = NULL,
  .max_files = 5,
  .format_if_mount_failed = true
};

// Use settings defined above to initialize and mount SPIFFS filesystem.
// Note: esp_vfs_spiffs_register is an all-in-one convenience function.
esp_err_t ret = esp_vfs_spiffs_register(&conf);

if (ret != ESP_OK) {
    if (ret == ESP_FAIL) {
        ESP_LOGE(TAG, "Failed to mount or format filesystem");
    } else if (ret == ESP_ERR_NOT_FOUND) {
        ESP_LOGE(TAG, "Failed to find SPIFFS partition");
    } else {
        ESP_LOGE(TAG, "Failed to initialize SPIFFS (%s)", esp_err_to_name(ret));
    }
    return;
}
sqlite3_initialize();

if (db_open("/spiffs/test1.db", &db1)) return;
//rc = db_exec(db1, "CREATE TABLE test1 (id INTEGER, content);");
//rc = db_exec(db1, "CREATE TABLE [IF NOT EXISTS] test1 (id INTEGER NOT NULL PRIMARY KEY, product TEXT,price REAL) [WITHOUT ROWID];");
rc = db_exec(db1, "CREATE TABLE IF NOT EXISTS test1 (\
                   id INTEGER,\
                   product TEXT,\
                   price REAL);");
if (rc != SQLITE_OK) {
    sqlite3_close(db1);
    return;
}

rc = db_exec(db1, "INSERT INTO test1 (id, product, price)\
                   VALUES (1, 'Laptop', 345.879);");
if (rc != SQLITE_OK) {
    sqlite3_close(db1);
    return;
}

rc = db_exec(db1, "DELETE FROM test1 WHERE id=1;");
if (rc != SQLITE_OK) {
    sqlite3_close(db1);
    return;
}
rc = db_exec(db1, "INSERT INTO test1 (id, product, price)\
                   VALUES (1, 'Laptop', 100.0);");
if (rc != SQLITE_OK) {
    sqlite3_close(db1);
    return;
}

rc = db_exec(db1, "SELECT price FROM test1 WHERE id=1");
if (rc != SQLITE_OK) {
    sqlite3_close(db1);
    return;
}

sqlite3_close(db1);

printPartitionSize();
// All done, unmount partition and disable SPIFFS
esp_vfs_spiffs_unregister(NULL);
ESP_LOGI(TAG, "SPIFFS unmounted");

//while(1);

}`

[siara-cc]

@hassin23ayz Thanks for letting us know. You are saying using esp_vfs_spiffs_register as above works for TTGO devices? The OP is trying this lib on TTGO devices.

[hassin23ayz]

@siara-cc No I have not tested the commented code on any TTGO / SD-CARD connection type device . The commented code by me was tested on ESP32 devkit C [ESP32-WROOM-32] i.e. internal SPI Flash