This question is about the use of SQLITE_STATIC and SQLITE_TRANSIENT, when using sqlite3_bind_text() in the context of compiling the amalgamation file with a C program. I'm not an experienced C programmer; so, this question might be little stupid.

If the pointer to the character array points to an area of managed/heap memory, should SQLITE_TRANSIENT always be used or can that memory space be freed in the usual manner at a later point elsewhere in the C code? Is SQLITE_TRANSIENT for convenience or is it mandatory?

If the memory is unmanaged/stack memory that is declared external to the function in which sqlite3_bind_text() is called, should SQLITE_STATIC still be used?

The reason for asking is because the plan was to allocate a certain amount of memory and re-use/overwrite it as much as possible instead of repeatedly allocating and freeing smaller blocks.

Also, when the SQL statement, as text, is written with question marks for variable placeholders, is it accurate that the space required for the variable values to be bound does not need to be accounted for in advance, because, at the point of binding, the statement will have already been prepared and SQLite will handle the memory allocation for the actual values bound?

Thank you.

If the argument is SQLITE_STATIC, then the application is promising SQLite that it will not change the text array until SQLite has finished using it, which usually means until after the prepared statement is finalized.

If you use SQLITE_TRANSIENT, then SQLite makes its own copy of the string, so the application can free the memory or reuse it for something else as soon as sqlite3_bind_text() returns.

It is always safe to use SQLITE_TRANSIENT. SQLITE_STATIC might be a little faster if you are dealing with large strings, but also runs the risk of SQLite trying to use memory that has been freed or overwritten by the application, if there is a bug.

Maybe start with SQLITE_TRANSIENT. Then profile and if you have a hotspot in memcpy(), consider trying to change some of the SQLITE_TRANSIENTs into SQLITE_STATIC at that point. Remember: Premature optimization is the root of all evil.

You do not need to work about space for ? substitutions. SQLite will deal with that automatically.

Thank you very much.

Confirmed

Thanks. And of course, I meant SQLITE_STATIC above, but I'm sure you figured that out.

Re use of SQLITE_STATIC and SQLITE_TRANSIENT, when using sqlite3_bind_text(), Gary asks:

If the pointer to the character array points to an area of managed/heap memory, should SQLITE_TRANSIENT always be used or can that memory space be freed in the usual manner at a later point elsewhere in the C code? Is SQLITE_TRANSIENT for convenience or is it mandatory?

Instead of worrying or asking about what sort of memory "the pointer to the character array" points to, you should answer the question, suggested in the doc for the sqlite3_bind_text() function), will that character sequence reliably remain accessible there, or might it be used for something else or become inaccessible as the SQLite execution engine runs the prepared statement? If the former, SQLITE_STATIC may be used to avoid an allocation, but if the latter, SQLITE_TRANSIENT must be used to ensure that an accessible copy is made. If you are managing the memory where that "character array" resides and can guarantee that the pointer to it remains useful while the execution engine runs, your scheme of taking over some of the memory management will work (if you do it right) and SQLITE_STATIC will do some good.

Your surmise about space required for bound variable values, and its relation to SQLite handling the memory allocation for the actual values bound raises the very issue that your previous question does. If you use SQLITE_TRANSIENT, SQLite will manage the necessary memory. If you use SQLITE_STATIC, SQLite will take you at your word that it need not make a copy into its own managed memory.

Thank you. I read that document before posting the question but I failed to make that conection. It makes a lot of sense now. I mean static and transient are pretty easy to grasp but, not knowing much, I was stupidly thinking the choice had to do with whether it was stack or heap memory.

In this case, I can guarantee that the pointer will remain useful until the execution engine runs and all results have been retrieved.

It feels good/calming to understand something. Thanks again.