Home > Article > Backend Development > Why does PDO_ODBC fail to retrieve UTF-8 accented characters from Access?
Why does PDO_ODBC fail to retrieve UTF-8 accented characters from Access?
- Barbara StreisandOriginal
- 2024-10-20 17:52:02789browse
PDO_ODBC failed to retrieve Access UTF-8 accented characters
Problem Description
Try to use Access Converting the database to MySQL, everything works fine, but I run into a major problem: if the Access database contains any non-standard characters, the conversion fails. The query returns the following error:
Incorrect string value: '\xE9d'
When directly outputting a line of text with "invalid" characters, the browser displays a question mark in a black square (so the é becomes this invalid symbol).
Note: The same form works fine to accept, save and display the "é" in the text box for the title of this database upload. Also, if I "save as" the page and reopen it, the "é" displays correctly.
Here's how to connect:
$conn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=$fileLocation;SystemDB=$securefilePath;Uid=developer;Pwd=pass;charset=utf;");
Have tried multiple methods including:
$conn -> exec("set names utf8");
When trying "CurrentDb.CollatingOrder" in Access, it gets It shows 1033, apparently this is dbSortGeneral for "English, German, French and Portuguese sorting rules".
What went wrong? It feels like PDO is sending me a proofread that my browser and PHP don't fully understand.
Problem Solved
1. Simple but incomplete fix
In fact, the text returned by Access ODBC is different from Windows This character matches the -1252 character encoding, even though it is stored in the Access database as a Unicode character. So for the example table "Teams", the
Team ----------------------- Boston Bruins Canadiens de Montréal Федерация хоккея России
code
<code class="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></code>
will be displayed in the browser
Boston Bruins Canadiens de Montr�al ????????? ?????? ??????
2. Complete Fix
To get full UTF-8 support we need to use COM with an ADODB connection and a recordset object like this:
<code class="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></code>The above is the detailed content of Why does PDO_ODBC fail to retrieve UTF-8 accented characters from Access?. For more information, please follow other related articles on the PHP Chinese website!