OK, try this modified version of the handlers I use. SQLExecute is for executing any old SQL, returning any result as just text.
SQLExecuteSelect is for executing a select statement and returning a list of lists (rows of field values). It uses a custom separator between the fields, something that should never appear in the actual data. It also adds a separator to the end of each row, so the row end can be distinguished from new lines tat appear in the data.
property sqlite3Path : "/usr/bin/sqlite3"
property fieldSeparator : character id 2
property recordSeparator : character id 3
on SQLExecute(databasePath, sqlCommand)
set shellCommand to "echo " & (quoted form of sqlCommand) & " | " & sqlite3Path & space & quoted form of databasePath
set resultText to do shell script shellCommand
return resultText
end SQLExecute
on SQLExecuteSelect(databasePath, sqlCommand)
-- 2008 BareFeet http://www.tandb.com.au
set sqlCommandWithSeparators to "select *, '" & recordSeparator & "' from (" & sqlCommand & ");"
set shellCommand to "echo " & (quoted form of sqlCommandWithSeparators) & " | " & sqlite3Path & space & "-separator " & (quoted form of fieldSeparator) & space & quoted form of databasePath
set recordsText to do shell script shellCommand
script speedyObject
property recordList : {}
end script
if recordsText is not "" then
set oldDelimiters to AppleScript's text item delimiters
set AppleScript's text item delimiters to (fieldSeparator & recordSeparator & return)
set speedyObject's recordList to text items in recordsText
set last item in speedyObject's recordList to text 1 thru -(1 + (length of (fieldSeparator & recordSeparator))) in last item in speedyObject's recordList
set AppleScript's text item delimiters to fieldSeparator
set recordCount to (count speedyObject's recordList)
repeat with recordN from 1 to recordCount
set item recordN in speedyObject's recordList to text items in item recordN in speedyObject's recordList
end repeat
set AppleScript's text item delimiters to oldDelimiters
end if
return speedyObject's recordList
end SQLExecuteSelect
To test it:
on run
set databasePath to "MyTestDatabase.sqlite"
set createData to "
create table if not exists Contacts(Id integer primary key, Company text collate nocase, Name text collate nocase, URL text collate nocase);
delete from Contacts;
insert into Contacts(Name, Company, URL) values('Tom', 'BareFeet', 'http://www.tandb.com.au');
insert into Contacts(Name, Company, URL) values('Steve', 'Apple', 'http://www.apple.com');
"
SQLExecute(databasePath, createData)
set myQuery to "select Name, Company from Contacts where URL not null"
SQLExecuteSelect(databasePath, myQuery)
end run
which returns:
{{“Tom”, “BareFeet”}, {“Steve”, “Apple”}}
Or to try it with your database:
on run
set DBs to "/Users/bart/Desktop/Phone.db"
set Query_String to "SELECT * FROM Numbers"
SQLExecuteSelect(DBs, Query_String)
end run
Please let me know how the speed compares to your other methods. At the very least, I think this method will cater for all data, whereas the other methods won’t (eg if the data includes pipes or returns).
This method (and others mentioned here) return all values as strings. That’s fine when you’re dumping the result directly into the contents of a table view. But I should point out that it’s not so good if you are processing the data and need to discern strings from integers from nulls etc.
Tom
BareFeet