Character encodings and databases

[William Blunn]

On 19/06/2014 15:58, Andrew Hill wrote:
> My code has an extremely annoying bug that I can't quite solve.
>
> The concept is simple - read some text from a text file; update a 
> database table based on that text.
>
> The text file is UTF8 and the database is Oracle 11g.
>
> I am reading the file with a normal
> open FILE, "<blah";
> while(<FILE>) {
>     chomp;
>     $foo = $_;
> }

This code is likely to lead to problems.

If you open a UTF-8 file without specifying a layer, then when you read 
the file you will get downgrade strings containing the UTF-8 bytes, 
whereas what you want is upgraded strings containing the characters.

So in the case of a file containing a UTF-8 representation of "Zürich", 
$foo will be left with "Z\xC3\xBCrich" (and it will be a downgrade 
string, i.e. with Perl's internal UTF-8 flag off).

Consider instead doing something like this:

my $filename = 'blah;
open my $file, '<:encoding(UTF-8)', $filename or die "Can't open 
$filename: $!";

In the case of a file containing a UTF-8 representation of "Zürich", the 
resulting will be a string "Z\xFCrich" (and it will be an upgraded 
string, i.e. with Perl's internal UTF-8 flag on), which is what you want.

> Then I select the VARCHAR2 field from the table into $bar, do a 
> straight string comparison between $foo and $bar, and if they are 
> different, I update the table with the value of $foo and output a 
> debugging line to say that, for example, Z<splodge>rich has been 
> updated to Zürich.

It looks like you are using Oracle. (Probably best to state that clearly 
in questions like this.)

DBD::Oracle does "interesting" things when you try to send downgrade 
strings to the database. Empirically, it seems to treat the downgrade 
string as a UTF-8 byte sequence. So in your case this will mean you 
accidentally end up writing the "right" thing to the database.

> However, the next time I read Zürich from the file, I get exactly the 
> same behaviour, ie $bar is again Z<splodge>rich, therefore $foo ne 
> $bar and it updates the table again. I don't understand why $foo ne 
> $bar, given I've just set the field to $foo.

When you read it back, you will get a string "Z\xFCrich", which will 
compare different to "Z\xC3\xBCrich", and from what you describe your 
program will update the database again.

You may also be getting strange behaviour with your debug output, and 
the pathway between your data and your monitor may not treating UTF-8 in 
a way which is consistently useful. Consider passing your debug output 
through Data::Dump::pp so that you can properly see what's going on.

> So, as I see it, these are the possible causes:
> 1. Data is not being stored in the database as UTF8 - not sure how to 
> check when Perl is the only tool available to query it

It is and it isn't. If you want to see what's really in your strings 
then you can use:

use feature 'say';
use Data::Dump 'pp';

say '$x contains ', pp($x);
say '$x is ', utf8::is_utf8($x) ? 'upgraded' : 'downgrade';

> 2. Conversion is occuring in the DBD driver
> 3. Something else because I've been staring at it for so long
>
> FWIW, NLS_CHARACTERSET is AL32UTF8 and $ENV{NLS_LANG} is 
> AMERICAN_AMERICA.AL32UTF8

Brilliant! You appear to have set the correct options for getting 
DBD::Oracle to do Unicode (reasonably) properly. That is actually the 
hard part :-)

Though you need to ensure that $ENV{NLS_LANG} is set to a suitable 
AL32UTF8 option fairly early on.

I say "(reasonably) properly" because everything is fine with 
DBD::Oracle provided all your strings are upgraded. If you accidentally 
pass a downgrade string to DBD::Oracle then strange things happen.

Most of the time you can just get away with it, because most properly 
patrolled borders end up generating upgraded strings anyway, even when 
the text is entirely in the Latin-1 range.

To be sure, use utf8::upgrade on all strings which you want to pass into 
DBD::Oracle. Alternatively, get DBD::Oracle fixed so that it does this 
for you.

Regards,

Bill