I’ve made the example database as described on the man page of sqlite3 http://developer.apple.com/documentation/Darwin/Reference/ManPages/man1/sqlite3.1.html and it works fine. The problem is that I can’t get the do shell script command in applescript to work.
In the terminal
returns the records as a | delimited string, as it should. (The ; inside the single quote looks weird but it is correct.)
But the exact same command in applescript returns an error:
do shell script "sqlite3 mydata.db 'select * from memos;'"
--SQL error: no such table: memos
set strCommand to "select * from memos;"
return do shell script "sqlite3 mydata.db " & quoted form of strCommand
--SQL error: no such table: memos
Any help would be greatly appreciated for I’m getting slightly obsessed.
Thank you, I was being sloppy with my path. I got it working in Script Editor, then had a bit of a struggle getting it working in xcode, but it seems to end happy. For anyone alse wanting to do something similar, here’s some of my script. BTW, I made my database with the free SQLite Database Browser http://sqlitebrowser.sourceforge.net/ and then added it to my xcode project in the Resources folder.
tell DBManager
set numOfRecs to setSelection(my pathToDatabase() & "mydata.db", "SELECT * FROM Memos;")
end tell
on pathToDatabase()
set appPath to (path to me from user domain) as text
return (appPath & "Contents:Resources:") as text
end pathToDatabase
script DBManager
property lstHeaders : {} as list
property lstRecords : {} as list
on getHeaders()
return lstHeaders
end getHeaders
on getRecords()
return lstRecords
end getRecords
on reset()
set lstHeaders to {} as list
set lstRecords to {} as list
end reset
on getValues(strColumnName)
set lstResults to {}
repeat with i from 1 to count of lstHeaders
if item i of lstHeaders is equal to strColumnName then
repeat with myItem in lstRecords
set lstResults to lstResults & item i of myItem
end repeat
end if
end repeat
return lstResults
end getValues
on setSelection(dbPath, strSQL)
reset()
set intResult to 0
set str to (do shell script "sqlite3 -header " & quoted form of (POSIX path of dbPath) & " " & quoted form of strSQL)
set lst to every paragraph of str
if lst is not equal to {} then
set intResult to ((count of lst) - 1)
set AppleScript's text item delimiters to {"|"}
set lstHeaders to every text item of (first item of lst)
repeat with myItem in (rest of lst)
copy (every text item of myItem) to the end of lstRecords
end repeat
set AppleScript's text item delimiters to {" "}
end if
return intResult
end setSelection
end