Problem in Querying SQLite Database

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
montymay
Posts: 132
Joined: Thu Jul 18, 2013 5:23 am

Problem in Querying SQLite Database

Post by montymay » Thu Apr 04, 2019 5:05 am

Hello

For some reason the following code, which appears correct to my newbie brain, does not work. The mouseup handler belongs to a locked text field with list behavior. There is a connection to the SQLite database that has the table "cleDetails" and the variable tSubject is correctly populated with the text of the selected line, but, when the script runs, tInfo returns empty. However, for example, if I replace the tSubject variable in the 5th line with "'briefs',"

Code: Select all

put "SELECT * FROM cleDetails WHERE subject = 'briefs'" into tQuery
tInfo is correctly populated with all rows whose column named "subject" contains "briefs," but, as I said, tInfo returns empty when the tSubject variable contains "briefs".

Code: Select all

global gCLE_DatabaseID
on mouseup
     set the itemdel to tab
     put the selectedtext of me into tSubject
     put "SELECT * FROM cleDetails WHERE subject = '"&tSubject&"'" into tQuery
     put revDataFromQuery(tab,cr,gCLE_DatabaseID,tQuery) into tInfo
     set the dgText of grp "dgCLEindex" to tInfo    
end mouseup
Thanks for your help in spotting the error.

Monty May

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

Re: Problem in Querying SQLite Database

Post by bangkok » Thu Apr 04, 2019 9:51 am

Add

Code: Select all

answer tQuery
answer tInfo
Just to be sure that's not related to the datagrid or the query.

Then, if tInfo still doesn't contains what it should :

Code: Select all

    put "SELECT * FROM cleDetails" into tQuery
     put revDataFromQuery(tab,cr,gCLE_DatabaseID,tQuery) into tInfo
     answer tInfo
... Just to be sure that you didn't make a mistake when populating your table...

montymay
Posts: 132
Joined: Thu Jul 18, 2013 5:23 am

Re: Problem in Querying SQLite Database

Post by montymay » Thu Apr 04, 2019 11:29 pm

Thanks, Bangkok, for your response.

I already tried the Answer test, and both tSubject & tInfo still return empty. When I omit the WHERE clause, yes, the contents of the entire cleDetails table populates the tInfo variable, so the problem obviously lies in "WHERE subject = '"&tSubject&"'. I think I read that SQL is not case-sensitive, but making the column name in the SQLite table and the reference to it in the SQL query the same case also did not cause the script to work. :sad:

Monty

SparkOut
Posts: 2015
Joined: Sun Sep 23, 2007 4:58 pm

Re: Problem in Querying SQLite Database

Post by SparkOut » Fri Apr 05, 2019 12:26 am

What do you get displayed when you answer tQuery?
Does it look like the tSubject variable has been populated properly so you get a valid-seeming WHERE clause?
The problem might be using the selectedText to populate the tSubject variable. It may have other data, perhaps spaces or line endings included. If one of your selectable text lines is supposed to be populated with "briefs" you could

Code: Select all

answer tSubject & "looks like briefs but is it ACTUALLY briefs? :" && tSubject is "briefs"
which will tell you if the variable contains the right data when you retrieve the selectedText. It might be solvable in the query by using LIKE instead of = but probably needs a data validation before you construct the query.

montymay
Posts: 132
Joined: Thu Jul 18, 2013 5:23 am

Re: Problem in Querying SQLite Database

Post by montymay » Fri Apr 05, 2019 2:37 am

Thanks, SparkOut, for your response.

When I check the contents of tQuery, I get (see image at bottom):
Image
So, it looks as though the tSubject variable was populated correctly, but, as you suggest, the selectedtext has an invisible something extra.

Following your suggestion, I modified the script as follows and got the indicated results:

Code: Select all

     answer "briefs" is "briefs"  -- true
     answer tsubject is "briefs" --true
answer tSubject & " looks like briefs but is it ACTUALLY briefs? :" && tsubject is "briefs" --false!
     put "SELECT title,subject,year,keywords,author FROM cleDetails WHERE subject = '"&tSubject&"'" into tQuery
Why would the same statement be true and then be false?
In any event, switching to LIKE finally worked, but I still wish I could use the = operator.

Monty
Attachments
capture1.PNG
capture1.PNG (6.04 KiB) Viewed 504 times

SparkOut
Posts: 2015
Joined: Sun Sep 23, 2007 4:58 pm

Re: Problem in Querying SQLite Database

Post by SparkOut » Tue Apr 09, 2019 8:54 pm

Errr, try checking

Code: Select all

answer tSubject && "looks like briefs but is it ACTUALLY briefs? :" && (tSubject is "briefs")
the parentheses will resolve the boolean test and answer true if the content of the tSubject variable actually matches "briefs". I suspect that the answer will still be false though, because of some invisible content, like an extra trailing carriage return or something.

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”