Why does PDO_ODBC fail to retrieve UTF-8 accented characters from Access?-PHP Tutorial-php.cn

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

Barbara Streisand

Release： 2024-10-20 17:52:02

Original

787 people have browsed it

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'

Copy after login

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;");

Copy after login

Have tried multiple methods including:

$conn -> exec("set names utf8");

Copy after login

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
Федерация хоккея России

Copy after login

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>

Copy after login

will be displayed in the browser

Boston Bruins
Canadiens de Montr�al
????????? ?????? ??????

Copy after login

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>

Copy after login

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!