Speak results from SQL query

Hi all,

I have managed to create some simple speakable items scripts which do a range of things (like put display to sleep, say some random stuff, show the weather,…). I am thinking about a use case which I think will be very usefull.

What I want to do is fire off a script by saying a command, this script itself will fire off a SQl query to a remote database and the results of the SQl query would be spoken. Managed to get this working on the iPhone with Siri, but struggling to get this working on the Mac.

Example:

I say: open orders
Mac comes back with: which customer
I say: Thinkinc
Script uses “thinkinc” as part of the SQL query (where customer = ‘thinkinc’) and results are spoken (put in the “say” part).

Do you guys think this can be done?

Welcome to MacScripter.

Of course it can be done! :slight_smile:

It is just a matter of time, but head on this fine article: MacScripter / Talkin’ The Talk with Apple’s Speech Tools

As for the parsing of the result from the sql query, you should find bits and pieces around here, and some friendly souls too!

The keyword for saying stuff in AppleScript is say


say "Welcome to Macscripters" using {"Victoria"}

Tx, saw those point and already managed to get some of it working.

The SQL part is worrying me. Can not find a way to get that running from a applescript. Any ideas?

If it cannot be done from that I might have to put Python In the game (with pyodbc via macports). Unfortunately could not get that working as my python script can not find the pyodbc object eventhough its installed. Weird stuff.

do shell script to log on and execute your sql-query.

Do shell script has “/” as root, and doesn’t give you your usual environment, then you’d have to start by executing

do shell script ". $HOME/.bashrc "

but you’ll have to cd to $HOME.

Sometimes this does the trick for running things from Apple Script when it works in Terminal

do shell script "/bin/bash -c \" echo $HOME\" ; echo `pwd`"

(Then more of the original bash shell is loaded.)

We don’t usually do like that, but set it up, up front, by the usual command grouping by “;” inside the string, when you need an environment variable to have a value, like $SQLPATH, or whatever.

Now you also should see how you quote double quotes within a string, you don’t have to quote single quotes, and normal shell rules for quoting applies. When passing variables often concatenated into the string we use quoted form to retain the spaces.

like this


set mytext to "s o m e    s t u f f"
set theResult to do shell script "sed 's/[[:alnum:]]/XXX/g' <<<" & quoted form of mytext
# here the variable works like it has been piped into the script like with a "<"
do shell script "echo " & quoted form of theResult & "| sed 's/X//g' |tr -s \" \""
# the variable here, almost works like a shell variable. then we use a normal pipe.

if the do shell script command executes something with an exit code unequal to 0 then the variable where you may extract any values are not set. (undefined) you can check for this with a on error try block, like this. (So it is a good idea to set any return values to null up front for instance.

set theMessage to "I'll never get out of the script"

try
	set theResult to do shell script "echo " & quoted form of theMessage & " ; false "
on error
	log theResult
end try

A usual way to figure out if it went ok, or not is to use a construct like this, when the command in speak doesn’t return any output of its own.


try
	set theResult to do shell script "false && echo \"true\" || echo \"false\""
on error
	log theResult
end try


DJ explains how to do a sql query:

http://macscripter.net/viewtopic.php?pid=147891#p147891

Tx for the quick help guys. My database is a bit exotic and is in the cloud. I need a dsn which points to the odbc driver I believe and find a shell script utility specifically for my database (reading all the posts).

Tx again guys.