Prepared queries are actually the query compiled into the metalanguage,
VDBE. You can have many statements in one transaction, and each of
those statements can be reusing a VDBE virtual machine instance with a
current set of data bound to it.
When you reset the compiled statement you make it ready for a new set of
bindings and when you finalize it you destroy it.
As far as I can see there is no simple way in Sqlite to use cursors and
have multiple transactions current. I am sure that with ingenuity you
could implement some cursor-like functionality.
It is a good idea to store and resuse compiled SQL because you avoid the
tedious and unecessary process of recompiling the same SQL over and over
during the life of an application invocation.
Wesley W. Terpstra wrote:
Good evening.
I've been working on an SQLite3 binding for Standard ML (SML)
targeting the MLton compiler. I've read all the material on the C API
I've found, but I have some questions about the VM prepare/step
methods. Specifically, I am confused about the lifetime of non-
finalized queries. It seems that there can be several prepared
queries at once, and the documentation implies that this represents a
savings: the queries need not be reparsed. However, it also means
it's possible to interleave steps to prepared queries.
Here are the things I believe to be true, but I would like
confirmation / denial, as it affects the interface I expose in SML:
Suppose you have two prepared queries: q1 and q2. I currently believe
that it is an error to execute: step q1, step q2, step q1.
In fact, I believe that after step q1, you must either call finalize
or reset on q1 before you can call step q2.
If I'm wrong with the above, what happens if I step q1, start a
transaction, then step q1 again?
What happens to an open transaction if you reset a query? I imagine
that a user might be running a select statement, have found the row
they were interested in, and then called reset rather than read the
rest of the result set. In this case, I would like to be able to
continue running new statements within the query. Similarly, I might
cancel the query with finalize. The documentation seems to imply that
this will abort a transaction?
I ask all this, because I think it would be convenient to allow uses
to create query objects that they can call / reuse at a later point.
However, it's not clear to me if I should expose the 'step' interface
to a user. If interleaving steps is bad, it is possible to design the
interface to allow stopping a query prematurely, without exposing
'step'. If this premature cancelation breaks an open transaction,
then perhaps I should forbid it as well.
Thanks for any clarification on these points.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
