Why does PDO_ODBC fail to retrieve UTF-8 accented characters from Access?

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');
?>


<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Access character test</title>


<?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";
}
?>

</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');
?>


<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Access character test</title>


<?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();
?>

</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!