Populating a table from a do shell script mySQL query


MacScripter.net is great! Thanks to the admins . . .

I’ve been a Mac user since 1984, however, I have only recently bothered with AppleScript. Wow, have I missed out!

I work in UNIX, PHP, and Web development mostly, but wanted to try creating in AppleScript Studio.

I have a window, with an NSTableView and button, and want to populate it with a column from a MySQL database running locally. (reason: eventually, to create a program that will take the data in, manipulate it, and send it to several programs (ie. accounting, FileMaker, etc.)

To start out, I need to populate the table with the result of the “Do Shell Script” to get the data. I’ve tried reading other posts, downloading scripts like Kevin’s wonderful MySQL4Applescript, and others, but haven’t had success.

Here is the script. I’m sure you will recognize parts of it from the Table example and a post by jonn8:

property tableData : {}

on clicked theObject
set mysql_user to “user”
set mysql_host to “localhost”
set mysql_pw to “password”
set mysql_db to “database”
set mysql_table to “table”
set mysql_key_field to “mysql_key_field”
set mysql_key_data to “mysql_key_data”
set sql_query to “SELECT column FROM table”
set the_data to (do shell script “/usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p” & mysql_pw & " -e “” & sql_query & “”")
return the_data

tell window of theObject
	set tableData to the_data
	tell table view "Table View" of scroll view "Scroll View for Table" to update
end tell

end clicked

on cell value theObject row theRow table column tableColumn
set theValue to “”
if (count of tableData) ? theRow then
set theRowData to item theRow of tableData
set theID to name of tableColumn
if theID is “Name Column” then
set theValue to the_data of theRowData
end if
end if

return theValue

end cell value

on number of rows theObject
return count of tableData
end number of rows

Thanks for the help! Coming to Hawaii? Send me a message and I’ll tell you where to eat, stay, etc! :lol:

Thanks for reading the post. . .

I found the answer. Look at “Command Line example.”

I looked at it earlier, but just figured it out.