Problem in Querying SQLite Database

[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

[bangkok]

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]

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

[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.

[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

[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.