Cannot create a new table in an existing database file on SD card

[jojo2357]

I am following the spi demo and I have so far only modified the setup function

#include <sqlite3.h>
#include <SPI.h>
#include <FS.h>
#include "SD.h"

//many includes excluded for conciseness

//provided functions excluded for conciseness

void test() {
    Serial.begin(115200);
    sqlite3 *db1;
    char *zErrMsg = 0;
    int rc;

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

    sqlite3_initialize();

    File myFile;
    myFile = SD.open("/testdb.db", FILE_WRITE);
    // close the file:
    myFile.close();
    // Test if the file exist:
    if (SD.exists("/testdb.db")) {
        Serial.println("/testdb.db exists.");
    } else {
        Serial.println("/testdb.db doesn't exist.");
    }

    // Open database 1
    if (openDb("/sd/testdb.db", &db1))
        return;

    rc = db_exec(db1, "create table surnames(name TEXT)");

    rc = db_exec(db1, "Select * from surnames where name = 'MICHELLE'");
    if (rc != SQLITE_OK) {
        sqlite3_close(db1);
        return;
    }

    sqlite3_close(db1);
}

The idea here is to create an empty db file (according to #53 which I somehow doubt) and then open it as a db, and then create a table, and run a query on it.

Expected output: i expect both queries to work and the select to return no entries

Actual output:

/testdb.db exists.
Opened database successfully
create table surnames(name TEXT)
SQL error: unable to open database file
Time taken:23569
Select * from surnames where name = 'MICHELLE'
SQL error: no such table: surnames
Time taken:3340

What doesnt make sense to me is how the two error messages are distinct from each other. I also opened the SD card on my computer and verified that it has created the file, and that it is still in fact empty.

I also have run a demo SD card writing program and verified that even obnoxiously large files can be written. If i am being a goofus I would like to know, but i suspect that there are supposed to be data files for me to put on the SD card similar to the esp8266 example. I also suspect that something fishy is going on in the create table query as it seems to time out, so there is a chance that error is actually lying.

[siara-cc]

Hi, can we try the same without these lines:

    File myFile;
    myFile = SD.open("/testdb.db", FILE_WRITE);
    // close the file:
    myFile.close();

Does the example work without changes?

[jojo2357]

I removed the code as you suggested, along with the file check, and added while (!SD.begin()) delay(100); to ensure that the SD was mounted in time, and I got the following:

Opened database successfully
create table surnames(name TEXT)
SQL error: unable to open database file
Time taken:23407
Select * from surnames where name = 'MICHELLE'
SQL error: no such table: surnames
Time taken:3470

After reverting back to the original example and running that out of the box, i get Can't open database: unable to open database file

if I wipe the testdb.db file from the sd card, and rerun my example, I get the same result as the first block (with nearly indistinguishable times)

After that, upon checking the SD card, it seems that a file TEST.DB with no disk size has been made. Thoughts?

[jojo2357]

Maybe it will help, but here is the partitioning of the SD card in use:

The partition in use is a FAT file system, and I doubt the fact that it only is allotted 16/32GB matters, since I have already run the demos provided by Arduino to verify that read and write both are working

[pauloslf]

sorry to bring this thread bacl to live but i'm having the same problem.. I can create db's and make queries on existing db files without problems on spiffs and sdcard, but cannot do inserts when using sdcard (spiffs is ok)

[siara-cc]

@pauloslf Not sure if your issue is the same as what was being discussed in this thread, but:

If you are able to read databases, is the Write protect switched on the SD Card?

If you are connecting SD Card with wires on the SPI Port, there could be some issues with EMI interference. Try reducing the speed. However, in this case both READ and WRITE should be affected.

[pauloslf]

There is no write protection on this card, and i can write other types of files to it without any issues. Also the db file is created, and i can make queries on existing dbs, just not inserts,updates or deletes..

In order to validate the EMI interference i tested with a of the shelf board (freenove esp32-s3 wroom) and i got the same result.

i'm using IDF 5.2.2, not arduino, but i assume it should be the same no ?