FreeTDS Development Group

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

Eddie Gieze wrote:
> And the command gives me this:
...
> I think it's not selecting the right database..

Maybe not.  You can prepend the database name to the table name: "from
mydatabase..tblitem".  

> 1> select content, cast(cast(content as nvarchar(40)) as varbinary(40)) 
> as HexContent FROM tblitem WHERE id = 1317
> 2> go
> content HexContent
> Msg 2403, Level 16, State 0, Server OpenClient, Line 0
> WARNING! Some character(s) could not be converted into client's 
> character set. Unconverted bytes were changed to question marks ('?').
> t????t  7400c4001c20c500a0007400
...
> What i find strange about the hexcontent is that it uses 8 bytes for 
> two characters.. and it's translated into 4 question marks. I'm 
> puzzled. My guess is i'm requesting the data in a wrong format from the 
> server? I'm requesting it as SQL_C_CHAR in my program.

I'm not sure I understand you; I think you might be confused about what
we've established.  This SQL:

        cast(cast(content as nvarchar(40)) as varbinary(40))

causes the server to convert your TEXT to VARCHAR (same bit pattern), and
then from VARCHAR to VARBINARY.  The server sends the data, labelling the
field as VARBINARY (rather than, say, TEXT), but the bit pattern hasn't
changed.  Since it arrives as "binary", it doesn't pass through iconv. 
(Only character data are subjected to that.)  tsql won't splat "binary"
data on the screen, so it converts it to a hex string for you.  Assuming
it did so correctly, what you see is exactly what the server sent, in hex.
 It matches what we saw in your TDSDUMP log.  If you try the same query
using Microsoft's tools, isn't that what you see?  

So, I'm not sure which "two characters" you mean.  As far as I'm
concerned, tblitem.content where id=1317 is a 6-character UCS-2 string:

        7400 c400 1c20 c500 a000 7400 (little endian)

which should be:

U+0074 't'
U+00c4 'Ä'
U+201c '"' [double turned left quotation mark]
U+00c5 'Å'
U+00a0 ' ' [nonbreaking space]
U+0074 't'

I'm a little bit confused by the error message and the incorrectly
converted string "t????t".  There is no UCS-2 datum that cannot be
represented as UTF-8; they're just two kinds of Unicode.  Why does FreeTDS
complain?  I have to guess that for some reason or other, this connection
was not using UTF-8 as its client-side charset.[1]  

We have to isolate a few things:

1.  The client-side charset.  If UTF-8, you won't see a 2403 message
unless a) the server-side data are corrupt, or b) you hit a bug.  At this
point, I don't think either of those is the case.  

2.  The server's data.  I don't see anything wrong with your UCS-2 data,
although I wouldn't try to pronounce it, especially not in mixed company. 
It sounds like it wasn't what you were expecting, though.  

3.  Your locale setting, if that's affecting anything.  

4.  Higher layers.  I'm working with tsql and the dump to make sure the
iconv conversion is happening correctly (as seems to be the case).  

> I'm requesting it as SQL_C_CHAR in my program.

Well, that's another layer.  Somewhere, something has to take the data out
of the TDS datastream and make it available to the application.  If you
bind your program's variable, the (ODBC?) library will park the data in it
for you.  The iconv work is long done by then; bindings will convert from,
say, int to char, but they won't (afaik) alter the encoding of character
data.  (Presumably, if you asked for UCS-2, UTF-8, whatever, then that's
what you want.)  But if you're the PHP stratosphere somewhere, then you're
talking to the wrong guy.  Once we establish your data were delivered
intact, well, you'll have to look elsewhere.  

--jkl

[1] In your last message, your locale wasn't UTF-8; it was ASCII.  But
your freetds.conf was right, because the connection used UTF-8 for the
client side.  I don't think tsql honors your locale, so I'd assume your
connection would (still) use UTF-8.  But it clearly isn't.