DBD:ODBC - Retrieving Unicode values on W2K + MSSQL 2000

DBD:ODBC - Retrieving Unicode values on W2K + MSSQL 2000

Post by Olivier Poul » Thu, 12 Sep 2002 05:33:41



Using ActiveState Perl 5.6.1 build 633
The following happens when using either ActiveState's DBI & DBD:ODBC
ppds or the latest ones posted by Jeff Urlwin.

When retrieving ntext, nvarchar et al. values from a MS SQL 2000 server
using DBD::ODBC, extended characters show up as ?????

I'm also unable to get "correct" values through DBD::ADO, Win32::OLE and
Win32::ODBC, even when disabling AutoTranslate in the DSN properties.
The only workaround I have found is by using DBD:JDBC (with a bad hack
for SQL_BIGINT) and MS's JDBC driver.

I've also tried to find hints as to where or what to look for in various
lists and archives, but to no avail.

Is it possible to retrieve / insert Unicode values using DBD::ODBC and
MS SQL 2000 ? If so, what am I missing here ?

--
Olivier Poulet

 
 
 

DBD:ODBC - Retrieving Unicode values on W2K + MSSQL 2000

Post by Jeff Urlw » Thu, 12 Sep 2002 06:46:09


Oliver,

I know there are some fixes in DBD::ODBC from a long time ago which handled
ntext columns, but I have not kept up with Perl and Unicode specific
changes, therefore there may be a problem.

If you can cobble together a short perl script which:
        - Creates a test table
        - inserts test data
        - retrieves test data (and check accuracy if possible)

If you can package up a short test like this, I can look at it...see the
mytest directory for similar samples, such as joetest?.pl or t\20SqlServer.t
for examples.  Ultimately, I'd like to make a unicode test part, but it will
have to start in t\20SqlServer.t anyway...

Regards,

Jeff

Quote:

> Using ActiveState Perl 5.6.1 build 633
> The following happens when using either ActiveState's DBI & DBD:ODBC
> ppds or the latest ones posted by Jeff Urlwin.

> When retrieving ntext, nvarchar et al. values from a MS SQL 2000 server
> using DBD::ODBC, extended characters show up as ?????

> I'm also unable to get "correct" values through DBD::ADO, Win32::OLE and
> Win32::ODBC, even when disabling AutoTranslate in the DSN properties.
> The only workaround I have found is by using DBD:JDBC (with a bad hack
> for SQL_BIGINT) and MS's JDBC driver.

> I've also tried to find hints as to where or what to look for in various
> lists and archives, but to no avail.

> Is it possible to retrieve / insert Unicode values using DBD::ODBC and
> MS SQL 2000 ? If so, what am I missing here ?

> --
> Olivier Poulet


 
 
 

DBD:ODBC - Retrieving Unicode values on W2K + MSSQL 2000

Post by John Mapol » Thu, 12 Sep 2002 06:55:15


Olivier,
Working in perl ActiveState 5.6.0 and cygwin with
DBD::ODBC I've had somewhat similar problems.  My data
set contained characters encoded in UTF-8.  When these
were loaded into SQL server they were
miss-represented, i.e. all UTF-8 characters appeared
as two extended characters.  I first translated my
data set to Latin-1 and have had no problems since.
My selects never return '????'.  But, the extended
characters will be represented differently depending
on your shell and editor.
John Mapoles


Quote:> Using ActiveState Perl 5.6.1 build 633
> The following happens when using either
> ActiveState's DBI & DBD:ODBC
> ppds or the latest ones posted by Jeff Urlwin.

> When retrieving ntext, nvarchar et al. values from a
> MS SQL 2000 server
> using DBD::ODBC, extended characters show up as
> ?????

> I'm also unable to get "correct" values through
> DBD::ADO, Win32::OLE and
> Win32::ODBC, even when disabling AutoTranslate in
> the DSN properties.
> The only workaround I have found is by using
> DBD:JDBC (with a bad hack
> for SQL_BIGINT) and MS's JDBC driver.

> I've also tried to find hints as to where or what to
> look for in various
> lists and archives, but to no avail.

> Is it possible to retrieve / insert Unicode values
> using DBD::ODBC and
> MS SQL 2000 ? If so, what am I missing here ?

> --
> Olivier Poulet

__________________________________________________
Yahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
http://dir.remember.yahoo.com/tribute