SQLite Forum

Do not auto reset after schema change
Login
It appears (to me, at least) that the OP is willing for a schema change to cause repreparation when it occurs between the initial sqlite3_prepare_v3(...) and the beginning of execution (or "first step".)  Once that begins, the only way for an ALTER TABLE statement to interact with that prepared (or reprepared) statement would be for it to be included within that same set of statements. Otherwise, the ALTER execution would occur in a different transaction, and thereby have no effect of the kind the OP wishes to avoid.  It would be pathological for the OP (or anybody else) to worry about "chance" schema changes occurring as a result of executing the statement sequence they wrote and then interacting with other statements within the same set and transaction.

I think it is important to understand that the ACID guarantees are integral to the transaction mechanism. Within a transaction, there can be no inter-statement interference because, presumably, each statement's nominal effect is exactly what is intended. (So we call it "what was commanded" rather than "interference".) And the ACID guarantees include preclusion of inter-statement interference between statements that belong to different transactions.

The only time a repreparation **can** occur is after a (re)preparation and before an execution of the engine code resulting from the preparation. If the OP wants to avoid that, the bare sqlite3_prepare() is available, which will produce an error upon execution instead of rebuilding the engine code (which is the only recourse because the SQL is not kept with which to do the rebuild.)