the single quote (apostrophe) needs to be escaped for inserting into MySQL
O'brien ==> O\'brien
(or two single quotes in some SQLs: O''brien)
are you using ESS? or Execute SQL (INSERT/UPDATE) script step?
Thank you very much, Beverly!
We use ESS...
But it looks like Substitute( fieldname ; "'" ; "\'" ) doesn't do the job.
well... "\" IS an escape character. to avoid the confusion:
Substitute ( myfield ( Char(39) ; Char(92) & Char(39) ) // '->\'
Thanks a lot, your help is highly appreciated.
But I seem to miss something basic. Both
Substitute ( myfield ; "'" ; "\'" )
Substitute ( myfield ; Char(39) ; Char(92) & Char(39) )
Breakfast at Tiffany's is correctly changed to Breakfast at Tiffany\'s, but still results in error 1408.
Breakfast at Tiffanys causes no problems.
Also tried '' instead of \' .
I already wonder if there is a problem with the MySql server.
Are you sure they are char(39) - "uneducated single quote"? Probably, because you see it being converted, but still get the error.
For ODBC imports and Execute SQL script steps, if an error occurs while performing a SQL query, the Get(LastError) function returns FileMaker error 1408. The Get(LastODBCError) function returns detailed information about the error. If there is no information about the error, the Get(LastError) function returns FileMaker error 1409.
Do you have logs to tell more details? Or screenshot of the error?
@actualjon may need to chime in here.
Yes, I checked the apostrophe using Code ( ' ) and it turned out as character 39.
We receive code 1408 and this message, (at the end of the record is the record's UUID):
One thing is interesting: The error only occurs when we create a new record on the MySql database and tell FileMaker to insert a value containing a single quotation mark into the MySql field. If the record already exists, and we insert such a value into the MySql field, everything is ok.
Sorry to have not much more information, but there simply isn't.
ok. Can you post your query to INSERT ?
Thank you for your answer.
Hm, a query to insert? We don't use an SQL statement together with the ExecuteSql script step.
We use Set Field script steps and set the fields through a 1:1-relationship. The error 1408 occurs exactly when we use the Commit record step.
Do you think the script would be of help? (Or did I misunderstand you?)
You are not using Execute SQL (script step - not the function) to INSERT? Then you are using "ess" and just using the fields on the layout which "points-to" the SQL. Can you check the logs (for the driver)? that should list the query being made by FileMaker (as SQL statements!)
The Script may help. Had I seen it, I would have known that you were using ESS.
Sorry, I mentioned at the beginning of the thread that we do ESS, not ExecuteSql().
We are trying to get the processes logged:
- As for the ODBC driver, I got a log file from the ODBC manager; but this does not seem to help too much. It does not change when SQL queries run. This log is attached.
- As for the MySql database itself (and the SQL queries) we are getting in touch with our provider. I hope to trace the unsuccessful queries.
As soon as I have more info I'm back... Am very excited :-)
Thanks for your help! :-)
Bh.log.zip 771 bytes
Yes, The ExecuteSQL() is a function that could be used on ESS tables. I think that was the confusion.
Execute SQL - the script step - is a way to write SQL calls to INSERT, UPDATE or DELETE. Import - the script step - is used to SELECT. These two script steps are not the 'live connect' that ESS has.
The log would be helpful if it showed the query.
I'm looking forward to more info! I'd still like to see if @actualjon has any hints.
I tested it with latest MySQL5.7.12 and 5.3.6 Unicode driver on Windows, there need nothing to get correct result.
It could be bug on driver or server. Do you use unicode driver? (or there is not ansi driver on OSX?)
So, here is the good news. We contacted Actual, and our issue now is fixed with the latest version of the driver. I just installed and tested it; and it seems that the error does not appear anymore. Everything seems to work as expected...