SQLITE3 and Android... A possible bug?

Solar2D General Questions/Discussion
#21

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]

#22

Rob,

I’m glad you go it working… and sorry about all of the trouble.

I know on iOS and OS X, we’re using the SQLite library that is included with the operating system, but on Windows and Android we have to include the SQLite source/library into Corona ourselves… and we may be using an older version. That may account for the difference in behavior here. I’ll add this to our to-do list. [import]uid: 32256 topic_id: 32035 reply_id: 128811[/import]

#23

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]

#24

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]

#25

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]

#26

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]

#27

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]

#28

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]

#29

Rob,

I’m glad you go it working… and sorry about all of the trouble.

I know on iOS and OS X, we’re using the SQLite library that is included with the operating system, but on Windows and Android we have to include the SQLite source/library into Corona ourselves… and we may be using an older version. That may account for the difference in behavior here. I’ll add this to our to-do list. [import]uid: 32256 topic_id: 32035 reply_id: 128811[/import]

#30

Rob,

We’ve confirmed that Android was using an older version of SQLite compared to iOS.
We’ve upgraded SQLite on Android from version 3.5.9 to 3.7.14.1.
We’ve also confirmed that the SQL syntax [lua]IS “string”[/lua] no longer crashes on Android with the newest SQLite version.

This change will be made available tomorrow in daily build #943.
Thank you for your help in narrowing down this issue. [import]uid: 32256 topic_id: 32035 reply_id: 129416[/import]

#31

Rob,

We’ve confirmed that Android was using an older version of SQLite compared to iOS.
We’ve upgraded SQLite on Android from version 3.5.9 to 3.7.14.1.
We’ve also confirmed that the SQL syntax [lua]IS “string”[/lua] no longer crashes on Android with the newest SQLite version.

This change will be made available tomorrow in daily build #943.
Thank you for your help in narrowing down this issue. [import]uid: 32256 topic_id: 32035 reply_id: 129416[/import]