[Haskell-cafe] HDBC database: error on accent table name

[Tobias Dammers]

On Thu, Nov 29, 2018 at 11:33:45AM +0100, Damien Mattei wrote:
> Hi,
> 
> i have this error:
> *** Exception: SqlError {seState = "", seNativeError = 1064, seErrorMsg
> = "You have an error in your SQL syntax; check the manual that
> corresponds to your MariaDB server version for the right syntax to use
> near 'es where Nom = 'A    20'' at line 1"}
> 
> when doing this :
> 
> rows_coordonnees <- quickQuery' conn "select * from sidonie.Coordonnées
> where Nom = 'A    20'" []
> 
> it seems tha the tabel name: Coordonnées that contain an accent is
> causing serious problem to the parser at some point, if i use a table
> name without accent it works fine.
> 
> i'm at the point to rename the table which have great impact on all the
> project build with many other languages (Scheme) that deal correctly the
> table name with accent.
> 
> any idea? to make accent works with haskell.

So you're using MariaDB, which is essentially MySQL, and that means that
queries are sent as bytestrings without encoding information; the
correct encoding for each client is stored per connection, and defaults
to whatever is the server default IIRC. Therefor, as a general best
practice, it is common to set the connection charset explicitly at the
beginning, and make sure the queries you send are encoded accordingly.
HDBC will not however do this for you.

HDBC-MySQL uses withCStringLen to marshal Haskell's String type to the
raw C string that MySQL expects, and that uses the current locale (on
the client, that is) for the conversion - on most modern *nix installs,
this is going to amount to utf-8. A typical MySQL (or MariaDB) server's
default encoding, however, is NOT utf-8, but some flavor of latin-1.

So my wild guess as to why it fails is this - the server is set to
default to latin-1, while your Haskell code uses the local system's
locale, and thus encodes queries as UTF-8.

This resource explains MySQL connection charsets and collations in more
depth: https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

In a nutshell, right after connecting, and assuming your client system
uses some UTF-8 locale, you run the query "SET NAMES utf8;" once, and
that should do the trick.