FreeTDS Development Group

["Justin Hart" <JHart AT webroot.com>]

(Was using digest mode which isn't the best for responding to messages!)

Ok, so I've chalked my errors below up to cruddy data in the database,
and a flawed test (was looking at one database for one thing, another db
for another).  In general, we've found our issue.

We were using odbtp to transport php/apache/freebsd database calls to
mssql - everything was good in testing (we had no problems, and
performance was pretty nice).  It doesn't require you to use the "INSERT
INTO ... (nvarcharcol) VALUES (N'unicode')" syntax (it seems to
automagically munge the query on the way).  When we tested with freeTDS,
this is not the case (really, the way we should have expected), so our
inserts without the N'' were coming in as ascii to mssql.  

This is a failing of our code (reliance on PEAR::DB and the autoprepare
functions, which don't seem to know about this convention), and we're
working around that.

As a big thanks to the FreeTDS community, we've switched away from odbtp
because of stability issues of their transport agent (odbtpsrv) and
we're rolling out FreeTDS as our primary connector for a 20+ webserver
infrastructure.  We'll be looking into FreeTDS pooling servers in the
future as we continue to require more front-end servers.  

We're also looking for any performance tuning tips you guys may have -
but since my request here is at the end of a long email, I'll make
another request later after perusing through the docs again.

Thanks again.

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of
freetds-request AT lists.ibiblio.org
Sent: Saturday, March 04, 2006 10:00
To: freetds AT lists.ibiblio.org
Subject: FreeTDS Digest, Vol 38, Issue 5

Subject: Re: [freetds] Dealing with MSSQL Unicode data
To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
Message-ID:
        
<3778FAB3D61A7A40833F1CB3B8C173AA054FCD9E AT AC2KAMA0107.ac.lp.acml.com>
Content-Type: text/plain;       charset="US-ASCII"

> 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.


------------------------------

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


End of FreeTDS Digest, Vol 38, Issue 5
**************************************