0

I am currently stuck at a problem in our project where I read text out of the Database, modify it somewhat and then save it back again. The data is saved in an ntext-field (and is actually an XML-document). Ther error I'm receiving is:

Zend_Db_Statement_Exception: SQLSTATE[HY000]: General error: 2402 [FreeTDS][SQL Server]Error converting characters into server's character set. Some character(s) could not be converted (SQLExecute[2402] at /build/php5-F6w79J/php5-5.5.9+dfsg/ext/pdo_odbc/odbc_stmt.c:254)

We are working with:

  • PHP 5.5.9-1ubuntu4
  • Freetds-Version 7.2 (and client charset = UTF-8)
  • Microsoft SQL Server 2012

I suspect the error has something to do with the encoding of the data. It works quite fine with most of the database-entries, but with some it throws the aforementioned error. There seems to be some characters whose encoding gets mixed up somewhere on their way from the database to php and back again. If the problematic XML is saved to a file and opened there is an error which says that the XML is not well-formed (although if I copy the xml directly from the database into an xml-validator everything is alright). We already tried filtering out any controll-characters that might be problematic and also looked this error up - but so far no answer really worked for me. Has anyone an idea where this error might originate? It would also help if I could determine which characters are causing this problem, maybe I could filter them out before writing the data back to the database.

If any additional information is required I'll gladly be of help.

Greetings,

Sajus

Sajus
  • 1
  • 1
  • 3
  • Have you tried this? https://stackoverflow.com/a/4298226/1461181 – odan Jul 10 '17 at 12:07
  • Thank you very much. The problem seems to be whether "client charset" is written together or without whitespaces in the freetds.conf - although the way I read the freetds-Doku it should be written WITH whitespaces. I'll run a few more tests tomorrow and post the hopefully successfull results here. – Sajus Jul 11 '17 at 15:10
  • Just to update this question: Unfortunately changing the freetds.conf also didn't help - when I wrote "clientcharset = UTF-8" (without whitespaces) the error didn't occur - but all my textentries in the row I edited with php were full of encoding errrors - i.e. there were a lot of question marks that didn't belong there in the texts which I had to remove manually (fortunately I only tried this on two rows). So I ended up ignoring the error for now in the hope that it doesn't occur too often. :/ – Sajus Jul 18 '17 at 06:58
  • 1
    Maybe your local settings are still ISO and not UTF-8. Please check your encoding settings (IDE, PHP file, HTML files etc...). https://stackoverflow.com/a/5445327/1461181 – odan Jul 18 '17 at 10:24
  • what is the db encoding? table encoding? column encoding? – delboy1978uk Dec 12 '19 at 14:11

1 Answers1

1

Check that you don't have any non-ascii characters in your code.

I got this error, and when I looked carefully I found that I had:

update TABLE set SOMETHING = null where IDENTIFIER = ‘VALUE’

When I changed the

‘VALUE’

to

'VALUE'

i.e. I used vanilla ASCII straight single quotes, instead of the 6 and 9 quotes... the problem went away.

JohnGH
  • 833
  • 8
  • 11