Perl TDS character sets

Question

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.

Somebody please move this back to StackOverflow.com, since it is a question about Perl programming. — skiphoppy, Jun 07 '10 at 21:51

score 3 · Answer 1 · answered May 05 '10 at 00:01

3

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

answered May 05 '10 at 00:01

Cool; that obviously did something. But now I get this error: Out of memory! What in the world is it doing to try that conversion? – skiphoppy May 05 '10 at 14:39
I would be surprised if you are running out of memory doing the actual conversion. I'd look for memory leaks/oversize queries elsewhere first. Your freetds installation should include a console client program called tsql. Try some queries to see if the problem still lies in freetds. – May 06 '10 at 01:00
The query works fine from tsql. I don't know enough about FreeTDS to know what is different. If I write a minimal Perl program that executes just this query, it fails. The query only gets about 10 records. I can't imagine that it's running out of memory. If I get 10 different records, without curly quotes and such, it works just fine. – skiphoppy May 06 '10 at 15:15
Googling around suggests that this is definitely the correct fix. Maybe I should start another question: "How come I get this out of memory error when I change the client charset?" – skiphoppy May 06 '10 at 15:43

score 2 · Answer 2 · answered May 04 '10 at 23:47

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}]}

Looks like that's only for Sybase. I get: DBD::Sybase::st execute failed: Server message number=195 severity=15 state=5 line=1 server=XXX text='char_convert' is not a recognized SET option. I appreciate it, though. — skiphoppy, May 05 '10 at 14:37

Perl TDS character sets

2 Answers2