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.