[freetds] UTF-8 support: What about N'xxx' string literals?

Sebastien FLAESCH sf at 4js.com
Thu Aug 14 05:08:45 EDT 2008


James K. Lowden wrote:
 > Sebastien FLAESCH wrote:
 >> 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.

Is this not the case for most single-byte / non-UNICODE/UCS-2 applications
using other characters as US-ASCII?

I mean, why would I use NCHAR/NVARCHAR if my ISO-8859-? characters fit
in a CHAR/VARCHAR columns?

Ok... one should go with UNICODE now, but we have customers out there with
millions of lines of legacy code, using ISO-8859-1 charset...

Remember our product is a 4GL language, where you can write for example:

    DATABASE stores
    INSERT INTO tab1 VALUES ( 123, "abcéîô" )

That code must work with different RDBMS (Oracle, SQL Server, Informix),
in the charset defined by LANG/LC_ALL. So I don't want to add the N prefix
syntax in our language, that stuff should be transparent...

As far as FreeTDS is concerned, I assume that we can automatically add
the N prefix before any string literal, whatever FreeTDS client charset is
...... correct?

Just to test:

I made a little ODBC test (attached), combining CHAR/VARCHAR, NCHAR/NVARCHAR
and string with and without N prefix, in a GREEK environment (client gets
GREEK charset and database was created with collation Greek_BIN).

In any case the characters are correctly inserted in the database and fetched
back... I checked on the server side if characters are ok in the tables.


James K. Lowden wrote:
> 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
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: odbctest11-greek.c
Url: http://lists.ibiblio.org/pipermail/freetds/attachments/20080814/df03b44b/attachment.c 

More information about the FreeTDS mailing list