Re: DBD::ODBC tricky unicode problem

On Sun, Oct 27, 2013 at 12:18:53PM +0000, Martin J. Evans wrote:
> 
> Inserting a unicode euro, utf8 flag on:
>   input string: €
>   data_string_desc of input string: UTF8 on, non-ASCII, 1 characters
> 3 bytes
>   ords of input string: 20ac,
>   bytes of input string: e2,82,ac,
>   database length: 3
>   data_string_desc of output string: UTF8 on, non-ASCII, 3
> characters 7 bytes
>   ords of output string:e2,201a,ac,
> # what happened here is SQLDecribeParam described the parameter as
> SQL_CHAR and that is what D:O bound it as. The driver converted 82
> in code page 1252 to 201a (a magic quote) and the database sees this
> as 3 chrs
> # the probably mistake here is that D:O should have looked at the
> perl data, seen it was unicode and not bound it as SQL_CHAR (but
> SQL_WCHAR) even though SQLDescribeParam said it was

Agreed.

> Inserting a UTF-8 encoded unicode euro, utf8 flag off:
> "\x{0082}" does not map to cp1252 at test_code.pl line 36.

I'd caution against using phases like "UTF-8 encoded unicode euro, utf8
flag off". From the application's perspective it's not a euro, it's just
a sequence of bytes (that just happens to match what a euro unicode
codepoint would look like when UTF-8 encoded).

To put it another way, if the application has that string of bytes and
thinks it's a euro then the application is almost certainly broken.

> Inserting a unicode euro, utf8 flag on, forced SQL_WVARCHAR:
> ...
> # the above is arguably what should have happened in the first test
> case i.e., D:O should have bound as SQL_WVARCHAR because the perl
> data was unicode. I included this one to show you what happens if
> you do it right.

Agreed.

> So, I'm thinking the problem above is D:O ignores utf8 flag on
> parameters when they are bound and uses whatever SQLDescribeParam
> says instead (SQL_CHAR in case of varchar columns). If it didn't
> ignore the utf8 flag here, it would have to rebind on every execute
> (which it may already do, I didn't check).

Agreed.

Great. Progress! :)

So, the next question is what are the implications of fixing it for
existing applications? Do you need a deprecation cycle with warnings etc?

Tim.