Jump to content
CFLam

SQLite "quotation mark" and "bracket" ?

Recommended Posts

CFLam
Posted (edited)

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 by CFLam

Share this post


Link to post
Share on other sites
FrancescoDiMuro
Posted (edited)

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 by FrancescoDiMuro
Thanks @jchd for pointing out!

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
jchd
Posted (edited)

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
CFLam

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×