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

[Jeff Zucker]

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)[EMAIL PROTECTED];
   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