FreeTDS Development Group

[christos AT zoulas.com (Christos Zoulas)]

On Jul 22,  7:04pm, jklowden AT freetds.org ("James K. Lowden") wrote:
-- Subject: Re: [freetds] unicode

| Christos Zoulas wrote:
| > 
| > If you declare a column nstring instead of string then you should
| > use N'literals' (and the other way around). If you don't the server
| > will make an implicit conversion for you. 
| 
| The server makes an implicit conversion regardless.  
| 
| The string arrives at the server encoded as UCS-2, as you know.  It is
| stored in a column according to the column's encoding, as you know. 
| Whether or not you put an 'N' at the front.  
| 
| > It is very important to
| > get the types right, because if you don't, then the implicit
| > conversion will happen on each row, prohibiting the use of indexes,
| > and have a severe performance impact.
| 
| Not if the client is correctly configured.  It's not as though there's a
| mix of unicode and non-unicode in the column.  
| 
| 1.  When  'literal' is stored as  varchar, it uses the single-byte
| encoding. 
| 2.  When N'literal' is stored as  varchar, it uses the single-byte
| encoding. 
| 3.  When  'literal' is stored as nvarchar, it uses USC-2.
| 4.  When N'literal' is stored as nvarchar, it uses USC-2.
| 
| There's no other choice.  
| 
| What you *can* do is goof up the client end.  You can get "correct"
| results as far as the application is concerned while storing complete
| garbage.  
| 
| If, say, your client is ISO 8859-1 and you use the N'UFT-8' form, FreeTDS
| will convert your utf-8 data as though it were 8859-1.  Byte are bytes; it
| will "convert".  The data arrive as "USC-2", seemingly.  When stored in a
| varchar column, those bytes are converted again, according to the column's
| collation.  (They won't be the text you sent, but they'll store OK.) 
| Querying with another tool will show problems.  
| 
| Except that it won't completely work, because not every value in the
| [0-255] range is valid ISO 8859-1.  But FreeTDS will complain about that. 
| 
| 
| What am I missing?  I still don't see what effect the 'N' prefix can have.

It is not a freetds issue. On query analyzer, create a table with a
varchar column. Add an index to it. Then select * from table where
column = N'foo'. Look at the query plan. Try the same with select * from
table where name = 'foo'.

If you are using java, then you should set the URL for the server to have
sendStringParametersAsUnicode appropriately.

As a point of reference in java, using stmt.setString(string)
produces a different trace as stmt.setBytes(string.getBytes()).
The names might be wrong here.  I have not had this problem for a
long time :-)

christos