PRAGMA ?

[AVDeveloppement]

Hello, i don't find why PRAGMA don't work. I try:

const char* data = "Output:";
static int callback(void *data, int argc, char **argv, char **azColName) {
  for (int i = 0; i<argc; i++) {
    if (i)
      Serial.print((char) '\t');
    Serial.printf("%s", argv[i] ? argv[i] : "NULL");
  }
  Serial.printf("\n");
  return 0;
}

void test()
{
  // Open database

  std::string dbSQL = "PRAGMA pragma_list;";
  
  int rc = sqlite3_exec(dbFile, dbSQL.c_str(), callback, (void*)data, &dbErrMsg);
  if (rc != SQLITE_OK) {
    Serial.print(F("SQL error: "));
    Serial.print(sqlite3_extended_errcode(dbFile));
    Serial.print(" ");
    Serial.println(dbErrMsg);
    sqlite3_free(dbErrMsg);
  }

i get empty result, same code with "select * from settings" get my table.
I want use "PRAGMA integrity_check" for restore backup at start if error found.
Thanks for the help.

[siara-cc]

You could customize the config file in this library to enable PRAGMA. But I doubt if it will work every time given the limited memory.

[AVDeveloppement]

Thanks for answer, i tried, my config:

`#define BUILD_sqlite -DNDEBUG
#define SQLITE_OMIT_LOAD_EXTENSION 1
#define SQLITE_DQS 0
#define SQLITE_OS_OTHER 1
#define SQLITE_NO_SYNC 1
#define SQLITE_TEMP_STORE 1
#define SQLITE_DISABLE_LFS 1
#define SQLITE_DISABLE_DIRSYNC 1
#define SQLITE_SECURE_DELETE 0
#define SQLITE_DEFAULT_LOOKASIDE 512,64
#define YYSTACKDEPTH 20
#define SQLITE_SMALL_STACK 1
#define SQLITE_DEFAULT_PAGE_SIZE 512
#define SQLITE_SORTER_PMASZ 4
#define SQLITE_DEFAULT_CACHE_SIZE -1
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_CORE 1
#define SQLITE_SYSTEM_MALLOC 1
#define SQLITE_THREADSAFE 0
#define SQLITE_MUTEX_APPDEF 1
#define SQLITE_OMIT_WAL 1
#define SQLITE_DISABLE_FTS3_UNICODE 1
#define SQLITE_DISABLE_FTS4_DEFERRED 1
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS 1
#define SQLITE_DEFAULT_FOREIGN_KEYS 0
#define SQLITE_DEFAULT_LOCKING_MODE 1
#define SQLITE_DEFAULT_PAGE_SIZE 512
#define SQLITE_DEFAULT_PCACHE_INITSZ 8
#define SQLITE_MAX_DEFAULT_PAGE_SIZE 16000
#define SQLITE_POWERSAFE_OVERWRITE 1
#define SQLITE_MAX_EXPR_DEPTH 0

/*
#undef SQLITE_OMIT_ALTERTABLE
#undef SQLITE_OMIT_ANALYZE
#undef SQLITE_OMIT_ATTACH
#define SQLITE_OMIT_AUTHORIZATION 1
#undef SQLITE_OMIT_AUTOINCREMENT
#define SQLITE_OMIT_AUTOINIT 1
#define SQLITE_OMIT_AUTOMATIC_INDEX 1
#define SQLITE_OMIT_AUTORESET 1
#define SQLITE_OMIT_AUTOVACUUM 1
#undef SQLITE_OMIT_BETWEEN_OPTIMIZATION
#define SQLITE_OMIT_BLOB_LITERAL 1
#define SQLITE_OMIT_BTREECOUNT 1
#define SQLITE_OMIT_BUILTIN_TEST 1
#define SQLITE_OMIT_CAST 1
#define SQLITE_OMIT_CHECK 1
#undef SQLITE_OMIT_COMPILEOPTION_DIAGS 1
#define SQLITE_OMIT_COMPOUND_SELECT 1
#define SQLITE_OMIT_CONFLICT_CLAUSE 1
#undef SQLITE_OMIT_CTE
#define SQLITE_OMIT_DECLTYPE 1
#define SQLITE_OMIT_DEPRECATED 1
#undef SQLITE_OMIT_DISKIO
#define SQLITE_OMIT_EXPLAIN 1
#define SQLITE_OMIT_FLAG_PRAGMAS 0
#define SQLITE_OMIT_FOREIGN_KEY 1
#define SQLITE_OMIT_GET_TABLE 1
#define SQLITE_OMIT_INCRBLOB 1
#define SQLITE_OMIT_INTEGRITY_CHECK 0
#undef SQLITE_OMIT_LIKE_OPTIMIZATION
#undef SQLITE_OMIT_LOCALTIME
#define SQLITE_OMIT_LOOKASIDE 1
#undef SQLITE_OMIT_MEMORYDB
#undef SQLITE_OMIT_OR_OPTIMIZATION
#undef SQLITE_OMIT_PAGER_PRAGMAS
#define SQLITE_OMIT_PARSER_TRACE 1
#define SQLITE_OMIT_PRAGMA 0
#define SQLITE_OMIT_PROGRESS_CALLBACK 1
#define SQLITE_OMIT_QUICKBALANCE 1
#undef SQLITE_OMIT_REINDEX
#define SQLITE_OMIT_SCHEMA_PRAGMAS 1
#define SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS 1
#define SQLITE_OMIT_SHARED_CACHE 1
#define SQLITE_OMIT_TCL_VARIABLE 1
#define SQLITE_OMIT_TEMPDB 1
#define SQLITE_OMIT_TRACE 1
#undef SQLITE_OMIT_TRIGGER
#define SQLITE_OMIT_TRUNCATE_OPTIMIZATION 1
#define SQLITE_OMIT_UTF16 1
#undef SQLITE_OMIT_VACUUM
#undef SQLITE_OMIT_VIEW
#undef SQLITE_OMIT_VIRTUALTABLE
#undef SQLITE_OMIT_WSD
#define SQLITE_OMIT_XFER_OPT 1
#define SQLITE_PERFORMANCE_TRACE 1
//#define SQLITE_OMIT_COMPLETE 1
//#define SQLITE_OMIT_SUBQUERY 1
//#define SQLITE_OMIT_DATETIME_FUNCS 1
//#define SQLITE_OMIT_FLOATING_POINT 1
#define SQLITE_COUNTOFVIEW_OPTIMIZATION 0
*/
`
but that don't work, have you a tips for check db table are ok at startup ? Because sometime when power off esp32 when it use db it become corrupted on some table, i cant change hardware or add ups for prevent power off, i have added db backup so i have more than 100 db ok but how know when need restore :/

[siara-cc]

For whichever option you would like to remove, please try #undef instead of making it 0.
However, I am not sure if enabling PRAGMA would serve your purpose. Why not just copy the backup back to original file?
You could also try my other library http://github.com/siara-cc/sqlite_micro_logger_arduino
if that suits you. It consumes much lesser memory and much faster if you just want to log sensor data.

[AVDeveloppement]

I have table with users, settings, data, logs, ...
the problem is sometime table are corrupted when i power off when it write db. I can't prevent that, ups cant be added.
For that i make lot of backup auto but how know when we need restore a backup ? Hum test write and read every table at start?
Sad PRAGMA can't be used :( but ok it's already good to have sqlite on esp thanks for that!

[barbiani]

Any update here on the integrity test? Is there an example?

Thank you.