[freetds] Cant's select columns with umlaute

Frediano Ziglio freddy77 at gmail.com
Tue Dec 9 11:08:53 EST 2008


2008/12/9 James K. Lowden <jklowden at freetds.org>:
> Michael Schodra wrote:
>>
>> I try to established a DB-Link from Oracle 11g to a MSSQL 2000 database
>> but it doesn't work.
>>
>> We install on Centos 5.1 (Linux 64bit).
>> Our database is Oracle 11.1.0.6.0 plus bugfix 7389110 and we installed
>> the "linux.x64_11gR1_gateways". I configured freetds and uinixodbc (see
>> below)
>>
>> I already told the unixodb folk about the problem, but they told me: ask
>> the freetds folk. So I do now and I hope you can help me?
>>
>> Here is my configuration and the problem.
>>
>>
>> 1. unixODBC (unixODBC-2.2.11)
>> 1.1 /etc/freetds.conf
>> combandb <http://forums.oracle.com/forums/>
>> host = 212.xxx.xxx.5
>> port = 1433
>> tds version = 8.0
>>
>> Client Charset = UTF-8
> ...
>> UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
>
> 1.  When the umlauted character is passed to SQLPrepare, is it in fact
> encoded as UTF-8, or is it possibly ISO 8859-1?  What is your intention?
>
> 2.  What is the output of "tsql -C"?  When you connect with tsql, what
> encoding does it report?
>
>> SQL = select ID, Ort, Straße from x_lt; length = 62 (SQL_NTS)
>
> 62?  There are 32 characters.  Evidently, 31 were converted to UCS-2,
> giving a length of 62 = 2 * 31.
>

Probably Oracle call SQLPrepareW (wide version)

>
> I suspect the 'ß' cannot be converted to UCS-2 because iconv(3) is
> presented with input (your SQL text) that is not encoded in the character
> set it was told to expect.  The 'ß' for example is 0xE1 in 8859-1, which
> is not a valid value in UTF-8.  The UTF-8 sequence is  c3 a1 (both of
> which are valid 8859-1 characters).
>

by default (I don't know how to change this behavior) unixODBC convert
UCS-2 using ISO8859-1 but FreeTDS assume UTF-8 so you get error
preparing.

> Because the 'ß' encoded as ISO 8859-1 cannot be interpreted as UTF-8 --
> but could be interpreted, albeit incorrectly, as ISO-8859 if encoded as
> UTF-8 -- I suspect that FreeTDS is responding to your freetds.conf entry
> above.  It expects UTF-8, but its input is actually ISO 8859-1.
>
> The solution is match what FreeTDS expects to what it's given.  Either
> change your locale(1) settings to use UTF-8 (so that what you type at the
> terminal arrives as UTF-8) or tell FreeTDS to expect ISO 8859-1.  The
> latter could be done by simply removing the "client charset" line from
> freetds.conf.
>

I think this can work with this particular configuration.

> You can verify the above with a TDSDUMP log.  It will show your SQL text
> as a hex dump, and at the top of the file it reports what encodings it's
> using.  You should also see errors from tds_iconv about inabilty to
> convert characters to be sent to the server.
>
>> Isql> select * from x_lt
>>
>> Works well, but umlaute are missing.
>
> When sending to the server, data encoding conversion errors result in an
> error and the function fails.  When fetching data from the server, data
> encoding conversion errors result in a warning, which your client tool
> seems not to report.
>
> HTH.
>
> --jkl

freddy77

PS: I have still to finish coding wide characters support for
SQLBindParameter/SQLBindCol/SQLGetData/SQLPutData (well.. only
SQLPutData is partially working, others works quite well)... for wide
functions (like SQLPrepareW) there is no plan at all...


More information about the FreeTDS mailing list