Page 1 of 1
SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 1:51 am
by quailcreek
Hi All,
I'm having a little trouble with the syntax of defining the tSQLStatement. I'm getting a db error regarding the WHERE part of the statement. What am I doing wrong?
Code: Select all
on mouseUp
put fld "theID" into tID
put specialFolderPath("documents") & "/MyTestDB.sqlite" into tDatabaseFile
put revOpenDatabase("sqlite",tDatabaseFile) into sDatabaseID
if tID is not empty then
put "SELECT MyInfo(MyName,MyType, WHERE theID = tID)" into tSQLStatement
revExecuteSQL sDatabaseID,tSQLStatement
end if
answer the result
end mouseUp
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 2:08 am
by Simon
Hi Tom,
Can you show what is actually in tID?
Quick guess it's probably missing single or double quotes (I forget which ones to use).
Simon
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 4:16 am
by quailcreek
Hi Simon,
tID will be an number. I'm trying to query for the information in a row based upon the unique ID number in the row. I might be using the wrong query structure though.
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 12:23 pm
by Klaus
Hi Tom,
you are supplying the STRING tID and not the content of that variable to the SQL string!
Code: Select all
...
if tID is not empty then
## put "SELECT MyInfo(MyName,MyType, WHERE theID = tID)" into tSQLStatement
put "SELECT MyInfo(MyName,MyType, WHERE theID =" & tID & ")" into tSQLStatement
revExecuteSQL sDatabaseID,tSQLStatement
end if
...
Best
Klaus
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 4:26 pm
by quailcreek
Hi Klaus,
Thanks for the reply. I tried ' " & tID & " ' and " & tID & " and ' & tID & '. None of these worked. When I "answer the result" I get (near “WHERE”: syntax error).
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 4:33 pm
by Klaus
Hi Tom,
hm, not being an SQL expert, but should that not be something like this:
...
put "SELECT MyInfo,MyName,MyType FROM TABLENAME WHERE theID =" & tID into tSQLStatement
...
?
I guess MyInfo is ALSO a db column you want to retrieve?
Best
Klaus
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 4:53 pm
by quailcreek
Hi Klaus,
Actually, MyInfo is the name of the table.
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 6:08 pm
by quailcreek
Here's what I ended up with:
Code: Select all
if tID is not empty then
put "SELECT MyName,MyType FROM MyInfo WHERE TheID = " & tID &" " into tSQLStatement
put revDataFromQuery(,,sDatabaseID,tSQLStatement) into tList
end if
Thanks everybody.
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 6:55 pm
by Klaus
quailcreek wrote:Hi Klaus,
Actually, MyInfo is the name of the table.
Ah, OK, was not sure!
But why the space at the end of your SQL string?
...
put "SELECT MyName,MyType FROM MyInfo WHERE TheID = " & tID
&" " into tSQLStatement
...
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 7:16 pm
by quailcreek
Good catch, Klaus. The space was there just so I could read the code easier. I can see that it really shouldn't be there. Thanks.
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 7:36 pm
by phaworth
You can also do this:
put "SELECT MyName,MyType FROM Myinfo WHERE theID =:1" into tSQLStatement
revExecuteSQL sDatabaseID,tSQLStatement,"tid"
Pete
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 8:07 pm
by quailcreek
Hi Pete,
Does (theID =:1") mean where the query evaluates to true?
Also, how would I get the result of the query? I tried "answer the result" but no-joy.
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 8:37 pm
by phaworth
Sorry, took the revExecuteSQL form your earlier post, should be revDataFromQuery
put "SELECT MyName,MyType FROM Myinfo WHERE theID =:1" into tSQLStatement
put revDataFromQuery(,,sDatabaseID,tSQLStatement,"tid") into tData
if tData begins with "revdberr" then
--insert your error handling code here
end if
The ":1" tells SQL to get the value from a variable you supply in the reDataFromQuery call, that's why it has "tid" as a parameter. Since you already have a variable containing the id, that's a convenient way to simplify the SELECT statement.
You can find out more about the ":1" syntax in the dictionary entry for revDataFromQuery.
Pete
Re: SQLite WHERE syntax error
Posted: Fri Apr 17, 2015 10:28 pm
by quailcreek
Thanks, Pete. That make a lot of sense. It is a bit simpler.