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