Any success with unicode?
Hello, all!
Does anybody succeded with DBD::mysql and unicode queries? Please, drop as an example:
- active charset variables ( show variables like 'char%'; )
- structure of your DB ( mysqldump -d )
- environment variables ( locale )
- the sample Perl application
My task is:
I am using MySQL-4.1.10 (InnoDB), perl v5.8.6, DBD::mysql 3.0002.
Most of the data stored in the database is in latin1 charset. However, there are some columns, I want to store in utf8. So I am using latin1 charset everywhere, except one table.
I was not able to load unicode data into that table via DBI. However, mysql CLI utility works fine, even it's connection all character_set_* variables are set to 'latin1', not 'utf8'. I don't know, what is not correct. Please, help.
=== Table ===
create table cms_hotels
(
id integer not null,
name varchar(120) not null,
city varchar(50),
lastUpdated datetime not null,
constraint pk_cms_hotels primary key (id),
) type=InnoDB character set utf8;
=== Perl code ===
use strict;
use HTML::Entities;
my $dbh = DBI->connect("DBI:mysql:$opt{mysql_dsn}", $opt{mysql_user}, $opt{mysql_pass},
{
PrintError=>0,
RaiseError=>1,
ShowErrorStatement=>1,
AutoCommit=>0,
InactiveDestroy=>1
});
$dbh->do('set names "utf8"');
$dbh->do('set transaction isolation level read committed');
my $stmt = "update cms_hotels set city = '" . HTML::Entities::decode_entities('Mikołajki „KARNAK“ und „NOOR“') . "' where id = 20655";
binmode(STDOUT, ":utf8");
print "$stmt;\n";
$dbh->do($stmt);
$dbh->commit();
$dbh->disconnect();
=== Locale ===
$ locale
LANG=POSIX
LC_CTYPE=en_US.utf-8
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE=en_US.utf-8
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=
=== Result ===
$ echo "select city from cms_hotels where id = 20655;" | mysql ...
city
Miko?ajki �KARNAK� and �NOOR�
=== How it works ===
$ echo "update cms_hotels set city = 'Mikołajki „KARNAK“ und „NOOR“' where id = 20655;" | mysql ...
giata@utetest1:~> echo "select city from cms_hotels where id = 20655;" | mysql ...
city
Mikołajki „KARNAK“ und „NOOR“