SQLITE3 and Android... A possible bug?

Solar2D General Questions/Discussion
#1

Any one experienced with SQLite on Android being different than iOS or the simulator?

Here is my code:

  
function M:loadTable(k)  
 print("k is", k, "!")  
 local query = [[SELECT content FROM history WHERE match IS "]]..[[Match]]..k..[[";]]  
 print("Query", query)  
 for row in storyboard.db:nrows(query) do  
 utility.print\_r(row)  
 local t = json.decode(row.content)  
 print("OOOO Fetching data", row.content)  
 return t  
 end  
end

Here is the output of an adb logcat on my Nexus 7

I/Corona (14713): Query SELECT content FROM history WHERE match IS “Match507e1a99201773000f00038d”;
I/Corona (14713): Lua Runtime Error: lua_pcall failed with status: 2, error message is: near ““Match507e1a99201773000f00038d””: syntax error

So this runs fine in the simulator and my query executes with no errors. I tested it on my iPhone 5 and it also ran fine, no errors. I only see this on Android.

The syntax seems fine when I print the query. The only thing different than the sample app is the sample app has the query string as a physical string inside of db:nrows() and that I init the database in main.lua but do the query in a storyboard module later, so I store the DB file handle in the storyboard table so it’s not global but “globally available” to me. The utility.print_r function is my table printing function and it appears that this syntax error is happening before that print_r().

No, I have not tried the sample app on android yet, going to go do that now.

Thanks
Rob
[import]uid: 19626 topic_id: 32035 reply_id: 332035[/import]

#2

Yep, db:nrows() doesn’t like quotes in their queries (or where clauses…)

Bug report filed: 18127 [import]uid: 19626 topic_id: 32035 reply_id: 127679[/import]

#3

It’s not a bug.

You’re supposed to surround your strings with apostrophes (aka: single quotes) in a SQL statement. That’s standard SQL string syntax for *all* relational databases such as SQLite, SQL Server, MS Access, Oracle, MySQL, Sybase, etc. I have experience with all of those databases, so trust me on this.

Double quotes is not standard and I’m a bit surprised Apple supports it. If their master plan was to confuse people, then they succeeded. [import]uid: 32256 topic_id: 32035 reply_id: 127721[/import]

#4

Here is SQLite’s official documentation on this…
http://www.sqlite.org/lang_keywords.html

The documentation states that single quotes are used for string literals and double quotes for identifiers (ie: table and column names). Interestingly enough, their documentation also states that “…SQLite will sometimes bend the quoting rules…” and allow double quotes to denote string literals. “sometimes” being the keyword. That’s the interesting part. In any case, I still I highly recommend using single quotes since that is the standard way to denote strings. [import]uid: 32256 topic_id: 32035 reply_id: 127726[/import]

#5

Yep, db:nrows() doesn’t like quotes in their queries (or where clauses…)

Bug report filed: 18127 [import]uid: 19626 topic_id: 32035 reply_id: 127679[/import]

#6

It’s not a bug.

You’re supposed to surround your strings with apostrophes (aka: single quotes) in a SQL statement. That’s standard SQL string syntax for *all* relational databases such as SQLite, SQL Server, MS Access, Oracle, MySQL, Sybase, etc. I have experience with all of those databases, so trust me on this.

Double quotes is not standard and I’m a bit surprised Apple supports it. If their master plan was to confuse people, then they succeeded. [import]uid: 32256 topic_id: 32035 reply_id: 127721[/import]

#7

Here is SQLite’s official documentation on this…
http://www.sqlite.org/lang_keywords.html

The documentation states that single quotes are used for string literals and double quotes for identifiers (ie: table and column names). Interestingly enough, their documentation also states that “…SQLite will sometimes bend the quoting rules…” and allow double quotes to denote string literals. “sometimes” being the keyword. That’s the interesting part. In any case, I still I highly recommend using single quotes since that is the standard way to denote strings. [import]uid: 32256 topic_id: 32035 reply_id: 127726[/import]

#8

Hi Rob,

Just as a follow up, are you satisfied with my above comments? [import]uid: 32256 topic_id: 32035 reply_id: 128670[/import]

#9

I tried singles and it still gives me the error.

The doubles work in the Simulator, in iOS and in the command line sqlite3 tool. It’s worked in every MySQL server I’ve ever used too.

As far as satisfied with the comments… well that’s a bit open-ended at this point. I’m always pleased that you take the time to answer and are committed to the community and your answers are always professional and well thought out. From that perspective, yes I’m pleased. I may not always like the answers. And in this case, since single quotes were still causing me the problem, I’m not quite ready to give up on this one yet. I switched my app to use singles last night just in case that was the problem and it was still giving me the syntax error on my Kindle Fire. But just for kicks, I’ll build the test app that I used to file the bug report with using singles and test that in case I have something else going on… Give me about 90 minutes to get home and run the test.

If that’s the case, I will have learned something and will bow to your awesomeness. If it’s still broken, I’ll beg and plead for a fix :slight_smile:

EDIT: fixed a typo.
[import]uid: 19626 topic_id: 32035 reply_id: 128678[/import]

#10

Oh… wait a minute… what type of value is “k” in your code up above? If it’s not of type “string” then it will cause a Lua error when you try to concatenate it to a string. Try doing the following instead…
[lua]local query = [[SELECT content FROM history WHERE match IS “]]…[[Match]]…tostring(k)…[[”;]][/lua]
[import]uid: 32256 topic_id: 32035 reply_id: 128685[/import]

#11

okay, it’s still happening with singles.

I/Corona ( 5671): k is 5089b5ac9e699e0007000018 !  
I/Corona ( 5671): Query SELECT content FROM history WHERE matchid IS 'Match5089b5ac9e699e0007000018'  
I/Corona ( 5671): Lua Runtime Error: lua\_pcall failed with status: 2, error message is: near "'Match5089b5ac9e699e0007000018'": syntax error

k as you can see is a really big hexstring that I get back from game minion. I presume it’s a string because print would print it as a decimal number if it was not.

I changed my code to look like this:

function M:loadTable(k)  
 print("k is", k, "!")  
 local m = "Match" .. k  
 local query = [[SELECT content FROM history WHERE matchid IS ']]..m..[[']]  
 print("Query", query)  
 --for row in storyboard.db:nrows("SELECT \* FROM test") do  
 for row in storyboard.db:nrows(query) do  
 utility.print\_r(row)  
 local t = json.decode(row.content)  
 print("OOOO Fetching data", row.content)  
 return t  
 end  
end

I am now making a new variable called “m” and the results of cat’ing a number to a string should be a string, even though k is being printed as a string not a really big number.

I’m using single’s now and I’m still getting the error. I’m still on the “Its a bug bandwagon” :slight_smile:

I’ll put in an explicit tostring() in to just make sure…

Thoughts?

Rob [import]uid: 19626 topic_id: 32035 reply_id: 128696[/import]

#12

Rob, what happens if you use this as your query?

local query = [[SELECT * FROM history WHERE matchid = ‘]]…m…[[’;]]

or

local query = [[SELECT * FROM history WHERE matchid =]]…m…[[;]]

[import]uid: 9422 topic_id: 32035 reply_id: 128757[/import]

#13

I’ve not tried the equals sign yet. But the syntax using “IS” is valid in the Simulator, iOS and in the command line. So even if I can work around it, Android behaves differently.

[import]uid: 19626 topic_id: 32035 reply_id: 128765[/import]

#14

I have a similar query to yours that is working on Android, but I use ‘=’ instead of ‘IS’ so that was my first shot in the dark. The corona docs suggest iOS has built in support for SQlite and a compiled version is used to cover the Android side, so it wouldn’t be too surprising if there are subtle differences between the two.

Also, (I am a database noob so forgive if I don’t know what I’m talking about) why use
‘…SELECT content FROM…’
instead of ‘…SELECT * FROM…’ in the query?
Don’t you extract the ‘content’ column using something like

for row in db:nrows(query) do
local content = row.content

[import]uid: 9422 topic_id: 32035 reply_id: 128771[/import]

#15

Why return content you don’t want? In this simple case, I think the only other field is the ID field, so it really wouldn’t matter, but I’ve never like to do a *.

[import]uid: 19626 topic_id: 32035 reply_id: 128776[/import]

#16

Hi Rob,

Just as a follow up, are you satisfied with my above comments? [import]uid: 32256 topic_id: 32035 reply_id: 128670[/import]

#17

Rob,

I’ve found your bug report. I’ll just have to go through it via a debugger to find out what is really going on. I won’t have time to do this personally just yet.

I do know for a fact that single quote string notation does work. I proved it on my end. I think the error is caused by something else.

Regarding the “IS” keyword, that “should” be valid syntax… although I’ve never used it for string equality before. I typically use it with subqueries like this: “IS IN” or “IS NOT IN”. The ‘=’ sign is definitely valid syntax for string equality as well and that’s what I normally use. You can try giving the ‘=’ sign a go since the error message states that there is syntax error near your string.
That said, the official SQLite documentation (see the link below) states that the “IS” keyword can be used in this fashion.
http://www.sqlite.org/lang_expr.html#isisnot

The only possible error that I can think is happening at this point is that the “history” table or the “matchid” column does not exist. That would cause a SQL error as well… but I’m guessing this is probably the unlikely cause?
[import]uid: 32256 topic_id: 32035 reply_id: 128790[/import]

#18

I tried singles and it still gives me the error.

The doubles work in the Simulator, in iOS and in the command line sqlite3 tool. It’s worked in every MySQL server I’ve ever used too.

As far as satisfied with the comments… well that’s a bit open-ended at this point. I’m always pleased that you take the time to answer and are committed to the community and your answers are always professional and well thought out. From that perspective, yes I’m pleased. I may not always like the answers. And in this case, since single quotes were still causing me the problem, I’m not quite ready to give up on this one yet. I switched my app to use singles last night just in case that was the problem and it was still giving me the syntax error on my Kindle Fire. But just for kicks, I’ll build the test app that I used to file the bug report with using singles and test that in case I have something else going on… Give me about 90 minutes to get home and run the test.

If that’s the case, I will have learned something and will bow to your awesomeness. If it’s still broken, I’ll beg and plead for a fix :slight_smile:

EDIT: fixed a typo.
[import]uid: 19626 topic_id: 32035 reply_id: 128678[/import]

#19

Oh… wait a minute… what type of value is “k” in your code up above? If it’s not of type “string” then it will cause a Lua error when you try to concatenate it to a string. Try doing the following instead…
[lua]local query = [[SELECT content FROM history WHERE match IS “]]…[[Match]]…tostring(k)…[[”;]][/lua]
[import]uid: 32256 topic_id: 32035 reply_id: 128685[/import]

#20

I changed it to use equals and the error went away.

I wonder if this indeed a problem where iOS/OSX is using the built in version and Android is using a compiled version and the android version didn’t like the IS syntax.

You can probably close this bug Joshua, or at least lower its priority so you can fry more important fish.
[import]uid: 19626 topic_id: 32035 reply_id: 128801[/import]