FreeTDS Development Group

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

Murray, Laura (US) wrote:
> Our OS is Solaris 10.
> Encoding within the Oracle database is UTF-8
> Encoding of MS SQL Server is: SQL_Latin1_General_CP1_CI_AS

http://msdn.microsoft.com/en-us/library/aa258233(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/aa176552(SQL.80).aspx

SQL_Latin1_General_CP1_CI_AS is Sort Order ID 52, "Dictionary order,
case-sensitive, for use with 1252 character set".  That is, your
char/varchar/text columns are meant to hold Roman characters, not
Japanese. 

Consistent with an earlier part of this thread, string constants in SQL
statements can be munged unless they're prefixed with "N" (N'something').
Unprefixed strings are first "converted" to the single-byte encoding (the
"collation") and then stored either in that encoding or as UCS-2,
depending on the column type.  

> However, within the Oracle Database (hosted by this same Solaris system)
> the Japanese characters appear properly.

That tells me that the data are stored correctly in the Oracle database
and that the Oracle tool (& environment) is set up correctly.  

> The Japanese characters do not
> display at all in the Solaris environment (i.e. in a text file on the
> server).  I xferred a small file from my laptop (where the Japanese
> characters appeared properly) to Solaris and on Solaris the Japanese
> characters are garbled.

You're talking about machines and "displaying properly".  You need to be
thinking about encodings and bit patterns and tools.  The fact that you
can't display a that small file suggests you need to understand why that
doesn't work.  

On your (Win32?) laptop you created a (UCS-2?) file.  Then you transferred
the file to the Solaris host.  You could use od(1) or perhaps hexdump(1)
to show the bytes; from that we could understand the encoding.  Also I
would hope file(1) would tell you.  

Then you tried to display it.  With cat(1) in an xterm?  If, as I suspect,
you're trying to display a UCS-2 file in an xterm, I would expect it to
fail. Is the terminal capable of displaying UCS-2 Little Endian? 
(Unlikely.)   What are your locale(1) settings in your Solaris
environment?  Can it display UTF-8?  (That's -u8 in XFree86; I don't know
about Solaris's xterm.) Is the right font installed?  

I'm not well positioned to help you with all that.  I understand the
issues, but I'm on the other end of an email list and I've never used
Solaris.  You really need someone local who can answer the questions I'm
posing and arrange things such that you can display your test file.  

Once you establish the file's encoding and the terminal's capability, you
might try displaying the file using iconv(1).  If you've got everything
right -- encoding, terminal capability, font, locale -- you should see
your characters.  

Similarly, if you have known good data in your Microsoft server -- you can
read the Japanese using Microsoft's tools -- then using the knowledge
gleaned from the above testing, you should be able to use tsql to display
the same data in your Solaris environment.  Then you'll know you have
FreeTDS correctly configured for dealing with Japanese text on that
machine.  Using tsql, you'll also be able to prove to yourself that INSERT
statements containing unprefixed string constants will not work with a
server that uses SQL_Latin1_General_CP1_CI_AS.  

Finally, and only after you can demonstrate the foregoing to yourself,
you'll need a TDSDUMP log.  That will show what your gateway is doing. 
You'll see the data packets being sent to the server, and the encodings
being used.  At that point it should be easy to know at least what's
happening.  Whether or not anything can be done is another question.  

HTH.  

--jkl