SQLite WHERE syntax error

[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

[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

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

[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

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

[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

[quailcreek]

Hi Klaus,
Actually, MyInfo is the name of the table.

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

[Klaus]

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

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

[phaworth]

You can also do this:

put "SELECT MyName,MyType FROM Myinfo WHERE theID =:1" into tSQLStatement
revExecuteSQL sDatabaseID,tSQLStatement,"tid"

Pete

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

[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

[quailcreek]

Thanks, Pete. That make a lot of sense. It is a bit simpler.