I just used Kevin’s wonderful interface “MySQL4AppleScript” (http://www.yvs.eu.com/otherproducts.html) and the Find Commands example from Apple to figure out how to put a return from a database query into a table.
Now, I’ve been working for hours trying to figure out how to take the answer and put the ID and Name fields in seperate columns.
In other words, the “do shell script” will return an answer with 2 fields selected. One field goes into one column, and the other field goes into another column.
Here’s the script that returns each record into one field:
using terms from application "Xcode"
property commandsDataSource : missing value
property commandNames : {}
property mysql : load script (file "Mac:Users:petersampson:tryitout:mysql.applescript")
on will finish launching theObject
set commandNames to every paragraph of mysql's SelectDataFromTable("", "colastname,id", "\`cards\`", "")
end will finish launching
on awake from nib theObject
if name of theObject is "commands" then
set commandsDataSource to make new data source at end of data sources with properties {name:"commands"}
make new data column at end of data columns of commandsDataSource with properties {name:"command"}
make new data column at end of data columns of commandsDataSource with properties {name:"description"}
set data source of theObject to commandsDataSource
end if
end awake from nib
on launched theObject
show window "main"
end launched
on clicked theObject
if name of theObject is "find" then
findCommands(window of theObject)
end if
end clicked
on findCommands(theWindow)
append commandsDataSource with commandNames
end findCommands
end using terms from
In your findCommands handler you need to have converted your commandNames from a list of tab delimited text into a list of lists.
to do that try:
property theList : missing value
set AppleScript's text item delimiter to tab
set theList to {}
-- start at 2 because the first row is the titles, we don't want to add them to the table.
repeat with i from 2 to the count of commandNames
set rowData to every text item of (item i of sqlQueryResults)
copy the rowData to the end of theList
end repeat
then in your find command handler
on findCommands(theWindow)
append commandsDataSource with theList
end findCommands
In my code I also make the table columns in the table view each time so that they are dynamically generated from the results of the sqlquery.
I think most of your code also needs to wrapped in tell window “main” end tell blocks for it to work.
I delete the old ones by
delete every table column of table view "tableviewqueryresults" of scroll view "scrollviewqueryresults"
I then iterate through the list of columns and do the following.
make new table column at the end of the table columns of table view "tableviewqueryresults" of scroll view "scrollviewqueryresults" with properties {name:ColumnTitle, identifier:ColumnTitle, editable:true}
This suggested code should also be wrapped by tell window “main” end tell blocks.
Kevin, thanks! Yarra Valley Software has a new fan. . .
Walking through the code you posted has really helped clarify things.
The working draft is below. I’m sure it’s not as “clean” and efficient as possible, but it’s a start for a newbie coder.
property commandsDataSource : missing value
property commandNames : {}
property mysql : load script (file "Mac:Users:petersampson:tryitout:mysql.applescript")
on will finish launching theObject
set commandNames to every paragraph of mysql's SelectDataFromTable("", "id,colastname", "\`cards\`", "")
end will finish launching
on awake from nib theObject
if name of theObject is "tableView" then
set commandsDataSource to make new data source at end of data sources with properties {name:"commands"}
make new data column at end of data columns of commandsDataSource with properties {name:"command"}
make new data column at end of data columns of commandsDataSource with properties {name:"description"}
set data source of theObject to commandsDataSource
set AppleScript's text item delimiters to tab
set theList to {}
-- start at 2 because the first row is the titles, we don't want to add them to the table.
repeat with i from 2 to the count of commandNames
set rowData to every text item of (item i of commandNames)
copy the rowData to the end of theList
end repeat
append commandsDataSource with theList
end if
end awake from nib
on launched theObject
show window "main"
end launched
Thanks again, Kevin.
Cheers.
PS Sorry about posting in the wrong forum. I’ll put these in Applescript Studio next time.