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

[Damien Mattei]

thank for your answer,

since the time of question (late november 2018)  as i had no solution else
remove the accent from database which would have for consequence to change
a lot in existing code (in Java,Scheme,Kawa and Haskell) i had used
Database.MySQL.Simple which worked 'out of the box' for accents.

i have checked the locale on both client and server and it is  the same:
LANG=en_US.UTF-8

client:
[mattei at asteroide Haskell]$ echo $LANG
en_US.UTF-8

server:
[root at moita ~]# echo $LANG
en_US.UTF-8

if i unset LANG it's worse all accent character display as ? or disappears:
*Main> main
2139
select `NumBD` from 'sidonie.Coordonn?es' where Nom = 'A    20'
*** 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
''sidonie.Coordonnes' where Nom = 'A    20'' at line 1"}

but the database seems to use latin1 as show below:

MariaDB [sidonie]> SHOW FULL COLUMNS FROM Coordonnées;
+----------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field          | Type        | Collation         | Null | Key | Default |
Extra | Privileges                      | Comment |
+----------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| N° Fiche       | int(11)     | NULL              | NO   | PRI | 0
|       | select,insert,update,references |         |
| Alpha 2000     | double      | NULL              | YES  |     | NULL
|       | select,insert,update,references |         |
| Delta 2000     | double      | NULL              | YES  |     | NULL
|       | select,insert,update,references |         |
| N° ADS         | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| NomSidonie     | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| mag1           | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| mag2           | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| N° BD          | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| Spectre        | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| N°Type         | float       | NULL              | YES  |     | NULL
|       | select,insert,update,references |         |
| N° HIP         | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| Orb            | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| Modif          | datetime    | NULL              | YES  |     | NULL
|       | select,insert,update,references |         |
| Date de saisie | datetime    | NULL              | YES  |     | NULL
|       | select,insert,update,references |         |
| Nom opérateur  | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
| Nom            | varchar(50) | latin1_swedish_ci | YES  |     | NULL
|       | select,insert,update,references |         |
+----------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
16 rows in set (0.00 sec)

i still do not know woth HDBC where to put the options at the connection to
set encoding in my code i had this:

do
    conn <- connectMySQL defaultMySQLConnectInfo {
                       mysqlHost     = "moita",
                       mysqlUser     = "mattei",
                       mysqlPassword = ""
                    }

i can not fin in doc an option for encoding

http://hackage.haskell.org/package/HDBC-mysql-0.7.1.0/docs/Database-HDBC-MySQL.html#t:MySQLConnectInfo

the solution to do :SET NAMES utf8;

i try this:
config  <- quickQuery' conn "SET NAMES utf8" []

but i get an error :

*Main> main
*** Exception: SqlError {seState = "", seNativeError = 2053, seErrorMsg =
"Attempt to read a row while there is no result set associated with the
statement"}

because SET return an empty result list,
do not know how to make it work....

Damien


On Wed, Jan 16, 2019 at 5:13 PM Tobias Dammers <tdammers at gmail.com> wrote:

> 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.
> _______________________________________________
> Haskell-Cafe mailing list
> To (un)subscribe, modify options or view archives go to:
> http://mail.haskell.org/cgi-bin/mailman/listinfo/haskell-cafe
> Only members subscribed via the mailman list are allowed to post.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.haskell.org/pipermail/haskell-cafe/attachments/20190116/2167c57b/attachment.html>