searching for "todo" in SQLite field fails

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

searching for "todo" in SQLite field fails

Post by marksmithhfx » Sun Oct 04, 2020 3:47 pm

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
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: searching for "todo" in SQLite field fails

Post by bangkok » 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.

How did you write the data into the db ? With an editor ? By queries ?

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: searching for "todo" in SQLite field fails

Post by marksmithhfx » Sun Oct 04, 2020 5:50 pm

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"
Attachments
testquery.zip
(3.53 KiB) Downloaded 284 times
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: searching for "todo" in SQLite field fails

Post by Klaus » Sun Oct 04, 2020 6:16 pm

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

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: searching for "todo" in SQLite field fails

Post by AxWald » Sun Oct 04, 2020 7:46 pm

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!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: searching for "todo" in SQLite field fails

Post by marksmithhfx » Sun Oct 04, 2020 10:37 pm

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
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: searching for "todo" in SQLite field fails

Post by marksmithhfx » Sun Oct 04, 2020 10:42 pm

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
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

Post Reply

Return to “Databases”