FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Sebastien FLAESCH wrote:
> 
> Otherwise, non-prefixed strings are converted by SQL Server to the
> default code page of the database... (following docs).
> 
> Actually SQL Server docs talk about "Server side" programming, where
> strings should use N prefix for UNICODE (UCS-2), but what about the
> clients then?
> 
> From my understanding, if I want to use UTF-8 encoded strings in my SQL
> stmts, (defining my FreeTDS client charset as UTF-8 and forcing FreeTDS
> to convert from UTF-8 to UCS-2 for TDS exchanges), I need always to
> specify N prefix to avoid the server-side conversion to the database
> charset defined by the collation.
> 
> Since FreeTDS converts from any client charset to/from UCS-2 for TDS
> exchanges, the final string literal will be encoded in UCS-2 and so the
> client is seen as a UCS-2 client from the server... right?

Right.  The server parses and interprets the SQL regardless of where and
how it's entered.  Unprefixed strings are converted to the single-byte
encoding first, then again to UCS-2 as needed.  

I decided there's a method in the madness: by losing information in this
way, the user gains a transparency between char/nchar columns.  No matter
the column's datatype, the character stored is always the same, given
"naïve" use i.e. unprefixed string literals.  Only when you manifestly
declare your interest in using Unicode -- by prefixing your strings with
'N' -- do you start seeing differences.  

Not my first choice, but not utterly stupid, either.  

> So should not ALL FreeTDS application use the N prefix for string
> literals, even if the encoding is for example ISO-8859-1?

Only if they're careful about their use of non-Unicode columns.  

> Note that the first insert with latin characters "abcéÓÎ" / "abcÛÊé"
> (2 bytes long) works, it's the second insert using Asian characters
> (3 bytes long)...

I bet you can store the word "naïve" (two dots over the i) in a CHAR
column in your database, because it's within ISO 8859-1.  (The "ï" is
0xEF.)  It's not a matter of "Latin" or not; it's a matter of whether or
not the character exists in your single-byte character set.  Try something
like a 'T' with '^' over it.  See the earlier thread a few weeks ago
wherein it lost (or not) its hat, depending on whether the string was
prefixed with 'N'.  

> Maybe some words about this could be added to:
> 
> http://www.freetds.org/userguide/localization.htm

Good suggestion.  I'm working on it.  Thanks.  

--jkl