Home / server / Questions / 141088
In Process
skiphoppy
Asked: 2010-05-05 13:24:40 +0800 CST

Perl TDS character sets

  • 772

I'm using the FreeTDS driver with DBD::Sybase, connecting to an MS SQL Server. When I query certain values of certain records, I get this error:

DBD::Sybase::st fetchrow_arrayref failed: OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (9) NUMBER = (99)
Server , database 
Message String: WARNING! Some character(s) could not be converted into client's character set. Unconverted bytes were changed to question marks ('?').

This seems to happen for records that contain special Windows character-set characters, such as curly quotes, copied and pasted from people's Outlook and Word messages.

Unfortunately, I do not have any control of this database; sanitizing the input on the way in is obviously the way to go, but is not available to me.

What FreeTDS settings do I need to change to be able to successfully query these records?

Additional information:

The query works fine from tsql. I only get this error through Perl's DBD::Sybase interface. (Should I test through something else? I don't have the expertise yet to install PHP or Python. I've got jTDS and can use it, but I think that's a completely different implementation, not an interface to FreeTDS.)

Adding

client charset = UTF-8

to my freetds.conf file results in "Out of memory!" printed to STDERR.

sql-server perl freetds dbi

2 Answers

  1. I had a similar problem recently.

    In your freetds.conf file you'll want an entry similar to the following:

    [DataSourceName]
        host = <IP Address>
        port = <databaseport>
        #version 8.0 seems to work  with sql server 2005
        tds version = 8.0
        client charset = UTF-8

    client charset is the important bit here.

    I was doing this with Perl from Linux, I expect UTF-8 is the best choice here as well, given Perl is the Client

    • 3

  2. Check if this helps (from Sybase Manuals, but may work on MSSQL server as well):

    Controlling Character Conversion During a Session

    set char_convert allows the user to decide how character set conversion operates during a particular work session. Use set char_convert to:

    * Set character set conversion on or off

* Start conversion into a specific character set

* Turn error reporting on or off

    The syntax for set char_convert is:

    set char_convert {off |
    {on [with {error | no_error}]} | charset [with {error | no_error}]}

    • 2