[freetds] Character conversion issues

James K. Lowden jklowden at schemamania.org
Thu Oct 28 22:36:23 EDT 2004


Chuck Bearden <cfbearden at gmail.com> wrote:
> I am using freetds-0.63.dev.20041026 with libiodbc-3.51.2 and mx.ODBC
> 2.0.6 on Red Hat Linux of some recent flavor.
> 
> I have a lot of data (including many non-English names) in a
> PostgreSQL database, encoded as UTF-8.  I want to insert this data
> into an MS SQL Server, but I am giving myself headaches over this
> problem.  I am not sure what combination of freetds.conf settings and
> input format for the mx.ODBC/MS SQL statements to use.
> 
> I can pass in UTF-8 data to mx.ODBC/MS SQL, or I can convert to UCS-2
> (or UCS-2LE) before passing it in.  What should I set my client
> charset to, and what encoding should I pass in?

Hi Chuck, 

UTF-8 is your best bet.  

> [IfSH_Server7]
>         host = mssql.university.edu
>         port = 1433
>         tds version = 7.0
>         client charset = UTF-8

Fine.  

> Trying to insert the UTF-8 data from PostgreSQL unconverted into MS
> SQL yields this exception:
> 
> mxODBC.OperationalError: ('S1000', 2402, "[FreeTDS][SQL Server]Error
> converting client characters into server's character set. Some
> character(s) could not be converted.", 4579)

Your TDSDUMP log will indicate what the server's default single-byte
encoding is.  I bet you're trying to insert these names into a
char/varchar column, and that the server's encoding can't handle it. 
Either that, or you found a bug.  

First test is to use nchar/nvarchar for your column definition.  That will
take care of the server end.  

If that doesn't work, or if the single-byte encoding should work, then
we'll need a TDSDUMP log.  There's still enough detail in the log left
over from when we were struggling with iconv that we should be able to see
what the matter is.  

> I can provide a TDSDUMP log of this if it would be helpful.

You guessed it.  ;-)

> If I change the client charset to UCS-2LE, I cannot even connect to MS
> SQL:

Sad but true.  There's not a big push to handle UCS-2 clients.  

--jkl



More information about the FreeTDS mailing list