Saving to disk an in-memory database

Question

I made a database through sqlite in c++.

The db has been created in memory (using the ":memory:" parameter insted of a filename), in order to have a very quick behavior.

The database is created by the following lines:

sqlite3* mem_database;
if((SQLITE_OK == sqlite3_open(":memory:", &mem_database)){
    // The db has been correctly created and
    // I can do some stuff with it.
}
sqlite3_close(mem_database);

My problem is: how can I write the in-memory database to disk? (through c/c++ of course).

I read something about the ATTACH and DETACH sqlite commands, but I can get them working only with the sqlite interactive shell (not from c/c++ code).

Greets.

Celtic

Answer 1

Check out this example: Loading and Saving In-Memory Databases

Ecchymosis

I am looking for C# documentation for the same purpose. please share if you know. – Sedulity

@Mubashar: I'm not familiar with C#. I believe it's easy to convert the example in C#. The sqlite3_backup_X functions do the job. – Ecchymosis

Redirecting to url is bad answer for stackoverflow. – Grosgrain

Answer 2

If you do not have enough time to read the whole documentation posted by @NickDandoulakis's answer, just copy and paste the below function (already mentioned in the link) in your code:

int loadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, int isSave) 
{
   int rc;                   /* Function return code */
   sqlite3 *pFile;           /* Database connection opened on zFilename */
   sqlite3_backup *pBackup;  /* Backup object used to copy data */
   sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
   sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

   rc = sqlite3_open(zFilename, &pFile);
   if (rc == SQLITE_OK) 
   {

      pFrom = (isSave ? pInMemory : pFile);
      pTo = (isSave ? pFile : pInMemory);

      pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
      if (pBackup) {
         (void)sqlite3_backup_step(pBackup, -1);
         (void)sqlite3_backup_finish(pBackup);
      }
      rc = sqlite3_errcode(pTo);
   }

   (void)sqlite3_close(pFile);
   return rc;
}

and then for saving the data of your SQLite db (in memory) into a file call:

loadOrSaveDb(db_con, target_file, 1);

Which db_con is your database connection object pointer and target_file is the target file address.

Remark: If you want to load a SQLite database file into memory, just run:

loadOrSaveDb(db_con, target_file, 0);

Fundament

Answer 3

Use transaction statement before doing anything to the table. This ensures fast handling and rollbacks as well. This way, you don't need to implement the database in memory directly.

Polk