无法通过PDO_ODBC从Access中检索带有UTF-8重音符号的字符 [英] Unable to retrieve UTF-8 accented characters from Access via PDO_ODBC
问题描述
我正在尝试将Access DB转换为MySQL.一切工作正常，请期待一把大猴子扳手...如果access db有任何非标准字符，则它将无法正常工作.我的查询会告诉我:
I am trying to get an Access DB converted into MySQL. Everything works perfectly, expect for one big monkey wrench... If the access db has any non standard characters, it wont work. My query will tell me:
Incorrect string value: '\xE9d'
如果我直接回显具有无效"字符的行文本，则会在浏览器中的黑色方框中出现一个问号(因此é在回显时会变成无效的符号).
If I directly echo out the rows text that has the 'invalid' character I get a question mark in a black square in my browser (so é would turn into that invalid symbal on echo).
注意:相同的内容将接受，保存并在用于为该数据库上载命名的文本框中显示é"罚款.另外，如果我另存为"页面并重新打开它，则正确显示é"....
NOTE: That same from will accept, save and display the "é" fine in a textbox that is used to title this db upload. Also if I 'save as' the page and re-open it up the 'é' is displayed correctly....
这是我的联系方式:
$conn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=$fileLocation;SystemDB=$securefilePath;Uid=developer;Pwd=pass;charset=utf;");
我尝试了很多事情，包括:
I have tried numerous things, including:
$conn -> exec("set names utf8");
当我尝试在访问中使用"CurrentDb.CollatingOrder"时，它显然告诉我1033是英语，德语，法语和葡萄牙语整理顺序"的dbSortGeneral.
When I try a 'CurrentDb.CollatingOrder' in access it tells me 1033 apparently that is dbSortGeneral for "English, German, French, and Portuguese collating order".
怎么了?几乎就像PDO向我发送了我的浏览器排序规则，而PHP无法完全理解.
What is wrong? It is almost like the PDO is sending me a collation my browser and PHP does not fully understand.
推荐答案
问题
在使用本机PHP ODBC功能(PDO_ODBC或更旧的odbc_函数)和Access ODBC驱动程序时，即使文本以Unicode字符存储在Access数据库中，文本也不是UTF-8编码的.因此，对于一个名为"Teams"的示例表
The Problem
When using native PHP ODBC features (PDO_ODBC or the older odbc_ functions) and the Access ODBC driver, text is not UTF-8 encoded, even though it is stored in the Access database as Unicode characters. So, for a sample table named "Teams"
Team
-----------------------
Boston Bruins
Canadiens de Montréal
Федерация хоккея России
代码
<?php
header('Content-Type: text/html; charset=utf-8');
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Access character test</title>
</head>
<body>
<?php
$connStr =
'odbc:' .
'Driver={Microsoft Access Driver (*.mdb)};' .
'Dbq=C:\\Users\\Public\\__SO\\28311687.mdb;' .
'Uid=Admin;';
$db = new PDO($connStr);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT Team FROM Teams";
foreach ($db->query($sql) as $row) {
$s = $row["Team"];
echo $s . "<br/>\n";
}
?>
</body>
</html>
在浏览器中显示
Boston Bruins
Canadiens de Montr�al
????????? ?????? ??????
简单但不完整的修复程序
Access ODBC返回的文本实际上与 Windows-1252 字符编码相匹配该字符集中的字符，因此只需更改行
The Easy but Incomplete Fixes
The text returned by Access ODBC actually matches the Windows-1252 character encoding for the characters in that character set, so simply changing the line
$s = $row["Team"];
到
$s = utf8_encode($row["Team"]);
将允许第二个条目正确显示
will allow the second entry to be displayed correctly
Boston Bruins
Canadiens de Montréal
????????? ?????? ??????
，但是 utf8_encode()函数会从 ISO-8859-1 ，而不是
but the utf8_encode() function converts from ISO-8859-1, not Windows-1252, so some characters (notably the Euro symbol '€') will disappear. A better solution would be to use
$s = mb_convert_encoding($row["Team"], "UTF-8", "Windows-1252");
但这仍然无法解决示例表中第三个条目的问题.
but that still wouldn't solve the problem with the third entry in our sample table.
要获得完整的UTF-8支持，我们需要对ADODB使用 COM 像这样的Connection和Recordset对象
For full UTF-8 support we need to use COM with ADODB Connection and Recordset objects like so
<?php
header('Content-Type: text/html; charset=utf-8');
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Access character test</title>
</head>
<body>
<?php
$connStr =
'Driver={Microsoft Access Driver (*.mdb)};' .
'Dbq=C:\\Users\\Public\\__SO\\28311687.mdb';
$con = new COM("ADODB.Connection", NULL, CP_UTF8); // specify UTF-8 code page
$con->Open($connStr);
$rst = new COM("ADODB.Recordset");
$sql = "SELECT Team FROM Teams";
$rst->Open($sql, $con, 3, 3); // adOpenStatic, adLockOptimistic
while (!$rst->EOF) {
$s = $rst->Fields("Team");
echo $s . "<br/>\n";
$rst->MoveNext;
}
$rst->Close();
$con->Close();
?>
</body>
</html>
这篇关于无法通过PDO_ODBC从Access中检索带有UTF-8重音符号的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
