I’ve added the ` in to see if that makes a difference, tried it both with and without. I’m missing something simple I’m sure, and can’t figure out what!
This is the error I’m getting:
…basically it seems to dislike any form of input for jobNum, jobName, jobClient, and jobSize. I’ve solved that before with the use of a `, but it’s not appreciated here it seems.
This isn’t an answer and I haven’t really looked at it yet, but I think that MySql uses the single hyphen around everything to escape stuff with.
You must look that up, and if that is so, then you might possibly use either the quoted form of command, or create your own escaping by surrounding your variables with “double single double” hyphen sequences.
This should at least be valid for your valid for your value sections, if I’m not totally wrong then.
set searchstr to "_EATLES"
set searchstr to "'" & searchstr & "'"
Heading vaguely towards a clearing - however the confusion exists because the failed command copied and pasted to the shell inserts in to the database perfectly!
I’ll tell you how I handled it. Understand that I used sqlite3 and not mysql, but I assume you’re running into the same problem I encountered.
When you pass commands from applescript to the command line and then into sqlite, your strings have to pass the character escaping rules of both bash and sqlite, and account for applescript’s escaping rules. You can avoid all of the bash rules for special characters by using the “quoted form of” to place single quotes around all of your values. The only character in your strings you have to worry about with this is single quotes. In bash we escape them with the backslash. To pass an escaped single quote from applescript through to bash we have to escape the escape character “\'”. It’s messy. Next, for sqlite we only have to worry about the single quote and we escape that by using a double single quote.
So you can see the problem is that applescript, bash and sqlite escape the single quote character differently. This is the problem. I solved the problem by completely removing the single quote from any of my strings by replacing it with a dummy value. In my case I replaced single quotes with “(@]”… which I figured was a character sequence I was very unlikely to come across. So by completely removing the single quote I didn’t have to worry about the applescript, bash or sqlite rules.
This has the side-effect that every time you pull information out of the database you have to do the opposite and replace “(@]” with “'”. This also has the side effect in that if you want to perform a search of the database then your search terms must go through the same process of replacing the single quote before running your search, because that’s how your strings are entered into the database.
It’s a huge pain!!! But I couldn’t find a better solution. See if the following script works.
ALWAYS REMEMBER: you can’t surround any of your values with double quotes, you must always use single quotes to avoid problems with bash special characters. As such you’ll see that I use “quoted form of” around your entire INSERT statement as well as around each individual item. And each individual item must go through the escapeForSQLite3() handler. And when you pull values out of the database you must send them through the unescapeForSQLite3() handler in order to get the original value back.
do shell script "/usr/local/mysql/bin/mysql -D " & mysql_db & " -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e " & quoted form of ("INSERT INTO " & mysql_db & "." & mysql_table & " (qid, unitNumber, jobName, jobClient, quotaGB, dateCreated) VALUES (NULL, " & quoted form of escapeForSQLite3(jobNum) & ", " & quoted form of escapeForSQLite3(jobName) & ", " & quoted form of escapeForSQLite3(jobClient) & ", " & quoted form of escapeForSQLite3(jobSize) & ", CURRENT_TIMESTAMP);")
on escapeForSQLite3(theText)
return my findReplace(theText, "'", "(@]")
end escapeForSQLite3
on unescapeForSQLite3(theText)
return my findReplace(theText, "(@]", "'")
end unescapeForSQLite3
on findReplace(theText, findText, replaceText)
if theText contains findText then
set text item delimiters to findText
set a to text items of theText
set text item delimiters to replaceText
set theText to a as text
set text item delimiters to ""
end if
return theText
end findReplace
