UTF8 in CSV (was Re: select through DBD::CSV)

Radomir Hejl wrote:

Why doesn't LIKE operator match the data when encoding pragma (any) is used?

I'm pretty clueless about encoding, but essentially, Text::CSV_XS, the CSV parser under DBD::CSV, treats utf data as binary and just passes it back and forth as bytes. Since it's done in C, it doesn't know about Perl's utf flags and so the values SELECTed from a table will not be marked as utf.

I am not sure if or how I should change Text::CSV_XS. If any persons wiser than I have a suggestion, I'm all ears.

Without changes to Text::CSV_XS, you currently have two options: 1) switch to DBD::AnyData which uses the same SQL engine as DBD::CSV but which parses the CSV with perl. It works as you expect for your example. 2) Continue to use DBD::CSV but use the newest version of the SQL engine (SQL::Statement 1.14) and use its user-defined-functions to create a UTF_LIKE() function. For example:

sub UTF_LIKE {
   my($self,$sth,$rowhash,$val1,$val2)=@_;
   require Encode;
   $val1 = '' unless defined $val1;
   $val2 = '' unless defined $val2;
   Encode::_utf8_on($val1);
   Encode::_utf8_on($val2);
   return ( $val1 =~ /\Q$val2/s ) ? 1 : 0;
}

$dbh->do("CREATE FUNCTION UTF_LIKE");
$sth = $dbh->prepare("SELECT * FROM test WHERE UTF_LIKE(col1,?)");

--
Jeff
.