PDO_ODBC 的字符编码问题

2021-12-29 00:00:00 odbc php pdo drupal

使用 PDO_ODBC 和以下代码从 PHP 访问 Microsoft SQL 数据库时,我遇到了编码问题.输出时,数据库中的文本是乱码.

When accessing a Microsoft SQL Database from PHP using PDO_ODBC with the following code, I have an encoding issue. When outputed the text from the DB is garbled.

$dsn = "odbc:DRIVER={SQL Server};SERVER=$hostname;DATABASE=$database;charset=UTF-8";
$pdo = new PDO($dsn,$username,$password);
$sql = "SELECT text FROM atable";
$result = $PDO->query($sql);
while($data = $result->fetchObject()){
  $values[] = $data->text; 
}
dpm($values);


(来源:bayimg.com)

这是从 Drupal 模块完成的.Drupal 中的所有内容都使用 UTF-8.最干净的解决方案是能够以 UTF-8 格式从数据库中检索数据,或者在输出之前将其转换为 UTF-8.

This is done from a Drupal module. Everything in Drupal is made to work with UTF-8. The cleanest solution would be to able to retrieve the data from the database in UTF-8 or to convert it UTF-8 before outputting.

我试过这些都没有成功

  • $dsn = "odbc:DRIVER={SQL Server};SERVER=$hostname;DATABASE=$database;client_charset=utf-8"
  • $dsn = "odbc:DRIVER={SQL Server};SERVER=$hostname;DATABASE=$database;charset=utf-8"
  • $pdo->exec('SET NAMES utf8') new PDO(...)
  • $pdo->exec('SET CHARACTER SET utf8'); new PDO(...)

PS:代码目前是在 Windows 上开发的,但它也必须在 GNU/Linux 上运行.

PS: The code is currently developped on Windows but it has to work on GNU/Linux too.

推荐答案

在 Linux 上运行并使用 FreeTDS 驱动程序时,可以使用 client charset 中的client charset 设置来配置客户端的字符集一个 href="http://www.freetds.org/userguide/freetdsconf.htm" rel="noreferrer">freetds.conf 文件.为了在使用 PDO ODBC 和 unixODBC 时使用 freetds.conf 文件,需要使用 ODBC 组合配置.当ODBC-only configuration用于配置ODBC数据源时,文件freetds.conf 未使用.有了这个,我就能够从/向 MS SQL Server 数据库中检索和插入 UTF-8 数据.

When running on Linux and using the FreeTDS driver, the charset for the client can be configured with the client charset setting in the freetds.conf file. In order for the freetds.conf file to be used when using PDO ODBC and unixODBC, one needs to configure the ODBC datasource using ODBC-combined configuration. When ODBC-only configuration is used to configure the ODBC datasource, the file freetds.conf is not used. With this I was able to retrieve and insert UTF-8 data from/into the MS SQL Server database.

作为 Linux/Unix 人员,我无法理解/找到如何配置在 Windows 上使用 PDO ODBC 时使用的字符集的方法.我模糊的理解是,在系统级别配置时,可以将 ODBC 数据源配置为使用 SQL Server 数据库的字符集或转换为客户端计算机字符集.

Being a Linux/Unix guy, I was unable to understand/find a way how to configure the charset used when PDO ODBC is used on Windows. My vague understanding is that when configured at the system level, an ODBC datasource can be configured to use either de SQL Server database's charset or convert to the client computer charset.

相关文章