HDBC database: error on accent table name

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

HDBC database: error on accent table name

Damien Mattei
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.

Regards,
Damien
--
[hidden email], [hidden email], UNS / OCA / CNRS
_______________________________________________
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.
Reply | Threaded
Open this post in threaded view
|

Re: 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.
_______________________________________________
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.
Reply | Threaded
Open this post in threaded view
|

Re: HDBC database: error on accent table name

Damien Mattei-2
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


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 <[hidden email]> 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.

_______________________________________________
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.