CODEWITHSUNDEEP
c++mysqlutf-8character-encodingodbc
davka
davka

Reputation: 14692

can't read utf8 strings from MySQL DB by an odbc client

I have a DB with utf8 default charset, and a table that contains strings with non-ascii characters. I can read the data correctly by the mysql client program and the isql odbc client program (see examples below). However, when I read the table in my C++ client using the libodbc++ odbc wrapper, I get garbage.

Curiously, I've been using the default MySql settings until now, i.e. latin1 charset, but the data actually contained utf8 strings. This way, I was getting utf8 ok. I changed the DB to utf8 so that I can use utf8_bin collation.

Since I use set names utf8 in the client I expect no charset conversion to happen between client and server. Am I wrong?

Are you aware of charset problems with libodbc++?

EDIT: just tested this with a "pure" odbc client (disgusting...), works ok. Strange, since libodbc++ is just a wrapper for odbc, I'd expect it not to have such effect on the data. Anyway, the suspect is libodbc++ library.

mysql> show full columns from tbl_list_domains;
+-------+-----------+-----------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type      | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-----------+-----------+------+-----+---------+-------+---------------------------------+---------+
| word  | char(100) | utf8_bin  | NO   | PRI | NULL    |       | select,insert,update,references |         | 
+-------+-----------+-----------+------+-----+---------+-------+---------------------------------+---------+


$ mysql -u mysql navajoLocal <<< "set names utf8; select * from tbl_list_domains order by word limit 30" > out

The file out comes out good:

word
aa
ab
ac
ad
ae
...etc.

Using isql odbc client:

echo -e "set names utf8 \n select * from tbl_list_domains order by word limit 30" |isql mysql3-test -v -b -x0x20 > out

still good.

however this:

int main()
{
 ConnectionPtr conn = ConnectionPtr( DriverManager::getConnection("Driver=mysql3;database=navajoLocal;server=localhost;user=mysql;option=3;socket=/var/lib/mysql/mysql.sock") );

 StatementPtr st = StatementPtr( conn->createStatement() );
 st->executeUpdate("set names utf8 collate utf8_bin");

 ResultSetPtr res = ResultSetPtr( st->executeQuery("select word from tbl_list_domains order by word limit 30") );

 string s;
 while (res->next()) {
  s = res->getString(1);
  cout << s << endl;
 }
}

gives out this:

a^@
a^@
a^@
a^@

Upvotes: 0

Views: 1543

Answers (2)

davka
davka

Reputation: 14692

Well, found the guilty - it's the libodbc++ library, as the edit says. Going to write to the authors. The solution would be to replace it with the MySql C++ connector. They both are modeled after JDBC, so the change is not big. Hope I will not be changing the DB soon...

Upvotes: 1

msmafra
msmafra

Reputation: 1714

I don't really know if your problem is the same as mine. I was trying to obtain a search result from a utf8_bin DB, using PHP and MySQL DB, but utf8_bin is very strict when talking about similar characters (like e é ë). So, when a query is used without the proper utf8 characters and/or case it doesn't return results or at least the ones expected. So, my oracle Google showed in MySQL docs the sollution for me using _utf8 (note the underline) and collate utf8_unicode_ci in the SELECTS's WHERE clause like that:

SELECT field1,field2,field3
FROM `table1`
WHERE `table`.`field2` LIKE _utf8 '%$q%' collate utf8_unicode_ci

Hope it solves your problem. P.S.: Sorry about my English. It is not my first language.

Upvotes: 1

Related Questions