On 2017/02/08 8:47 PM, x wrote:
Thanks, it certainly looks more useful with that additional info. I sometimes
have to pass SQL to my own currently unsophisticated parser before I can send
it to prepare and hoped it would help with that. Given what you’ve told me It
will help to a degree but when I first saw it I hoped it would check the sql
keywords without checking the validity of the columns, tables etc.
Yes, that would be nice, but that requires the full-blown parser.
You can achieve the very same by breaking the SQL into single statements
(perhaps using the sqlite3_complete() function to assist) and then
simply preparing-and-releasing the single statements one by one (You'd
have to access SQLite via the C API for this though) and then see if any
of it errors out or not, and use the error code and description to
report back on what's wrong with the SQL. This WILL however check the
validity of columns.
Note that depending on the SQL, this can take more than a few
milliseconds - some complicated statements can take minutes to simply
prepare on a large DB.
Also, this method is severely flawed in that if the list of statements
is a transaction, and tables get created/altered during the transaction,
subsequent statements involving that table and/or its new columns will
fail if the SQL that was supposed to create/alter the table never ran to
completion. It is also flawed in that a statement might succeed
perfectly to prepare, and only running it to completion reveals the flaw
- such as a unique constraint failing late in an update run.
A trick some DB management engines use (as did I) is to run the entire
statement list inside a transaction (if it isn't already in one) and
then just roll it back at the end in stead of committing. It's a
horrible waste of time and resources, and might write-lock the DB for
the duration, but useful if you want to see HOW and IF a script succeeds
without altering the database. This is typically done during development
phases and most devs are aware enough to not do that on a terabyte-sized
database.
In the end, accurately judging the effect and validity of a statement
without actually running it to completion, is just not useful.
</musing>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
