Stephan Austermühle wrote:
> Hello Martin,
> Martin Evans schrieb:
> 
>> What do you mean by that? Select stops working it you stipulate your
>> perl script is utf-8?
> 
> Yes: When adding "use encoding 'utf8'" the result reads like this:
> 
>       1 G?nz v?le ?ml?ute und ?eugs
> 
>> You need to use utf8 to have utf-8 encoded data in your Perl script.
> 
> You mean "use utf8"? Tried this, no success.
> 
>> Your setup looks ok except I am not familiar with a "unicode built"
>> FreeTDS (what does that mean and what does it change in FreeTDS).
> 
> Sorry, I mixed things up. There is no Unicode option for FreeTDS.
> 
>> I have a default build of freeTDS here and it passes the unicode tests
>> in DBD::OBDC's t dir. Does your setup pass the DBD::ODBC tests?
> 
> No, it does not:
> 
>     t/40UnicodeRoundTrip.t .. 1/63 DBD::ODBC::st execute failed:
> [unixODBC][FreeTDS][SQL Server]Invalid data type (SQL-HY004) at
> t/40UnicodeRoundTrip.t line 116.
> 
>     #   Failed test 'no warnings'
>     #   at t/40UnicodeRoundTrip.t line 53.
>     # There were 1 warning(s)
>     #       Previous test 9 'bind WVARCHAR'
>     #       DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL
> Server]Invalid data type (SQL-HY004) at t/40UnicodeRoundTrip.t line 116.
>     #  at t/40UnicodeRoundTrip.t line 116
>     #
>     # Looks like you planned 63 tests but ran 10.
>     # Looks like you failed 1 test of 10 run.
>     # Looks like your test exited with 255 just after 10.
>     t/40UnicodeRoundTrip.t .. Dubious, test returned 255 (wstat 65280,
> 0xff00)
>     Failed 54/63 subtests
>     t/41Unicode.t ........... 1/56 DBD::ODBC::st execute failed:
> [unixODBC][FreeTDS][SQL Server]Invalid data type (SQL-HY004) at
> t/41Unicode.t line 118.
> 
>     #   Failed test 'no warnings'
>     #   at t/41Unicode.t line 49.
>     # There were 1 warning(s)
>     #       Previous test 8 'Bind parameter SQL_WVARCHAR'
>     #       DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL
> Server]Invalid data type (SQL-HY004) at t/41Unicode.t line 118.
>     #  at t/41Unicode.t line 118
>     #
>     # Looks like you planned 56 tests but ran 9.
>     # Looks like you failed 1 test of 9 run.
>     # Looks like your test exited with 255 just after 9.
>     t/41Unicode.t ........... Dubious, test returned 255 (wstat 65280,
> 0xff00)
>     Failed 48/56 subtests
> 
> Anyway, some other test fail as well but besides the Unicode issue
> everything is working fine.
> 
> Regards,
> 
> Stephan
> 

You have answered posts from me and Hendrik but rather than split them
up I've put all my comments in this post.

"By the way, UTF-16 != UCS-2LE?"

As far as I was aware, UCS-2 is the 2-byte Universal Character Set which
is an encoding system used before UTF-16 and they only differ in that
UCS-2 does not support surrogate pairs so it can only handle the Basic
Multilingual Plane (i.e., unicode chrs up to U+FFFF).

You are entering a world of pain. I'll try and explain how it is but it
is really complicated. You probably just want a straight forward answer,
build x like this and y like that but the truth is there is more than
one answer and I think we may need some FreeTDS input.

Some background:

At the time DBD::ODBC was made unicode aware (other than the ability it
had of being able to accept SQL_WCHAR as a bind type but just return the
raw data from the db) SQL Server 2000 and SQL Server 7 only supported UCS-2.

The ODBC specification for unicode data is really old now and although
it does not rule out surrogate pairs it is mostly described in a way
which leads to the belief it was written with UCS-2 in mind. Some of the
ANSI ODBC APIs have an equivalent unicode/"Wide" version e.g.,
SQLDriverConnectA and SQLDriverConnectW (the W referred to in the spec
as Wide) which take 2 byte characters and no mention of surrogates. On
Windows (where ODBC originated) UCS-2 was used by Windows
and applications which are unicode enabled call the Wide functions
passing and accepting UCS-2 characters. The driver manager has not got
to perform any translations because the application is UCS-2, the
application calls the Wide functions and the driver has the Wide
functions (and accepts UCS-2). However, if the application uses ASCII
(with 8 bit extensions) it will call the ANSI ODBC API and if the driver
has the Wide functions the driver manager will take the 8bit data passed
to the SQLxxxA functions, convert it to UCS-2 then pass it to the
SQLxxxW function in the driver. When the application retrieves data, the
driver manager calls SQLFetch in the driver which returns UCS-2 or ASCII
data depending on the column type, the driver manager attempts to
convert it back to 8bit data and passes it back to the application
(obviously lossy). The same happens the other way around if the
application is unicode and the driver is not unicode but the lossy bit
is the other way around.

In unix things are quite different and there is more than one unix-like
OS and they are not the same. We have wchar_t in C to store unicode
characters and it is sometimes 2 bytes and sometimes 4 bytes and some
compilers even have options to make it one or the other however, these
days the UTF-8 encoding seems to be a lot more popular (as it is in
Perl) probably because people got fed up with wchar_t and UTF-8 and 7
bit ASCII are the same and all those string functions in C work on them
(well most do). No unix OS manufacturer even released their own ODBC as
far as I know although some of them have released ODBC drivers for
databases they also wrote (e.g., IBM).

Over time there have been 3 main ODBC driver implementations on unix -
iODBC in 2 guises (the latest looked after by OpenLink) which is open
source and I have been told uses wchar_t, the DataDirect one (which I
believe, but may be wrong, is licensed such that you can only use it
with their drivers, is closed source and use UTF-8 encodings
exclusively) and unixODBC which is open source and the one I know best.
I cannot really talk that much about the driver managers other than
unixODBC as I have had little or nothing to do with them. Each have
taken a different tact with regards to Unicode but I know the intention
of the maintainers of unixODBC has always been to mirror the ODBC driver
manager on Windows as closely as possible. Initially there was little
interest in unicode on unix with ODBC but at some stage unixODBC was
changed to use the iconv library to perform conversions from one
encoding to another. This was only really necessary because each side of
unixODBC (the application and the driver) was not compatible - i.e.,
they both didn't talk "Wide" characters. Most ODBC drivers on UNIX did
not even support the "Wide" functions. At this stage you can build
unixODBC with iconv and specify the source and destination encoding and
it will perform some translations for you if the app is using the ANSI
functions but the driver has the Wide functions or vice versa BUT
unixODBC does no translation with bound data (i.e., data retrieved via
SQLBindCol or passed with SQLBindParam and DBD::ODBC uses those calls a
lot). Also, bare in mind that ODBC itself, cannot really handle UTF-8
data because of anomalies like SQLGetData that require counts of bytes
returned but also require a full buffer of data to be returned if there
is more data requested than the buffer provided (e.g., if you supply a
buffer of 1000 bytes to SQLGetData and the driver was to return UTF-8
encoded data then what happens when at byte 1000 you are part way
through a UTF-8 sequence but there are more chrs to return?).

SQL Server

MS SQL Server uses UCS-2 and that encoding is determined per connection
or per result-set for later SQL Servers (I think what is deemed TDS 8).
There are issues with NCHAR data mixed with CHAR data in the protocol
which I cannot remember right now but I think they were something to do
with the char data always comes back as ASCII and the NCHAR as UCS-2.

DBD::ODBC

When you build DBD::ODBC with the -u switch it changes quite a lot - see
http://search.cpan.org/~mjevans/DBD-ODBC-1.23/ODBC.pm#Unicode - and
since then (I note I omitted to update this, it supports unicode
usernames, passwords and DSN names now too).

If you have a unicode aware ODBC driver which supports the Wide
functions unixODBC does not come in to it when using DBD::ODBC and Perl.
An example of this is the Easysoft SQL Server ODBC Driver (pleae note my
sig as I work for Easysoft) where to get and pass unicode data in perl
to/from SQL Server, DBD::ODBC looks at strings passed to it from Perl to
see if they are UTF-8 encoded and translates them to UTF-16 and passes
them to the Wide functions and data which is returned from the driver
which is unicode is translated back to UTF-8 (by DBD::ODBC) before
passing back to Perl.

freeTDS

FreeTDS is not a unicode ODBC driver in terms of ODBC i.e., it does not
have the Wide functions. However it does have a thing called client
charset which tells FreeTDS the encoding of data coming from your
program and what encoding should be used for data returned
from the server, before sending it back to your program. I do not know
for certain but I'm guessing it does not touch SQL_WCHAR characters
bound as SQL_WCHARs but that is something you'd have to ask the FreeTDS
guys. What I do know is DBD::ODBC is just an ODBC application like many
on Windows and a) works fine with unicode on Windows to SQL Server, MS
Access etc and b) works fine on unix with a conforming driver.

A quote from the FreeTDS web site - "At some future time, FreeTDS aims
to support Unicode and other multi-byte character sets. It does not do
so at the current time."

use encoding xxx

This is used in Perl to say your script is encoded in xxx. Just because
you have and accept UTF-8 encoded data does mean you need to "use
encoding" but if your script is encoded in xxx you need "use encoding
xxx". For instance, the example Hendrik gave you includes unicode
characters but does not need encoding. As a result, I cannot see how
adding "use encoding 'utf-8'" should make any difference to data
returned from sql server through DBD::ODBC.

odbc_default_bind_type

This is used to override the default bind type (type passed to
SQLBindParam). If the ODBC driver supports SQLDescribeParam, DBD::ODBC
uses the type the driver reports via SQLDescribeParam and if it does not
support SQLDescribeParam it defaults the type to either SQL_VARCHAR or
SQL_WVARCHAR (depending on whether it was built for unicode or not - see
"-u" switch for Makefile.PL). You have FreeTDS, you also have a unicode
enabled DBD::ODBC and so it cannot know the bind type and defaults to
SQL_WVARCHAR - I don't think there is anything wrong with that and so I
don't know why SQL Server via FreeTDS reports:

DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Invalid
data type (SQL-HY004)

I think changing odbc_default_bind_type to SQL_VARCHAR has just muddied
the waters.

I have tried with various combinations of unixODBC and freeTDS to get
unicode working but have not managed to do so. This is the script I used:

use DBI;
use strict;
use warnings;
use Data::Dumper;

my $h = DBI->connect('dbi:ODBC:xxx','xxx','xxx');
#####$h->{odbc_default_bind_type} = 12;
eval {$h->do(q/drop table martin/);};
print "Has unicode: " . $h->{odbc_has_unicode} . "\n";
$h->do(q/create table martin (a nvarchar(100))/);
my $s = $h->prepare(q/insert into martin values(?)/);
$s->execute("\x{20ac}");
my $r = $h->selectall_arrayref(q/select * from martin/);
print Dumper($r);
my $sql = 'insert into martin values(' . $h->quote("\x{20ac}") . ')';
$h->do($sql);
$r = $h->selectall_arrayref(q/select * from martin/);
print Dumper($r);

and this is what is output with the Easysoft SQL Server driver:

Has unicode: 1
$VAR1 = [
          [
            "\x{20ac}"
          ]
        ];
$VAR1 = [
          [
            "\x{20ac}"
          ],
          [
            "\x{20ac}"
          ]
        ];

and this is what happens with FreeTDS:

Has unicode: 1
DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Invalid
data type (SQL-HY004) at unicode_sql.pl line 16.
$VAR1 = [];
$VAR1 = [
          [
            '�'
          ]
        ];

or the following when changing odbc_default_bind_type to 12:

Has unicode: 1
DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Error
converting characters into server's character set. Some character(s)
could not be converted (SQL-HY000) [state was HY000 now 42000]
[unixODBC][FreeTDS][SQL Server]The incoming tabular data stream (TDS)
protocol stream is incorrect. The stream ended unexpectedly. (SQL-42000)
at unicode_sql.pl line 16.
$VAR1 = [];
$VAR1 = [
          [
            '�'
          ]
        ];

I tried setting the "client charset" both in the odbc.ini and in the
global section of the freetds.conf to no avail.

I'd welcome any comments from the FreeTDS folks but if none are
forthcoming I suggest you try their mailing list. If they tell you
anything is wrong in DBD::ODBC please pass back the comments and I'll
happily look into them or you/they can just rt them.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

Reply via email to