Martin Evans wrote:
> 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

I persevered and got a little closer with cvs version of freetds,
unixODBC 2.2.14 (built with --with-iconv-char-enc='UTF-8',
--with-iconv-ucode-enc='UCS-2LE' and --with-iconv-prefix=x). CVS
versions seems to sort out SQL_WVARCHAR issue and we now get some unicode.

results for same code above still with odbc_default_bind_type commented out:

Has unicode: 1
$VAR1 = [
          [
            "\x{20ac}" # <-- ok
          ]
        ];
$VAR1 = [
          [
            "\x{20ac}" # <-- ok
          ],
          [
            "\x{ac}" # <-- wrong
          ]
        ];

So, just the insert from SQL not working there. I had "client charset =
UTF-8" in both my DSN/odbc.ini and freetds.conf global section and then
I had them commented out - did not seem to make much difference. BTW, I
had some trouble building from CVS - problems in unittests and getting
it to build with iconv.

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

Reply via email to