RAM consumption inside a C++ program using Sqlite3 blob

Question

I'm using sqlite3 dbms inside a C++ program that I use mainly to store files as blob objects (I know that's not the best option).

Obviously I write them incrementally since they can be sometimes large (40-80MB) in order to do so I have to create first a placeholder of the blob using the binding function sqlite3_bind_zeroblob(...) and after that I open the blob writing and reading incrementally from and to it.

The problem I'm facing is that when i create the blob placeholder (during sqlite3_step) the RAM consumption of my app reaches 80-160MB for 2-3 seconds, once it has been created the RAM consumption goes back to 2-3MB at most.

I do not get why! If they created a way to write to a blob incrementally there is for sure a way to create that stupid placeholder without wasting 160MB of RAM, but I didn't find it. Have you got any suggestion?

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

It is an issue reported HERE.
And the oficial answer is:

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

So you need to change the order 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");
}