On Feb 12, 2007, at 1:21 AM, John Stanton wrote:
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.
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.
Yes, I've understood this.
Unfortunately, an sqlite3_stmt is not just the VDBE, but also the
current execution state and bindings of that VDBE.
|deally, what I'd like is something where a user can prepare a bunch
of SQL statements, creating query objects, like this:
local
open SQL.Template
in
val Query1 = query db "select * from table where x="iS" and
y="iI";" oS oI $
val Query2 = query db "select * from table2 where x="iS";" oS oI $
end
Here the iS and iI refer to input string and input integer
respectively. The oS and oI refer to output string and integer.
Then, later the user might do the following:
val Iterator1a = SQL.execute Query1 ("parameter1" & 2)
val Iterator1b = SQL.execute Query1 ("foobar" & 3)
val Iterator2 = SQL.execute Query2 4
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1b () of
NONE => print "End of the table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
case Iterator1a () of
NONE => print "End of this table"
| SOME (x & y) => print ("Got a row: " ^ x ^ ", " ^ Int.toString y ^
"\n")
The point is that here I use the same prepared query twice, with two
different associated states and bindings. It seems this sort of
interface is impossible at the moment without reparsing the SQL
statement again. Even if I choose not to expose the 'step' method as
an iterator, during the processing of a query's results there's
nothing I can do to prevent a user from executing the query again.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
