the utf8 set problem, please help :)

[Meifang]

hi,
Thanks first to John for adversing me to post here :)

I met some problems in upgrading my project on using Rose::DB.
And really hope you can give me a hand. The problem is that:

there are some Chinese characters in my mysql database, all are
encoded in utf8. and all of my web pages are charset=utf8
At first it works fine when both database server and web server are of
the same OS(freebsd); however, now I have to upgrade the webserver to
another machine with different OS, the Chinese characters displays
abnormal, and I add the post_connect_sql => ["SET NAMES utf8"] to my
abc.pm, look like this:

use base qw(Rose::DB);
our @ISA = qw(Rose::DB);

__PACKAGE__->use_private_registry;

__PACKAGE__->register_db (
driver => 'mysql',
database => 'dbname',
host => 'abc.abc.com',
username => 'abc',
password => 'abc',
post_connect_sql => ["SET NAMES utf8"],
# mysql_enable_utf8 => 1, # this way does not work and cause
errors see ****
);

(the errors when using "mysql_enable_utf8 => 1":
[error] Can't locate object method "mysql_enable_utf8" via package
"database::abc" at /data/abc.abc.com/database/abc.pm line 18.\n
)

using post_connect_sql => ["SET NAMES utf8"], is ok ( no error), but
suffer a strange problem:
sometimes the Chinese displays normal, and when i reload the page, it
is abnormal, and reload, normal again and reload again then may go
abnormal...(most of the times are normal).

I think the problem is as stated in the docs of Rose::DB,

" If the database handle returned by dbi_connect was originally
connected by another Rose::DB-derived object (e.g., if a subclass's
custom implementation of dbi_connect calls DBI's connect_cached
method) then the post_connect_sql statements will not be run, nor will
any custom DBI attributes be applied (e.g., Rose::DB::MySQL's
mysql_enable_utf8 attribute). "

(from http://search.cpan.org/~jsiracusa/Rose-DB-0.748/lib/Rose/DB.pm#OBJECT_METHODS)

I wonder how can I make it always work right? Is there any solution to
make it always "enable utf8"?

My src code is quite the same as the "Rose::DB::Object::Tutorial" you
presented, with many tables, each is a pm and defines init_db. All are
included in the startup.pl (use).

Since I did not deal with DBI or DBD::Mysql directly in my code. How
can I make DBD::Mysql always "enable utf8"?

Really appreciate if anyone can help me figure out this.

Best regards,

[John Siracusa]

On Fri, Dec 12, 2008 at 1:09 AM, Meifang <Li.Me...@gmail.com> wrote:
> __PACKAGE__->register_db (
> driver => 'mysql',
> database => 'dbname',
> host => 'abc.abc.com',
> username => 'abc',
> password => 'abc',
> post_connect_sql => ["SET NAMES utf8"],
> # mysql_enable_utf8 => 1, # this way does not work and cause
> errors see ****
> );
>
> (the errors when using "mysql_enable_utf8 => 1":
> [error] Can't locate object method "mysql_enable_utf8" via package
> "database::abc" at /data/abc.abc.com/database/abc.pm line 18.\n
> )

That error is fixed in SVN. (The registry entry class was actually
missing a whole bunch of driver-specific attributes. I've added them
all.)

> using post_connect_sql => ["SET NAMES utf8"], is ok ( no error), but
> suffer a strange problem:
> sometimes the Chinese displays normal, and when i reload the page, it
> is abnormal, and reload, normal again and reload again then may go
> abnormal...(most of the times are normal).
>
> I think the problem is as stated in the docs of Rose::DB,
>
> " If the database handle returned by dbi_connect was originally
> connected by another Rose::DB-derived object (e.g., if a subclass's
> custom implementation of dbi_connect calls DBI's connect_cached
> method) then the post_connect_sql statements will not be run, nor will
> any custom DBI attributes be applied (e.g., Rose::DB::MySQL's
> mysql_enable_utf8 attribute). "

I doubt if that's the problem. If one of your database handles "was
originally connected by another Rose::DB-derived object," then it will
already have had the post_connect_sql run on it. You only need to run
"SET NAMES utf8" once per connection, right?

Given the information presented so far, I think this issue is probably
unrelated to Rose::DB. But please try the SVN version where the
mysql_enable_utf8 registry attribute is supported, just in case it
actually solves your problem.

SVN URL: http://rose.googlecode.com/svn/trunk/modules/Rose-DB

-John

[Meifang]

Hi， John,

Thanks. Really appreciate your help.

I update Rose::DB to the latest version(0.749), and after that, the "
mysql_enable_utf8 => 1" works :), but for the chinese characters,
except those info from the Mysql database displays normal, all others
of the html page all display abnormal (e.g., print "我们" will not
display correctly in the HTML page.)

It's really a headache for me.

But your help is encouraging. Thanks indeed :)


On Dec 13, 1:24 am, "John Siracusa" <sirac...@gmail.com> wrote: