Search, match and sort sqlite db

cindy.h1986 · January 26, 2013, 5:46pm

I just realized, my db query do sort out words less than 3char. Now I just need to figure out how to sort the results by word length.

Getting there… [import]uid: 65840 topic_id: 35342 reply_id: 140509[/import]

rob · January 26, 2013, 5:52pm

local sql = "SELECT \* FROM names WHERE name LIKE '"..first..second..third.."%'"

I think will work better for you. Is where you’re getting first, second and third from already a string? You can just search for the word with a % on the end to match the first X number of characters.
[import]uid: 199310 topic_id: 35342 reply_id: 140511[/import]

cindy.h1986 · January 26, 2013, 6:23pm

Yes, first, second and third are the letters entered in the textfield and saved as a storyboard variable.

Your way just return words starting with those letters in that order, my way results in 4 times more entries and the letters can occur anywhere in the word/names.

I just can’t get a grasp on how to sort the entries by word length…

this returns the length but how to sort?
string.len(row.name) [import]uid: 65840 topic_id: 35342 reply_id: 140515[/import]

ingemar · January 27, 2013, 8:31am

Have you tried slapping on a "ORDER BY string.len(row.name)" at the end of the SQL statement? [import]uid: 70847 topic_id: 35342 reply_id: 140539[/import]

cindy.h1986 · January 28, 2013, 3:20am

Here’s what I have tried but they all throw error because of string.len()

Does anyone have any ideas how to make it work?

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY string.len(row.name) DESC LIMIT 500"

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY “…string.len(row.name)…” DESC LIMIT 500"

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY “…string.len(…“row.name”…)…” DESC LIMIT 500"

[code]
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ ORDER BY “…string.len(…“name”…)…” DESC LIMIT 500"

cindy.h1986 · January 28, 2013, 3:32am

It has come to the point where I’m thinking of inserting a new row to the db called “count”, perform a check of word length for entire db and insert that result so I can sort easier, but since the db is so huge and is downloaded from 3rd party I may have to do that every time.

Very frustrating…aaargh!!! [import]uid: 65840 topic_id: 35342 reply_id: 140605[/import]

ingemar · January 28, 2013, 3:33am

Ooops. I had a typo in my previous reply.
SQLite should be able to process this: "ORDER BY length(name)". [import]uid: 70847 topic_id: 35342 reply_id: 140606[/import]

cindy.h1986 · January 28, 2013, 5:45am

That worked:)

Now that it get “ORDER BY length(name)” is there a way to tell sqlite only pick names <= 9 char?

I have tested with "ORDER BY length(name) <= 9 " but that breaks sorting by “DESC”.

What am I doing wrong? [import]uid: 65840 topic_id: 35342 reply_id: 140617[/import]

ingemar · January 28, 2013, 5:57am

This should do it:

local sql = "SELECT \* FROM names ".. "WHERE name LIKE '"..first.."%"..second.."%"..third.."%' ".. "AND length(name) \<= 9 ".. "ORDER BY length(name) DESC LIMIT 500" [import]uid: 70847 topic_id: 35342 reply_id: 140620[/import]

cindy.h1986 · January 28, 2013, 6:18am

That did the trick

thanks [import]uid: 65840 topic_id: 35342 reply_id: 140623[/import]

ingemar · January 28, 2013, 6:27am

(^_^) [import]uid: 70847 topic_id: 35342 reply_id: 140626[/import]

cindy.h1986 · February 9, 2013, 11:14am

Continue my quest of knowledge…

When I do a query like this it works fine but if the result returns nothing the app crash because the array is empty. So I’m trying to do a check by adding a count variable that I can use later in my “if” statement so that if “count == 0” then save another table but it doesn’t save it, why?

[code]
function saveSearchToJSON()
local sqlite = require(“sqlite3”)
local path = system.pathForFile(“name_list.sqlite”, system.DocumentsDirectory)
local db = sqlite3.open(path)

– LIMIT is only there to because it’s just too many results atm…
– first, second, third are values from a textfield, nameLen, nameLimit are returned from a widget picker.
local sql = “SELECT * FROM names WHERE name LIKE '”…first…"%"…second…"%"…third…"%’ AND length(name) <="…nameLen…" ORDER BY length(name) DESC LIMIT "…nameLimit

local count = 0
local namesTable = {}
namesTable.names = {}
for row in db:nrows(sql) do
count = count + 1

namesTable.names[#namesTable.names + 1] = { name = row.name}
namesTable.count = { count = count}

end

– if search returns nothing then save this
if count == 0 then
namesTable.names = { name = “”}
namesTable.count = { count = 0}
end

savetable(namesTable, “names.json”)
end
[/code] [import]uid: 65840 topic_id: 35342 reply_id: 142208[/import]

ingemar · February 10, 2013, 8:14am

Hi again!

Sorry for the late reply, but Chinese New Year’s celebrations were in order (I’m living in Korea)

I see nothing wrong in your code that should cause the app to crash. I think there’s something else in your savetable() function that’s causing the crash.

Also, if your only intent is to save a list of names you can optimize your code like this:

function saveSearchToJSON()  
 local sqlite = require("sqlite3")  
 local path = system.pathForFile("name\_list.sqlite", system.DocumentsDirectory)  
 local db = sqlite3.open(path)  
  
 -- LIMIT is only there to because it's just too many results atm...  
 -- first, second, third are values from a textfield, nameLen, nameLimit are returned from a widget picker.  
 local sql = "SELECT \* FROM names "..  
 "WHERE name LIKE '"..first.."%"..second.."%"..third.."%' "..  
 "AND length(name) \<="..nameLen.." ORDER BY length(name) DESC "..  
 "LIMIT "..nameLimit  
  
 local namesTable = {}  
  
 for row in db:nrows(sql) do  
 namesTable[#namesTable + 1] = row.name  
 end  
  
 savetable(namesTable, "names.json")  
end

I’m assuming you’re using json.encode() to encode your table, and it’s possible to encode an empty table.
You can always get the number of names in the table with #namesTable without having a separate count variable. [import]uid: 70847 topic_id: 35342 reply_id: 142269[/import]

cindy.h1986 · February 10, 2013, 11:37am

Thanks for the reply, very helpful. I write my namesTable like that because I query other things too.

Heres the output from a valid search (mar)

{"count":{"count":2},  
 "names":[  
 {"name":"Mark"},   
 {"name":"Mary"}  
 ]  
}

not valid search (xxx,yyy,qqq etc ) return:

{  
 "names":[]  
}

it doesn’t save the count table so that is why I want to check:

 -- save this instead if count == 0  
 if count == 0 then  
 namesTable.names = { name = ""}  
 namesTable.count = { count = 0}  
 end

If I could save this then I wouldn’t get the error when I switch screens, because in my “showNamesScreen” I have the names listed along with a phrase “You found “…count…” names matching your search.”

So now that the I can’t get the above to work then I tried to check if the tables are nil before my for loop

local namesTable = loadTable("")  
if not namesTable["names"] == nil then  
 for i = 1, #namesTable do   
 -- newText....etc  
 end  
 count = namesTable["count"]  
 msg = newText......" you found"..count.."......etc"  
 else  
 count = 0  
 msg = newText...." you found"..count.."......etc"  
end

So now, instead of do all the checkups and the headaches that comes with this I’s rather save an alternative table if the count == 0 just to avoid the error. But I can’t get it to work. [import]uid: 65840 topic_id: 35342 reply_id: 142287[/import]

ingemar · February 10, 2013, 9:18pm

There are a few things in your last chunk of code which will not work. There are also a few things I don’t understand with your code, however before I comment on that let’s get to the bottom of why your namesTable isn’t working the way you want.

(This will take a few postings back-and-forth, but eventually it’ll work)

Given the code you posted in post #16 above, your namesTable *should* have a ‘count’ table even when the SQL returns no rows. If you get a json object like {"names":[]}, then something is going on.

if you add a print(json.encode(namesTable)) just before your savetable(), does the output in the terminal from print() still give the same result without a ‘count’?
[import]uid: 70847 topic_id: 35342 reply_id: 142331[/import]

cindy.h1986 · February 14, 2013, 2:01am

It works now, don’t know why but it does.

I render all my results into a scrollview and create a newText( ) for each result returned from my query. However, I get this error after about 5 queries.

Corona Simulator[24939:707] Runtime error  
 ?:0: attempt to call method 'setTextColor' (a nil value)  
stack traceback:  
 [C]: in function 'setTextColor'  
 ?: in function <?:3>  
 ?: in function <?:229>

The app doesn’t crash but I really bugs me that I can’t find why and where it happens. I use storyboard and it doesn’t matter if I purge or remove the screen or manually remove the objects.

Normally if you get errors they’re followed by a line in the code but this doesn’t say anything. I set textColor on my text objects but I commented them out to check if the error would go away but it doesn’t. I’ve tried purging, removing, auto purge…

So next I thought maybe I have too many locals so I created tables to hold text and vector shapes but that didn’t solve it…

So then I check my buttons, I have three. Comment out the text part and now it’s just an image. Still that error…

So now I have an app with no setTextColor reference what so ever. I commented out the results rendered in my scrollview, so now that is empty.

I’m using Corona 971.

Is there a text bug, simulator bug, widget button bug…?

[import]uid: 65840 topic_id: 35342 reply_id: 142721[/import]

ingemar · February 14, 2013, 2:47am

I remember seeing another post about this issue, and when I searched for it I saw that you were the OP

I’d say that’s a Widget bug. If I’m not completely mistaken the error above points to line 3 and line 229 of an internal Corona module. I’m assuming it’s the widget module.
I think it’d be a good idea to write a bug report and include the error above, also stating that you’re using the Scrollview widget.

FWIW I’ve reported other bugs in the widget module, and the guys responded that they’re re-writing the widget module to fix many issues. Hopefully the new module will be released soon…

As I see it there isn’t much we can do to fix it (unless somebody else has some suggestions).
I’d say that if the error isn’t crashing your app, and it’s behaving as it should despite the error, you *could* go ahead and ignore it if your app needs to be released. It’s your call.
I still think it’s important to report this issue just in case you’ve found some edge-case that nobody else has reported.
[import]uid: 70847 topic_id: 35342 reply_id: 142728[/import]

cindy.h1986 · February 9, 2013, 11:14am