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
