[freetds] Cant's select columns with umlaute
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 18.104.22.168.0 plus bugfix 7389110 and we installed
>> the "linux.x64_11gR1_gateways". I configured freetds and uinixodbc (see
>> 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
> 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
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.
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