UTF-8 Character Encoding in SQL

Question

I am trying out Bullzip's Access to mySQL app on an Access DB full of special chars like é and ä. The app allows you to specify UTF-8 encoding but in the resulting SQL file I get "Vieux Carré" instead of "Vieux Carré".

I tried opening the SQL file in UltraEdit and doing a UTF-8 conversion but it does not resolve this issue as I guess it is converting "é" and never sees the "é"?

What is a Good™ solution for this?

Lodie

Answer 1

The problem is in the UTF-8 to Unicode conversion into or out of Access. Access, like SQL Server, can only natively store data in ASCII format or Unicode (UTF-16) (With Unicode compression off). In order to ensure a given value was stored properly, you would need to convert it to Unicode on storage and convert it back to UTF-8 on retrieval. You may be able to use the StrConv function for such a purpose.

Sadie

Thank you! I was not aware of that limitation. That is very enlightening! – Lodie

@Lodie If you use the ADODB.Command and pass the values as adVarWChar ADO should handle the conversion to unicode for you, rather than having to manually convert. – Vitality

Answer 2

I have the same problem with Bullzip convertor now, so it could still help someone.

It doesn´t show the special characters right if I have my pc language set to english. I have to switch it back to czech (language of the special characters) and it works now and SQL looks correct.

Pyrolysis