On 2015-09-21 11:18 AM, Rowan Worth wrote: > On 21 September 2015 at 16:36, Simon Slavin <slavins at bigfraud.org> wrote: > >> On 21 Sep 2015, at 8:29am, Rowan Worth <rowanw at dugeo.com> wrote: >> >>> 1) Statement is prepared >>> 2) Statement is stepped >>> 3) Statement is reset >>> 4) ... time passes ... >>> 5) Statement is stepped >>> 6) Statement is reset >>> 7) ... time passes ... >>> etc. etc. >>> >>> The assertion seems to be that if the return value of >> sqlite3_column_count >>> is cached at step 2 it will still be valid at step 5 >> Sorry, no. I was saying that if it was cached at step 2 it will still be >> valid until the end of step 3. And that if cached at step 5 it will still >> be valid until the end of step 6. My understanding is that the database is >> locked by the first call to _step() and unlocked at the first of ... >> > Sorry, I meant gwenn's assertion, not yours :) Although assertion was a > poor choice of words - "premise of this thread" would have fit better. > It seems reasonable on the surface - I can't think of how to phrase an SQL > query such that it returns a different number of columns on a subsequent > execution without the schema changing. But then I'm no SQL expert.
There is no way to phrase SQL to produce different results excepting to use a wildcard or wildcards. One of the very basic-most promises of SQL is that a query will not have a different answer or layout result to what was asked for. I think Simon's explanation and even test of the premise were great answers. I am slightly confused to Gwenn's asking if there is a way to read if the query has changed because of not wanting to make an "expensive" call to see the column count... Problem is the "expensive" call to read the column count will never be any more expensive than whatever call will give the "My schema changed" answer. As I noted earlier, and Simon noted / vetted via example, there is no way to change the layout and/or schema inside the transaction (between 1 & 3, or 4 & 5 above, etc.), so there is only one point at which there exists a need to check - before the first step after a prepare or a reset. At that point, a call to "sqlite3_didmyschemachange()" or whatever would be equally expensive than a "sqlite3_colcount(pstmnt)" (forgive me I don't have the docs in front of me to see the real procedure name). Again, that might just be me misunderstanding Gwenn, but even so I think the solution is not an elusive one.
