Re: Decoding data from the database in DBD

On 10/05/2011 09:08, Martin J. Evans wrote:
> On 09/05/11 22:06, Tim Bunce wrote:
>> On Mon, May 09, 2011 at 07:42:53PM +0100, Martin J. Evans wrote:
>>> I've recently had an rt posted
>>> (http://rt.cpan.org/Public/Bug/Display.html?id=67994) after a
>>> discussion on stackoverflow 
>>> (http://stackoverflow.com/questions/5912082/automatic-character-encoding-handling-in-perl-dbi-dbdodbc).
>>>
>>> In this case the Perl script is binding the columns but the data
>>> returned is windows-1252 and the user is having to manually
>>> Encode::decode all bound columns. DBD::ODBC already had a
>>> odbc_utf8_on flag
>>> (http://search.cpan.org/~mjevans/DBD-ODBC-1.29/ODBC.pm#odbc_utf8_on)
>>> for a derivative of Postgres which returns bound data UTF-8 encoded
>>> but in that case I can just call sv_utf8_decode (in the XS) and it
>>> is converted in place. Initially I thought I could combine
>>> odbc_utf8_on into a new flag saying my data is returned as xxx and
>>> just call Encode::decode with xxx (then eventually I could drop
>>> odbc_utf8_on).
>>
>> I'd be wary of going down this path.  I sense pain just beyond the 
>> horizon.
>> A twisty-turny maze of sharp edge cases and unforseen issues.
>
> So do I which is why I am still thinking about it and why I value any 
> other opinions/ideas.
>
>> For a start: What about the charset of bind values?  What about the
>> charset of SQL literals?
>
> Both of those are inputs but this case is data retrieved from MS SQL 
> Server. I see this as different. You can ensure your Perl data is 
> inserted into a unicode aware database via ODBC by ensuring your data 
> is encoded as unicode - DBD::ODBC will spot that and bind the data as 
> SQL_WCHAR or pass the SQL to SQLPrepareW/SQLExecDirectW (after 
> converting it to UCS2). The opposite for retrieving data is not true 
> unless you specifically bind all char types as SQL_WCHAR as this is 
> not the default in DBD::ODBC for varchar columns (just nvarchar 
> columns). Perhaps your thinking now this is where the flaw really is 
> but it is more difficult than that. DBD::ODBC asks the database what 
> the column type is and if it says it is an SQL_CHAR is is bound as an 
> SQL_CHAR (retrieving bytes in whatever encoding the database and 
> client libs decide) and if it says it is an SQL_WCHAR it is bound as 
> an SQL_WCHAR (and returned as UCS2 which DBD::ODBC happily converts 
> for you). In this guy's case, the column is identified as SQL_CHAR, it 
> is bound as SQL_CHAR and the returned data is windows-1252 and 
> DBD::ODBC knows nothing about it. I appreciate his problem to a degree 
> as it is a pain to have to decode every bound column.
>
> Of course, I could change DBD::ODBC when built in unicode mode to bind 
> all SQL_CHAR columns as SQL_WCHAR and that would also solve his 
> problem but a) it would be more expensive for those who don't need to 
> do that b) I'd worry it might break existing code e.g., anyone like 
> him who is already decoding the data themselves.
>
>> Can't the database connection/session settings be altered to assume utf8
>> at the client end and have the server or client libs automatically
>> convert for you? If so, that's a good way to go.
>>
>> Tim.
>
> Perhaps although at this stage I don't know how and in any case even 
> if they could be returned utf-8 encoded I would need to know they are 
> utf-8 encoded to do anything with them. The additional problem is that 
> UTF-8 encoded data may be longer that the size of the column as 
> reported to DBD::ODBC so it would need to know this in advance and 
> multiple all buffers by 4 or perhaps even 6 (can't remember right now 
> if unicode goes that far).
>
> Martin

Thinking about this more today and discussing it some of my colleagues I 
think I've come to a conclusion.

The points that sway me are:

o by default DBD::ODBC on Windows is built to support the Wide ODBC API 
which supports Unicode (not every unicode chr because it is UCS2 but most).

o if you are using the unicode build of DBD::ODBC you probably want to 
support unicode data.

o you are obviously doing something with any data you retrieve from the 
database and even if the encoding you retrieve from the database (or 
chrset) is not what you finally need (say as the person who rt'ed 
originally) you can easily add an encoding layer to stdout or the file 
etc you output to.

o returning the data in some native codepage like windows-1252 does not 
help you really, it probably hinders you and in any case you can encode 
it as windows-1252 afterwards when you output it somewhere.

As a result, I think it was probably an omission when DBD::ODBC was 
changed to support Unicode that it continued to retrieve varchar columns 
as SQL_CHAR and it should retrieve them as SQL_WCHAR (so long as the 
driver supports this - there is a SQLGetInfo call to check). In this way 
the data retrieved to a Perl script should always be accurate and can 
easily be translated to other charsets and encodings if that is what you 
need. The hopefully slight downside is that anyone running code with 
DBD::ODBC as it stands and like the person who started this on rt, if 
they know the data was coming back as windows-1252 (or whatever) and 
attempt to decode it, they will get the wrong data as it is already 
unicode characters.

I propose to change DBD::ODBC to always bind varchar etc columns as 
SQL_WCHAR and hence get unicode data in the unicode build but add a 
connection attribute to return it to the previous behaviour. I could 
have done it the other way around but this default seems more logical to me.

If you have any comments before I make this change I'd be happy to hear 
them.

Martin