[prev in list] [next in list] [prev in thread] [next in thread]
List: haskell-cafe
Subject: Re: [Haskell-cafe] HDBC database: error on accent table name
From: Damien Mattei <damien.mattei () gmail ! com>
Date: 2019-01-16 21:07:41
Message-ID: CADEOaddFhzj9hWCoWk+9+R-cZbxoeG-ZCUaZ_3FHyfX4f7dqvg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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-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@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.
[Attachment #5 (text/html)]
<div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div class="gmail_default" style="font-size:large">thank for \
your answer,</div><div class="gmail_default" style="font-size:large"><br></div><div \
class="gmail_default" style="font-size:large">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 <br></div><div class="gmail_default" \
style="font-size:large">Database.MySQL.Simple which worked 'out of the box' \
for accents.<br></div><div class="gmail_default" \
style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">i have checked the locale on both client and server and it is \
the same:</div><div class="gmail_default" \
style="font-size:large">LANG=en_US.UTF-8<br></div><div class="gmail_default" \
style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">client:</div><div class="gmail_default" \
style="font-size:large">[mattei@asteroide Haskell]$ echo \
$LANG<br>en_US.UTF-8</div><div class="gmail_default" \
style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">server:</div><div class="gmail_default" \
style="font-size:large">[root@moita ~]# echo $LANG<br>en_US.UTF-8</div><div \
class="gmail_default" style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">if i unset LANG it's worse all accent character display \
as ? or disappears:</div><div class="gmail_default" style="font-size:large">*Main> \
main<br>2139<br>select `NumBD` from 'sidonie.Coordonn?es' where Nom = 'A \
20'<br>*** 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"}<br></div><div class="gmail_default" style="font-size:large"><br></div><div \
class="gmail_default" style="font-size:large">but the database seems to use latin1 as \
show below:</div><div class="gmail_default" style="font-size:large"><br></div><div \
class="gmail_default" style="font-size:large">MariaDB [sidonie]> SHOW FULL COLUMNS \
FROM Coordonnées;<br>+----------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+<br>| \
Field | Type | Collation | Null | Key \
| Default | Extra | Privileges | Comment \
|<br>+----------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+<br>| \
N ° Fiche | int(11) | NULL | NO | \
PRI | 0 | | select,insert,update,references | \
|<br>| Alpha 2000 | double | NULL | YES \
| | NULL | | select,insert,update,references | \
|<br>| Delta 2000 | double | NULL | YES \
| | NULL | | select,insert,update,references | \
|<br>| N ° ADS | varchar(50) | latin1_swedish_ci | YES | | \
NULL | | select,insert,update,references | |<br>| \
NomSidonie | varchar(50) | latin1_swedish_ci | YES | | NULL | \
| select,insert,update,references | |<br>| mag1 | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | |<br>| mag2 | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | |<br>| N ° BD | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | |<br>| Spectre | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | |<br>| N °Type | \
float | NULL | YES | | NULL | \
| select,insert,update,references | |<br>| N ° HIP | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | |<br>| Orb | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | |<br>| Modif | \
datetime | NULL | YES | | NULL | \
| select,insert,update,references | |<br>| Date de saisie | datetime \
| NULL | YES | | NULL | | \
select,insert,update,references | |<br>| Nom opérateur | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | |<br>| Nom | \
varchar(50) | latin1_swedish_ci | YES | | NULL | | \
select,insert,update,references | \
|<br>+----------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+<br>16 \
rows in set (0.00 sec)<br></div><div class="gmail_default" \
style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">i still do not know woth HDBC where to put the options at the \
connection to set encoding in my code i had this:</div><div class="gmail_default" \
style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">do<br> conn <- connectMySQL defaultMySQLConnectInfo \
{<br> mysqlHost = \
"moita",<br> mysqlUser \
= "mattei",<br> mysqlPassword = \
""<br> }</div><div \
class="gmail_default" style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">i can not fin in doc an option for encoding <br></div><div \
class="gmail_default" style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large"><a \
href="http://hackage.haskell.org/package/HDBC-mysql-0.7.1.0/docs/Database-HDBC-MySQL.h \
tml#t:MySQLConnectInfo">http://hackage.haskell.org/package/HDBC-mysql-0.7.1.0/docs/Database-HDBC-MySQL.html#t:MySQLConnectInfo</a></div><div \
class="gmail_default" style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">the solution to do :SET NAMES utf8;<br></div><div \
class="gmail_default" style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">i try this:<br></div><div class="gmail_default" \
style="font-size:large">config <- quickQuery' conn "SET NAMES \
utf8" []</div><div class="gmail_default" style="font-size:large"><br></div><div \
class="gmail_default" style="font-size:large">but i get an error :</div><div \
class="gmail_default" style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">*Main> main<br>*** Exception: SqlError {seState = \
"", seNativeError = 2053, seErrorMsg = "Attempt to read a row while \
there is no result set associated with the statement"}</div><div \
class="gmail_default" style="font-size:large"><br></div><div class="gmail_default" \
style="font-size:large">because SET return an empty result list,</div><div \
class="gmail_default" style="font-size:large">do not know how to make it \
work....</div><div class="gmail_default" style="font-size:large"><br></div><div \
class="gmail_default" style="font-size:large">Damien<br></div><div \
class="gmail_default" \
style="font-size:large"><br></div></div></div></div></div></div></div></div></div></div></div></div></div><br><div \
class="gmail_quote"><div dir="ltr">On Wed, Jan 16, 2019 at 5:13 PM Tobias Dammers \
<<a href="mailto:tdammers@gmail.com">tdammers@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, Nov 29, 2018 \
at 11:33:45AM +0100, Damien Mattei wrote:<br> > Hi,<br>
> <br>
> i have this error:<br>
> *** Exception: SqlError {seState = "", seNativeError = 1064, \
seErrorMsg<br> > = "You have an error in your SQL syntax; check the manual \
that<br> > corresponds to your MariaDB server version for the right syntax to \
use<br> > near 'es where Nom = 'A 20'' at line 1"}<br>
> <br>
> when doing this :<br>
> <br>
> rows_coordonnees <- quickQuery' conn "select * from \
sidonie.Coordonnées<br> > where Nom = 'A 20'" []<br>
> <br>
> it seems tha the tabel name: Coordonnées that contain an accent is<br>
> causing serious problem to the parser at some point, if i use a table<br>
> name without accent it works fine.<br>
> <br>
> i'm at the point to rename the table which have great impact on all the<br>
> project build with many other languages (Scheme) that deal correctly the<br>
> table name with accent.<br>
> <br>
> any idea? to make accent works with haskell.<br>
<br>
So you're using MariaDB, which is essentially MySQL, and that means that<br>
queries are sent as bytestrings without encoding information; the<br>
correct encoding for each client is stored per connection, and defaults<br>
to whatever is the server default IIRC. Therefor, as a general best<br>
practice, it is common to set the connection charset explicitly at the<br>
beginning, and make sure the queries you send are encoded accordingly.<br>
HDBC will not however do this for you.<br>
<br>
HDBC-MySQL uses withCStringLen to marshal Haskell's String type to the<br>
raw C string that MySQL expects, and that uses the current locale (on<br>
the client, that is) for the conversion - on most modern *nix installs,<br>
this is going to amount to utf-8. A typical MySQL (or MariaDB) server's<br>
default encoding, however, is NOT utf-8, but some flavor of latin-1.<br>
<br>
So my wild guess as to why it fails is this - the server is set to<br>
default to latin-1, while your Haskell code uses the local system's<br>
locale, and thus encodes queries as UTF-8.<br>
<br>
This resource explains MySQL connection charsets and collations in more<br>
depth: <a href="https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html" \
rel="noreferrer" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html</a><br>
<br>
In a nutshell, right after connecting, and assuming your client system<br>
uses some UTF-8 locale, you run the query "SET NAMES utf8;" once, and<br>
that should do the trick.<br>
_______________________________________________<br>
Haskell-Cafe mailing list<br>
To (un)subscribe, modify options or view archives go to:<br>
<a href="http://mail.haskell.org/cgi-bin/mailman/listinfo/haskell-cafe" \
rel="noreferrer" target="_blank">http://mail.haskell.org/cgi-bin/mailman/listinfo/haskell-cafe</a><br>
Only members subscribed via the mailman list are allowed to post.</blockquote></div>
[Attachment #6 (text/plain)]
_______________________________________________
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.
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic