MySQL Forums
Forum List  »  Perl

Any success with unicode?
Posted by: Dmitry Katsubo
Date: November 02, 2005 12:38PM

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“

Options: ReplyQuote


Subject
Written By
Posted
Any success with unicode?
November 02, 2005 12:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.