On 7/27/06, Peter van Dijk <[EMAIL PROTECTED]> wrote:
Using double quotes to quote identifiers in sqlite is dangerous, as mistyping a fieldname will not yield an error. Currently the only safe way to quote identifiers is using backticks, as in MySQL.
Sadly you are almost correct. :-( Using the MS Access style square brackets for quoting also produces correct error message for incorrect column names. Unfortunately, an sqlite extension causes it to misinterpret non-existent column names as string literals when they are quoted using SQL standard double quotes. The following trace shows the results for various styles of quotes. SQLite version 3.2.8 Enter ".help" for instructions sqlite> create table t (a); sqlite> insert into t values(1); sqlite> select a from t; 1 sqlite> select c from t; SQL error: no such column: c sqlite> select "a" from t; 1 sqlite> select "c" from t; c sqlite> select 'a' from t; a sqlite> select 'c' from t; c sqlite> select `a` from t; 1 sqlite> select `c` from t; SQL error: no such column: c sqlite> select [a] from t; 1 sqlite> select [c] from t; SQL error: no such column: c I'm sure this extension seemed like a good idea when it was introduced, but this example shows the dangers of changing standard functionality to "improve" it. Now we are stuck using the non-standard quote characters introduced for compatibility with other non-standard implementations in order to get reliable error detection. Consequently, the SQL using these non-standard quotes will not be portable to other standard conforming implementations. The moral of this story is: stick to the standard unless you have a *VERY* good reason to deviate. Dennis Cote
