No error ... but no table

[pploume]

Hi, I'm facing a strange problem : -the database is opened successfully -the sql query (CREATE TABLE clefs (rowid INTEGER, clef TEXT, status INTEGER, user INTEGER, calendar INTEGER ); is executed with no error -but : the next sql query (INSERT INTO clefs VALUES (1, '0011460855', 1, 1, 1 ); ) returns the following error : erreur:no such table: clefs

Should be a stupid mistake from myself ... I can't see it (almost same code run perfectly ...)

I suspected a spiffs room problem, but only 3 files : FS File: /edit.htm.gz, size: 4.02KB FS File: /index.html, size: 15.41KB FS File: /db1.db, size: 0B <-------- size is 0 !

Context: ESP32 Dev Board Deviot with last update

Thanks in advance

[tobozo]

c'est un probleme d'accord :D

clef vs clefs

[edit] more seriously, how do you proceed with the table creation + query. do you close() and re-open() between both operations ?

[pploume]

@tabozo

Still a lot of shi... in my eyes : CREATE TABLE clefs (rowid INTEGER, clef TEXT, status INTEGER, user INTEGER, calendar INTEGER ); & INSERT INTO clefs VALUES (1, '0011460855', 1, 1, 1 );

both table names have an 's' ending (clef without 's' is a column name )

sqlite3_finalize(res); if no error, just return is error

and thanks for your help :) [edit] tryied to close, delay,open database ... nothing better

[tobozo]

can you confirm you proceed as follows:

1) SPIFFS.remove( file )

2) open db 3) exec "CREATE TABLE...." query 4) close db

5) open db 6) exec "INSERT INTO..." query 7) close db

[williamesp2015]

I have problem with CREATE TABLE using ESP32 on SD sqlite3_close(db1); ////////////////// Start Create new db rc = db_exec(db1, "CREATE TABLE test (x INTEGER, y INTEGER);"); if (rc != SQLITE_OK) { sqlite3_close(db1); Serial.println("ERROR"); return; } else { sqlite3_close(db1); // Open database 1 if (openDb("/sd/test.db", &db1)) return; rc = db_exec(db1, "INSERT INTO test VALUES (1, 3);"); if (rc != SQLITE_OK) { sqlite3_close(db1); return; } rc = db_exec(db1, "Select * from test where x = 1"); if (rc != SQLITE_OK) { sqlite3_close(db1); return; } } OUTPUT: CREATE TABLE test (x INTEGER, y INTEGER); Guru Meditation Error: Core 1 panic'ed (LoadProhibited). Exception was unhandled. Core 1 register dump:

Please provide example for CREATAE TABLE, INSERT different types (e.g. INTEGER, FLOAT, TEXT)

[tobozo]

maybe replace :

sqlite3_close(db1);
////////////////// Start Create new db
rc = db_exec(db1, "CREATE TABLE test (x INTEGER, y INTEGER);");

by :

sqlite3_open("/sd/test.db", &db1);
////////////////// Start Create new db
rc = db_exec(db1, "CREATE TABLE test (x INTEGER, y INTEGER);");

[edit] fixed syntax

[williamesp2015]

Many thanks. I can confirm successful create, insert on SD Card sqlite3_open("/sd/test.db", &db1); rc = db_exec(db1, "CREATE TABLE test (x INTEGER, y INTEGER);"); if (rc != SQLITE_OK) { sqlite3_close(db1); // remove existing file deleteFile(SD, "/test.db"); Serial.println("ERROR"); return; } else { sqlite3_close(db1); if (openDb("/sd/test.db", &db1)) // Open database 1 return; rc = db_exec(db1, "INSERT INTO test VALUES (1, 3);"); if (rc != SQLITE_OK) { sqlite3_close(db1); return; } rc = db_exec(db1, "Select * from test where x = 1"); if (rc != SQLITE_OK) { sqlite3_close(db1); return; } } sqlite3_close(db1);

[siara-cc]

Hi, I'm facing a strange problem : -the database is opened successfully -the sql query (CREATE TABLE clefs (rowid INTEGER, clef TEXT, status INTEGER, user INTEGER, calendar INTEGER ); is executed with no error -but : the next sql query (INSERT INTO clefs VALUES (1, '0011460855', 1, 1, 1 ); ) returns the following error : erreur:no such table: clefs

Should be a stupid mistake from myself ... I can't see it (almost same code run perfectly ...)

I suspected a spiffs room problem, but only 3 files : FS File: /edit.htm.gz, size: 4.02KB FS File: /index.html, size: 15.41KB FS File: /db1.db, size: 0B <-------- size is 0 !

Context: ESP32 Dev Board Deviot with last update

Thanks in advance

Hi, I tried your SQL on my board and it works fine, I am able to retrieve the record inserted. Please try the example sqlite3_spiffs to first check everything in place.

[siara-cc]

@pploume I am not sure if it is related to type of board and spiffs. There are some such options under the tools menu. See if changing them makes any difference.

[siara-cc]

@pploume by the way there are some extra underscore in your SQL. also i don't think rowid needs to be specifically mentioned in create table. rowid is available even if not specified.

[tobozo]

If you get a SQL error: "table test already exist"s, you can ignore it and go on with your insert.

Here's a link to the example @siara-cc is mentioning : https://github.com/siara-cc/esp32_arduino_sqlite3_lib/blob/master/examples/sqlite3_spiffs/sqlite3_spiffs.ino#L103

And the code to remove a file is the same regardless the filesystem:

SD.remove( "/file.db" )
SD_MMC.remove( "/file.db" )
SPIFFS.remove( "/file.db" )

[siara-cc]

@williamesp2015 I tried your SQL on my dev board with sqlite3_console on SPIFFS and it works fine. I think you are using SPI communication mechanism for Micro SD Card (/sd/). Please try the example sqlite3_sdspi to check if wiring and other things are alright.

[siara-cc]

@williamesp2015 if still facing problem on your side, i will do the wiring on my side and confirm with your code tomorrow. I already checked it is not related to INTEGER/FLOAT.

[pploume]

@tobozo still not working. can't be more simple ... may be the troubel comes from I'm not using db_exe but sqlite3_prepare_v2 i'll try tomorrow ...

code :

include

include

include "SPIFFS.h"

const char ssid = "dlink"; const char password = "";

sqlite3 *db1;
int rc;
sqlite3_stmt *res;
int rec_count = 0;

const char *tail; String reponse;

int onloadData(String table, String debut="", String fin="") { String current=""; String rowCount=""; bool retour=true; Serial.println("entree dans loaddata");

    sqlite3_open("/spiffs/db1.db", &db1);

        String sql = "CREATE TABLE clefs (rowid INTEGER, clef TEXT, status INTEGER, user INTEGER, calendar INTEGER );"; //+" where rowid between '";
    rc = sqlite3_prepare_v2(db1, sql.c_str(), 1000, &res, &tail); 
    sqlite3_finalize(res);
    sqlite3_close(db1);
    delay(500);
    sqlite3_open("/spiffs/db1.db", &db1);      
    sql="INSERT INTO clefs VALUES (1, '0011460855', 1, 1, 1 );  ";
    rc = sqlite3_prepare_v2(db1, sql.c_str(), 1000, &res, &tail);   
    if (rc != SQLITE_OK) 
    {
      String resp = "Failed insert data into clefs: ";
      resp += sqlite3_errmsg(db1);
      Serial.println(resp.c_str());
      sqlite3_finalize(res);
      sqlite3_close(db1);
      return false;
    }
    sqlite3_finalize(res);
    sqlite3_close(db1);
    delay(500);
    sqlite3_open("/spiffs/db1.db",&db1);
    sql="SELECT * FROM clefs;  ";
    Serial.println(sql);
    rc = sqlite3_prepare_v2(db1, sql.c_str(), 1000, &res, &tail); 
    if (rc != SQLITE_OK) 
    {
      String resp = "Failed select * from clefs: ";
      resp += sqlite3_errmsg(db1);
      Serial.println(resp.c_str());
      sqlite3_finalize(res);
      sqlite3_close(db1);
      return false;
    }
    while (sqlite3_step(res) == SQLITE_ROW)
    {
        rowCount = sqlite3_column_int(res, 0);              // résultat de count(*) ci-dessus
    }
    sqlite3_finalize(res);                          // pour effacer le statment précédent/en cours ...
    Serial.println("SQLite the end...");
    sqlite3_finalize(res);
    sqlite3_close(db1);

}

void setup ( void ) { Serial.begin(115200);

SPIFFS.begin();
SPIFFS.remove("/spiffs/db1.db");

sqlite3_initialize();
onloadData("clefs","","");

}

void loop ( void ) {

}

[siara-cc]

@pploume Hi, you have to use sqlite3_step for CREATE and INSERT also. Calling prepare does not run it.

[siara-cc]

@pploume if still not working, send code again. i will check on my side.