I’m looking to talk to a Microsoft SQL Server with AppleScript. I need AppleScript to search for a record, create a new record, add data to a record and retrieve information from a record. Has anyone been down this road? I have found things like MacSQL that are discontinued…
A very common way to do this (with postgresql and mysql) is installing a command line utility that can access the server and use do shell script to run the shell command and fetch the results.
For example in MySQL:
set theQuery to "Select column_name from table where other_column = 'somevalue'"
set MySQL to "/usr/local/mysql/bin/mysql -hserver -uroot -ppassword -D database -e "
set theResult to do shell script mysql & quoted form of theQuery
I am working with an MS-SQL server. But it’s not with “plain” Applescript.
I am using 2 things: The SQL Driver from Actual Technologies (www.actualtech.com) to talk to the DB
and a free Objective-C framework called “ODBCKit” (www.druware.com)
So I have built a Cocoa application that can use the ODBCKit functions to query the DB, and then step through the result set. It definitely works. I have only queried but not written commands to the server, but that’s only because my application doesn’t require writing, only reading. But ODBCKit will do that too of course. I can run “raw commands” or execute “EXEC” stored functions passing a variable to it.
For MS-SQL, I think the Actual Tech driver is a necessity, I hadn’t found anything else that would connect to it for Macs. There is an “iobctest” unix function provided in OSX, but it’s just a test function and worthless for anything other than simple tests.
edit: Oh and btw, the ODBCKit has some bug in v0.5.0, so try to get v0.4.1 or v0.4.2