FreeTDS Development Group

["Lowden, James K" <james.k.lowden AT alliancebernstein.com>]

> From: Justin Hart
> Sent: Friday, March 03, 2006 1:19 PM
> 
> Data is getting stored into the database (from a native odbc
> connection on windows) UTF-8 encoded.  That is how it is stored in
> the database, on the lower order byte of the UCS-2 'character'.

When you say "Data is getting stored into the database", do you mean
"via a non-FreeTDS connection"?  Because FreeTDS won't store UTF-8 in a
Microsoft database without being tricked into it.  

There are two kinds of character data on a Microsoft server:

1.  char/varchar/text.  These are single-byte encoded, cf. "Collations"
in Microsoft's documentation.  

2.  nchar/nvarchar/ntext.  These are UCS-2 encoded.  

There's no provision for UTF-8 on a Microsoft server; in an
all-Microsoft environment, there's no need for it, because Microsoft
clients support UCS-2 natively.  Microsoft's native tools (e.g.
isql.exe) wouldn't be too happy with UTF-8 data, either.  

The server declares the data's encoding prior to sending them, and
FreeTDS takes the server at its word.  At the top of a TDSDUMP log,
you'll see a record of the encodings being used for the session.  TDS
8.0 allows per-column encoding, which IIRC is also logged.  The
intention is to honor the server's declaration perfectly, giving the
user a little work as possible.  

> When that gets passed in through FreeTDS, 'looking' like UCS-2 (but
> is really just UTF8 on the low order byte), iconv attempts to
> convert it to UTF8.  This causes a jumbled mess.

If your data are declared to be encoded as UCS-2 but are in fact UTF-8,
the iconv layer in FreeTDS will likely complain early and often.  From
its point of view, its inputs will be invalid, a/k/a "a jumbled mess".
;-)

> Has anyone seen this?  Is there a way to turn off the auto-handling
> in FreeBSD to let another library deal with the characters, or
> something like that?  I'm a little stuck as far as Unicode goes.

If you want to "just get the bits" you can try changing your query to
convert the UTF-8 data to image ("CAST(columnname AS IMAGE)").  FreeTDS
does not interpret non-character data; it will pass them through to your
application.  

The right answer, though, is to work from first principles.  If in fact
you're storing UTF-8 data in a UCS-2 column, stop!  Either change the
column's datatype to IMAGE or correct the data's encoding.  Then you'll
be able to view the data naturally using native tools and extract them
easily with FreeTDS.  

HTH.  

--jkl
 

-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent 
responsible
for delivering this message to the intended recipient, any review, 
dissemination,
distribution or duplication of this communication is strictly prohibited. If 
you are
not the intended recipient, please contact the sender immediately by reply 
e-mail
and destroy all copies of the original message. Please note that we do not 
accept
account orders and/or instructions by e-mail, and therefore will not be 
responsible
for carrying out such orders and/or instructions.  If you, as the intended 
recipient
of this message, the purpose of which is to inform and update our clients, 
prospects
and consultants of developments relating to our services and products, would 
not
like to receive further e-mail correspondence from the sender, please "reply" 
to the
sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New 
York,
NY 10105.