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.

score 30 · Accepted Answer · answered Sep 17 '09 at 08:01

30

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

answered Sep 17 '09 at 08:01

Nick Dandoulakis

42,588
16
104
136

I am looking for C# documentation for the same purpose. please share if you know. – Mubashar Jan 18 '11 at 05:45
@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. – Nick Dandoulakis Jan 18 '11 at 17:37
Redirecting to url is bad answer for stackoverflow. – iperov Oct 18 '21 at 14:31

TonySalimi · Answer 2 · 2019-12-12T16:22:14.163

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);

score 0 · Answer 3 · answered Sep 17 '09 at 08:03

0

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.

answered Sep 17 '09 at 08:03

nhaa123

9,570
11
42
63

Saving to disk an in-memory database

3 Answers3

Linked