Ryan Ryan - 4 months ago 12
AppleScript Question

Format MySQL Script In Terminal Through AppleScript - Quote Escaping Problems

I'm currently writing an AppleScript to trigger a MySQL script to run in order to send a csv every day at a certain time using a calendar event.

I believe I have the general idea on both parts correct. This is a version of the MySQL script (which works when run directly in MySQL):

SET @sql_text := CONCAT('SELECT "ID", "FirstName", "LastName", "Employer", "Activist/Lead"
UNION ALL
SELECT * FROM
(SELECT e.id, m.firstname, m.lastname, e.employer, (CASE WHEN m.Lead="X" THEN "Lead" ELSE "Activist" END) AS "Activist/Lead"
FROM events e
LEFT JOIN people m ON e.id = m.id
WHERE ((STR_TO_DATE(e.startdate,"%m/%d/%Y") >= SUBDATE(now(), INTERVAL 1 YEAR)) AND (e.eventtype IN("House Party","Blitz","Politics"))
GROUP BY e.id
HAVING count(e.id)>=1) t
INTO OUTFILE \'/Users/Ryan/Activists_', DATE_FORMAT(NOW(), '%Y%m%d'), '.csv\'
FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\'
LINES TERMINATED BY \'\\n\'
');

PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;


That MySQL script alone does a good job at creating a .csv with my naming convention into the folder of my choosing. But as you can see there are lots of single and double quotes (some with escapes already) that I need in there to make the MySQL script work.

Though when I put it into AppleScript to run through Terminal, I get all sorts of errors due to the quotes:

AppleScript Error

As you can see, it's highlighting "ID" because it's the first appearance of double quotes in the MySQL script. I've tried changing all the double quotes to
\"
but it seems to just get more confusing -- particularly at the
INTO OUTFILE
section because that's also when the single quotes start showing up. (And at that point [single quotes towards the end] it starts giving the error
Expected “"” but found unknown token.
)

Does anyone know a way I can restructure the escape quotes to make AppleScript not give me errors, even though I need the quotes to look the way they do in order for the MySQL script to run properly? Feels like they're fighting against each other.

Answer

I just stopped trying to post the script directly into AppleScript and used Terminal to grab my script activists.sql directly from its text file by using this: do script with command "mysql 'database' < activist.sql" within AppleScript after telling it to open Terminal.