Am 28.10.2017 um 19:22 schrieb Bart Smissaert:
This is interesting, not so much for the performance regression, but for
the fact that the posted Northwind database has
spaces in table names and view names. I wasn't aware this is allowed
and it caused a lot of errors in my app. I have this nearly fixed now
(by adding the square brackets),...
More SQL-Standard-like would be DoubleQuote-chars instead of the
SquareBrackets, but thankfully SQLite allows for [...] (as well
as for SingleQuotes around column-names).
...I am left with one problem. This is the fact that for example the view
Product Sales for 1997 has a UDF in the view definition: DatePart.
Obviously this is causing an error:
no such function: DatePart
Yep, sorry about that - the SQLite-COM-wrapper (vbRichClient5.dll)
opens an SQLite-DB with nearly the whole set of MS-JET-engine compatible
Functions (DatePart, DateDiff, Left$, Right$, Mid$, Format$, Instr etc.)
by default (though there is a Parameter in the OpenDB-calls which allows
to switch that off).
The Northwind-DB (introduced by MS as a Demo-DB with MS-Access a
few decades ago) was used deliberately, to show the quite painless
migration from the JET-engine to the SQLite-COM-wrapper in some
Demo-Apps (including nearly compatible View-Defs, along with
compatible Jet-SQL-Functions).
I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION,
but will only avoid the error for
EXPLAIN and EXPLAIN QUERY PLAN.
Is there a way to handle this with a compile option or otherwise, so that
instead of an error a Null will be produced or just the
original value?
I for my part would *insist* on SQLite throwing an error, when
it encounters unknown (userdefined) functions or collations
in a Database, which a currently given wrapper-extension does
not support.
How else would you have been able, to stumble over the (for your
wrapper-lib) non-working view-definition?
The question it boils down to (using "VB-speak") is:
"Do we really want an 'On Error Resume Next'-behaviour in SQLite?"...
;-)
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users