RAM consumption inside a C ++ program using Sqlite3 blob

Question

I use sqlite3 dbms inside a C ++ program, which I mainly use to store files as blob objects (I know this is not the best option).

Obviously, I write them down in stages, as they can sometimes be large (40-80 MB) to do this. I need to first create a blob placeholder using the sqlite3_bind_zeroblob(...) binding function, after which I will open the blob record and read step by step from and to it.

The problem I am facing is that when I create a placeblob placeholder (during sqlite3_step ), my application's RAM consumption reaches 80-160 MB in 2-3 seconds, once it has been created, RAM consumption goes back to Maximum 2-3 MB.

I do not understand why! If they created a way to write to a blob in stages, there is probably a way to create this stupid placeholder without losing 160 MB of RAM, but I did not find it. Do you have any suggestions?

 sqlite3_stmt* stm = NULL; sqlite3_blob *BLOB = NULL; rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stm, NULL); rc = sqlite3_bind_blob(stm, 1, wpath.c_str(), wpath.size()*sizeof(wchar_t), SQLITE_STATIC); rc = sqlite3_bind_text(stm, 2, hash.c_str(), hash.size(), SQLITE_STATIC); rc = sqlite3_bind_zeroblob(stm, 3, size); rc = sqlite3_bind_int(stm, 4, versione); rc = sqlite3_bind_blob(stm, 5, last.c_str(), last.size()*sizeof(wchar_t), SQLITE_STATIC); rc = sqlite3_step(stm); if (rc != SQLITE_DONE) { fprintf(stderr, " This file was already present in the database!\n", rc); return; } else { fprintf(stdout, "Record FILE created successfully\n"); } 

Answer 1

The problem is reported HERE .
And the official answer:

In order for zeroblobs to work as described above (using a fixed amount of memory, no matter how large they are), all zeroblobs must be at the end of the line. In other words, the columns of the table that get zeroblobs should be the last columns in the table. If any non-zero content follows zeroblob, then zeroblob is expanded to a literal sequence of zero bytes, i.e. memory should be allocated for the entire zeroblob.

So you need to reorder to fix it:

 sqlite3_stmt* stm = NULL; sqlite3_blob *BLOB = NULL; rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stm, NULL); rc = sqlite3_bind_blob(stm, 1, wpath.c_str(), wpath.size()*sizeof(wchar_t), SQLITE_STATIC); rc = sqlite3_bind_text(stm, 2, hash.c_str(), hash.size(), SQLITE_STATIC); rc = sqlite3_bind_int(stm, 3, versione); rc = sqlite3_bind_blob(stm, 4, last.c_str(), last.size()*sizeof(wchar_t), SQLITE_STATIC); rc = sqlite3_bind_zeroblob(stm, 5, size); rc = sqlite3_step(stm); if (rc != SQLITE_DONE) { fprintf(stderr, " This file was already present in the database!\n", rc); return; } else { fprintf(stdout, "Record FILE created successfully\n"); }