IInserting into MySQL accented characters

[fahirsch]

As part of learning how to use FM Pro 9 and MySQL I built a FM base called testbase that has one table with three fields: 'id', 'apellido', 'nombre'. I also created an MySQL base also with 3 fields named the same way. 'id' is integer, the other two text (varchar).

I created a simple script (a loop) in FileMaker that includes the following Execute SQL script step:

"INSERT INTO cliente (id,apellido,nombre) VALUES (" & testbase::id & ",'" & testbase::apellido &"','"& testbase::nombre &"')"

The script works on all records except when either apellido or nombre includes accents or other high ascii characters. Instead I get an error:

"ODBC Error: [Actual][MySQL] You have an error in your SQL syntax. Check the manual etc."

What am I doing wrong or what should I do?

[fahirsch]

I wrote Actual about my problem. This is the reply:

Thank you for contacting us. The easiest way to solve issues related to high ASCII characters is to use the new "External SQL Sources" (ESS) feature of FileMaker 9. I highly recommend that you use ESS, because it takes care of all these encoding issues automatically. But, if you can't, then I'll give you the details of how to make it work through scripting.

The "Import Records" and "Execute SQL" script steps (and menu operations) require special configuration of the "Advanced Language" panel of the driver's DSN setup assistant:

[ ] Auto-detect language settings for application

Application uses the system encoding:

[ ] to send text

[X] to receive text

[ ] Return Unicode data types

[X] Set application locale to match driver encoding

NOTE: if you decide to migrate to ESS, you will need a separate DSN that just has "Auto-detect language settings" enabled.

For "Execute SQL", you need to treat apostrophes (single quotes) specially. You should use the Substitute function to "escape" all the apostrophes (insert a backslash before it) in your text:

Calculated SQL:

"insert into contact_test(last_name) values ('" & Substitute(Untitled::name; ["'"; "'"]; ) & "')"

There is also a bug in the ODBC libraries used by FileMaker that causes it to truncate SQL strings when there are non-ASCII characters in the string.  The solution is to append one semicolon to the end of the string for each special character that appears in your string.  There is no problem with appending extra (unneeded semicolons), so you can just make a safe estimate of the maximum number you might need.



If you think you might have a maximum of 10 special characters, then append 10 semicolons, like this:

"insert into contact_test(last_name) values ('" & Substitute(Untitled::name; ["'"; "'"]; ) & "');;;;;;;;;;"

Note that these extra steps are only required for the "Execute SQL" script step. You can avoid all this extra processing by just using ESS to handle the database communication transparently.

[Rothko]

Calculated SQL:

"insert into contact_test(last_name) values ('" & Substitute(Untitled::name; ["'"; "'"]; ) & "')"

note quite right... that last semicolon shouldn't be there. it needs to look like this:

"insert into contact_test(last_name) values ('" & Substitute(Untitled::name; ["'"; "'"] ) & "')"