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@asteroide Haskell]$ echo $LANG
en_US.UTF-8
server:
[root@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-MyS...
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
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.