Page 1 of 1
Problem in Querying SQLite Database
Posted: Thu Apr 04, 2019 5:05 am
by montymay
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
Re: Problem in Querying SQLite Database
Posted: Thu Apr 04, 2019 9:51 am
by bangkok
Add
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...
Re: Problem in Querying SQLite Database
Posted: Thu Apr 04, 2019 11:29 pm
by montymay
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.
Monty
Re: Problem in Querying SQLite Database
Posted: Fri Apr 05, 2019 12:26 am
by SparkOut
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.
Re: Problem in Querying SQLite Database
Posted: Fri Apr 05, 2019 2:37 am
by montymay
Thanks, SparkOut, for your response.
When I check the contents of tQuery, I get (see image at bottom):
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
Re: Problem in Querying SQLite Database
Posted: Tue Apr 09, 2019 8:54 pm
by SparkOut
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.