[Gambas-user] Problem with UTF-8 in Gambas-connection to MySQL

[Benoît Minisini]

> > > (I don't know English well)
> > > Use Debian 5 "Lenny" -32, Gambas 2.7 from repository.
> > >
> > > I develop an account program for russian users. It must work with
> > > cyrillic correct.
> > > I have made MySQL5 (default UTF8) database with direct CHARACTER SET
> > > UTF8 option, tables with direct CHARACTER SET UTF8 option. Then
> > > connected to it from gambas, output data to GridView. All string fields
> > > were not readable.
> > >
> > > I run example "Database". It works correct with some french symbols,
> > > but with cyrillic not. Program "Database manager" do the same.
> > >
> > > I got sources of Gambas-2.8.2. Opened main.c file of MySQL driver,
> > > found function
> > >   <<< static int open_database(DB_DESC *desc, DB_DATABASE *db) >>>.
> > > Added string
> > >   <<< fprintf(stderr, "Character set GB[%s] mysql[%s]\n",
> > > GB.System.Charset(), mysql_character_set_name(conn)); >>>
> > > before and after string
> > >   <<< set_character_set(db); >>>.
> > > Compiled only this driver, copied it to /usr/lib/gambas2. Run my
> > > program. Result:
> > >   <<< Character set GB[UTF-8] mysql[latin1]
> > >          Character set GB[UTF-8] mysql[latin1] >>>
> > > in console.
> > >
> > > Why latin1 instead UTF-8?
> > >
> > > --
> > > Dima Malkov
> >
> > What is the value of DB.Charset just after you have connected to the
> > database in Gambas?
> >
> > What happens if you uncheck the "use encoding" menu entry if the popup
> > menu in the database manager?
> >
> > Can you send me your database contents, or part of it, so that I test by
> > myself?
> >
> > --
> > Beno?t Minisini
> 
> The value of DB.Charset just after connection to the database in
> Gambas is "utf8".
> 
> Unchecking "use encoding UTF8" menu entry has no any visible effect.
> 
> This letter includes a ZIP archive. I created a small database by
> command "CREATE DATABASE Cyrillic CHARACTER SET utf8;" in
> MySQL-console. Then just created a table by command "CREATE TABLE tbl1
> (id SERIAL, word VARCHAR(20), PRIMARY KEY (id));". Inserted 5 rows in
> MySQL-console, then 5 rows from the "database manager". First 5 rows
> are not readable in the "database manager", and last 5 rows are not
> readable in MySQL-console.
> 
> --
> Dima Malkov
> 

Hi,

I copied your database in the mysql server directory on my own system, then I 
run the "mysql" utility:

---------------------------------------------------------------------------

$ mysql -u root cyrillic
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 593
Server version: 5.1.34 Mandriva Linux - MySQL Standard Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from tbl1;
+----+------------------------------+
| id | word                         |
+----+------------------------------+
|  1 | один                     |
|  2 | два                       |
|  3 | три                       |
|  4 | четыре                 |
|  5 | пÑÑ‚ÑŒ                     |
|  6 | шесть                        |
|  7 | семь                         |
|  8 | восемь                       |
|  9 | девять                       |
| 10 | десять                       |
+----+------------------------------+
10 rows in set (0.00 sec)

mysql>

---------------------------------------------------------------------------

So I got the same thing in 'mysql' than in the Gambas database manager.

So I think that you didn't specify UTF-8 as default character set when 
entering the data from 'mysql', and that you used a Cyrillic-specific 
character set. But I didn't succeed in guessing what charset is used to encode 
the five first rows of the 'tbl1' table.

Can you check that by typing "mysql --help" and look in the list of variables 
displayed after the help contents? You should see a "default-character-set" 
line.

On my system, for example, 'mysql' uses utf8 by default.

---------------------------------------------------------------------------

$ mysql --help
mysql  Ver 14.14 Distrib 5.1.34, for mandriva-linux-gnu (i586) using readline 
6.0
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
...
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
auto-rehash                       FALSE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
...
default-character-set             utf8
...

---------------------------------------------------------------------------

See http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html

In a few words, everything must be "UTF-8" from the beginning to the end. Not 
just the character set specified at database creation.

Regards,

-- 
Benoît Minisini