Page 1 of 1
searching for "todo" in SQLite field fails
Posted: Sun Oct 04, 2020 3:47 pm
by marksmithhfx
I'm having a very strange problem and wonder if anyone has an sqlite database handy they could try duplicating it on. When I query any field on the word "todo" (the string is in the field) nothing is found. I can query on anything but when I change the content of a field to "todo" the program fails to find it. If I insert a hyphen (to-do) or add or change a letter (dodo, todoo) , it works fine, but if change it back to "todo" it is never found. This is in any of the fields in the db. I also have a simple test database and app I can share if anyone is interested. It has 4 fields and I was using it to test simple and compound queries. Quite puzzling.
Thanks
Mark
Re: searching for "todo" in SQLite field fails
Posted: Sun Oct 04, 2020 4:36 pm
by bangkok
Sure. Send the database so we can look at its structure.
And what about the queries you're trying ? What are they ? We need them too.
How did you write the data into the db ? With an editor ? By queries ?
Re: searching for "todo" in SQLite field fails
Posted: Sun Oct 04, 2020 5:50 pm
by marksmithhfx
bangkok wrote: ↑Sun Oct 04, 2020 4:36 pm
Sure. Send the database so we can look at its structure.
And what about the queries you're trying ? What are they ? We need them too.
Thanks Bangkok,
This is just a test db so only 10 records. Everything was created (including the sample records) with buttons on the card so you can see the code. The program looks for testquery.sqlite (included) in your documents folder. Here are many of the queries I tried, all of them succeeded in finding the correct records with the exception of anything that has "todo" in it. The entries I am testing here were intended to be self-described categories so often would be brief 1-2 words things like project names. So I have not put a lot of text in the fields.
SELECT * from table1 WHERE category="todo"
SELECT * from table1 WHERE category="nothing"
SELECT * from table1 WHERE category="salt"
SELECT * from table1 WHERE category=""
SELECT * from table1 WHERE category="todo"
SELECT * from table1 WHERE category="dodo"
SELECT * from table1 WHERE category="todo"
SELECT * from table1 WHERE category="black"
SELECT * from table1 WHERE category="black"
SELECT * from table1 WHERE category="toodo"
SELECT * from table1 WHERE category="todo"
SELECT * from table1 WHERE category="PHAC"
SELECT * from table1 WHERE complete="true"
SELECT * from table1 WHERE complete="false"
SELECT * from table1 WHERE complete="todo"
SELECT * from table1 WHERE complete="to-do"
SELECT * from table1 WHERE itemdate="10/4/20"
SELECT * from table1 WHERE itemdate="10/4/20" AND category="to-do"
SELECT * from table1 WHERE itemdate="10/4/20" AND category="nothing"
SELECT * from table1 WHERE itemdate="10/4/20" AND category=""
SELECT * from table1 WHERE itemdate="10/4/20" AND category="nothing"
SELECT * from table1 WHERE itemdate="10/4/20" AND category="PHAC"
SELECT * from table1 WHERE itemdate="10/4/20" AND category=“Swift Course"
SELECT * from table1 WHERE itemdate="10/4/20" AND category="to-do"
SELECT * from table1 WHERE itemdate="10/4/20" AND category="todo"
SELECT * from table1 WHERE itemdate="10/4/20" AND category="todoo"
SELECT * from table1 WHERE itemdate="10/4/20" AND complete="todo"
SELECT * from table1 WHERE itemdate="10/4/20" AND complete="false"
Re: searching for "todo" in SQLite field fails
Posted: Sun Oct 04, 2020 6:16 pm
by Klaus
Hi Mark,
if -> todo is not the only content of a field in your database, you should use LIKE!
https://www.w3schools.com/sql/sql_like.asp
That will find everything that CONTAINS the string "todo" (among other text). Maybe this is the problem?
Code: Select all
...
SELECT * from "table1" WHERE category LIKE "%todo%"
...
Best
Klaus
Re: searching for "todo" in SQLite field fails
Posted: Sun Oct 04, 2020 7:46 pm
by AxWald
Hi,
this works flawlessly in your db:
Code: Select all
put "SELECT * FROM table1 WHERE (`complete` = 'todo') OR (`category` = 'todo');" into tSQL
Tested with LC 9.6/64 on Win10:
Code: Select all
4 todo This is another reminder 10/4/20 todo
5 todo This is important 10/4/20 nothing
The fields containing "todo" are both text fields, so "todo" must be quoted in your tSQL. Use single quotes for strings.
Both fields have names that smell like "reserved words". if so, these must be quoted, too. Use Graves for reserved words.
If you try above example in your db manager (with removing quotes) you'll see that it's the lacking single quotes around "todo" that don't give results. In any way, proper quoting avoids all troubles :)
Have fun!
Re: searching for "todo" in SQLite field fails
Posted: Sun Oct 04, 2020 10:37 pm
by marksmithhfx
AxWald wrote: ↑Sun Oct 04, 2020 7:46 pm
this works flawlessly in your db:
Code: Select all
put "SELECT * FROM table1 WHERE (`complete` = 'todo') OR (`category` = 'todo');" into tSQL
The fields containing "todo" are both text fields, so "todo" must be quoted in your tSQL. Use single quotes for strings.
Both fields have names that smell like "reserved words". if so, these must be quoted, too. Use Graves for reserved words.
If you try above example in your db manager (with removing quotes) you'll see that it's the lacking single quotes around "todo" that don't give results. In any way, proper quoting avoids all troubles
Have fun!
Axwald, thank you so so much. I would never have thought to try single quotes. I switched to single quotes and all the queries succeeded, including the one previous exception, 'todo'. Strange how it only failed on that term. Had me pulling out my hair so I am so relieved you knew or found the solution. That is what is so great about these forums -- so many here have experience that can fill in for our lack of it.
I've rerun all my queries and they work fine now. I did check before for reserved words and none of the column names were reserved so need to `quote` those I don't think. And the trailing ";" doesn't seem to be necessary with SQLite either (although possibly it might be for other SQL applications). I am using this form of the syntax.
SELECT * FROM table1 WHERE complete = 'todo' AND itemdate ='10/4/20', parameterized on the query terms, of course.
Forever in your gratitude,
Mark
Re: searching for "todo" in SQLite field fails
Posted: Sun Oct 04, 2020 10:42 pm
by marksmithhfx
Klaus wrote: ↑Sun Oct 04, 2020 6:16 pm
if -> todo is not the only content of a field in your database, you should use LIKE!
https://www.w3schools.com/sql/sql_like.asp
That will find everything that CONTAINS the string "todo" (among other text). Maybe this is the problem?
Code: Select all
...
SELECT * from "table1" WHERE category LIKE "%todo%"
...
Good point Klaus, and I thank you for pointing out how to search for strings within text. The app I am working on will actually need that for a different feature and voila, there it is. Appreciated!!
The problem I was having turned out to be related to quoting. I was using double quotes which ironically worked for everything I tried except one term. When I switched to single quotes all the queries worked.
Amazing what kind of help you can find here. I appreciate it so much.
Mark