SQLite Forum

Blobs & SQLITE_TRANSIENT clarification

Blobs & SQLITE_TRANSIENT clarification

(1) By anonymous on 2021-04-27 23:42:31 [link] [source]

I know that the docs state the 5th argument SQLITE_TRANSIENT to be

If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns.

My question is can I then dispose of the object containing the data of the 3rd argument after the sqlite3_bind_blob( ... ).


std::unique_ptr<TMemoryStream> pUniqueMemStrm (new TMemoryStream);


sqlite3_bind_blob(&stmt, valueCount, pUniqueMemStrm->Memory, pUniqueMemStrm->Size, SQLITE_TRANSIENT);

then, immediately delete pUniqueMemStrm without repercussion?

Sorry if this appears to be a dumb question...


(2) By Larry Brasfield (larrybr) on 2021-04-28 00:37:13 in reply to 1 [source]

Yes, sort of. The value pointer passed into sqlite3_bind_...() need only remain valid until the function returns. (That is the point of SQLITE_TRANSIENT .)

However, (considering the C++ aspect of your question), it should not be necessary and IMO is not advisable for your own code to delete anything. std::unique_ptr will handle resource release when it goes out of scope, via its destructor. (That is its rraison d'ĂȘtre.)

(3) By anonymous on 2021-04-28 01:08:02 in reply to 2 [link] [source]

Thank you Larry,

I have gone with your advice of not deleting anything, much appreciated.

(4.2) By ddevienne on 2021-04-28 08:12:32 edited from 4.1 in reply to 1 [link] [source]

sqlite3_bind_blob([...], SQLITE_TRANSIENT);
then, immediately delete pUniqueMemStrm without repercussion?

Yes. Because SQLITE_TRANSIENT means SQLite makes an internal copy.
So when the bind returns, you are free to release your own memory.
BUT, you are probably pessimizing things...

I use SQLITE_STATIC myself, AND make sure the memory stays around
until the _step; _reset; calls, after which I either rebind a NULL
or _clear_bindings, to make SQLite forget about that pointer.

Binds are sticky until explicitly cleared, or rebound over.
The above technique avoids unnecessary copies.

As to deleting a std::unique_ptr it's perfectly OK to .reset() it
instead of letting it do it implicitly when going out-of-scope, to release
memory as early as possible. RAII is about exception safety to avoid leaks
in this case, and there's nothing wrong with explicitly releasing resources earlier.

(5) By anonymous on 2021-04-28 09:45:34 in reply to 4.2 [link] [source]

Thanks for that, very informative.

My initial thinking was that it would be best to release memory asap to avoid memory leaks, but did not consider  SQLITE_STATIC or .reset() so thanks again.

I am very new to working with sqlite at this level, like 1 month, so lot's to learn but extremely happy with my progress. 

I am sure I will have other questions for you guy's 


(6) By anonymous on 2021-04-28 09:53:27 in reply to 4.2 [link] [source]

I've always wondered quite the rules around SQLITE_STATIC and only felt confident to use it very rarely - mainly with constant C strings or where the passed buffer has remained valid until after _finalize() is called on a prepared statement. It's also a bit subtle that _reset() doesn't clear bindings.

Perhaps the documentation for SQLITE_STATIC could be improved a little to describe just how static static has to be?


(Posting anonymous as I've forgotten my password and can't find a reset option!)

(7) By ddevienne on 2021-04-28 12:56:34 in reply to 6 [link] [source]

See also Richard's answer here and his confirmation of the above here.

Yes, the doc could be a bit better I suppose.

(8) By anonymous on 2021-04-28 20:28:10 in reply to 7 [link] [source]

That is very helpful - thank you.

(9) By Larry Brasfield (larrybr) on 2021-04-28 22:06:04 in reply to 7 [link] [source]

What facts, or relationship among facts, do you see as unclear or too indirect in the present doc for sqlite3_bind_...() ? Is it the required validity lifetime of the text or blob pointer when SQLITE_TRANSIENT is not used?

As written now, there appear to be 3 lifetime choices per the API doc, determined by the 5th actual argument:

  1. If SQLITE_STATIC is passed, "then SQLite assumes that the information is in static, unmanaged space". IOW, there is no lifetime limitation beyond the implied one set by process duration.

  2. If a destructor is passed, it is "used to dispose of the BLOB or string after SQLite has finished with it." IOW, the lifetime is what the SQLite library determines, without further detail exposed.

  3. If SQLITE_TRANSIENT is passed, then the pointer must remain valid until "the sqlite3_bind_*() routine returns."

I suspect that the shortcoming you see is that the actually required lifetime of choice 1 is (reasonably) expected to be much shorter, (which Richard confirms in the posts you linked), yet the bind() doc provides no guidance as to how much shorter it will be under various scenarios. Or, put another way, you would see a different API as better, one with a lifetime requirement stated that more closely follows what the implementation in fact requires.1

I think it might be reasonable to alter the API to require SQLITE_STATIC-qualified pointers to remain valid until the associated statement is finalized. I don't know how Richard will see that.

Until the API is changed, library users who want to reclaim memory referenced by the pointer sooner have a simple and effective option: Create a function which knows how to free the pointer, and pass it as the 5th argument. That way, the object lifetime will expire when "SQLite has finished with it."

1. The docs define the API and provide usage guidance. Their role is not to describe the complete and detailed contour between what will "work" and what will not.

(10) By anonymous on 2021-04-28 23:13:47 in reply to 4.2 [link] [source]

An update on this from my point of view.

I attempted the use of SQLITE_STATIC in my case scenario and it failed, not that it failed to save the blob to the database, but it was not what it was when I used SQLITE_TRANSIENT so, in my case, using SqlExpert, found here http://www.sqliteexpert.com/, it's internal image viewer opened the image I saved and it was shown as an image, using SQLITE_STATIC, it showed the byte code instead.

Cheers and always open to learning new things each day.

(11) By anonymous on 2021-04-29 08:46:14 in reply to 9 [link] [source]

Hi Larry - yes it's case 1.

SQLITE_STATIC is of more limited use if the information has to be static for the duration of the entire executable process.

I don't think the API needs to change, but it could be helpful if it is stated in the docs that SQLITE_STATIC requires the information only to remain valid only until one of the following completes:

  1. The bound parameter is bound to something else.
  2. The bound parameters are cleared using sqlite3_clear_bindings().
  3. The prepared statement is destroyed with sqlite3_finalize().

Richard's posts seem to say this, but clarifying it in the docs would be even better.

As an example of a common (toy) use case, consider a function like:

bool updateName(sqlite3 *db, uint32_t id, const char *name)
  sqlite3_stmt *ps;

  if(sqlite3_prepare_v2(db, "UPDATE names SET name=? WHERE id=?", 0, &stmt, NULL) !=  SQLITE_OK) {
  sqlite3_bind_int(ps, 0, id);
  sqlite3_bind_text(ps, 1, name, SQLITE_TRANSIENT, NULL);



  return true;

This seems like a simple case where SQLITE_STATIC would be fine having read Richard's posts, but is a little uncertain from the present docs. The motivation for preferring SQLITE_STATIC would be for a slight performance improvement, expecially if the function were rewritten to reuse a prepared statement.