Fellow Monks,
I need to manipulate Unicode data stored in an MS SQL server, and would rather
use DBI + DBD::ODBC than my current Win32::OLE solution, for easier portability
(the scripts currently run on Win32, but could be moved to a Unix box...)

However, I am unable to correctly fetch from MSSQL's nvarchar, nchar and ntext fields
with DBI/DBD ODBC. I only get "??????" characters from the DB

I've included a small sample script that can be run to test this, provided you have non-ASCII values in the table. 
use utf8;
use strict;
use DBI;
use Win32::OLE;

# Required to get Unicode correctly CP specifies Perl's "internal" enc
+oding
# LCID 65001 foces OLE to use (or stop translating) UTF8
# Beware that $variant->Time and $variant->Date will need an LCID in a
+ddition
# to the format string, e.g. 1033 for US English

Win32::OLE->Option(CP => Win32::OLE::CP_UTF8, LCID => 65001);

my $odbc_dsn = qq/driver={SQL Server};server=$database_name;AutoTransl
+ate=OFF/;
my $ado_oledb_dsn = <<EODSN;
Provider=sqloledb;
Data Source=$database_name;
Integrated Security=SSPI
EODSN

my $sql =<<EOFSQL;
select unicode_text
from unicode_table
where
ut_id = 1
EOFSQL


my $dbh = DBI->connect("dbi:ODBC:$odbc_dsn", "", "");

open FH, ">:utf8", "test_odbc.txt";
print FH ($dbh->selectrow_array($sql))[0];
close FH;

$dbh->disconnect;


$dbh = DBI->connect("dbi:ADO:$ado_oledb_dsn", "", "");
open FH, ">:utf8", "test_ado.txt";
print FH ($dbh->selectrow_array($sql))[0];
close FH;
$dbh->disconnect;

my $odbc_connection = Win32::OLE->new('ADODB.Connection');
$odbc_connection->Open($ado_oledb_dsn);

my $recordset = Win32::OLE->new('ADODB.Recordset');

$recordset->Open($sql,$odbc_connection);
open FH, ">:utf8", "test_ole.txt";
print FH $recordset->Fields(0)->Value;
close FH;

$odbc_connection->Close();

[download]

test_odbc.txt contains this: ?????????
test_ole and test_ado.txt contains the correct value, a string in Japanese.

I am running this on WinXP / Win2k, with DBI 1.43 & DBD::ODBC 1.07 on Activestate perl 5.8.0
I have tried various DBI/DBD::ODBC combinations on As 5.8.0 & 5.8.3, but to no avail.
Has anyone encountered this and found a solution ?

Update

I've found that DBD::ADO would be something to consider, as I found out it uses Win32::OLE itself, and
thus can coerce Windows into using CP_UTF8 / 65001 as Codepage
Before trying this out deeper, I'm wondering what would happen if Win32::OLE were already initialized
by another script/module, before DBD::ADO gets called and changes the Codepage. I'm asking, as my data
access functions are actually stored in a package that I then include. Does anyone have tips/pointers for this ?
Regards,

--
Olivier

In reply to DBD-ODBC: How to use Unicode on Win32 ? by olivierp

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.