Php / ODBC Encoding Problem

Question

I am using ODBC to connect to SQL Server with PHP. In PHP, I read the string data (nvarchar) from SQL Server, and then I want to insert it into the mysql database. When I try to insert such a value into the mysql database table, I get this mysql error:

Incorrect string value: '\xB3\xB9ow...' for column 'name' at row 1 

For a string with all ASCII characters everything is in order, the problem occurs when there are characters other than ASCII (from some European languages).

Thus, in more general terms: there is a Unicode string in the MS SQL Server database that is retrieved by the ODBC PHP trough. Then it is placed in the sql insert query (as the value for the varchar utf-8 column), which is executed for the mysql database.

Can someone explain to me what is happening in this situation in terms of coding? At what stage can character encodings be converted?

I use: PHP 5.2.5, MySQL5.0.45-community-nt, MS Sql Server 2005.

PHP must run on the Linux platform.

UPDATE: An error does not occur when I call utf8_encode ($ s) on this line and use this value in the mysql insert request, but then the inserted line does not display correctly in mysql (so utf8 encoding worked only to ensure the correct utf8 line, but she was losing the right characters).

Answer 1

First you have a DB encoding. Then you have the encoding used by the ODBC client.

If the encoding of your ODBC client connection does not match one of the databases, in some cases the ODBC level will automatically transcode your data.

The trick here is to force the coding of the ODBC client connection.

To configure "total UTF-8":

 $conn=odbc_connect(DB_DSN,DB_USR,DB_PWD); odbc_exec($conn, "SET NAMES 'UTF8'"); odbc_exec($conn, "SET client_encoding='UTF-8'"); // processing here 

This works great with PostgreSQL + Php 5.x. The syntax and exact parameters depend on the database provider.

You can find very useful and understandable additional information for MySql here: http://dev.mysql.com/doc/refman/5.0/fr/charset-connection.html

hope this helps.

Answer 2

Perhaps you can use the PDO extension if that makes any difference?

The user added a comment here , which suggests changing the data types in the sql server to something else, if this is not possible, look at the user class that creates the fields.

Answer 3

I have no experience working with ODBC through PHP, but with the mysql functions, apparently the default PHP for ASCII and UTF8 connections needs to be made explicit if you want to avoid problems.

Are you sure PHP and MySQL server communicate in UTF8? As long as PHP 6, Unicode support is generally not annoying.

I remember that in MySQL docs, the connection string parameter for setting Unicode encoding is mentioned.

From your description, it looks like PHP only treats a connection as ASCII.