SQLite "quotation mark" and "bracket" ?

[CFLam]

Hello all,

   What is the different between ' and [ in SQLite?
I use the following query to delete certain row in table:

 

$inSql="delete from '" & $DBName & "' where 'Assoc Cnt'=0"
$RV=_SQLite_Exec(-1,$inSql)
if $RV <> $SQLITE_OK then
 ConsoleWrite("delete  error " & "," & @error & @CRLF)
endif

I find that the query doesn't perform what I expected i.e. no rows deleted and it give no error.
But if I change to:

$inSql="delete from [" & $DBName & "] where [Assoc Cnt]=0"

It perform what I expected, what is the different between ' and [ ?
Seems that SQLite accept both but why 1 perform and the other not?? Since the "'" doesn't give any error message, it takes me a lot of time to find out this issue
Thanks.

Regds
LAM Chi-fung

Edited May 8, 2018 by CFLam

[FrancescoDiMuro]

Hi @CFLam
Why do you have to put '' in your column names?

You could easily use 

$strQuery = "SELECT ColumName FROM TableName WHERE ColumName LIKE '%ABCD%';"

; Or, even better

$strQuery = "SELECT ColumName FROM TableName WHERE ColumName LIKE " & _SQLite_FastEscape("%ABCD%") & ";"

Best Regards

Francesco

Edited May 8, 2018 by FrancescoDiMuro
Thanks @jchd for pointing out!

[jchd]

In SQL single quotes are used to delimit literal strings, double it to represent a literal one.

In SQLite schema names can be delimited by either [ ] " " or ` `. You must delimit schema names when it contains one or more spaces when it is empty⁽¹⁾ and when it is an SQL keyword. " " and [ ] are specified by SQL standards while ` ` are accepted to ensure compatibility with MS RDBMS engines.

So the following SQLite statements are equivalent⁽²⁾:

select ID, L."column" "It's me" from MyTable "This is My Table" join log L using "  this column  " where "col_1" like '%abc_e%';
select ID, `L`.[Column] [It's me] from "MyTable" [This is My Table] join "log" [L] using [  this column  ] where [col_1] like '%abc_e%';
select ID, [L]."cOLUmn" `It's me` from [MyTable] "This is My Table" join `log` "L" using `  this column  ` where `col_1` like '%abc_e%';

Obviously the code is more readable if you stick to only one style of delimiters and use them only when strictly necessary. Since backticks `` are more or less a microsoftism thing you may be better avoiding using them if you envision portability to other standard-compliant SQL engines.

SQLite is also very helpful in that it tries hard to properly parse a statement where schema names would wrongly use single quotes '' instead of "" [] `` or when blatant literal strings wrongly use "", but that isn't a guaranteed feature and you shouldn't rely on it.

⁽¹⁾ SQLite allows empty schema names
⁽²⁾ schema names are case-insensitive

EDIT: forgot to point out that in the OP $DBName should refer to a table name, not a database name.

Edited May 8, 2018 by jchd

[CFLam]

I see, seems that I better stick to using [] whenever table/field name has space. Thanks a lot.