Wesley W. Terpstra wrote:
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]
-----------------------------------------------------------------------------
Look at how Sqlite implements callbacks in sqlite3_exec to discover
a way to implement your language. It looks like it requires a little
extra in your language compiler to resolve the forward references.
Doing that will ensure that the transactions are atomic and that
database integrity is maintained.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
