[freetds] Unicode problem
James K. Lowden
jklowden at freetds.org
Mon Sep 9 23:58:59 EDT 2013
On Mon, 09 Sep 2013 22:48:48 +0400
Vano Beridze <vano.beridze at silkroad.ge> wrote:
> This is the first lines of /tmp/freetds.log
> log.c:196:Starting log file for FreeTDS 0.91
> on 2013-07-05 23:20:33 with debug flags 0x4fff.
> iconv.c:330:tds_iconv_open(0x7fd771f79e10, UTF-8)
> iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
> iconv.c:187:local name for UTF-8 is UTF-8
> iconv.c:187:local name for UCS-2LE is UCS-2LE
> iconv.c:187:local name for UCS-2BE is UCS-2BE
> iconv.c:349:setting up conversions for client charset "UTF-8"
> iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
> iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
OK, you're using an iconv library, and UTF-8 for a client character
If you select just one row with tsql, is it displayed correctly? If
not, does tsql print an error about not being able to convert?
If the FreeTDS can't convert the data received from the server to the
client's encoding, it raises an error. tsql will print that message if
it occurs. There are 3 possibilities:
1. tsql displays the characters correctly in the terminal. Hooray!
Go look into PHP.
2. tsql does not display the characters correctly, and produces a
message saying it encountered problems during conversion. That's an
iconv problem. Check your documentation and make sure iconv(1) works
on known data.
You can also execute a query like
select cast('hello' as nvarchar(5)) as hello
If tsql displays that correctly, you might have corrupt "Unicode"
data in the nvarchar column you're working with.
3. tsql does not display the characters correctly, but produces no
error message. If you can't read the characters and don't see an error,
then FreeTDS encountered no difficulty converting, but your system
can't actually display the data. Perhaps because of fonts or
> As for UCS2 data, table column type is nvarchar(30) and as I know if
> the column is nvarchar or nchar it is stored as UCS2
Figure out what the bit representation is for one row, and satisfy
yourself that it's little-endian 2-byte Unicode. You can use "select
cast( foo as varbinary )" to get a hex representation, or look at the
data packet in a TDSDUMP log.
> On 9/5/2013 6:51 AM, James K. Lowden wrote:
> > On Tue, 03 Sep 2013 14:20:28 +0400
> > Vano Beridze <vano.beridze at silkroad.ge> wrote:
> >> The script works fine. it connects and gets the data. The only
> >> problem I have is that data is shown as question marks, so it's not
> >> converted to UTF-8. I've read all the information I could find on
> >> the internet but can not solve the problem.
> > I would set TDSDUMP and examine the log. You're right that
> > FreeTDS would substitute question marks for any non-convertible
> > character. Perhaps the character set name you specified in
> > freetds.conf isn't exactly right, perhaps because you're linked to
> > the system iconv library.
> > The status of iconv is listed at the top of the log. If it says it
> > set the client charset to UTF-8, that's what it will use.
> > A different problem that sometimes crops up: sometimes the "Unicode"
> > data on the server isn't UCS2. In that event, the server will send
> > the data, but the iconv library will be unable to convert the
> > characters correctly.
> > --jkl
More information about the FreeTDS