Android Question SQL query error

[andredamen]

I've encountered something very strange. My database definitely contains the column "hit2." When I execute this query, SELECT * FROM data WHERE hit2 = aaaa000600166, I get the error:

android.database.sqlite.SQLiteException: no such column: aaaa000600166 (code 1 SQLITE_ERROR[1]): , while compiling: SELECT * FROM data WHERE hit2 = aaaa000600166

When I change the query to: SELECT * FROM data WHERE hit2 = 000600166, no error appears. It seems that adding "aaaa" to the search criteria triggers the error. I don't get an error message with the query SELECT * FROM data WHERE hit2 = +000600166 either, but if I change it to SELECT * FROM data WHERE hit2 = a000600166 or any other letter, the error message appears again.

Does anyone have any idea what's going on here?

 

[DonManfred]

Post the code using this SQLs.
Make sure to use parametrized queries.
What is the type of hit2? varchar, int?

 

[walt61]

It should be:
SELECT * FROM data WHERE hit2 = 'aaaa000600166' <<< quotes around the literal

Better solution:

sql1.ExecQuery2("SELECT * FROM data WHERE hit2=?", Array As String("aaaa000600166"))

 

[emexes]

Like in B4X and most programming languages, a spaceless string of characters beginning with a digit is taken to be a literal number, and a spaceless string of characters beginning with a letter is taken to be an identifier (variable name, or statement, function or operator).

So 000600166 is taken to be a decimal number (or octal in C, Java, etc, because it begins with an otherwise-unnecessary leading 0)

and aaaa000600166 is taken to be an identifier, in this case a column name, resulting in the "no such column" error

 

[andredamen]

Thank you all. I am going to use the solution with the quotes around the literal. That works good for me.
Thanks emexes! With your explenation I understand why the error became.

 

[DonManfred]

with the quotes around the literal.

the correct and suggested solution would be a parametrized query

sql1.ExecQuery2("SELECT * FROM data WHERE hit2=?", Array As String("aaaa000600166"))