searching for "todo" in SQLite field fails
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- VIP Livecode Opensource Backer
- Posts: 931
- Joined: Thu Nov 13, 2008 6:48 am
- Location: London, UK
searching for "todo" in SQLite field fails
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
Thanks
Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS
Targets: Mac, iOS
Re: searching for "todo" in SQLite field fails
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 ?
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 ?
-
- 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
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 316 times
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS
Targets: Mac, iOS
Re: searching for "todo" in SQLite field fails
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?
Best
Klaus
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%"
...
Klaus
Re: searching for "todo" in SQLite field fails
Hi,
this works flawlessly in your db:
Tested with LC 9.6/64 on Win10:
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!
this works flawlessly in your db:
Code: Select all
put "SELECT * FROM table1 WHERE (`complete` = 'todo') OR (`category` = 'todo');" into tSQL
Code: Select all
4 todo This is another reminder 10/4/20 todo
5 todo This is important 10/4/20 nothing
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!
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!
-
- 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
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.AxWald wrote: ↑Sun Oct 04, 2020 7:46 pmthis works flawlessly in your db:The fields containing "todo" are both text fields, so "todo" must be quoted in your tSQL. Use single quotes for strings.Code: Select all
put "SELECT * FROM table1 WHERE (`complete` = 'todo') OR (`category` = 'todo');" into tSQL
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!
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
Targets: Mac, iOS
-
- 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
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!!Klaus wrote: ↑Sun Oct 04, 2020 6:16 pmif -> 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%" ...
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
Targets: Mac, iOS