SQL error: file is not a database.

[aguilard07]

Hello, I'm trying to use SPIFFS with a very little 3 row Database with the ESP32 Dev Module. My problem is that the sketch is succesfully opening the data base but no making the query.

Maybe I have an error when I make de .sql file. This is the schema:

PRAGMA foreign_keys = on; 
PRAGMA journal_mode = WAL;
PRAGMA page_size=512; VACUUM;
drop table if exists users;
create table if not exists users(
    id_user                integer      primary key,
    fullname               text  not null,
    username               text  not null,
    password               text  not null,
    status                 text  not null default 't',
    admin                  text  not null default 't'
);
create unique index if not exists users_id_user  on users (id_user);
create unique index if not exists users_username on users (username);
insert into users (fullname,username,password,status,admin)values('Máster Vitronic','vitronic','123456','t','t');
insert into users (fullname,username,password,status,admin)values('Marcel Guzman'  ,'konev'   ,'123456','t','t');
insert into users (fullname,username,password,status,admin)values('Daniel Aguilar' ,'daniel'  ,'123456','t','t');

And this is the Arduino Skecth

#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;
   sqlite3 *db2;
   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();
   }

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

   rc = db_exec(db1, "SELECT * FROM users");
   if (rc != SQLITE_OK) {
       sqlite3_close(db1);
       return;
   }
    sqlite3_close(db1);    
}

void loop() {
}

And finally, the OUTPUT msg:

 FILE: /db.db   SIZE: 16384
Opened database successfully
SELECT * FROM users
SQL error: file is not a database
Time taken:4142

[siara-cc]

PRAGMA journal_mode = WAL is not supported in this library. Please create new database without this command and try again.

[aguilard07]

Hello PRAGMA foreign_keys = on is supported by this lib? If it is so, how can I implement it? Because I'm having issues with it.

[siara-cc]

Foreign keys are supported. What problem are you facing?

[aguilard07]

I don't know how yo implement it. After I execute the pragma foreign_keys = on sentence all the queries end un error and makes my ESP32 reboot.

El vie., 23 de ago. de 2019 13:47, Arun notifications@github.com escribió:

Foreign keys are supported. What problem are you facing?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/14?email_source=notifications&email_token=AMLWZJDROE3ZBIFOP7XU7ZDQGAPBZA5CNFSM4ILBTHUKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD5A4GVA#issuecomment-524403540, or mute the thread https://github.com/notifications/unsubscribe-auth/AMLWZJEWXHVIQHN73A3MV4LQGAPBZANCNFSM4ILBTHUA .

[siara-cc]

I am not sure where you are running the SQL statements. I thought you are creating the database using Desktop computer and running above code to display data.

In any case the above code should run and your use case is ok. The database may have got corrupted due to power problems. Please copy again and try.

[aguilard07]

Well, I'm doing these set of steps:

• Initialize the sqlite3 with sqlite3_initialize()
• Open the DB.
• Execute this commandpragma foreign_keys = on
• Try to make a bad insertion to a table. The issue is that no matter if the foreign keys are enabled or disabled, the insertion is made despite the foreign constraints.

I test it in my PC, and the constraints work.

[siara-cc]

Constraint checking is not enabled. Several features are disabled to avoid memory problems. This was adopted from the NodeMCU version of Sqlite.

I will check if constraint checking and some other features can be enabled without affecting memory in the next release. Meanwhile, please avoid bad inserts using your code.

[siara-cc]

By the way, for checking foreign key constraints, at least two tables are required. In the above schema, there is only one table?

[aguilard07]

I changed the schema a week ago, now I have around 6 tables. BTW, triggers and raise messages are supported? With raise messages I mean, how can I get them if I implement a trigger?

[siara-cc]

See new library https://github.com/siara-cc/sqlite_micro_logger_arduino. It overcomes all memory problems and corruption issues. However there are limitations.