Hi PerlMonks,

I have a problems with DBIx/DBI and UTF8 chars. I use 0.08195 version of DBIx and the problem is that on my dev machine I get correctly encoded chars like "\x{6211}\x{4eec}\x{4e0e}", but on my staging machine I get "???" instead. DB, table, code - everything the same.

Final Update: I've went through the thread once again and checked DBD::mysql. On dev was 4.018 version, but on stg it was 3.x. After the update everything worked perfectly. I just cannot belive that I somehow (while checking) missed to notice this. Thanks a lot for your help. It has been a tricky one for me, and, apart from learning the cause to this, I learned extra on utf8 difficulties.

Update 8.8.2011. 15:57 - This might not be Perl/DBI/DBIx problem. Still investigating.

Check below:

Table:

CREATE TABLE `translation` (
  `id` int(10) NOT NULL auto_increment,
  `section_id` int(11) NOT NULL,
  `language_id` char(4) NOT NULL,
  `translator_id` int(11) default NULL COMMENT 'Translator to blame fo
+r this translation',
  `version` int(10) unsigned default NULL,
  `value` text,
  `validation_count` tinyint(3) unsigned default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `natural_key` (`section_id`,`language_id`,`version`),
  KEY `index_language` (`language_id`),
  KEY `index_section` (`section_id`),
  KEY `index_version` (`version`),
  KEY `index_translator` (`translator_id`),
  FULLTEXT KEY `fulltext_value` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

[download]

DBIx connection with utf8 set:

package Models;

use strict;
use warnings;
use Data::Dumper;
use System::Config;

use base 'DBIx::Class::Schema';

my @connection_args = (
    $c->{db_dsn},
    $c->{db_username},
    $c->{db_password},
    {
        loader_options => {
            debug => 1,
            use_namespaces => 1,
            mysql_auto_reconnect => 0,
        },
        mysql_enable_utf8 => 1,
    }
);

__PACKAGE__->connection(@connection_args);
__PACKAGE__->load_namespaces;

[download]

Model

package Models::Result::Translation;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("translation");

__PACKAGE__->add_columns(
  "id",
  { data_type => "integer", extra => { unsigned => 1 }, is_nullable =>
+ 0 },
  "section_id",
  { data_type => "integer", extra => { unsigned => 1 }, is_nullable =>
+ 0 },
  "language_id",
  { data_type => "char",    is_nullable => 0, size => 4,  },
  "translator_id",
  { data_type => "integer", extra => { unsigned => 1 }, is_nullable =>
+ 0 },
  "version",
  { data_type => "integer", extra => { unsigned => 1 }, is_nullable =>
+ 0 },
  "value",
  { data_type => "text", is_nullable => 0 },
  "validation_count",
  { data_type => "integer", extra => { unsigned => 1 }, is_nullable =>
+ 0 },
);

__PACKAGE__->set_primary_key("id");

1;

[download]

DBIx in script:

#!/usr/bin/perl -w
use Data::Dumper;
use strict;
use Models;

print STDERR Dumper [ map{ { $_->get_columns } } Models->resultset('Tr
+anslation')->search({ language_id => 'zhCN'})->all() ];

[download]

Correct output on my dev mechine:

[...
{
            'translator_id' => '99',
            'section_id' => '523',
            'version' => '1',
            'value' => "\x{6211}\x{4eec}\x{4e0e}\x{7f51}\x{7edc}\x{4e0
+a}\x{6700}\x{70ed}\x{95e8}\x{7684}\x{54c1}\x{724c}\x{5408}\x{4f5c}\x{
+ff01}\x{8bf7}\x{6d4f}\x{89c8}\x{60a8}\x{6700}\x{5fc3}\x{4eea}\x{7684}
+\x{5355}\x{54c1}\x{ff0c}\x{8bf7}\x{65f6}\x{5e38}\x{56de}\x{8bbf}\x{62
+11}\x{4eec}\x{7684}\x{7f51}\x{7ad9}\x{ff0c}\x{4e86}\x{89e3}\x{6700}\x
+{65b0}\x{4e0a}\x{67b6}\x{548c}\x{66f4}\x{65b0}\x{7684}\x{5546}\x{54c1
+}\x{3002} ",
            'validation_count' => '0',
            'id' => '31373',
            'language_id' => 'zhCN'
          },
          {
            'translator_id' => '99',
            'section_id' => '244',
            'version' => '1',
            'value' => "\x{7f16}\x{53f7}",
            'validation_count' => '0',
            'id' => '31365',
            'language_id' => 'zhCN'
          },...]

[download]

Incorrect output on my staging machine:

[...
{
            'translator_id' => '99',
            'section_id' => '1283',
            'version' => '1',
            'value' => '??????????????????????????????????????????????
+????????????????????????????????????????????  ',
            'validation_count' => '0',
            'id' => '38392',
            'language_id' => 'zhCN'
          },
          {
            'translator_id' => '99',
            'section_id' => '1274',
            'version' => '1',
            'value' => '??????????????????????????????????????????????
+?????????????????????????????????????',
            'validation_count' => '0',
            'id' => '38394',
            'language_id' => 'zhCN'
          }, ...]

[download]

Please help, Stepamil

In reply to DBIx/DBI returns question marks by stepamil

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.