"Ulrich Telle" <[EMAIL PROTECTED]> wrote:
>
> Sure, it would be nice if SQLITE_SCHEMA errors could be handled internally
> by SQLite, but I think it's a non-trivial task to handle this kind of error.
>
> When I created my SQLite C++ wrapper wxSQLite3 I thought about handling
> SCHEMA errors, but decided against it due to the complexities involved.
Isn't that really the whole point of a wrapper - to deal with complexities
so that the user doesn't have to. If you are passing all of the
complexities up to the user, why use you wrapper at all? Just call
SQLite directly.
>
> For INSERT, UPDATE or DELETE a simple retry might be a good choice. But how
> often should the retry take place? The SQLite FAQ code example contains an
> endless loop!
Not. OK, I guess in theory, if another process were updating the
schema at just the right rate so that the schema was different every
times you retried, you could get an infinite loop. But in practice,
the loop never runs more than twice - 3 times in the extreme.
> Additionally the number of columns in a table used in these statements might
> have changed, that is the statement would probably fail again.
If the number of columns changes, you will get a syntax error, not
a schema error. At that point you exit the loop.
>
> In case of a SELECT statement the situation is still more complex. The
> SCHEMA error could happen after reading several result rows.
No. SCHEMA errors happen prior to reading any data.
>
> When retrying a query another problem arises if the SQL statement contains
> bind variables. You would have to rebind the variables. To handle this
> automatically would induce a lot of extra house keeping, wouldn't it?
See the sqlite3_transfer_bindings() API.
------------------------ A Digression --------------------------
To all writers of wrappers, my I please call your attention to
the TCL wrapper for SQLite. In that wrapper I have attempted
to hide as many details of the interface as possible from the user and
to make everything automatic. This allows the user to focus on their
application and not worry so much about the details of the interface
to the database. I and everybody else I have talked to find this to
be a very refreshing approach.
In the TCL wrapper, you run SQL statements using this template:
DBOBJECT eval SQL-STATEMENT CODE-TO-HANDLE-EACH-RESULT-ROW
The DBOBJECT is the object that is created when you open the database.
On this object you call the "eval" method with two arguments, the
text of the SQL you want to process and a lambda procedure that runs
once for each row of output. (The lambda is optional and is usually
omitted for non-query statements.) The bindings automatically process
named parameters within the SQL statement by binding them with the
value of TCL variables of the same name. So for example, if you
say:
db eval {UPDATE table1 SET x=$var1 WHERE y=$rownum}
The $var1 and $rownum named parameters are bound to the values of
the var1 and rownum variables in TCL. This is all automatic.
Within the lambda procedure that runs once for each row of the result,
local variables are created to contain the value of each column -
the names of the local variables are chosen to match the column names.
That way the user does not have to remember any method calls or
other such complication to get at the results.
Note the complete absence of prepared statements. Prepared statements
are handled automatically by the wrapper. The wrapper keep a cache
of recently used SQL statements and reuses them if the reappear.
Statements are cleared from the cache using the LRU algorithm. There
are methods on the database object to changes the size of the prepared
cache or flush the cache. But those methods never get used in practice
because the cache just seems to work without any problems.
Note also that the user never needs to worry about how to bind
values. Bindings all happens automatically and transparently.
SQLITE_SCHEMA errors are also handled automatically.
The end results it that the user of the TCL binds does not need to
remember much about how the wrapper work. If they can just remember
how to create a database object (hint: use the sqlite3 constructor)
and how to run the eval method, then they have access to the full
power of the SQLite interface without having to know or remember
ny of the details.
There is an important principle at work here: keep it simple.
An interface with less complication, with fewer choices and options,
with less to remember and think about, is a better interface. I
want to encourage all authors of wrappers and bindings for SQLite
and for other libraries to keep this principle in mind.
--
D. Richard Hipp <[EMAIL PROTECTED]>
