Can't get shell script to talk to AppleTalk script in Automator

Question

I'm trying to make an Automator Quick Action that will:

1. Take a highlighted word,
2. look it up in a database,
3. allow the user to pick from among the hits returned by the database, and
4. put the user's selection in the clipboard.

I had it sorta working, but some of the records returned for user selection were showing up NULL. I squashed that bug in the database, but now, nothing is apparently being passed between the two scripts: I get "The action 'Run AppleScript' encountered an error: 'List is empty.'" Every. Single. Time.

Except it isn't.

I can copy the shell script and paste it into a Terminal, and it produces expected results: a number of lines of text.

I can insert >/tmp/x.txt to the script, run it via the QuickAction, and see that the script is sending expected results to stdout, which I assume is what is passed to the AppleScript.

Here is the two scripts, as shown in Automator:

I can change the output of the database script to this, to capture the output in a file:

/usr/local/bin/mysql --user=QuotesBrowser --password=resworBsetouQ --host localhost --database=Quotes --raw --silent --batch >/tmp/output.txt <<-EnDoFsQl SELECT QuoteAttributed FROM Quotes WHERE Quote LIKE "%$*%" ORDER BY RAND() LIMIT 20 EnDoFsQl 

Then, when I highlight the word "kiss" in Mail.app message, and run the Quick Action via the contextual menu, I get the following in /tmp/output.txt:

I am not now, and never have been, a girl friend of Henry Kissinger. -- Gloria Steinem My lips pressed themselves involuntarily to hers <97> a long, long kiss, burning intense <97> concentrating emotion, heart, soul, all the rays of life's light, into a single focus. -- Bulwer Satire died in this country the day Henry Kissinger won the Nobel Peace Prize. -- Tom Lehrer On a planet with a rapidly changing climate, Canada should be figuring out now how to wind down carbon-intensive resource extraction. Otherwise we may soon find that we're producing masses of stuff we can't sell. But anyone who even hints at this idea in Canada is regarded as nuts. Any politician aspiring to national leadership who says such a thing can kiss his or her political career goodbye. -- Thomas Homer-Dixon Kiss me Kate? Petruchio? The taming of the shrew -- William Shakespeare It is now 10 p.m. Do you know where Henry Kissinger is? -- Elizabeth Carpenter Kiss the tear from her lip, you'll find the rose the sweeter for the dew. -- Daniel Webster Platonic friendship: The interval between the introduction and the first kiss. -- Sophie Irene Loeb 

And yet, removing the stdout re-direction and making the database script look like the first picture results in the following:

I'm not experienced in AppleScript, and copied those lines out of other solutions I found. But it was working!

Thanks in advance for your help!

Answer 1

It appears that the Apple-supplied "Run AppleScript" Automator module is not Unicode-friendly. :-(

My database includes foreign words, with things like accents, umlauts, etc. (é, ü, etc.) And I'm rather fond of m-dashes ("—").

When I "fixed" the bug in the database that was returning NULL for many records, I changed† the double-dash construct " -- Author Name" to " — Author Name" with an m-dash. That's when the entire thing stopped working!

On a hunch, I changed† that back to double-dash, and many, but not all things started working with the supplied AppleScript above.

With a bit more testing and research, I discovered that adding the following command-line-parameter to the mysql invocation totally solved the problem: --default-character-set=UTF8. My database used UTF8MB3, the standard for multi-byte character sets, and somehow that wasn't getting through Automator and into AppleScript.

So at this point, I'm avoiding pointing fingers, except that it would have been nice if Apple had given some clue as to why it did not like its input, rather than insisting it simply did not exist. Also, all other tools (BBEdit, Terminal, (bash, wc, sort, etc.), TextEdit, etc.) that I used to examine the output of mysql behaved properly with output that "Run AppleScript" refused to even look at.

I'd like to acknowledge the help of @nohillside, who got me thinking it might have something to do with the contents of what was getting passed from the bash script to the AppleScript.

†The field causing the problem was a "generated" field. I did not make global changes to the database, rather, I changed the calculation of the generated field.