SQLite syntax error

Scripting Forums AppleScript | Mac OS X
#1

The following statement produces an error:

sqlite3 ‘/Users/MSPS/DataBases/SQLite/Clients/Clients.db’ ‘SELECT * FROM Clients WHERE Type = ‘Client’’

While statement works

sqlite3 ‘/Users/MSPS/DataBases/SQLite/Clients/Clients.db’ ‘SELECT * FROM Clients WHERE rowID=‘1’’

it seems that whenever I try to query a VARCHAR column I get an error “No such Column: Client”

How to I make this work?

PS all of this is being called from applescript in the usual way

Model: MacBook
AppleScript: 2.3
Browser: Safari 531.21.10
Operating System: Mac OS X (10.6)

#2

If I got your code right, it is amazing that anything works. In

‘SELECT * FROM Clients WHERE Type = ‘Client’’

the inner string marks around client need to be escaped. If typed the way you did here, the shell argument ends with “.Type = '”

Have a look at the following apple script code:

set arg to “SELECT * FROM Clients WHERE rowID=‘1’”
quoted form of arg

The result is
“‘SELECT * FROM Clients WHERE rowID=’\‘‘1’\’‘’”

Difficult to read since this is a combination of escaping for the shell and escaping for AppleScript. But using “quoted form” this way should help:


set prog to "sqlite3"
set arg1 to "/Users/MSPS/DataBases/SQLite/Clients/Clients.db"
set arg2 to "SELECT * FROM Clients WHERE Type = 'Client'"
set cmd to prog & " " & quoted form of arg1 & " " & quoted form of arg2
do shell script cmd

(The entire AppleScript string for cmd here is: ““sqlite3 ‘/Users/MSPS/DataBases/SQLite/Clients/Clients.db’ ‘SELECT * FROM Clients WHERE Type = ‘\’‘Client’\’‘’””)

Regards, Jürgen