"G. Roderick Singleton" <[EMAIL PROTECTED]> wrote:
> The following issue, http://www.sqlite.org/cvstrac/tktview?tn=1959 has
> proved a bit of a stopper with the OpenOffice.org driver. I recall a
> discussion on prepared statements and how to manage them but cannot find
> the details. If I remember correctly, there is a way to handle temps via
> prepared statements but need some pointers, please.
>
The issue with ticket #1959 is that SQLite does not allow
you to DROP a table while simultaneously reading from a
different table in the database. This is to prevent a DROP
from deleting a table or index out from under a reader.
I am disinclined to relax the constraint.
Notice, however, that you do not need this capability in order
to build a driver for OpenOffice.
For the benefit of all readers, I will recap the situation.
The OpenOffice database interface wants to use random
access read/write cursors on queries. So, for example, if
you have a query like:
SELECT * FROM t1;
You would have a cursor into the result set which you could
move to any arbitrary entry in the result set. And you could
go back and update entries as you moved about.
SQLite does not support random-access cursors. It would
be easy enough to add cursor support for simple queries like
the one shown above, but cursor support becomes more
troublesome as the complexity of the query increases.
Imagine trying to randomly address the result set of
this query:
SELECT eqptid, roomid
FROM eqpt
WHERE typeid IN (
SELECT typeid FROM typespec
WHERE attrid=(
SELECT attrid FROM attribute WHERE name='autoactuate'
)
AND value=1
INTERSECT
SELECT typeid FROM typespec
WHERE attrid=(
SELECT attrid FROM attribute WHERE name='algorithm'
)
AND value IN ('smokealarm','wetbulb')
)
The only way I know to provide some approximation of random
access cursors for queries like the above is to store the
complete result of the query in a temporary table. You can
always do so by prepending:
CREATE TEMP TABLE result_set_1 AS
in front of the text of the query. Then you can move about
inside the temporary table looking at results all you want.
Writing back to the original table is still a problem. I
think you have to solve that on a case-by-case basis. But
at least here you have random access to the result set.
Now if you follow this strategy, notice that you never need
to keep pending read operations around. The original query
builds the temp table in a single sqlite3_step() call. To
access the results, you prepare a statement like this:
SELECT * FROM result_set_1 WHERE rowid=?
To get the 5th row, bind the value 5 to the ? parameter and
run the prepared statement. Copy the results into variables
and reset the statement. To get the 6th row, bind the value 6
and repeat. Notice that you never keep any query open for
longer than a single call sqlite3_step(). So there are never
any pending reads laying around to interfere with DROP operations.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
