Thanks for the replies. My own pre-prepare parser makes use of the sqlite3GetToken and sqlite3KeywordCode functions although that requires some minor changes to the sqlite3.c and .h files to make them accessible. Subsequently the text is sent to a CheckSQL function that returns true or false depending on if sqlite3_prepare returns SQLITE_OK. Never thought about running the statement inside a transaction that will be rolled back so thanks for that, it may come in useful later.
When I want to do something I usually find SQLite has something lying about to do much of it for me. It’s an incredible piece of software, particularly when you consider the lack of bloat. Updates regularly seem to bring new gems such as indexes on expressions or the sublime row values. I even discovered a new one yesterday (I hadn’t seen it announced) in sqlite3_expanded_sql which lets you see the SQL with any bindings in place. Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: R Smith<mailto:rsm...@rsweb.co.za> Sent: 08 February 2017 22:42 To: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] sqlite3_complete 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
